Section 21.3. Programming SQL Server Mobile
21.3. Programming SQL Server MobileThe SQL Server Mobile data provider classes in the Microsoft.Data.SqlServerCe namespace provide programmatic access to SQL Server Mobile databases from a managed application running on a supported device. The classes are similar to the classes in the .NET data provider for SQL Server. They let you connect to a SQL Server Mobile database, execute commands, retrieve result sets, refresh result sets, work with data offline, and synchronize local updates with the database. The data provider for SQL Server Mobile does not support batch queries or nested transactions. The following subsections provide examples that show how to use the SQL Server Mobile classes and include descriptions of the classes. You need a reference to the System.Data.SqlServerCe assembly to compile and run the examples. To add the reference, select Microsoft SQL Mobile from the .NET tab of the Add Reference dialog box in Visual Studio 2005. 21.3.1. Creating a DatabaseThis example creates a database named TestDb.sdf: using System; using System.Data.SqlServerCe; class Program { static void Main(string[] args) { SqlCeEngine engine = new SqlCeEngine( "data source=TestDb.sdf; database password=password;"); engine.CreateDatabase( ); engine.Dispose( ); Console.WriteLine("Press any key to continue."); Console.ReadKey( ); } } Running the example creates the mobile database (.sdf file) in the bin\Debug folder (if you compile a debug version of the example).
The local connection string that can be specified either in the SqlCeEngine class constructor or using the LocalConnectionString property has properties described in Table 21-1.
The classes used to manage SQL Server Mobile databases and access data in a SQL Server Mobile database are described in Table 21-2. The data access classes are similar to those for the SQL Server data provider. Corresponding classes are prefixed by SqlCe instead of Sqlfor example, SqlCeConnection instead of SqlConnection.
21.3.2. Maintaining a DatabaseThe SqlCeEngine class public properties and methods used to create and manage SQL Server Mobile databases are described in Table 21-3.
The examples in this section show how to maintain a SQL Server Mobile database using the SqlCeEngine class. 21.3.2.1. Verifying and repairingThis example verifies that a database is not corrupted. If the database is corrupted, it is repaired. using System; using System.Data.SqlServerCe; class Program { static void Main(string[] args) { // connect to the database SqlCeEngine engine = new SqlCeEngine( "data source=TestDb.sdf; database password=password;"); // check if the database is corrupted and repair if it is if (!engine.Verify( )) { engine.Repair(null, RepairOption.RecoverCorruptedRows); Console.WriteLine("Database repaired."); } Console.WriteLine("Press any key to continue."); Console.ReadKey( ); } } This example connects to the SQL Server Mobile database created in the preceding example. The Verify( ) method of the SqlCeEngine class checks the checksum for each database page to determine whether the database file is corrupt. A corrupt database file returns false and should be repaired using the Repair( ) method of the SqlCeEngine class. Repair( ) takes a single argument from the RepairOption enumerationeither DeleteCorruptedRows or RecoverCorruptedRows. The RecoverCorruptedRows option causes the engine to try to recover data from corrupted pages. However, the data is not guaranteed to be free of corruption. The DeleteCorruptedRows option results in data that is free of corruption, but because corrupt data is discarded, significant data can be lost. 21.3.2.2. Reclaiming spaceThe internal structure of a SQL Server Mobile database can become fragmented over time, resulting in wasted space. You can use the Shrink( ) or Compact( ) method of the Engine class to reclaim the space: engine.Shrink( ); The Shrink( ) method of the SqlCeEngine class is used to reclaim wasted space in the .sdf file. The Compact( ) method is described in the following subsection. You can configure the database to automatically shrink when a fragmentation threshold is exceeded by setting the autoshrink threshold property (described in Table 21-1) in the LocalConnectionString property of the SqlCeEngine object. 21.3.2.3. Modifying propertiesThe Compact( ) method of the SqlCeEngine class reclaims space in the database just as the Shrink( ) method does, but also lets you change database connection settings by specifying them in an optional argument. For example, the following statement changes the database password to newPassword: engine.Compact("database password=newPassword;"); 21.3.2.4. Backing up and restoringSQL Server Mobile is file based, so you can perform some common database tasks using the filesystem. You can back up a database by closing all open connections to it and copying the .sdf database file. Similarly, you can restore the database by copying the backup .sdf file to its original location. You drop a database by closing all connections to it and deleting the .sdf file using the filesystem APIs. For example, the following statement deletes the database named TestDb.sdf created at the beginning of this section: System.IO.File.Delete("TestDb.sdf"); 21.3.3. Creating, Altering, and Dropping Database ObjectsBecause SQL Server Mobile does not support SMO, you create a table by executing T-SQL DDL commands using the ExecuteNonQuery( ) method of the SqlCeCommand class. This example creates a table named TestTable containing two columns: using System; using System.Data.SqlServerCe; class Program { static void Main(string[] args) { SqlCeConnection conn = new SqlCeConnection( "data source=TestDb.sdf; database password=password;"); conn.Open( ); SqlCeCommand cmd = new SqlCeCommand( "CREATE TABLE TestTable(ID int, Description nvarchar(100))", conn); cmd.ExecuteNonQuery( ); conn.Close( ); Console.WriteLine("Press any key to continue."); Console.ReadKey( ); } } The example uses SqlCeConnection and SqlCeCommand objects to execute the CREATE TABLE T-SQL command against the SQL Server Mobile database. This is similar to how you would accomplish the same task in SQL Server using SqlConnection and SqlCommand objects. 21.3.4. Reading and Updating DataThis example adds two rows to the SQL Server Mobile table named TestTable created in the preceding example. The example then reads the new rows from the database and outputs them to the console. You execute queries against a SQL Server Mobile database by using the SQL Server Mobile database classes similarly to using the SQL Server data provider against a SQL Server 2005 database. This example uses a SqlCeDataAdapter object to do the following:
The example then uses a SqlCeDataReader object to display the rows added to the table from the database. using System; using System.Data; using System.Data.SqlServerCe; class Program { static void Main(string[] args) { // create a data adapter and configure a command builder // to update the database SqlCeDataAdapter da = new SqlCeDataAdapter( "SELECT * FROM TestTable", "data source=TestDb.sdf; database password=password;"); SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da); // retrieve the results from the database into a DataTable DataTable dt = new DataTable( ); da.Fill(dt); // add two rows to the DataTable dt.Rows.Add(new object[] { 1, "Row 1 description" }); dt.Rows.Add(new object[] { 2, "Row 2 description" }); // update the database with the new rows da.Update(dt); // create a connection for the data reader SqlCeConnection conn = new SqlCeConnection( "data source=TestDb.sdf; database password=password;"); conn.Open( ); // create the data reader SqlCeCommand cmd = new SqlCeCommand( "SELECT * FROM TestTable", conn); SqlCeDataReader dr = cmd.ExecuteReader( ); // output the rows to the console while (dr.Read( )) Console.WriteLine(dr["ID"] + ", " + dr["Description"]); // clean up dr.Close( ); conn.Close( ); Console.WriteLine(Environment.NewLine + "Press any key to continue."); Console.ReadKey( ); } } The console output is shown in Figure 21-1. Figure 21-1. Results from reading and updating data example 21.3.5. Error HandlingA SqlCeException object is created when a data provider for SQL Server mobile encounters an error. These exceptions are handled in a typical manner. The following example catches a SqlCeException object, raised because a nonexistent table is queried, and returns details about the exception: using System; using System.Data.SqlServerCe; class Program { static void Main(string[] args) { SqlCeConnection conn = new SqlCeConnection( "data source=TestDb.sdf; database password=password;"); conn.Open( ); SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Table1", conn); try { SqlCeDataReader dr = cmd.ExecuteReader( ); } catch (SqlCeException ex) { foreach (SqlCeError sce in ex.Errors) { Console.WriteLine("HResult = {0:X}", sce.HResult); Console.WriteLine("Message = {0}", sce.Message); Console.WriteLine("NativeError = {0:X}", sce.NativeError); Console.WriteLine("Source = {0}", sce.Source); Console.WriteLine( ); } } finally { conn.Close( ); } Console.WriteLine("Press any key to continue."); Console.ReadKey( ); } } The console output is shown in Figure 21-2. Figure 21-2. Results from error handling example The SqlCeException class inherits from the Exception class and adds the several properties described in Table 21-4.
|
Registered Server Types 
EAN: 2147483647
Pages: 147