VS 2010 SP1 and SQL CE

Wednesday, January 12, 2011

.NET ASP.NET Data LINQ MVC Visual Studio

Last month we released the Beta of VS 2010 Service Pack 1 (SP1). You can learn more about the VS 2010 SP1 Beta from Jason Zander’s two blog posts about it, and from Scott Hanselman’s blog post that covers some of the new capabilities enabled with it. You can download and install the VS 2010 SP1 Beta here.

Last week I blogged about the new Visual Studio support for IIS Express that we are adding with VS 2010 SP1. In today’s post I’m going to talk about the new VS 2010 SP1 tooling support for SQL CE, and walkthrough some of the cool scenarios it enables.

SQL CE – What is it and why should you care?

SQL CE is a free, embedded, database engine that enables easy database storage.

No Database Installation Required

SQL CE does not require you to run a setup or install a database server in order to use it. You can simply copy the SQL CE binaries into the \bin directory of your ASP.NET application, and then your web application can use it as a database engine. No setup or extra security permissions are required for it to run. You do not need to have an administrator account on the machine. Just copy your web application onto any server and it will work. This is true even of medium-trust applications running in a web hosting environment.

SQL CE runs in-memory within your ASP.NET application and will start-up when you first access a SQL CE database, and will automatically shutdown when your application is unloaded. SQL CE databases are stored as files that live within the \App_Data folder of your ASP.NET Applications.

Works with Existing Data APIs

SQL CE 4 works with existing .NET-based data APIs, and supports a SQL Server compatible query syntax. This means you can use existing data APIs like ADO.NET, as well as use higher-level ORMs like Entity Framework and NHibernate with SQL CE. This enables you to use the same data programming skills and data APIs you know today.

Supports Development, Testing and Production Scenarios

SQL CE can be used for development scenarios, testing scenarios, and light production usage scenarios. With the SQL CE 4 release we’ve done the engineering work to ensure that SQL CE won’t crash or deadlock when used in a multi-threaded server scenario (like ASP.NET). This is a big change from previous releases of SQL CE – which were designed for client-only scenarios and which explicitly blocked running in web-server environments. Starting with SQL CE 4 you can use it in a web-server as well.

There are no license restrictions with SQL CE. It is also totally free.

Easy Migration to SQL Server

SQL CE is an embedded database – which makes it ideal for development, testing, and light-usage scenarios. For high-volume sites and applications you’ll probably want to migrate your database to use SQL Server Express (which is free), SQL Server or SQL Azure. These servers enable much better scalability, more development features (including features like Stored Procedures – which aren’t supported with SQL CE), as well as more advanced data management capabilities.

We’ll ship migration tools that enable you to optionally take SQL CE databases and easily upgrade them to use SQL Server Express, SQL Server, or SQL Azure. You will not need to change your code when upgrading a SQL CE database to SQL Server or SQL Azure. Our goal is to enable you to be able to simply change the database connection string in your web.config file and have your application just work.

New Tooling Support for SQL CE in VS 2010 SP1

VS 2010 SP1 includes much improved tooling support for SQL CE, and adds support for using SQL CE within ASP.NET projects for the first time. With VS 2010 SP1 you can now:

Create new SQL CE Databases
Edit and Modify SQL CE Database Schema and Indexes
Populate SQL CE Databases within Data
Use the Entity Framework (EF) designer to create model layers against SQL CE databases
Use EF Code First to define model layers in code, then create a SQL CE database from them, and optionally edit the DB with VS
Deploy SQL CE databases to remote servers using Web Deploy and optionally convert them to full SQL Server databases

You can take advantage of all of the above features from within both ASP.NET Web Forms and ASP.NET MVC based projects.

Download

You can enable SQL CE tooling support within VS 2010 by first installing VS 2010 SP1 (beta).

Once SP1 is installed, you’ll also then need to install the SQL CE Tools for Visual Studio download. This is a separate download that enables the SQL CE tooling support for VS 2010 SP1.

Walkthrough of Two Scenarios

In this blog post I’m going to walkthrough how you can take advantage of SQL CE and VS 2010 SP1 using both an ASP.NET Web Forms and an ASP.NET MVC based application. Specifically, we’ll walkthrough:

How to create a SQL CE database using VS 2010 SP1, then use the EF4 visual designers in Visual Studio to construct a model layer from it, and then display and edit the data using an ASP.NET GridView control.

How to use an EF Code First approach to define a model layer using POCO classes and then have EF Code-First “auto-create” a SQL CE database for us based on our model classes. We’ll then look at how we can use the new VS 2010 SP1 support for SQL CE to inspect the database that was created, populate it with data, and later make schema changes to it. We’ll do all this within the context of an ASP.NET MVC based application.

You can follow the two walkthroughs below on your own machine by installing VS 2010 SP1 (beta) and then installing the SQL CE Tools for Visual Studio download (which is a separate download that enables SQL CE tooling support for VS 2010 SP1).

Walkthrough 1: Create a SQL CE Database, Create EF Model Classes, Edit the Data with a GridView

This first walkthrough will demonstrate how to create and define a SQL CE database within an ASP.NET Web Form application. We’ll then build an EF model layer for it and use that model layer to enable data editing scenarios with an <asp:GridView> control.

Step 1: Create a new ASP.NET Web Forms Project

We’ll begin by using the File->New Project menu command within Visual Studio to create a new ASP.NET Web Forms project. We’ll use the “ASP.NET Web Application” project template option so that it has a default UI skin implemented:

Step 2: Create a SQL CE Database

Right click on the “App_Data” folder within the created project and choose the “Add->New Item” menu command:

This will bring up the “Add Item” dialog box. Select the “SQL Server Compact 4.0 Local Database” item (new in VS 2010 SP1) and name the database file to create “Store.sdf”:

Note that SQL CE database files have a .sdf filename extension. Place them within the /App_Data folder of your ASP.NET application to enable easy deployment.

When we clicked the “Add” button above a Store.sdf file was added to our project:

Step 3: Adding a “Products” Table

Double-clicking the “Store.sdf” database file will open it up within the Server Explorer tab. Since it is a new database there are no tables within it:

Right click on the “Tables” icon and choose the “Create Table” menu command to create a new database table. We’ll name the new table “Products” and add 4 columns to it. We’ll mark the first column as a primary key (and make it an identify column so that its value will automatically increment with each new row):

When we click “ok” our new Products table will be created in the SQL CE database.

Step 4: Populate with Data

Once our Products table is created it will show up within the Server Explorer. We can right-click it and choose the “Show Table Data” menu command to edit its data:

Let’s add a few sample rows of data to it:

Step 5: Create an EF Model Layer

We have a SQL CE database with some data in it – let’s now create an EF Model Layer that will provide a way for us to easily query and update data within it.

