Thursday, July 15, 2010

Getting started with SQL Server Compact 4.0 and ASP.NET 4.0 (no WebMatrix)

While professional developers are waiting for the Visual Studio Tools and Designers for SQL Server Compact 4.0, I will show how impatient develoers 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.0 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).

image

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

image

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

image

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). – UPDATE: EF CTP with SQL Compact support just released: Microsoft announced a new Entity Framework CTP today.



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.0, 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.



8 comments:

ErikEJ said...

Bernard: You can use WebMatrix or http://sqlcetoolbox.codeplex.com for table design

Derek said...

With the release of Visual Studio 2010 and the highly anticipated SQL CE Version 4.0 it is now possible to connect a web-based application to an .sdf (SQL CE) file and host it without the need of SQL Server.

Our question is will it or is it now possible to connect to the .sdf files through the use of Visual Web Developer data source objects such SQLDataSource or ObjectDataSource which in turn would allow developers to bind the results of these objects to GridView, ListView, DropDownMenus etc. You have demonstrated that it is possible to extract data and write to data through the generic SQL code, however for larger applications it would be advantageous to have the same GUI to interact with the .sdf files much the same as Web Developer 2008 interacts with .mdf or data context modules. Also on the topic of DataContext, will it be soon possible to connect the .sdf file with LINQ much the same as the LINQ to SQL class works ? I do apologize for the long winded message, however the timing of the SQL CE 4.0 looks to be lining up perfectly for our asp.net application that would increase performance on our customers end if .sdf data object models are/will be available for developers.

The importance is that the app is stand-alone that must run on a laptop and we are using Cassini to host it. We are using Visual Web Developer Express and SQL Server 2008 Express. Thank you very much for your time and look forward to your reply.

ErikEJ said...

Derek: Yes, an experience similar to what you can do today in terms of DataSets and wrappers on top of these will be available for SQL Compact as weel, but the Microsoft VS 2010 Web Dev Express add-in to support this has not been released yet. You will be able to use LINQ to Entities/Entity Framework 4.0 with SQL Compact 4 (not LINQ to SQL). VS Express support for SQL Compact 4 combined with http://SqlCeToolbox.codeplex.com, your toolset should be OK.

Hasan Gürsoy said...

I've installed SQL CE 4 x64 at my Windows Server 2008 R2 but I'm having "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." error. I also included dlls to bin folder but no change.

Hasan Gürsoy said...

Also I am able to run a WinForm Application which uses SQL CE 4

ErikEJ said...

Hasan: Looking on StackOveflow, guess you got it sorted?

Uğur said...

Hi, I do not know English but I'll try to explain my problem. Thank you in advance.

Web.Config

add name="ConnectionString" connectionString="Data Source=|DataDirectory|\naouturkucarkp.sdf"
providerName="System.Data.SqlServerCe.4.0"


Class

SqlCeConnection baglanti = new SqlCeConnection();
baglanti.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
baglanti.Open();
return (baglanti);

I received the error

Erişim engellendi. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.UnauthorizedAccessException: Erişim engellendi. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

thanks

ErikEJ said...

You web application process account must have the required rights in the App_Data folder.