Filling a DataSet by Using the SqlCeDataAdapter


Filling a DataSet by Using the SqlCeDataAdapter

The Compact Framework also provides the ability to load data directly from SQL Server CE into a DataSet . This is done by using the SqlCeDataAdapter to fill the DataSet . The SqlCeDataAdapter can fill the DataSet and then update the underlying database with any changes. In other words, the DataSet can manage all communication between your application and the SQL Server CE database.

The SqlCeDataAdapter manages the underlying database by running four different commands against the database when synchronization is required. These four commands are exposed as properties on the SqlCeDataAdapter . They are the SelectCommand property, the InsertCommand property, the UpdateCommand property, and the DeleteCommand property. All of the properties are of the SqlCeCommand type, and you can set them to any command you choose, or you can use the SqlCeCommandBuilder class to generate the commands.

The SelectCommand property is the SqlCeCommand object that defines the SQL command the SqlCeDataAdapter will use to retrieve data from the SQL Server CE database. The SqlCeDataAdapter will then use this data to populate the DataSet .

Precisely, the fill process consists of three steps:

  1. Building the DataSet schema and mappings

  2. Retrieving the data

  3. Populating the DataSet

First, the SqlCeDataAdapter initializes the schema of the DataSet to match the schema of the data source. This means that DataTable s are built to match the source database tables as well as building DataColumn s that match the source database table columns . The relationships between DataSet and source database are known as mappings because they map DataSet objects to database objects. Next, the data is retrieved from the source database by using the SelectCommand property. Finally, the DataRows are created for the retrieved data, and the rows are inserted into the DataTable s.

Before we investigate the code to fill a DataSet , let's look at a few of the properties of SqlCeDataAdapter that should be set before we attempt to populate the DataSet . These properties will determine how the SqlCeDataAdapter will fill the DataSet .

The MissingMappingAction property determines what action to take when a mapping is missing from a source table or a source column. Table 7.9 describes the values of the MissingMappingAction enumeration and how the SqlCeDataAdapter will handle the missing mapping.

Table 7.9. MissingMappingAction Values

VALUE

DESCRIPTION

Error

An InvalidOperationException is generated if the specified column mapping is missing.

Ignore

The column or table not having a mapping is ignored.

Passthrough (Default)

The source column or source table is created and added to the DataSet by using its source name .

The MissingSchemaAction property determines the action to take when existing DataSet schema does not match incoming data. Table 7.10 describes the values of the MissingSchemaAction enumeration and how the SqlCeDataAdapter will handle the missing schema objects.

Table 7.10. MissingSchemaAction Values

VALUE

DESCRIPTION

Add (Default)

Adds the missing columns to complete the schema

AddWithKey

Adds the missing columns and primary key information to complete the schema

Error

Generates an InvalidOperationException if the schema is incomplete

Ignore

Ignores the missing columns

By default, the SqlCeDataAdapter will use the names of the column as they are in the source database ( MissingMappingAction.Passthrough ), and the SqlCeDataAdapter will add any new columns to the DataSet ( MissingSchemaAction.Add ).

The code to fill a DataSet by using the SqlCeDataAdapter is very simple. Listing 7.9 demonstrates how to fill a DataSet with the contents of the sample Package table by using the SqlCeDataAdapter .

Listing 7.9 Filling a DataSet with the contents of the sample Package table
 C# public static DataSet GetPackageDataSet() {   string connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connstr)) {     conn.Open();     string dmlPackageInfo = "SELECT * FROM Package";     SqlCeDataAdapter daPackages = new SqlCeDataAdapter();     daPackages.MissingMappingAction = MissingMappingAction.Passthrough;     daPackages.MissingSchemaAction = MissingSchemaAction.Add;     daPackages.SelectCommand = new SqlCeCommand(dmlPackageInfo, conn);     DataSet dsPackages = new DataSet();     daPackages.Fill(dsPackages);     return dsPackages; } VB Function GetPackageDataSet() As DataSet   Dim connstr As String   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   Dim dmlPackageInfo As String   Dim daPackages As SqlCeDataAdapter   dmlPackageInfo = "SELECT * FROM Package"   daPackages = New SqlCeDataAdapter   daPackages.MissingMappingAction = MissingMappingAction.Passthrough   daPackages.MissingSchemaAction = MissingSchemaAction.Add   daPackages.SelectCommand = New SqlCeCommand(dmlPackageInfo, conn)   Dim dsPackages As DataSet   dsPackages = New DataSet   daPackages.Fill(dsPackages)   conn.Close()   Return dsPackages End Function 

First, create and open a connection to the SQL Server CE database. Then, create a SqlCeDataAdapter object and set the MissingMappingAction and MissingSchemaAction properties. Set the properties to the default. In practice this is unnecessary; it is done here just to demonstrate the use of the properties. Next, set the SelectCommand to a SqlCeCommand object that will select all of the data from the Package table. Finally, create a DataSet object and call SqlCeDataAdapter.Fill method to populate the DataSet with the Package data.

Handling the FillError Event

Errors may occur while the SqlCeDataAdapter is filling the DataSet with data. If an error does occur, the SqlCeDataAdapter will raise a FillError event. The FillError event provides a user with information that should be examined and used to decide if the fill operation should continue. The FillError event may be raised when errors occur that pertain to filling the DataSet object. For example, a piece of data could not be converted to the DataColumn 's Common Language Runtime type. A FillError will not be raised if the error is generated at the data source.

To handle this event, you must provide an implementation of the FilllErrorEventHandler delegate. This delegate receives a parameter of the FillErrorEventArgs type. The FillErrorEventArgs object contains properties that help decide whether the fill operation should continue. Table 7.11 lists the properties and their descriptions.

Table 7.11. FillErrorEventArgs Properties

PROPERTY

DESCRIPTION

Continue

Gets or sets a value indicating whether the fill should continue in light of the error

DataTable

Gets the DataTable being filled when the error occurred

Errors

Displays the errors that were generated

VALUES

Gets the values for the row being filled when the error occurred

When handling a FillError event, you should evaluate the properties of the FillErrorEventArgs parameter and then set the Continue property to signal whether the fill operation should continue. Listing 7.10 demonstrates how to handle the FillError event.

Listing 7.10 Handling the FillError event
 C# protected static void OnFillError(object sender, FillErrorEventArgs args) {   if( typeof(System.OverflowException) == args.Errors.GetType() ) {     // Handle the conversion precision loss     // The fill should continue     args.Continue = true;   } } VB Private Shared Sub _ OnFillError(ByVal sender As Object, ByVal args As FillErrorEventArgs)   If TypeOf args.Errors Is System.OverflowException Then     ' Handle the conversion precision loss     ' The fill should continue     args.Continue = True   End If End Sub 


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