Let’s right-click on our project and choose the “Add->New Item” menu command. This will bring up the “Add New Item” dialog – select the “ADO.NET Entity Data Model” item within it and name it “Store.edmx”

This will add a new Store.edmx item to our solution explorer and launch a wizard that allows us to quickly create an EF model:

Select the “Generate From Database” option above and click next. Choose to use the Store.sdf SQL CE database we just created and then click next again.

The wizard will then ask you what database objects you want to import into your model. Let’s choose to import the “Products” table we created earlier:

When we click the “Finish” button Visual Studio will open up the EF designer. It will have a Product entity already on it that maps to the “Products” table within our SQL CE database:

The VS 2010 SP1 EF designer works exactly the same with SQL CE as it does already with SQL Server and SQL Express. The Product entity above will be persisted as a class (called “Product”) that we can programmatically work against within our ASP.NET application.

Step 6: Compile the Project

Before using your model layer you’ll need to build your project. Do a Ctrl+Shift+B to compile the project, or use the Build->Build Solution menu command.

Step 7: Create a Page that Uses our EF Model Layer

Let’s now create a simple ASP.NET Web Form that contains a GridView control that we can use to display and edit the our Products data (via the EF Model Layer we just created).

Right-click on the project and choose the Add->New Item command. Select the “Web Form from Master Page” item template, and name the page you create “Products.aspx”. Base the master page on the “Site.Master” template that is in the root of the project.

Add an <h2>Products</h2> heading the new Page, and add an <asp:gridview> control within it:

Then click the “Design” tab to switch into design-view. Select the GridView control, and then click the top-right corner to display the GridView’s “Smart Tasks” UI:

Choose the “New data source…” drop down option above. This will bring up the below dialog which allows you to pick your Data Source type:

Select the “Entity” data source option – which will allow us to easily connect our GridView to the EF model layer we created earlier. This will bring up another dialog that allows us to pick our model layer:

Select the “StoreEntities” option in the dropdown – which is the EF model layer we created earlier. Then click next – which will allow us to pick which entity within it we want to bind to:

Select the “Products” entity in the above dialog – which indicates that we want to bind against the “Product” entity class we defined earlier. Then click the “Enable automatic updates” checkbox to ensure that we can both query and update Products. When you click “Finish” VS will wire-up an <asp:EntityDataSource> to your <asp:GridView> control:

The last two steps we’ll do will be to click the “Enable Editing” checkbox on the Grid (which will cause the Grid to display an “Edit” link on each row) and (optionally) use the Auto Format dialog to pick a UI template for the Grid.

Step 8: Run the Application

Let’s now run our application and browse to the /Products.aspx page that contains our GridView. When we do so we’ll see a Grid UI of the Products within our SQL CE database. Clicking the “Edit” link for any of the rows will allow us to edit their values:

When we click “Update” the GridView will post back the values, persist them through our EF Model Layer, and ultimately save them within our SQL CE database.

Learn More about using EF with ASP.NET Web Forms

Read this tutorial series on the http://asp.net site to learn more about how to use EF with ASP.NET Web Forms.

The tutorial series uses SQL Express as the database – but the nice thing is that all of the same steps/concepts can also now also be done with SQL CE.

Walkthrough 2: Using EF Code-First with SQL CE and ASP.NET MVC 3

We used a database-first approach with the sample above – where we first created the database, and then used the EF designer to create model classes from the database.

In addition to supporting a designer-based development workflow, EF also enables a more code-centric option which we call “code first development”. Code-First Development enables a pretty sweet development workflow. It enables you to:

Define your model objects by simply writing “plain old classes” with no base classes or visual designer required
Use a “convention over configuration” approach that enables database persistence without explicitly configuring anything
Optionally override the convention-based persistence and use a fluent code API to fully customize the persistence mapping
Optionally auto-create a database based on the model classes you define – allowing you to start from code first

I’ve done several blog posts about EF Code First in the past – I really think it is great. The good news is that it also works very well with SQL CE.

The combination of SQL CE, EF Code First, and the new VS tooling support for SQL CE, enables a pretty nice workflow. Below is a simple example of how you can use them to build a simple ASP.NET MVC 3 application.

Step 1: Create a new ASP.NET MVC 3 Project

We’ll begin by using the File->New Project menu command within Visual Studio to create a new ASP.NET MVC 3 project. We’ll use the “Internet Project” template so that it has a default UI skin implemented:

Step 2: Use NuGet to Install EFCodeFirst

Next we’ll use the NuGet package manager (automatically installed by ASP.NET MVC 3) to add the EFCodeFirst library to our project. We’ll use the Package Manager command shell to do this. Bring up the package manager console within Visual Studio by selecting the View->Other Windows->Package Manager Console menu command. Then type:

install-package EFCodeFirst

within the package manager console to download the EFCodeFirst library and have it be added to our project:

When we enter the above command, the EFCodeFirst library will be downloaded and added to our application:

Step 3: Build Some Model Classes

Using a “code first” based development workflow, we will create our model classes first (even before we have a database). We create these model classes by writing code.

For this sample, we will right click on the “Models” folder of our project and add the below three classes to our project:

The “Dinner” and “RSVP” model classes above are “plain old CLR objects” (aka POCO). They do not need to derive from any base classes or implement any interfaces, and the properties they expose are standard .NET data-types. No data persistence attributes or data code has been added to them.

The “NerdDinners” class derives from the DbContext class (which is supplied by EFCodeFirst) and handles the retrieval/persistence of our Dinner and RSVP instances from a database.

Step 4: Listing Dinners

We’ve written all of the code necessary to implement our model layer for this simple project.

Let’s now expose and implement the URL: /Dinners/Upcoming within our project. We’ll use it to list upcoming dinners that happen in the future.

We’ll do this by right-clicking on our “Controllers” folder and select the “Add->Controller” menu command. We’ll name the Controller we want to create “DinnersController”. We’ll then implement an “Upcoming” action method within it that lists upcoming dinners using our model layer above. We will use a LINQ query to retrieve the data and pass it to a View to render with the code below:

We’ll then right-click within our Upcoming method and choose the “Add-View” menu command to create an “Upcoming” view template that displays our dinners. We’ll use the “empty” template option within the “Add View” dialog and write the below view template using Razor:

Step 4: Configure our Project to use a SQL CE Database

We have finished writing all of our code – our last step will be to configure a database connection-string to use.

We will point our NerdDinners model class to a SQL CE database by adding the below <connectionString> to the web.config file at the top of our project:

EF Code First uses a default convention where context classes will look for a connection-string that matches the DbContext class name. Because we created a “NerdDinners” class earlier, we’ve also named our connectionstring “NerdDinners”. Above we are configuring our connection-string to use SQL CE as the database, and telling it that our SQL CE database file will live within the \App_Data directory of our ASP.NET project.

Step 5: Running our Application

