Creating a Microsoft SQL Server CE Database


There are two options for creating a SQL Server CE database. One option is to use the SQL Server CE Query Analyzer to graphically create and design a SQL Server CE database. To learn more about the Query Analyzer, see Microsoft SQL Server CE Books Online.

You can also create a SQL Server CE database programmatically by using the SQL Server CE Data Provider classes defined in the System.Data.SqlServerCE namespace. When creating a database programmatically, you only need to interact with one SQL Server CE Data Provider class, System.Data.SqlServerCe.SqlCeEngine . The SqlCeEngine class provides programmable access to the engine-specific features in SQL Server CE. The SqlCeEngine provides two major pieces of functionality: the ability to create a new database and the ability to compact an existing database. To learn more about SQL Server CE database compaction, see the "Maintaining a SQL Server CE Database" Shop Talk.

SHOP TALK: MAINTAINING A SQL SERVER CE DATABASE

As with most data stored by an application, the internal structure of a SQL Server CE's database can, over time and repeated use, become fragmented . Needless to say, this fragmentation results in a waste of space and eventual degradation in your application's performance. To remedy this fragmentation, SQL Server CE provides the CompactDatabase method, exposed as the Compact method on the SqlCeEngine class. The Compact method performs the following tasks :

  • Reorganizes a table's pages to reside in adjacent database pages, which improves performance by reducing table fragmentation

  • Reorders the rows of the table when there is a unique or primary key constraint present, which increases search/insert performance

  • Reclaims used space created by object and record deletions

  • Resets incrementing identity columns so that the next values will be one more than the highest value in current rows

  • Regenerates the table statistic, which may help improve the query optimization process

  • Attempts to repair errors in the database

  • Might modify database properties

The Compact method writes a new database to the file system. Because a new version of the database is being created, you can change the database's password, encryption properties, and locale identifier.

Before attempting to compact a database, it must be closed. Sufficient storage space must exist for both the original and compacted versions of the database. The source database path and destination databases path must not be the same. If any of these conditions are not met, the SqlCeEngine will throw a SqlException . Listing 7.1 demonstrates how to compact a SQL Server CE database:

Listing 7.1 Using the SqlCeEngine 's Compact method
 C# string srcDBName = "testdb.sdf"; string destDBName = "testdb.sdf.tmp"; string newPassword = "Password = 123Testing"; SqlCeEngine engine = new SqlCeEngine("Data Source = " + srcDBName); try {   // Compact the database and add password protection   engine.Compact("Data Source = " + destDBName + ";" + newPassword);   engine.Dispose();   File.Delete(srcDBName);   File.Move(destDBName, srcDBName); } catch (SqlCeException e) {   // Handle the exception } finally {   // Clean up the SqlCeEngine object   engine.Dispose(); } VB Dim srcDBName as string Dim destDBName as string Dim newPassword as string srcDBName = "testdb.sdf" destDBName = "testdb.sdf.tmp" newPassword = "Password = 123Testing" Dim engine as new SqlCeEngine("Data Source = " & srcDBName) Try    // Compact the database and add password protection   engine.Compact("Data Source = " & destDBName & ";" & newPassword)   engine.Dispose()   File.Delete(srcDBName)   File.Move(destDBName, srcDBName) Catch e As SqlCeException   // Handle the exception Finally   // Clean up the SqlCeEngine object   engine.Dispose() End Try 

Creating a SQL Server CE database programmatically is very simple. There are only three steps:

  1. The best practice is to ensure that the SQL Server CE database file ( .sdf ) does not exist before the database is created. The existing database will be deleted when creating the new database. So, caution must be taken when creating a new database.

  2. An instance SqlCeEngine class must be instantiated and initialized with a connection string that describes how the engine should connect to the database. Table 7.1 describes the complete list of connection string properties.

  3. Call the CreateDataBase method on the SqlCeEngine instance. Listing 7.2, demonstrates how to create a new SQL Server CE database.

Listing 7.2 Creating a new SQL Server CE database
 C# public void CreateNewDatabase() {   if(File.Exists("tempdb.sdf")     File.Delete("tempdb.sdf");   string connStr = "Data Source = tempdb.sdf; Password = testing123"   using(SqlCeEngine engine = new SqlCeEngine(connStr)) {     engine.CreateDatabase();   } } VB Sub Main()   If File.Exists("tempdb.sdf") Then     File.Delete("tempdb.sdf")   End If   Dim connStr As String   connStr = "DataData Source = tempdb.sdf; Password = testing123"   Dim engine As SqlCeEngine   engine = New SqlCeEngine(connStr)   engine.CreateDatabase()   engine.Dispose() End Sub 

It is important to note that the SqlCeEngine object supports the IDisposable interface, and its resources can be cleaned up by calling the Dispose method. For C# users, wrapping the use of the SqlCeEngine instance in the using statement is the easiest and most reliable way to ensure that the SqlCeEngine is cleaned up after.

Table 7.1. SQL Server CE Command Line Parameters

PARAMETER

REQUIRED OR OPTIONAL

DESCRIPTION

Provider

Optional

The name of the data source provider. If the provider is not specified, Microsoft.sqlserver.oledb.ce.2.0 is assumed.

Data Source

Required

The name of the database. By convention, you should name the SQL Server CE databases with the file extension .sdf .

Locale Identifier

Optional

The database locale. This property specifies the collation order for string comparisons in the database. The default database locale is Latin1_General (0 x 00000409).

SSCE:Database Password

Optional

The database password. This parameter must be specified if the database was created with a password.

SSCE:Encrypt Database

Optional

The property that specifies whether a newly created database should be encrypted. This property is only meaningful when creating a database. The created database is encrypted when this Boolean parameter is true and a database password is specified.



Microsoft.NET Compact Framework Kick Start
Microsoft .NET Compact Framework Kick Start
ISBN: 0672325705
EAN: 2147483647
Year: 2003
Pages: 206

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net