Click here to Skip to main content
15,867,568 members
Articles / Web Development / ASP.NET
Article

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

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
11 Oct 2013CPOL2 min read 11.9K   1  
While professional developers are waiting for the Visual Studio Tools and Designers for SQL Server Compact 4.0, I will show how impatient developers

This articles was originally at wiki.asp.net 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 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.

<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>{
        <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;
                conn.Open();
                <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.

License

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


Written By
United States United States
The ASP.NET Wiki was started by Scott Hanselman in February of 2008. The idea is that folks spend a lot of time trolling the blogs, googlinglive-searching for answers to common "How To" questions. There's piles of fantastic community-created and MSFT-created content out there, but if it's not found by a search engine and the right combination of keywords, it's often lost.

The ASP.NET Wiki articles moved to CodeProject in October 2013 and will live on, loved, protected and updated by the community.
This is a Collaborative Group

755 members

Comments and Discussions

 
-- There are no messages in this forum --