Filling a DataSet by Using the SqlCeDataAdapterThe 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:
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
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
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 tableC# 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 EventErrors 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
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 eventC# 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 |