Now that we’ve built our application, let’s run it!

We’ll browse to the /Dinners/Upcoming URL – doing so will display an empty list of upcoming dinners:

You might ask – but where did it query to get the dinners from? We didn’t explicitly create a database?!?

One of the cool features that EF Code-First supports is the ability to automatically create a database (based on the schema of our model classes) when the database we point it at doesn’t exist. Above we configured EF Code-First to point at a SQL CE database in the \App_Data\ directory of our project. When we ran our application, EF Code-First saw that the SQL CE database didn’t exist and automatically created it for us.

Step 6: Using VS 2010 SP1 to Explore our newly created SQL CE Database

Click the “Show all Files” icon within the Solution Explorer and you’ll see the “NerdDinners.sdf” SQL CE database file that was automatically created for us by EF code-first within the \App_Data\ folder:

We can optionally right-click on the file and “Include in Project" to add it to our solution:

We can also double-click the file (regardless of whether it is added to the project) and VS 2010 SP1 will open it as a database we can edit within the “Server Explorer” tab of the IDE.

Below is the view we get when we double-click our NerdDinners.sdf SQL CE file. We can drill in to see the schema of the Dinners and RSVPs tables in the tree explorer.

Notice how two tables - Dinners and RSVPs – were automatically created for us within our SQL CE database. This was done by EF Code First when we accessed the NerdDinners class by running our application above:

We can right-click on a Table and use the “Show Table Data” command to enter some upcoming dinners in our database:

We’ll use the built-in editor that VS 2010 SP1 supports to populate our table data below:

And now when we hit “refresh” on the /Dinners/Upcoming URL within our browser we’ll see some upcoming dinners show up:

Step 7: Changing our Model and Database Schema

Let’s now modify the schema of our model layer and database, and walkthrough one way that the new VS 2010 SP1 Tooling support for SQL CE can make this easier.

With EF Code-First you typically start making database changes by modifying the model classes. For example, let’s add an additional string property called “UrlLink” to our “Dinner” class. We’ll use this to point to a link for more information about the event:

Now when we re-run our project, and visit the /Dinners/Upcoming URL we’ll see an error thrown:

We are seeing this error because EF Code-First automatically created our database, and by default when it does this it adds a table that helps tracks whether the schema of our database is in sync with our model classes. EF Code-First helpfully throws an error when they become out of sync – making it easier to track down issues at development time that you might otherwise only find (via obscure errors) at runtime. Note that if you do not want this feature you can turn it off by changing the default conventions of your DbContext class (in this case our NerdDinners class) to not track the schema version.

Our model classes and database schema are out of sync in the above example – so how do we fix this? There are two approaches you can use today:

Delete the database and have EF Code First automatically re-create the database based on the new model class schema (losing the data within the existing DB)
Modify the schema of the existing database to make it in sync with the model classes (keeping/migrating the data within the existing DB)

There are a couple of ways you can do the second approach above. Below I’m going to show how you can take advantage of the new VS 2010 SP1 Tooling support for SQL CE to use a database schema tool to modify our database structure. We are also going to be supporting a “migrations” feature with EF in the future that will allow you to automate/script database schema migrations programmatically.

Step 8: Modify our SQL CE Database Schema using VS 2010 SP1

The new SQL CE Tooling support within VS 2010 SP1 makes it easy to modify the schema of our existing SQL CE database. To do this we’ll right-click on our “Dinners” table and choose the “Edit Table Schema” command:

This will bring up the below “Edit Table” dialog. We can rename, change or delete any of the existing columns in our table, or click at the bottom of the column listing and type to add a new column. Below I’ve added a new “UrlLink” column of type “nvarchar” (since our property is a string):

When we click ok our database will be updated to have the new column and our schema will now match our model classes.

Because we are manually modifying our database schema, there is one additional step we need to take to let EF Code-First know that the database schema is in sync with our model classes. As i mentioned earlier, when a database is automatically created by EF Code-First it adds a “EdmMetadata” table to the database to track schema versions (and hash our model classes against them to detect mismatches between our model classes and the database schema):

Since we are manually updating and maintaining our database schema, we don’t need this table – and can just delete it:

This will leave us with just the two tables that correspond to our model classes:

And now when we re-run our /Dinners/Upcoming URL it will display the dinners correctly:

One last touch we could do would be to update our view to check for the new UrlLink property and render a <a> link to it if an event has one:

And now when we refresh our /Dinners/Upcoming we will see hyperlinks for the events that have a UrlLink stored in the database:

Summary

SQL CE provides a free, embedded, database engine that you can use to easily enable database storage. With SQL CE 4 you can now take advantage of it within ASP.NET projects and applications (both Web Forms and MVC).

VS 2010 SP1 provides tooling support that enables you to easily create, edit and modify SQL CE databases – as well as use the standard EF designer against them. This allows you to re-use your existing skills and data knowledge while taking advantage of an embedded database option. This is useful both for small applications (where you don’t need the scalability of a full SQL Server), as well as for development and testing scenarios – where you want to be able to rapidly develop/test your application without having a full database instance.

SQL CE makes it easy to later migrate your data to a full SQL Server or SQL Azure instance if you want to – without having to change any code in your application. All we would need to change in the above two scenarios is the <connectionString> value within the web.config file in order to have our code run against a full SQL Server. This provides the flexibility to scale up your application starting from a small embedded database solution as needed.

Hope this helps,

Scott

P.S. In addition to blogging, I am also now using Twitter for quick updates and to share links. Follow me at: twitter.com/scottgu

101 Comments

I'm very happy SQL CE is getting some attention. It was a very handy product that I frequently used when I needed a small database.

With previous versions of SQL CE, you could access and perform management on CE databases via SQL Server Management Studio. Will there be a SP for SQL Server that will allow you to do the same with CE4?

Robert Cain, MVP - Wednesday, January 12, 2011 7:20:52 AM

Perfect!

And as a lazy programmer I wonder about "We are also going to be supporting a “migrations” feature with EF in the future". Can you give us some information on the priority of that feature, any estimate of when it will come?

Jonas Eriksson - Wednesday, January 12, 2011 7:32:14 AM

Thanks Scott

That's great!!

Soner Gonul - Wednesday, January 12, 2011 8:02:01 AM

You've stated that SQL CE creates a DB file in app_data.

