How do you work with SQL Server CE and SQL Server 2008 Express?

Question

I have installed:

• Visual C# 2008 Express
• Visual Web Developer 2008 Express
• Visual Studio 2008
• SQL Server 2008 Express
• SQL Server Management Studio

In Visual C# 2008 Express, I can "Add Item" and create a "Local Database" (.sdf file) or a "Service Based Database" (.mdf file).

Visual Studio 2008 is the same as Visual C# 2008 for applications but in Websites, the only way I can create .sdf or .mdf files is via the Database Explorer.

In Visual Web Developer 2008 Express, however, I can "Add Item" and ONLY create a "SQL Server Database" (.mdf file). Even the Database Explorer doesn't offer to create a SQL CE database.

Questions:

• why the discrepency between the applications, is this normal or did something go wrong with my installations?
• I never "installed SQL CE", where did it come from and how is it that I am able to create .sdf file?
• How do you work with these in projects on a day to day basis, do you use Visual Studio to edit the databases or do you use SQL Server Management Studio?

Answer 1

The discrepancy is odd, but since SQL CE is a plugged-in item, it may well be that the team didn't test every possible scenario and some of the applications simply didn't get the info they needed to know that it's there. I'm not saying that anything went wrong - it may simply be a scenario that the SQL CE team never tested.

SQL CE was installed with Studio (can't recall offhand, but I think 3.1 shipped with the RTM and 3.5 shipped with SP1).

I always use Management Studio for making database modifications (except those done directly in code).

Answer 2

This is normal. The database types supported by the Visual Studio designer differ by edition. Note that any version of Visual Studio can write and use code that connects to any of those databases. It's just that the visual "wizard" tools included with the IDE that walk you through it are different for each edition.

SQL CE is the Compact Edition of sql server. It's intended for use on portable devices like smart phones or as an Access replacement for a local data store in desktop apps. It's very portable: only 2 dlls that clock in at less than 1Mb of disk space, and was probably included with at least one of your Visual Studio packages, probably all of them.

I normally do all my database work from Management Studio, but that's a personal preference.

Answer 3

The key point in why you can't access CE from Web express is that CE is not supported on the web server.

Douglas

Answer 4

I believe only Professional and above may create MSSQL CE databases.

SQL CE is installed with 2008 studio, I was surprised to see it myself and didn't see an option to exclude it.

Personally I use VS 2008 to add items to my project databases. Since it's open anyway I just use it. It provides most of the same functionality as Management Studio. If I am doing separate database work, I usually do it in Management studio.

The CE databases are pretty useless. They have a limited sub-set of types and functions. They do not support views or stored procedures. Basically they only store tables; I haven't attempted to create foreign keys in them.

There is no way to rename tables or columns. You must create a new table.

Once you create your database it must be manually added to your project.

You cannot directly create a LINQ to SQL class in VS2008 on a CE database. You need to use the command line tool sqlmetal (called from .NET console) to create the class for you.

I've had issues where the Class created for a CE database didn't work like a class built for a regular SQL database. Because of this I abandoned using a CE database all together.

It would be nice if there was a reliable way to use Linq to SQL on SQLite databases. SQLite is a great self-contained database not requiring any type of external engine.

Sorry I don't have any performance statistics for an SQL CE database.

I hope this is somewhat helpful.

Brett

Answer 5

Ok registered now and not sure why got a -1 vote on previous answer but will expand.

Web Developer Express 2008 Does not create or work with Sql Server CE simply because SQL Server CE is not designed to work on the Web Server.

When you load c# express or vb express. sql server express 2008 is offered to be installed. SQL CE isn't installed. if you go and add a data source the two I know will be there, Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5)and Microsoft SQL Server Database File (SqlClient), the access client may be there also.

It should be noted with a default Express setup that if you choose to use the data provider for sql server compact it will fail even though it is an option. you would have to download sql server ce separately to create sdf databases.

Also Express versions are targeted to a different audience and doesn't have the same ui setup as the main vs sku's have. For example Web express in database setup doesn't even offer the choice to use sql server ce. while in VS it is a choice from the data menu mainly because VS supports more than just web developer.

Now you said you loaded VS 2008 (didn't say what version but doesn't matter since this affects standard and above)

The default install for vs 2008 will install Sql server ce (and as previous answer stated 3.1 was with rtm, 3.5 was with sp1). you can turn it off in advance options.

One concern I have is that you mention that you can't create a database in vs on web projects. which template are you using. You should be able to create server based database Items in add item and both local and server database objects in the data tab.

so in Summery.

on client based express skus and client based projects you should be able to create a local or server based database from add item and from the data tab.

Since sql CE is not officially supported on the Web Server. on the web skus in add Item you should only have access to create a server based database. on express since it is a dedicated sku CE isn't an option in either the data tab or the add item. on vs you can create a CE database from the Data tab. and you should have access to create a server database in add item.

sql server management studio doesn't care where the source is. so it will work with all and create the database.

I use all the options listed just depending on what or where I am at the time. as was mentioned CE on the desktop is primarily a cache database perfect for offline database support. but does have its limitations and in addition really needs better integration. once I am created or working with a database in support I primarily use management studio.