JavaScript Editor Ajax toolkit     Ajax tutorials 



Main Page

Previous Page
Next Page

21.3. Programming SQL Server Mobile

The 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 Database

This 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).

You can connect to this database in SQL Server Management Studio. From the main menu, select View Registered Server Types SQL Server Mobile . Right-click SQL Server Mobile Edition Databases in the Registered Servers window and select Server Registration from the context menu to open the New Server Registration dialog box. Complete the Database file field with the full path to the TestDb.sdf file and the Password field with the password. Click the Save button to register the mobile database.


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.

Table 21-1. SQL Server Mobile connection string properties

Property

Description

autoshrink threshold

Percent of free space allowed in the database before autoshrink starts. The default value is 60. A value of 100 disables autoshrink.

data source

Name of the SQL Server Mobile database file (.sdf) and, optionally, specifies the absolute path.

database password

Database password up to 40 characters long. If not specified, the default is no password.

A database password cannot be recovered if lost.

default lock timeout

Length of time, in milliseconds, that a transaction will wait for a lock. The default value is 2000.

default lock escalation

Number of locks a transaction will acquire before escalating from row to page or from page to table. The default value is 100.

encrypt database

Boolean value specifying whether the database is encrypted. You must specify a password to enable database encryption. The default value is false.

If the database password is lost, the data cannot be retrieved.

flush interval

Interval before all committed transactions are committed to disk, in seconds. The default value is 10.

locale identifier

Locale ID (LCID) to use with the database.

max buffer size

Largest amount of memory, in kilobytes, that SQL Server Mobile can use before it starts flushing data changes to disk. The default value is 640.

max database size

Maximum size of the database file, in megabytes. The default value is 128.

mode

Specifies how the database is opened. The options are:


Read Write

Opens the database so that other processes can open and modify the database


Read Only

Opens a read-only copy of the database


Exclusive

Opens the database so that other processes cannot open or modify the database


Shared Read

Opens the database so that other processes are allowed read-only access to the database

The default mode is Read Write.

temp file directory

Location of the temporary database. The data source is used for temporary storage if a temporary database is not specified.

temp file max size

Maximum size of the temporary database file, in megabytes. The default value is 128.


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.

Table 21-2. SQL Server Mobile data provider classes

Class

Description

SqlCeCommand

T-SQL statement to execute against a database.

SqlCeCommandBuilder

Automatically creates single-table commands based on a SELECT query. Also used to update a database with changes made to a DataTable or DataSet object using a data adapter.

SqlCeConnection

Connection to the SQL Server Mobile database.

SqlCeDataAdapter

Used to fill a DataTable or DataSet object and subsequently update the database with changes made offline.

SqlCeDataReader

Provides access to a result set as a forward-only stream of data rows.

SqlCeEngine

Represents the SQL Server Mobile Database Engine. Used to create, modify, and manage a SQL Server Mobile database.

SqlCeError

Information about a specific SqlCeException object returned by the SQL Server Mobile data provider.

SqlCeErrorCollection

Collection of all errors generated by the SQL Server Mobile data provider.

SqlCeException

The exception raised when the provider returns a warning or error from the SQL Server Mobile database.

SqlCeFlushFailureEventArgs

Data for a flush failure (FlushFailure) event.

SqlCeFlushFailureEventHandler

The method that handles the FlushFailure event.

SqlCeInfoMessageEventArgs

Data for a warning (InfoMessage) event from the database.

SqlCeInfoMessageEventHandler

The method that handles the InfoMessage event.

SqlCeLockTimeoutException

The exception raised when a lock timeout occurs.

SqlCeParameter

A parameter to a SQL command (SqlCeCommand).

SqlCeParameterCollection

A collection of parameter (SqlCeParameter) objects and their mappings to columns.

SqlCeRemoteDataAccess

A remote data access instance.

SqlCeReplication

A replication instance.

SqlCeResultSet

An updateable, bindable, scrollable cursor.

SqlCeRowUpdatedEventArgs

Data for the row updated (RowUpdated) event that occurs when a row in the database is updated using a data adapter.

SqlCeRowUpdatedEventHandler

The method that handles the RowUpdated event.

SqlCeRowUpdatingEventArgs

Data for the row updating (RowUpdating) event that occurs before a row in the database is updated using a data adapter.

SqlCeRowUpdatingEventHandler

The method that handles the RowUpdating event.

SqlCeTransaction

A SQL transaction.

SqlCeTransactionInProgressException

The exception raised when an attempt is made to modify a database while a transaction is in progress.

SqlCeUpdatableRecord

A row of updateable data from the database. The SqlCeResult set contains a collection of SqlCeUpdatableRecord objects.


21.3.2. Maintaining a Database

The SqlCeEngine class public properties and methods used to create and manage SQL Server Mobile databases are described in Table 21-3.

Table 21-3. SqlCeEngine class properties and methods

Constructor

Description

SqlCeEngine

Takes an optional argument specifying the connection string to the SQL Server Mobile database.

Property

 

LocalConnectionString

The connection string to the SQL Server Mobile database. The connection string properties are described in Table 21-1.

Methods

 

Compact( )

Reclaims space in the database file and changes properties of the database specified in the local connection string.

CreateDatabase( )

Creates a new database.

Repair( )

Attempts to repair a corrupted database.

Shrink( )

Reclaims space in the database file.

Verify( )

Verifies that the database is not corrupted.


The examples in this section show how to maintain a SQL Server Mobile database using the SqlCeEngine class.

21.3.2.1. Verifying and repairing

This 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 space

The 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 properties

The 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 restoring

SQL 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 Objects

Because 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 Data

This 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:

  • Retrieve the contents of the table named TestTable into a DataTable object. Because TestTable has no rows, the DataTable object will have no rows.

  • Add two rows to the DataTable object.

  • Update the SQL Server Mobile database with the new rows.

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 Handling

A 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.

Table 21-4. SqlCeException class properties

Property

Description

Errors

A collection of SqlCeError objects, each containing details about an exception generated by the SQL Server Mobile data provider.

HResult

The hrESULTa numeric value that corresponds to a specific exception. This corresponds to the value of the HResult property for the first SqlCeError object in the SqlCeErrorCollection collection returned by the Errors property.

InnerException

Inherited from Exception class.

Message

The description for the first SqlCeError object in the SqlCeErrorCollection collection returned by the Errors property.

NativeError

The native error number for the first SqlCeError object in the SqlCeErrorCollection collection returned by the Errors property.

Source

The name of the provider that caused the exception. This corresponds to the value of the Source property for the first SqlCeError object in the SqlCeErrorCollection collection returned by the Errors property.

StackTrace

Inherited from Exception class.



Previous Page
Next Page


JavaScript Editor Ajax toolkit     Ajax tutorials 
Cable Ties . Video deposition services