How to create a SQL Compact 3.5 database for Windows Mobile in an ASP.NET web application?

Question

In an ASP.NET web application I have to dynamically create a SQL Compact 3.5 database for a Windows Mobile Compact Framework application from a data source (SQL Server).

I have to create the database file, needed objects (tables, ...) and fill the tables with data. The client application (Windows Mobile device ) will download that generated file.

What prerequisites (referenced assemblies, installations) do I need - or: is this even possible?

Answer 1

Essentially Henk's answer points in the right direction. But there are some caveats. The SQLServerCE assembly throws runtime errors.

I found these two helpful links:

• SQL Server Compact Edition under ASP.net and IIS (Steve Lasker's blog)
• Using SQL Compact Edition Under ASP.NET by Geoff Lane

In order to use the assembly, you have to call this method:

AppDomain.CurrentDomain.SetData("SQLServerEverywhereUnderWebHosting", true)

Answer 2

You need the SQL Compact 3.5 SP1 Runtime:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9&displaylang=en

Then add a reference to System.Data.SqlServerCe

Here is some sample code that creates a new database:

using System.Data.SqlServerCe;

public static class SqlCompactServices
{
    public static void CreateDatabase()
    {
     string connectionString = "Data Source=file.sdf";
     using (SqlCeEngine engine = new SqlCeEngine(connectionString))
     {
      engine.CreateDatabase();
     }

     using (SqlCeConnection connection = new SqlCeConnection(connectionString))
     {
      connection.Open();
      // preform queries just like "regular" SQL Server
     }
    }
}