Why this post about SQL Compact Edition 4 (SQL CE 4)? In short it’s a free database engine that doesn’t require an installation of a database server (SQL Server, MySQL, …). Copy the SQL CE binaries in your /bin folder and you’re ready to go. You don’t need an administrator account and can even run it in medium trust environments, like a web host. Interested? This is what you need:
- Visual Studio 2010 SP1
- SQL CE 4 Tools
Note: around MIX 11 the link for the tools broke down. It is still available in the Web Platform Installer (WebPI) v3.
New project
We get started with a standard ASP.NET 4.0 Web Application. Right-click on the App_Data folder and select “Add->New Item”. On top of the “Add item” dialog box, you’ll find the option to create a “SQL Server Compact 4.0 Local Database”, with .sdf as extension. On adding the database, all references for SQL CE get added automatically.
I could continue writing every single step of the process to get your site building, but maybe even better is checking out ScottGu’s very detailed guide on creating the database and building your ASP.NET site with SQL CE and Entity Framework 4.
Interesting note: if you want to use a regular LINQ datacontext with your SQL CE database, have a look at this Compact LINQ codeproject.
ASP.NET Membership
Now next up is authentication on your site. If you opt for Forms Authentication, you have the problem that ASP.NET Membership uses the full version of SQL Server (Express). Luckily Erik Ejlskov Jensen wrote a SQL CE 4 ASP.NET Membership provider that is hosted on CodePlex. Add the 3 code files or the assembly to your project, change your web.config file as specified on the project site and run your site. The membership database gets created automatically. Can’t be much easier, can it?
In part 2 we’ll deploy to shared hosting.