Reference not found using SQL Compact Edition

I'm currently programming a small application for Windows Mobile 5.0 and .NET Compact Frameword 2.0 including SQL Server Compact Edition.

The following code unfortunately does not for compiling.

C#-Code:

38 SqlCeEngine engine = new SqlCeEngine("Data Source = 'test.sdf'");
39 engine.CreateDatabase();
40
41 System.Data.SqlServerCe.SqlCeConnection ssceconn = new SqlCeConnection("Data Source = 'test.sdf'");
42 ssceconn.Open();

There's no more code inside the project except the standard code für a blank Form. I'm getting the following error when i try to compile:

Zeile 41: Der Typ System.Data.Common.DbConnection ist in einer Assembly, auf die nicht verwiesen wird, definiert. Fügen Sie einen Verweis zur Assembly System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 hinzu. (CS0012)

For all those who don't understand german: The Type System.Data.Common.DBConnection is in an assembly for which there isn't a reference defined. Please add a reference for System.Data, Version 2.0.0.0, Culture=....

I already have a reference on System.Data inside my project, and the version, culture and the publickeytoken are the same as required by the compiler... I'm getting really hopeless, does anybody have an idea? Thanks a lot in advance!!

By the way: My system: .NET Framework 1.1, 2.0 and 3.0 as well as .NET Compact Framework 2.0 and .NET Framework SDK v2.0. Also there is MS SQL Server Compact Edition 3.1 and SQL Server CE 3.1 SDK installed (all in german). My OS is Windows XP SP2, all updates are done and I'm using SharpDevelop 2.1.0.

You need to add a reference to the SQLCe engine (system.data.SqlServerCe) in your project. If you create a new Data Connection in the Server Explorer in Visual Studio, and use it to build a Data Source, the path of the needed DLL will be added to your project. On my system it's located at C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\System.Data.SqlServerCe.dll.

I discuss the details of this in my EBook.

hth

|||

Thanks a lot for your answer!

I found the "error" myself, or at least a solution for this problem. It was not because of the missing reference to the system.data.SqlServerCe, because I already added this reference before and compiling didn't work. I just removed all the references from my project and added them manually, referencing not the files in the GAC but selecting them from the .net framework sdk-folder/compact framework (As I can rememember it is something like: C:\Programme\Microsoft .NET\.Net Framework SDK\Compact Framework\....). Don't ask me why it works this way, but i'm able to compile now and everything works fine. Is it possible that the versions of the referenced assemblies in the GAC are not for the compact framework?

Refactoring Database...How Much is Too Much?

I have a new database that I am designing. I come from a programming background, so as far as I am concerned nothing should ever be repeated, and if it is, refactor it.

I am getting to the point where I am not sure if I am refactoring too much. For instance I have a contact table which contains a first_name, and a last_name. This also shows up in a salesman table that I have. So I refactored it to a name table:

Name:
id
first_name
last_name

Contact:
id
name_id
...

Salesman:
id
name_id
...

I am going to far with my refactoring, or am I still on the right track? I figured I would ask this early before I get waist deep in refactoring something that needs to not be refactored.

Thanks in advance

I do not see the need to create the Name table. Contact table should be it. You can then reference the Contact.ContactID in the SaleMan table (if needed).

Here is a very good reference to schema design. You should be able to pick out one that best fits your need.

http://www.databaseanswers.org/data_models/index.htm

|||

I commonly think of tables as "like" information. For example, if you're going to be dealing with human beings, then perhpas a "People" table...this might be clients, or employees or relatives, suppliers...but they're all "people" with attributes (perhaps) like:

PeopleID bigint IDENTITY(1,1)

Prefix (Mr. Ms. Dr. ) (or this could be Prefix ID and you might have a separate PeoplePrefix table)

FirstName

MiddleName

LastName

GoesBy

Gender

Birthday

Phone

Email

Type

(for "Type" if any one person might have two roles in your organization, then maybe use Binary(16) or something where each of the 16 bits represents a different Role).

Then you PeopleID = 1 might be a Sales Person and People ID 15 might be a customer.

Abstracting the data is good, to a point. But too far can become quite cumbersome.

THis is purely opinion...actual mileage may vary.

|||

The word for this in SQL is "normalization." The process of normalization (more or less) seeks to equate one table with one "thing". A big way to notice commonality that might represent a common "thing" is just like you have, the same columns showing up in multiple tables. But, you would not want to normalize just to only have the same column names in a table together. The question is, do they represent a "thing" and is it the same thing?

You can find commonality in many places. For example, a car and a television set both have a color, and a model number, manufacturer, etc. But it is very unlikely they would ever show up in the same table, as there is little reason to inventory television sets with automobiles. An extreme example, perhaps, but the point is that you need to normalize to make things easier to maintain, not just to be doing it.

In your case, what you have really identified is that a Contact is a person with a name, and a Salesman is too. From that angle, you might just want to have a table called Person, with more than a first_name and last_name, more than likely you need some form of identification, like a social security number, or employee id....

Yet, this is where the paradigm starts to break down. A contact is a person, and a salesman is too. But is there any reason to model them as one table? Possibly not. The key comes down to whether or not you have any reason to do any common task with a contact and a salesman.

Like if you needed a list of persons in your system, you might have a person table, then a contactPerson and a salesPerson table for the non-common values. This would be especially true if you cared if a person was a contact AND a salesPerson.

On the other hand, if they are completely seperate concepts, so much so that their data didn't make sense being in the same table, then it is doesn't make logical sense to have a person table, at least not in this situation.