Click here to Skip to main content
13,292,205 members (58,370 online)
Click here to Skip to main content
Add your own
alternative version


1 bookmarked
Posted 11 Oct 2013

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

, 11 Oct 2013
Rate this:
Please Sign up or sign in to vote.
While professional developers are waiting for the Visual Studio Tools and Designers for SQL Server Compact 4.0, I will show how impatient developers

Editorial Note

This articles was originally at but has now been given a new home on CodeProject. Editing rights for this article has been set at Bronze or above, so please go in and edit and update this article to keep it fresh and relevant.

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 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).

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

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). 

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

<span style="COLOR:blue;">  <</span><span style="COLOR:#a31515;">connectionStrings</span><span style="COLOR:blue;">>
    <</span><span style="COLOR:#a31515;">add </span><span style="COLOR:red;">name </span><span style="COLOR:blue;">=</span>"<span style="COLOR:blue;">NorthWind</span>"
    <span style="COLOR:red;">connectionString</span><span style="COLOR:blue;">=</span>"<span style="COLOR:blue;">data source=|DataDirectory|\Nw40.sdf</span>" <span style="COLOR:blue;">/>
  </</span><span style="COLOR:#a31515;">connectionStrings</span><span style="COLOR:blue;">></span>

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).

<span style="COLOR:blue;">using </span>System;
<span style="COLOR:blue;">using </span>System.Configuration;
<span style="COLOR:blue;">using </span>System.Data.SqlServerCe;

<span style="COLOR:blue;">namespace </span>Ce40ASPNET
    <span style="COLOR:blue;">public partial class </span><span style="COLOR:#2b91af;">_Default </span>: System.Web.UI.<span style="COLOR:#2b91af;">Page
        <span style="COLOR:blue;">protected void </span>Page_Load(<span style="COLOR:blue;">object </span>sender, <span style="COLOR:#2b91af;">EventArgs </span>e)
            <span style="COLOR:blue;">using </span>(<span style="COLOR:#2b91af;">SqlCeConnection </span>conn = <span style="COLOR:blue;">new </span><span style="COLOR:#2b91af;">SqlCeConnection</span>())
                conn.ConnectionString = <span style="COLOR:#2b91af;">ConfigurationManager</span>.ConnectionStrings[<span style="COLOR:#a31515;">"Northwind"</span>].ConnectionString;
                <span style="COLOR:blue;">using </span>(<span style="COLOR:#2b91af;">SqlCeCommand </span>cmd = <span style="COLOR:blue;">new </span><span style="COLOR:#2b91af;">SqlCeCommand</span>(<span style="COLOR:#a31515;">"SELECT TOP (1) [Category Name] FROM Categories"</span>, conn))
                    <span style="COLOR:blue;">string </span>valueFromDb = (<span style="COLOR:blue;">string</span>)cmd.ExecuteScalar();
                    Response.Write(<span style="COLOR:blue;">string</span>.Format(<span style="COLOR:#a31515;">"{0} Time {1}"</span>, valueFromDb, <span style="COLOR:#2b91af;">DateTime</span>.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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

ASP.NET Community
United States United States
This member doesn't quite have enough reputation to be able to display their biography and homepage.
Group type: Collaborative Group

764 members

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171207.1 | Last Updated 11 Oct 2013
Article Copyright 2013 by ASP.NET Community
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid