SqlCeEngine Class

SQL Server Compact is optimized to serve as a database for Web sites and Web applications. SQL Server Compact is optimized for use as an embedded database within Web applications. SQL Server Compact can be used as a database for Web sites for multiple users and concurrent data changes.

SQL Server Compact can be used for developing ASP.NET Web applicatons. In the previous versions of SQL Server Compact a flag, SQLServerCompactEditionUnderWebHosting had to be set to use SQL Server Compact in ASP.NET Web applications. The flag is removed in SQL Server Compact 4.0.

The following sample demonstrates how to create a new SQL Server Compact database.

if (File.Exists("Test.sdf"))
    File.Delete("Test.sdf");

string connStr = "Data Source = Test.sdf; Password = <password>";

SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();
engine.Dispose();

SqlCeConnection conn = null;

try {
    conn = new SqlCeConnection(connStr);
    conn.Open();

    SqlCeCommand cmd = conn.CreateCommand();
    cmd.CommandText = "CREATE TABLE myTable (col1 int, col2 ntext)";
    cmd.ExecuteNonQuery();
}
catch {}
finally {
    conn.Close();
}

Reference