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?
You will need at least the System.Data.SqlServerCe assembly. That is normally in the GAC but it might be usable as a locally copied DLL.
From there on you can instantiate a SqlCeEngine object and use CreateDatabase()
Here is some CodeProject example code in VB.NET.
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:
In order to use the assembly, you have to call this method:
AppDomain.CurrentDomain.SetData("SQLServerEverywhereUnderWebHosting", true)
You need the SQL Compact 3.5 SP1 Runtime:
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
}
}
}
