In this topic, you will learn how to compact a SQL Server Compact 3.5 database by using the Compact method of the SqlServerCe.Engine object and by using the Shrink method of the SqlServerCe.Engine object. The Compact and Shrink methods differ slightly in how they reduce database size.

You use the Compact method to reclaim space in the database file. You can also use it to change database settings such as the password and locale ID (LCID) settings. When you compact a database, a new database file is created, table pages are reorganized so they reside in adjacent database pages, and unused space is reclaimed by rewriting all database data into the new data pages.

You can also use the Shrink method to reclaim space in the database file. However, the Shrink method cannot be used to change database settings, because the Shrink method does not create a new database file; it only reorganizes records and deletes empty records.

In addition, this topic provides information about how to use the Compact method to change the case sensitivity setting of a SQL Server Compact database.

For more information about Compact and Shrink, see Maintaining Databases (SQL Server Compact). For more information about using the SqlServerCe namespace, see the SqlServerCe namespace reference documentation.

Procedures for SQL Server Compact 3.5

To compact a database

1. Create an Engine object and pass in the connection string to the existing database you want to compact.

   SqlCeEngine engine = new SqlCeEngine("Data Source = AdWks.sdf");
   

2. Call the Compact method. When you call the Compact method, you can also specify new database properties, including adding password protection or encryption.

   engine.Compact("Data Source=; Password = <enterStrongPasswordHere>");
   

To shrink a database

1. Create an Engine object and pass in the connection string to the database you want to shrink.

   SqlCeEngine engine = new SqlCeEngine("Data Source = AdWks.sdf");
   

2. Call the Shrink method.

   engine.Shrink();
   

To change the case sensitivity of a compact database

1. Create an Engine object and pass in the connection string to the existing database you want to compact.

   SqlCeEngine engine = 
        new SqlCeEngine("Data Source= Test.sdf; LCID= 1033");
   

2. Call the Compact method. When you call the Compact method, you can also specify a new database property, such as case sensitivity. If you do not specify "Case Sensitive" when calling the Compact method, the case-sensitivity setting is not changed.

   engine.Compact("Data Source= Test.sdf; LCID= 1033; Case Sensitive=true");
   

Example

This example compacts an existing SQL Server Compact 3.5 database and shows how to change database properties.

SqlCeEngine engine = new SqlCeEngine("Data Source = AdventureWorks.sdf");

// Specify null destination connection string for in-place compaction
//
engine.Compact(null);

// Specify connection string for new database options
//
engine.Compact("Data Source=; Password =<enterStrongPasswordHere>");

Dim engine As New SqlCeEngine("Data Source = AdventureWorks.sdf")

 ' Specify null destination connection string for in-place compaction
engine.Compact(Nothing)

' Specify connection string for new database options
'
engine.Compact("Data Source=; Password =<enterStrongPasswordHere>")

This example shrinks an existing SQL Server Compact 3.5 database.

SqlCeEngine engine = new SqlCeEngine("Data Source = AdventureWorks.sdf");
engine.Shrink();

Dim engine As New SqlCeEngine("Data Source = AdventureWorks.sdf")
engine.Shrink()

The following example demonstrates how to use the Compact method to change the case sensitivity of a SQL Server Compact database. Then, the code example calls the GetDatabaseInfo method to retrieve the locale, encryption mode and a case sensitive value of the database.

// Default case-insentive connection string.
string connStringCI = "Data Source= Test.sdf; LCID= 1033";

// Set "Case Sensitive" to true to change the collation from CI to CS.
string connStringCS = 
    "Data Source= Test.sdf; LCID= 1033; Case Sensitive=true"; 

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

SqlCeEngine engine = new SqlCeEngine(connStringCI);
// The collation of the database is case-insensitive.
engine.CreateDatabase();

// The collation of the database will be case-sensitive because of 
// the new connection string used by the Compact method.  
engine.Compact(connStringCS);

SqlCeConnection conn = null;
conn = new SqlCeConnection(connStringCS);
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);
}

' Default case-insentive connection string.
Dim connStringCI As String = "Data Source= Test.sdf; LCID= 1033"

' Set "Case Sensitive" to true to change the collation from CI to CS.
Dim connStringCS As String = "Data Source= Test.sdf; LCID= 1033; Case Sensitive=true"

If File.Exists("Test.sdf") Then
    File.Delete("Test.sdf")
End If

Dim engine As New SqlCeEngine(connStringCI)
' The collation of the database is case insensitive.
engine.CreateDatabase()

' The collation of the database will be case sensitive because of 
' the new connection string used by the Compact method.
engine.Compact(connStringCS)

Dim conn As SqlCeConnection = Nothing
conn = New SqlCeConnection(connStringCS)
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(vbNewLine & "GetDatabaseInfo() results:")

Dim kvp As KeyValuePair(Of String, String)
For Each kvp In dbinfo
    Console.WriteLine(kvp)
Next

Concepts

Maintaining Databases (SQL Server Compact)

Common Database Tasks (SQL Server Compact)