Are you considering adding a in memory DB feature?
This can help a lot with unit testing (Create/Drop a in memory DB using Code-First for every test) which should be much faster than a file based DB (I've used this approach with other DBs, and would love to actually use SQL Server CE for it).

This also reduces the need to mock the Data access/Repository when unit testing.

linkgoron - Wednesday, January 12, 2011 8:03:44 AM

Hi Scott! Great article!

What's the difference between SQL CE (sdf-files) and the conventional mdf-database files?

Regards
Yngve

Yngve Nilsen - Wednesday, January 12, 2011 8:12:18 AM

ASP.NET is getting better and better,thank you for your hard work.

jQuery学习 - Wednesday, January 12, 2011 8:39:47 AM

Sounds very promising.

Is there any restriction in SQL CE 4 such database file size?

Eugene - Wednesday, January 12, 2011 8:42:12 AM

@Robert,

>>>>>>> With previous versions of SQL CE, you could access and perform management on CE databases via SQL Server Management Studio. Will there be a SP for SQL Server that will allow you to do the same with CE4?

That is a good question. I don't believe there will be an SP that enables this initially (meaning this week) - but am not sure if one is coming.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 8:44:29 AM

@Jonas,

>>>>>>>> And as a lazy programmer I wonder about "We are also going to be supporting a “migrations” feature with EF in the future". Can you give us some information on the priority of that feature, any estimate of when it will come?

We are going to be talking a lot more about our scaffolding story later this week (and will be releasing a cool preview of that). Migrations is next on the list of cool packages we want to ship via NuGet.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 8:45:18 AM

@linkgoron,

>>>>>>>> Are you considering adding a in memory DB feature? This can help a lot with unit testing (Create/Drop a in memory DB using Code-First for every test) which should be much faster than a file based DB (I've used this approach with other DBs, and would love to actually use SQL Server CE for it). This also reduces the need to mock the Data access/Repository when unit testing.

SQL CE itself runs in-memory - and so is quite fast to create and use for unit test projects. I believe it does require the DB file on disk - but immediately maps it in to memory (so it as fast as in-memory). You would still create/delete the file as part of your setup/teardown logic - but this should be as easy as deleting the file (there is no explicit drop or cleanup required).

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 8:46:51 AM

@Yngve,

>>>>>>>> What's the difference between SQL CE (sdf-files) and the conventional mdf-database files?

MDF files are handled by SQL Express. SQL Express runs as a Windows Service and so runs in the background. You can kind of think of it is like the full SQL Server from an engine perspective.

SQL CE uses .sdf files and runs them in-memory within the application. That means no separate process and a lot less overhead.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 8:48:11 AM

@Saber,

>>>>>>> When I create database form modle every thing work fine and generate DDL After that when I execute the SQL code I prompted with [Connect to SQL server Compact Edition] dialog. When I enter the database file name and I click connect I get this erroe message:

Can you send me email (scottgu@microsoft.com) with more details on the issue? We can then follow up.

Thanks,

Scott

ScottGu - Wednesday, January 12, 2011 8:49:28 AM

@Eugene,

>>>>>>> Is there any restriction in SQL CE 4 such database file size?

Historically I believe they can be up to 4GB each. I'm not sure if this increased, though, for SQL CE 4

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 8:50:37 AM

Tnx, it's really big post, but it's really helpful, now I can uninstall WebMatrix and normally use VS to work with sdf files!

BatsIhor - Wednesday, January 12, 2011 9:03:44 AM

When do we expect a final release of the VS2010 SP1?

Ahmed Magdy - Wednesday, January 12, 2011 9:13:53 AM

Hi Scott,

Awesome stuff. Once I have deployed a database to a live site and it becomes populated with data, how to I then manage that database if I were to deploy an update to my site which required a change in the schema? Obviously I wouldn't want to just drop it and recreate it as the data would be lost.

I have tried something with the SP1 where I had a database, and I updated my code model. The next time I run the site it complains that the model and the DB schema match, so I should drop the database or update the schema manually. So rather than drop the DB I open the schema and update the table with the new field or whatever I'm doing, but it still won't work. It seems I have to update the model hash value as well (in the EF metadata table) but there doesn't seem to be an easy way to do this either.

I guess I'm just wondering if there is going to be better support for post-deployment management scenarios by the time all of this new tech gets a proper release?

Steve Hobbs - Wednesday, January 12, 2011 10:00:07 AM

Is SQL CE 4.0 now released or it is still in beta? If still in beta, is it on the same release cycle as VS 2010 SP1?

KK - Wednesday, January 12, 2011 10:08:46 AM

@ScottGu - does SQL CE4 support the GEOGRAPHY type?

PK - Wednesday, January 12, 2011 10:29:13 AM

Thanks for the quick answer.

So, is it possible to have a complete "in-memory" (including drop/create/insert/delete etc.) environment?
Basically, I don't want SQL CE to ever flush to disk.

I've read about Flush Interval, but I want to set it to "never" instead of some large amount of time, which seems like a workaround.

If I could set the flush to "never" what I could do is create a DB file once at the beginning of my tests (which it appears I'm forced to do), and delete it after all my tests have run.
What I achieve is no I/O in my unit tests, and much better performance.

linkgoron - Wednesday, January 12, 2011 10:40:05 AM

Hi Scott,

What about SQL CE for Windows Phone 7

Sergii - Wednesday, January 12, 2011 11:16:30 AM

Any chance SQL CE 4 could eventually run on Windows Phone? I'm hopeful...

David - Wednesday, January 12, 2011 12:11:31 PM

Nice! Was looking for something similar.

Rush - Wednesday, January 12, 2011 12:47:03 PM

@Linkgoron,

>>>>>>> then using a mock framework might be what your looking at.

Rush - Wednesday, January 12, 2011 12:59:19 PM

Great work. About DB schema update options.. A scenario when EF drops and re-creates database is appropriate only for some small projects I think. And with manual update option, that would be cool to have EF make more dirty work like generating SQL update script, allow to modify it before run, it shouldn't overwrite any custom stuff in DB but at the same time take custom stuff into consideration (for example when you have custom view with schema binding and underling db table cannot be updated without dropping the view first and then recreating it).

Koistya `Navin - Wednesday, January 12, 2011 1:21:31 PM

So when is it going to be renamed "Access" and put into Office?

Geiger - Wednesday, January 12, 2011 2:27:03 PM

Any love here for Silverlight or Windows Phone?

Jason - Wednesday, January 12, 2011 3:26:28 PM

I don't care for the name SQL CE. It conjures up horrible memories of Windows CE. The marketing department should spend a few bucks rethinking the branding.

Jeremy Sullivan - Wednesday, January 12, 2011 4:07:12 PM

Does SQL CE 4 support LINQ-to-SQL as SQL CE 3.5 does?

Mike - Wednesday, January 12, 2011 4:10:34 PM

Will it be possible to use the same EF models for both the CE and full edition of the database? Will it be possible to deploy a VS database project to CE? I'm now using SQL Server + database project + EF for a website, but would like to use some of the business objects (that use EF) in an offline scenario with SQL CE. It's ok to have separate database projects for the two databases, but it would be a lot more work if I also have to duplicate the EF code.

Frank R - Wednesday, January 12, 2011 4:19:49 PM

I'm glad to see SQL CE now supports more server-side scenarios, but what about its original use: use in embedded/compact applications? Windows Embedded Handheld 6.5 recently came out, and a whole lot of major companies have a whole lot of major apps written for that platform, deployed to a WHOLE lot of existing devices! (WM5, WM 6.x, we even have a few PPC2003 devices still out there!) Assuming WEH is a strategic Microsoft platform, why haven't we heard anything about SQL CE 4 support, .NET 4 support, VS2010 support, etc.? Please don't leave us behind!

James - Wednesday, January 12, 2011 4:24:05 PM

Please correct me if these assertions are incorrect:

Maximum database size 4 gb
No File streaming support

If this is correct then SQL CE still doesn't work for scenarios with any media that needs to be linked etc. and to support syncing with remote servers as the remote servers use filestreaming and thus wouldn't know about file links etc.

Thus until we get file streaming and the same 10 gb limit as express we can't use SQL CE.... :<

john galt - Wednesday, January 12, 2011 4:42:44 PM

Can it be used for WP7 apps?

hfournier - Wednesday, January 12, 2011 4:48:27 PM

Are you planning on updating the ASP.NET Forms Authentication system to handle storing credentials in a SQL CE database? As I understand it, CE doesn't support stored procedures (which the Forms Auth providers depend on for user/role/profile CRUD actions).

Robert R. - Wednesday, January 12, 2011 4:48:43 PM

Scott,

I have been waiting for SQL CE 4 for so long, thanks! I have followed the instructions but when I deploy I get "The specified store provider cannot be found in the configuration, or is not valid."

Do I need to manually add dlls to output or does using the IDE add them for me automatically?

Chris - Wednesday, January 12, 2011 5:19:41 PM

When creating a new site, will Visual Studio be configurable to create a default membership database using SQLCE like it currently does with SQLExpress. Thanks.

Don - Wednesday, January 12, 2011 5:33:12 PM

What about an example of creating and using SQL CE in a native C++ application?

...Matt

mwhouser - Wednesday, January 12, 2011 6:13:58 PM

Thank for the post Scott. I just wish you would have added stored procedure support. I hope in the next version (SQL CE 5.0?) you add stored procedure support.

Ncage - Wednesday, January 12, 2011 7:01:37 PM

Thanks for the examples, Scott. As an application architect that is planning a new project using ASP.NET MVC and considering different OR/M tools (EF, Linq To SQL, NHibernate), your posts are very applicable and interesting.

I would, however, like to see slightly more complex data structures than the 1-to-1 entities you often use (Dinners and RSVPs, here). How about Dinners and Diners, with a corresponding table to relate them, Dinners_Diners (with DinnerID and DinerID foreign keys comprising a composite primary key and providing referential integrity, nullable IsAttending and ResponseText fields). I'd love to see this example illustrated for ASP.NET MVC, SQL CE and EF Code First!

NickS - Wednesday, January 12, 2011 8:26:45 PM

@Ahmed,

>>>>>>>> When do we expect a final release of the VS2010 SP1?

We haven't announced a date yet - we are working on incorporating feedback and finsihing it now. We'll post more about the dates once we know them.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 10:21:39 PM

@Steve,

>>>>>>>>> Awesome stuff. Once I have deployed a database to a live site and it becomes populated with data, how to I then manage that database if I were to deploy an update to my site which required a change in the schema? Obviously I wouldn't want to just drop it and recreate it as the data would be lost.

If you have a live site, then typically you write change scripts to evolve the schema (and keep the data within them). Our new migrations support in the future will make it easier to create these. Typically today people either write these by hand or use tools from companies like RedGate to do so.

>>>>>>> I have tried something with the SP1 where I had a database, and I updated my code model. The next time I run the site it complains that the model and the DB schema match, so I should drop the database or update the schema manually. So rather than drop the DB I open the schema and update the table with the new field or whatever I'm doing, but it still won't work. It seems I have to update the model hash value as well (in the EF metadata table) but there doesn't seem to be an easy way to do this either.

Yes - you'll need to delete the EdmTable in order to avoid EF code first complaining (if the database was auto-generated). I cover how to do this in my blog post above.

>>>>>>> I guess I'm just wondering if there is going to be better support for post-deployment management scenarios by the time all of this new tech gets a proper release?

That is definitely something we are investing in and you should see a lot more focus on this shortly.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 11:15:10 PM

@KK,

>>>>>>>> Is SQL CE 4.0 now released or it is still in beta? If still in beta, is it on the same release cycle as VS 2010 SP1?

The final release of SQL CE 4.0 will happen very, very shortly (this week). VS 2010 SP1 (which supports tooling for it) will still be in beta for awhile longer.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 11:16:04 PM

@PK,

>>>>>>>> does SQL CE4 support the GEOGRAPHY type?

Unfortunately this won't be supported in SQL CE 4 - sorry!

Scott

ScottGu - Wednesday, January 12, 2011 11:17:03 PM

@linkgoron,

>>>>>>>> So, is it possible to have a complete "in-memory" (including drop/create/insert/delete etc.) environment?
Basically, I don't want SQL CE to ever flush to disk. I've read about Flush Interval, but I want to set it to "never" instead of some large amount of time, which seems like a workaround. If I could set the flush to "never" what I could do is create a DB file once at the beginning of my tests (which it appears I'm forced to do), and delete it after all my tests have run. What I achieve is no I/O in my unit tests, and much better performance.

I'm following up on this - but I believe a file does need to be written to disk. SQL CE itself runs in-memory (and the file is not locked) so it should be easy to use for unit testing scenarios.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 11:20:21 PM

@David, @Sergeii, others

>>>>>> What about SQL CE for Windows Phone 7

We don't support SQL CE on Windows Phone 7 right now I'm afraid.

Sorry!

Scott

ScottGu - Wednesday, January 12, 2011 11:21:40 PM

@Koistya `Navin,

>>>>>>> About DB schema update options.. A scenario when EF drops and re-creates database is appropriate only for some small projects I think. And with manual update option, that would be cool to have EF make more dirty work like generating SQL update script, allow to modify it before run, it shouldn't overwrite any custom stuff in DB but at the same time take custom stuff into consideration (for example when you have custom view with schema binding and underling db table cannot be updated without dropping the view first and then recreating it).

Yep - that is what we are looking to enable with our migrations story.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 11:25:19 PM

@Jiho Han,

>>>>>> Is there a separate download for SQL CE 4 only? In other words, is VS2010 the only way to create and use SQL CE 4?

Yes - you'll be able to download SQL CE separately (no VS required). We have also integrated it as part of the NuGet gallery so that you can easily embedd it within your projects. VS 2010 SP1 is only needed if you want to use the new tooling support for it.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 11:26:29 PM

@Jason,

>>>>>>>> Any love here for Silverlight or Windows Phone?

Unfortunately we don't support this yet with Silverlight or Windows Phone 7. Hopefully in the future though!

Scott

ScottGu - Wednesday, January 12, 2011 11:27:00 PM

@Mike,

>>>>>>>> Does SQL CE 4 support LINQ-to-SQL as SQL CE 3.5 does?

Unfortunately it doesn't. SQL CE 4 ships with an ADO.NET data provider implementation - which Linq to SQL unfortunately doesn't support. EF, NHibernate and raw ADO.NET are supported.

Sorry!

Scott

ScottGu - Wednesday, January 12, 2011 11:28:02 PM

@Frank R,

>>>>>>>>> Will it be possible to use the same EF models for both the CE and full edition of the database? Will it be possible to deploy a VS database project to CE? I'm now using SQL Server + database project + EF for a website, but would like to use some of the business objects (that use EF) in an offline scenario with SQL CE. It's ok to have separate database projects for the two databases, but it would be a lot more work if I also have to duplicate the EF code.

Yes - that is one of the cool things that SQL CE enables. You can have the exact same EF models and just change the connection-string in the web.config file to switch between SQL CE to full SQL Server. No code changes required.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 11:28:59 PM

@James,

>>>>>>>> I'm glad to see SQL CE now supports more server-side scenarios, but what about its original use: use in embedded/compact applications? Windows Embedded Handheld 6.5 recently came out, and a whole lot of major companies have a whole lot of major apps written for that platform, deployed to a WHOLE lot of existing devices! (WM5, WM 6.x, we even have a few PPC2003 devices still out there!) Assuming WEH is a strategic Microsoft platform, why haven't we heard anything about SQL CE 4 support, .NET 4 support, VS2010 support, etc.? Please don't leave us behind!

Sorry about that - we are hopefully going to be able to talk more about these scenarios in the future.

Sorry for the delay,

Scott

ScottGu - Wednesday, January 12, 2011 11:38:06 PM

@john galt,

>>>>>>> Please correct me if these assertions are incorrect:
>>>>>>> Maximum database size 4 gb
>>>>>>> No File streaming support
>>>>>>> If this is correct then SQL CE still doesn't work for scenarios with any media that needs to be linked etc. and to support syncing with remote servers as the remote servers use filestreaming and thus wouldn't know about file links etc. Thus until we get file streaming and the same 10 gb limit as express we can't use SQL CE.... :<

I believe the above is correct - but I'm trying to check to see if it is different. I'll post another comment if I discover that isn't the case.

Hope this helps,

Scott

ScottGu - Wednesday, January 12, 2011 11:39:07 PM

Can use SQL CE 4 in WPF based apps ?

Baski - Thursday, January 13, 2011 1:23:15 AM

I want to use SQL CE to back a self-hosted WCF app or in a WPF desktop app, hope thats well supported too :)

sad to hear it wont support Ling2Sql though :(

Guest - Thursday, January 13, 2011 3:11:02 AM

It would really be useful to make clear on these types of posts that SQL CE 4 is NOT just for web apps. I understand the enthusiasm and the big push MS is making into ASP.NET, but posts like these give on the impression that it only works with ASP.NET, or that it's a web-only technology. Or maybe I am indeed wrong and will SQL CE be supported only in ASP.NET?

I'm glad to hear that there will be a stand-alone install, it'll come in handy for those of us that need a simple install without grabbing any meta installers. It's great to hear it's almost out of beta nevertheless, I am still programming (my non-web app) with the SQL CE 4 CTP which had a plain old installer.

gm - Thursday, January 13, 2011 3:12:24 AM

Thank for the post Scott.

Mike Tran - Thursday, January 13, 2011 4:22:31 AM

@Mike,

>>>>>>>> Does SQL CE 4 support LINQ-to-SQL as SQL CE 3.5 does?

>>>>>>>> Unfortunately it doesn't. SQL CE 4 ships with an ADO.NET data provider implementation - which Linq to SQL unfortunately doesn't support. EF, NHibernate and raw ADO.NET are supported.

>>>>>>>> Sorry!

>>>>>>>> Scott

However, there is a work-around using sqlmetal, you can generate a dbml file from a CE database, here's what I've been using:

"c:\program files (x86)\microsoft sdks\windows\v7.0a\bin\netfx 4.0 tools\sqlmetal" YourCEDB.sdf /dbml:YourCEDBContext.dbml

Which you can then add to your project, a small price to pay for Linq-to-Sql IMO.

Kev

Mantorok - Thursday, January 13, 2011 11:49:13 AM

Thanks Scott,

Just a question :
For asp.net application, is hosting service must be have some criteria?
Indeed, what's the minimum requirements to use Sql-CE on the web ?

Ey ghorboonet...

Neda - Thursday, January 13, 2011 12:18:22 PM

When no connectionStrings is defined CF creates by default a SQL Express database.
After adding the SQL CE connectionString no .sdf file was created, apparently CF continued using the SQL Express database. After having dropped the Express DB, the CE database was created. My guess this isn’t by design.

DanG - Thursday, January 13, 2011 1:13:04 PM

Great, one question Scott should I be able to use it with MonoDevelop on Linux, or it's only for Windows?

Timur Poperecinii - Thursday, January 13, 2011 2:47:55 PM

Doesn't this work with Windows Forms? I would love to distribute my software with a SLQ CE, so I don't need to install a SQL Express on my clients.

Isn't that possible?

Morten - Thursday, January 13, 2011 3:23:01 PM

What is the best way to use SQL CE4 with a Silverlight OOB running with elevated trust? And how would you deploy to the client? It would also be nice to use SQL CE4 with Blend for "Design Time Data" that is more realistic.

James - Thursday, January 13, 2011 3:55:59 PM

Scott, I've noticed on occasion in the past (as well as with this blog post) that you often discuss a technology and talk about in only in terms of ASP.NET. I don't do any web development at all, yet I have use for SQL Server CE in my desktop application. I wish you would at least mention the fact that these technologies work with any UI technology (web, WinForms, WPF, etc.).

(And now I see that there are some comments before mine that basically say the same thing).

Thanks for listening.

Eric Harmon - Thursday, January 13, 2011 5:02:52 PM

@Baski, Guest, gm...

I currently use SQL CE 3.5 with Entity Framework for my WPF business application and it works quite well. So I'd imagine that 4.0 will also continue to work for other apps besides ASP.NET (I think they just use it as an example due to the nature of it being a small file database rather than a service for apps that don't need to house large amounts of data).

Also, I didn't see it meantioned above, but I believe SQL CE 4.0 will now support Identity Columns with Entity Framework (which 3.5 did not). This will be a nice feature for me! Hope VS 2010 SP1 gets shipped soon!

Scott C - Thursday, January 13, 2011 5:28:57 PM

That's great. Really good for new version of EF code first and ASP.NET MVC 3. Let try it now!
Thanks, Scott.

thangchung - Thursday, January 13, 2011 5:32:16 PM

Really great. Will it be possible to run a Reporting Services report or an Integration Services package against SQL CE?

Ronny - Thursday, January 13, 2011 8:41:36 PM

Thanks - great news. A question: does EF POCO template work with Sql CE?

ignatandrei - Thursday, January 13, 2011 10:01:41 PM

Couple of questions:

1) Can you use this for a WinForms application?

2) Although it does not run on WP7 now are there plans to make it run on wp7?

