Getting Started with SQL Server Compact 4.0 and ASP.NET 4

While professional developers are waiting for the Visual Studio Tools and Designers for SQL Server Compact 4.0, I will show how impatient developers can include SQL Server Compact with ASP.NET applications, and use it from ASP.NET pages.

Previously, you had to circumvent the SQL Compact ASP.NET blocker by adding a line of code to global.asax, as I describe here. This is no longer required, and SQL Compact 4.0 can now reliably handle web load.

Including SQL Server Compact 4.0 with your ASP.NET 4 app

1: Download http://tiny.cc/cfjia and install the 4.0 CTP runtime.

2: Copy the contents of the folder C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private (show below) to the bin folder in your web app (you may have to use Show All Files in VS to see this folder).

http://lh6.ggpht.com/_S3gOGymJVuE/TD6xrLpfdlI/AAAAAAAAAWs/I-F7DnoXUog/s1600-h/image%5B3%5D.png

3: Place your SQL Compact sdf file in your App_Data folder, so your solution looks like this (with Show All Files on):

http://lh3.ggpht.com/_S3gOGymJVuE/TD6xsRADpxI/AAAAAAAAAW0/TlrWjacWQD4/s1600-h/image%5B7%5D.png

You can include the database file and the SQL Compact as content (Do not copy), if desired (so they become part of your project file).

http://lh5.ggpht.com/_S3gOGymJVuE/TD6xtb4R6HI/AAAAAAAAAW8/sblcOUMbPAY/s1600-h/image%5B11%5D.png 

4: Add a connection string to web.config, notice the |DataDirectory| macro, which will expand to the App_Data folder.

  <connectionStrings>
    <add name ="NorthWind"
    connectionString="data source=|DataDirectory|\Nw40.sdf" />
  </connectionStrings>

5: Write code to connect. For now, you must use vanilla ADO.NET.Later you will be able to use Entity Framework and other OR/Ms to provide and model of your database. (Not LINQ to SQL, however).

using System;
using System.Configuration;
using System.Data.SqlServerCe;

namespace Ce40ASPNET
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (SqlCeConnection conn = new SqlCeConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
                conn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand("SELECT TOP (1) [Category Name] FROM Categories", conn))
                {
                    string valueFromDb = (string)cmd.ExecuteScalar();
                    Response.Write(string.Format("{0} Time {1}", valueFromDb, DateTime.Now.ToLongTimeString()));
                }
            }
        }
    }
}

You can now upload you project files to any web hosting site running ASP.NET 4, and your database runtime will be included in the upload. No SQL Server subscription will be required.

Hope this will get you started with SQL Compact 4.0  and ASP.NET.