How to: Create a Database by Using the Engine Object (Programmatically)

In this topic, you will learn how to programmatically create a SQL Server Compact database by using the CreateDatabase method of the SqlServerCe.Engine object. For more information about using the SqlServerCe namespace, see the SqlServerCe namespace reference documentation.

Procedures for SQL Server Compact 4.0

To create a database by using the Engine object

1. Initialize a new Engine object.

   SqlCeEngine engine = new SqlCeEngine();
   

2. Set the LocalConnectionString property of the Engine object. The LocalConnectionString property specifies the name and location of the database that will be created, and might specify additional database options, including encryption.

   eng.LocalConnectionString= "Data Source='Test.sdf'; LCID=1033;" +
      "Password='<enterStrongPasswordHere>';";
   

3. Call the CreateDatabase method to create the database.

   engine.CreateDatabase();
   

To create a case-sensitive database by using the Engine object

1. Initialize a new Engine object.

   SqlCeEngine engine = new SqlCeEngine();
   

2. Set the LocalConnectionString property of the Engine object. The LocalConnectionString property specifies the name and location of the database that will be created, and might specify additional database options, including case sensitivity.

   eng.LocalConnectionString= "Data Source='Test.sdf'; LCID=1033;" +
      "Password='<enterStrongPasswordHere>'; Case Sensitive = TRUE;";
   

3. Call the CreateDatabase method to create the database.

   engine.CreateDatabase();
   

Example

The first example creates a new database named Test.sdf.

System.IO.File.Delete("Test.sdf");
string connString = "Data Source = 'Test.sdf'; LCID=1033; Password = <enterStrongPasswordHere>;";
SqlCeEngine engine = new SqlCeEngine(connString);
engine.CreateDatabase();

System.IO.File.Delete("Test.sdf")
Dim connString As String = "Data Source='Test.sdf'; LCID=1033; Password=<enterStrongPasswordHere>; Encrypt = TRUE;"
Dim engine As New SqlCeEngine(connString)
engine.CreateDatabase()

The second example uses the LocalConnectionString property, which supports an additional Boolean property called "Case Sensitive" or "CaseSensitive". This property can have either a true or a false value. Its default value is always false. This property is introduced starting with the SQL Server Compact 3.5 SP1 release. For more information, see Working with Collations (SQL Server Compact).

The example demonstrates how to set the case-sensitive property in a connection string by using the LocalConnectionString property. Then, the code example uses the GetDatabaseInfo method to retrieve the locale, encryption mode and a case-sensitivity setting of the database:

// First approach: 
// Create a case-sensitive database by using the SqlCeEngine 
// constructor.
if (File.Exists("Test.sdf"))
    File.Delete("Test.sdf");

// 1033: specifies the English locale with collation 
// SQL_Latin1_General_CP1_CI_AS
string connStr = 
   "Data Source='Test.sdf'; LCID=1033; Case Sensitive = TRUE";

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

// Second approach: 
// Create a case-sensitive database by using the LocalConnectionString 
// property.
if (File.Exists("Test.sdf"))
   File.Delete("Test.sdf");

SqlCeEngine engine2 = new SqlCeEngine();

engine2.LocalConnectionString = 
   "Data Source='Test.sdf'; LCID=1033; Case Sensitive = TRUE";
// 1033: specifies the English locale with collation 
// SQL_Latin1_General_CP1_CI_AS
engine2.CreateDatabase();
engine2.Dispose();

SqlCeConnection conn = null;

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

   //Retrieve the connection string information - 
   // notice the 'Case Sensitive' value
   List<KeyValuePair<string, string>> dbinfo = conn.GetDatabaseInfo();

   Console.WriteLine("\nGetDatabaseInfo() results:");

   foreach (KeyValuePair<string, string> kvp in dbinfo)
   {
      Console.WriteLine(kvp);
   }
   }
   catch (Exception ex)
   {
      Console.WriteLine(ex.Message);
   }
   finally
   {
      conn.Close();
      Console.WriteLine("\n\n\nPress any key to continue...");
      Console.Read();
   }

' First approach: 
' Create a case sensitive database by using the SqlCeEngine 
' constructor.
If File.Exists("Test.sdf") Then
    File.Delete("Test.sdf")
End If

' 1033: specifies the English locale with collation 
' SQL_Latin1_General_CP1_CI_AS
Dim connStr As String = "Data Source='Test.sdf'; LCID=1033; Case Sensitive = TRUE"

Dim engine As New SqlCeEngine(connStr)
engine.CreateDatabase()
engine.Dispose()

' Second approach: 
' Create a case-sensitive database by using the LocalConnectionString 
' property.
If File.Exists("Test.sdf") Then
    File.Delete("Test.sdf")
End If

Dim engine2 As New SqlCeEngine
engine2.LocalConnectionString = _
    "Data Source='Test.sdf'; LCID=1033; Case Sensitive = TRUE"
    ' 1033: specifies the English locale with collation 
    ' SQL_Latin1_General_CP1_CI_AS
engine2.CreateDatabase()
engine2.Dispose()

Dim conn As SqlCeConnection = Nothing

Try
    conn = New SqlCeConnection(connStr)
    conn.Open()

    'Retrieve the connection string information - 
    ' notice the 'Case Sensitive' value
    Dim dbinfo As List(Of KeyValuePair(Of String, String)) = conn.GetDatabaseInfo

    Console.WriteLine(ChrW(10) & "GetDatabaseInfo() results:")

    Dim kvp As KeyValuePair(Of String, String)

    For Each kvp In dbinfo
        Console.WriteLine(kvp)
    Next
Catch ex As Exception
    Console.WriteLine(ex.Message)
Finally
    conn.Close()
    Console.WriteLine(vbNewLine & vbNewLine & vbNewLine & "Press any key to continue...")
    Console.Read()
End Try

See Also

Concepts

Overview of Database Engine (SQL Server Compact)