3) If no to #2, what are the plans for a DB for WP7. Developers needs a lightweight/efficient DB for storing data on a WP7 device, so any information you can provide as to the future plans would be great.

smehaffie - Friday, January 14, 2011 4:13:33 AM

Hi Scott,

Is Entity Framework 4.0 meant to have an 'Sql Server CE 4.0' option? If so, I don't see it after I install SP1 & SQL CE Tools for Visual Studio... can I just use the CE 3.5 driver? Or should the 4.0 driver appear?

andrewjboyd - Friday, January 14, 2011 9:20:02 AM

Hi Scott,

When I add a "ADO.NET Entity Data Model" -> "Generate from database" -> "New Connection", there is no option for SQL CE 4.0, only 3.5. I have installed SP1 & the SQL CE Tools for Visual Studio, but it still doesn't appear, is this right?

Cheers,

Andrew

andrewjboyd - Friday, January 14, 2011 11:35:36 PM

All frameworks addons builing on OR mapping fall apart because of the LOUSY ER editor in VS. :(

Main problems is it's very slow an featureless. <<---- FIX IT!!!1

640k - Saturday, January 15, 2011 10:27:34 PM

Since it appears that LINQ to SQL is now truly abandoned, any chance of open-sourcing it, since it's a lot more lightweight than EF for small/quick projects, and I'm sure the community could figure out how to add different DB support (e.g. SQL CE, MySQL, etc.) if there was source visibility...

Philip the Duck - Sunday, January 16, 2011 6:43:51 PM

Hi, I found issue when adding new SQL Server Compact 4.0 Local Database, I'm getting error: "The assembly reference "System.Data.SqlServer.Ce, Version=4.0.0.0, Culture=neutral, PublicToken="89845dcd8080cc91" could not be added to the project. This wizard will continue to run, but the resulting project may not build properly.
I have VS2010 SP1 with SQL CE 4 Tools installed. Any help will be appreciated.

Darek - Sunday, January 16, 2011 6:45:56 PM

@Darek, I've had same problem, after repairing SQL CE Tools for Visual Studio installation. VS started Web installer and asked me to install install MS server Compact 4.0 with web installer

nLL - Monday, January 17, 2011 12:06:11 PM

Hey Scott, is there any way to use SQL CE 4 on non-web projects without jumping through hoops? I've found a way to do it but it's easier to use sqllite for those scenarios.

Thanks!

Obi - Tuesday, January 18, 2011 5:30:22 PM

Hi Scott,
We have a large number of customers using our WPF app with CE 3.5 on tablet PCs. What would you describe as "compelling reasons" to migrate to 4.0 ? Performance ? Memory footprint ? Could you please give quick comparison.
Also, is 3.5 sdf format compatible with 4.0 ?
Thanks !

andrew.tx - Thursday, January 20, 2011 4:51:00 PM

Hi Scott,

Any idea when VS 2010 SP1 is due to be RTMed? I tried to install SQL CE 4.0 tooling for VS2010 through Web Installer 3.0, but it failed warning me I need VS 2010 SP1 installed, but I do not like installing beta tools for developing systems I intend to roll out to production environments.

Still regarding to VS2010, will Asp.net Dynamic Data projects templates be updated to use razor and MVC 3 syntax and new features? Will it be also possible to store asp.net membership, roles and profile and wcf ria services authentication, roles, and profiles to SQL CE 4.0?

Thanks and regards.

Luciano Evaristo Guerche (Gorše)
Taboão da Serra, SP, Brazil

Luciano Evaristo Guerche (Gorše) - Friday, January 21, 2011 12:56:00 PM

My main problem with SQL Server CE 4.0 not so much with it, it's that that SQLmetal doesn't seem to support with it (says SSCE 4.0 databases are incompatible) and using oldschool embedded SQL in this LINQ age wohn't cut it.

Bent Rasmussen - Monday, January 24, 2011 4:52:57 PM

Is it enough to install SQL CE 4 by using NuGet with "install-package SqlServerCompact"? I am trying the code-first approach but get the following error when I run my MVC3 application with no existing database. I have uninstalled the package reference and re-installed but get the same error...

System.IO.FileNotFoundException was unhandled by user code
Message=Could not load System.Data.SqlServerCe.Entity.dll. Reinstall SQL Server Compact.
Source=System.Data.SqlServerCe
StackTrace:
Server stack trace:
at System.Data.SqlServerCe.ExtensionMethods.SystemDataSqlServerCeSqlCeProviderServices_Instance()
at System.Data.SqlServerCe.SqlCeProviderFactory.System.IServiceProvider.GetService(Type serviceType)
at System.Data.Common.DbProviderServices.GetProviderServices(DbProviderFactory factory)
at System.Data.Common.DbProviderServices.GetProviderServices(DbConnection connection)
at System.Data.Entity.ModelConfiguration.Utilities.DbConnectionExtensions.GetProviderInfo(DbConnection connection, DbProviderManifest& providerManifest)
at System.Data.Entity.ModelConfiguration.ModelBuilder.Build(DbConnection providerConnection)
at System.Data.Entity.Internal.LazyInternalContext.CreateModel()
at System.Lazy`1.CreateValue()
Exception rethrown at [0]:
at System.Data.SqlServerCe.ExtensionMethods.SystemDataSqlServerCeSqlCeProviderServices_Instance()
at System.Data.SqlServerCe.SqlCeProviderFactory.System.IServiceProvider.GetService(Type serviceType)
at System.Data.Common.DbProviderServices.GetProviderServices(DbProviderFactory factory)
at System.Data.Common.DbProviderServices.GetProviderServices(DbConnection connection)
at System.Data.Entity.ModelConfiguration.Utilities.DbConnectionExtensions.GetProviderInfo(DbConnection connection, DbProviderManifest& providerManifest)
at System.Data.Entity.ModelConfiguration.ModelBuilder.Build(DbConnection providerConnection)
at System.Data.Entity.Internal.LazyInternalContext.CreateModel()
at System.Lazy`1.CreateValue()
at System.Lazy`1.LazyInitValue()
at System.Lazy`1.get_Value()
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.InternalContext.Initialize()
at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
at System.Data.Entity.Internal.Linq.InternalSet`1.get_Provider()
at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
at System.Linq.Queryable.Where[TSource](IQueryable`1 source, Expression`1 predicate)
at MvcApplication1.Controllers.HomeController.Index() in C:\Projects\MvcApplication1\MvcApplication1\Controllers\HomeController.cs:line 16
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.c__DisplayClass15.b__12()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
InnerException: System.IO.FileNotFoundException
Message=Could not load file or assembly 'System.Data.SqlServerCe.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
Source=mscorlib
FileName=System.Data.SqlServerCe.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
FusionLog==== Pre-bind state information ===

Brian Behm - Monday, January 24, 2011 8:46:15 PM

@Brian Behm: you need to install the 'EFCodeFirst.SqlServerCompact' package for this. It will itself pull SqlServerCompact as a dependency.

David Ebbo - Monday, January 24, 2011 9:38:01 PM

@davidebb Thanks for the tip! Things are working better for me now. The description in NuGet for EFCodeFirst.SqlServerCompact says it's for SQL Server Compact CTP2 but so far so good.

Brian Behm - Monday, January 24, 2011 10:24:56 PM

@Brian: good catch, SQL Compact has been released and should not have said CTP2. I fixed up the description.

David Ebbo - Tuesday, January 25, 2011 7:57:28 AM

Hi Scott,

Thanks for the post. Are there any performance stats available for SQLce? Especially I would like to see a comparison of SQLce vs SqL Express.

Regards,

Andy

Andy - Tuesday, January 25, 2011 12:02:03 PM

I'm very interested in SQLCE 4 for winforms or wpf desktop apps. I've even got a prototype winforms app that uses it with Code First. However, I've not been able to find any concise instructions on what is required to actually deploy it. I keep reading that you only need to put the binaries in the bin folder but I've seen no documentation as to which binaries are required or how to go about getting the correct binaries for 64bit or 32bit.

k4gdw - Wednesday, January 26, 2011 7:17:28 PM

Just been interested to use it to my open-source project Employee Info Starter Kit (http://eisk.codeplex.com/), which is based on Entity Framework 4.0 and Visual Studio 2010. The main issue that I face is I really can't use the same edmx file for sql express and sql ce edition, which causes me to drop sql ce from my project. Hope your team will take it into consideration.

joycsharp - Friday, January 28, 2011 11:52:07 PM

Still regarding to VS2010, will Asp.net Dynamic Data projects templates be updated to use razor and MVC 3 syntax and new features? Will it be also possible to store asp.net membership, roles and profile and wcf ria services authentication, roles, and profiles to SQL CE 4.0?

Thanks for sharing information about this topic
Nice article
Great tips

ecommerce developers - Monday, January 31, 2011 6:50:33 AM

Hi, just a quick question... does SQL CE 4.0 support NVARCHAR(MAX) or field restrictions of more than 4000 chars.

Great article!
Cheers

Brett - Tuesday, February 1, 2011 8:46:54 AM

Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8482. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

osman - Thursday, February 3, 2011 11:00:47 AM

I see we still can't see the control events list in the property window (in C#) without switching to the design view. Please, please, pretty please can this one day be fixed??

cjmos - Tuesday, February 8, 2011 9:38:05 PM

Scott:
This is a great product. I am using this with a windows forms app. What I am planning is deploy the sdf file in a shared location, and not "root". I dont want the sdf file to be overwritten everytime I do a release to the windows app. But my connection string are managed using the |DataDirectory|. Is there a better way of managing the location of my SDF?
I dont see support for XML data type and currently using ntext. Will XML datatype be supported for SQL CE 4? thanks.

jenad1kr - Thursday, February 10, 2011 1:59:06 PM

Hi,

I've settled down with a nice mug of coffee to have a tinker with SQL CE 4. I've installed the VS2010 SP1 Beta. However, the link to the SQL CE Tools for Visual Studio download fails. Where can I get hold of the SSCEVSTools-ENU_msi file it refers to?

Thanks.

Glyn - Friday, February 11, 2011 2:19:22 PM

404 error on SQLCE toosl for VS Sp1, can anyone help?

Lionelo Espina - Friday, February 11, 2011 5:42:29 PM

The SQL CE Tools for Visual Studio download link to the file SSCEVSTools-ENU.msi is broken.

Is there an alternative location to download this file from?

Glyn - Saturday, February 12, 2011 5:04:51 PM

The url to the SQL CE Tools for Visual Studio download, is broken. The same applies for Web Platform Installer which resolves to the same url. I've googled the package but all leads back to the broken link. Do you perhaps know how to retrieve it? Thanks in advance!

Phillip - Sunday, February 13, 2011 5:21:23 PM

Hi,
The link for SQLCE tools for Visual Studio 2010 is broken.

Timur Poperecinii - Sunday, February 13, 2011 6:19:29 PM

Yea! The SQLCE Tools link is back.

Todd - Tuesday, February 15, 2011 4:32:24 PM

Scott, are you able to share any indication of the SP1 RTW date?
Thanks

Alistair - Thursday, February 24, 2011 8:03:46 PM

Is the following addressed in VS2010 sp1.
Is it possible to generate deployments packages and deploy.cmd scripts which are compatible with web deploy 2.0?

Currently we started using web deploy 2.0 on some test servers. But the web deploy deploy.cmd scripts generated by visual studio are not working anymore :(
1st, resolving the path to webdeploy is not working anymore. This can be fixed by updating the registry lookup.
2nd, the username and password parameters are case sensitive and should be UserName and Password. This had me puzzled for a while.

Cheers.

nyos1979 - Wednesday, March 2, 2011 9:13:54 AM

My 2nd point is not correct. I was missing AuthType='Basic' in the msdeploy parameters which was not needed to a web deploy 1.0 service.

Cheers.

nyos1979 - Wednesday, March 2, 2011 9:40:57 AM

Hi Scott,

I'm stuck... I'm trying to get to grips with MVC3 and EFCodeFirst as I've been lounging around in ASP.NETsville.

I've gone about creating some models and a DbContext in order to deal with the persistence, I run my code which utilises the DbContext, the mdf file is created for me automatically... however, no tables are created?!

What am I doing wrong?

Kind Regards,
Alex

Alex - Monday, March 7, 2011 4:34:13 PM

@Alex_j_Butler,

>>>>>>>> I've gone about creating some models and a DbContext in order to deal with the persistence, I run my code which utilises the DbContext, the mdf file is created for me automatically... however, no tables are created?!

Can you send me email (scottgu@microsoft.com) with details on the issue? I can then get you help.

Thanks,

Scott

ScottGu - Monday, March 7, 2011 8:30:04 PM

First Problem: after you add an entity connection, and try to refresh the schema, unable to cast from system.data.sqlserverce.sqlceconnection to microsoft sqlserverce.client.sqlceconnection

Giuseppe - Saturday, March 12, 2011 9:09:26 AM

Comments have been disabled for this content.