Updating the Microsoft SQL Server CE Database by Using the SqlCeDataAdapter

Updating the Microsoft SQL Server CE Database by Using the SqlCeDataAdapter

Once a DataSet has been populated by using the SqlCeDataAdapter , you can make changes to the data and then update the data source by using the same SqlCeDataAdapter . In order for the SqlCeDataAdapter to update the data source, you must specify three additional SqlCommand objects for the SqlCeDataAdapter 's UpdateCommand , InsertCommand , and DeleteCommand properties.

These commands will need to be parameterized commands so that the SqlCeDataAdapter will be able to update individual rows in the data source tables. To learn more about parameterized SQL commands, see the section titled "Using Parameterized SQL Commands" in this chapter. Listing 7.11 demonstrates how to create a SqlCeDataAdapter that will handle filling and updating a DataSet that is mapped to the Package table.

Listing 7.11 Using the SqlCeDataAdapter to update a database

 C# public static SqlCeDataAdapter GetPackageDataAdapter(SqlCeConnection conn){   string dmlPackageInfo = "SELECT * FROM Package";   string dmlUpdatePackage="UPDATE Package " +                           "SET CODE = ?, " +                           "    DestinationID = ? " +                           "WHERE ID = ?";   string dmlInsertPackage="INSERT INTO " +                           "Package(Code, DestinationID) " +                           "VALUES (?, ?)";   string dmlDeletePackage="DELETE FROM " +                           "Package " +                           "WHERE ID = ?";   SqlCeDataAdapter daPackages = new SqlCeDataAdapter();   daPackages.SelectCommand = new SqlCeCommand(dmlPackageInfo, conn);   daPackages.UpdateCommand = new SqlCeCommand(dmlUpdatePackage, conn);   daPackages.UpdateCommand.Parameters.Add("Code", SqlDbType.NVarChar);   daPackages.UpdateCommand.Parameters.Add("DestinationID",     SqlDbType.NVarChar);   daPackages.UpdateCommand.Parameters.Add("ID", SqlDbType.Int);   daPackages.InsertCommand = new SqlCeCommand(dmlInsertPackage, conn);   daPackages.InsertCommand.Parameters.Add("Code", SqlDbType.NVarChar);   daPackages.InsertCommand.Parameters.Add("DestinationID",     SqlDbType.NVarChar);   daPackages.DeleteCommand = new SqlCeCommand(dmlDeletePackage, conn);   daPackages.DeleteCommand.Parameters.Add("ID", SqlDbType.Int);   return daPackages; } VB Function _ GetPackageDataAdapter(ByVal conn As SqlCeConnection) _ As SqlCeDataAdapter   Dim dmlPackageInfo As String   Dim dmlUpdatePackage As String   Dim dmlInsertPackage As String   Dim dmlDeletePackage As String   dmlPackageInfo = "SELECT * FROM Package"   dmlUpdatePackage = "UPDATE Package " & _                      "SET CODE = ?, " * _                      "    DestinationID = ? " & _                      "WHERE ID = ?"   dmlInsertPackage = "INSERT INTO " & _                      "Package(Code, DestinationID) " & _                      "VALUES (?, ?)"   dmlDeletePackage = "DELETE FROM " & _                      "Package " & _                      "WHERE ID = ?"   Dim daPackages As SqlCeDataAdapter   daPackages = New SqlCeDataAdapter   daPackages.SelectCommand = New SqlCeCommand(dmlPackageInfo, conn)   daPackages.UpdateCommand = New SqlCeCommand(dmlUpdatePackage, conn)   daPackages.UpdateCommand.Parameters.Add("Code", SqlDbType.NVarChar)   daPackages.UpdateCommand.Parameters.Add("DestinationID",     SqlDbType.NVarChar)   daPackages.UpdateCommand.Parameters.Add("ID", SqlDbType.Int)   daPackages.InsertCommand = New SqlCeCommand(dmlInsertPackage, conn)   daPackages.InsertCommand.Parameters.Add("Code", SqlDbType.NVarChar)   daPackages.InsertCommand.Parameters.Add("DestinationID",     SqlDbType.NVarChar)   daPackages.DeleteCommand = New SqlCeCommand(dmlDeletePackage, conn)   daPackages.DeleteCommand.Parameters.Add("ID", SqlDbType.Int)   Return daPackages End Function 

The SqlCeDataAdapter updates the data source when you call its Update method. The Update method performs five steps when updating the data source.

1. The values to be updated are loaded from the DataRow object into the relevant command parameters.

2. The RowUpdating event is raised.

3. The relevant command is executed against the data source.

4. The RowUpdated event is raised.

5. The RowSet property of the DataRow is reset to RowState.Unchanged by calling the AcceptChanges method.

We will discuss the RowUpdating and RowUpdated events shortly, but let's take a look at some code that will update a DataSet. Listing 7.12 demonstrates how to update the Package table by using the SqlDataAdapter and a DataSet populated with the data from the Package table. This code uses the GetPackageDataAdapter method from Listing 7.11.

Listing 7.12 Updating the Package table by using the SqlDataAdapter

 C# public static void UpdatePackageTable(DataSet dsPackages) {   string connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connstr)) {     conn.Open();     SqlCeDataAdapter daPackages = GetPackageDataAdapter(conn);     daPackages.Update(dsPackages);   } } VB Sub UpdatePackageTable(ByVal dsPackages As DataSet)   Dim connstr As String   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   Dim daPackages As SqlCeDataAdapter   daPackages = GetPackageDataAdapter(conn)   daPackages.Update(dsPackages) End Sub 

Handling SqlCeDataAdapter Update Events

When you call the Update method on the SqlCeDataAdapter , there are two events raised: the RowUpdating event and the RowUpdated event. The RowUpdating event is raised before the Update command is executed against the data source. The RowUpdated event is raised after the Update command is executed against the data source.

The RowUpdating event can be handled by providing an implementation of the SqlCeRowUpdatingEventHandler delegate. The delegate receives a SqlCeRowUpdatingEventArgs object that contains data about the Update command that is about to be updated. Table 7.12 describes the properties of SqlCeRowUpdatingEventArgs and how they affect the update process.

Table 7.12. Properties of the SqlCeRowUpdatingEventArgs Class

PROPERTY	DESCRIPTION
Command	Gets or sets the SqlCommand to execute
Errors	Gets any errors generated when the command executes
Row	Gets the DataRow that is going to be sent through the Update command
StatementType	Gets the type of SQL statement that will be executed
Status	Gets the status of the Update command (see Table 7.13 for values)
TableMappings	Gets the DataTableMapping that will be sent through the Update command

When you receive a RowUpdating event, you should evaluate the properties of the SqlCeRowUpdatingEventArgs object and decide whether the update should continue. This is done by setting the value of the Status property. The Status property is of UpdateStatus . UpdataStatus is an enumeration, and its members are described in Table 7.13.

Table 7.13. Members of the UpdateStatus Enumeration

MEMBER	DESCRIPTION
Continue	The SqlCeDataAdapter should continue updating the DataRows .
ErrorsOccurred	The event handler reports that the update should be treated as an error. Examine the errors before deciding how to continue.
SkipAllRemainingRows	The remaining rows as well as the current row will not be updated.
SkipCurrentRow	The remaining rows will be updated, but the changes to the current row will be abandoned .

After a row is updated, the RowUpdated event is raised. You can handle this event by providing an implementation of the SqlCeRowUpdatedEventHandler delegate. This delegate receives an instance of the SqlCeRowUpdatedEventArgs as a parameter. Table 7.14 describes the properties of the SqlCeRowUpdatedEvent and their effects on the updating process.

Table 7.14. Properties of the SqlCeRowUpdatedEventArgs Class

PROPERTY	DESCRIPTION
Command	Gets or sets the SqlCommand that was executed
Errors	Gets any errors generated when the command was executed
RecordsAffected	Gets the number of rows changed, inserted, or deleted by the command
Row	Gets the DataRow that was sent through the Update command
StatementType	Gets the type of SQL statement that was executed
Status	Gets the status of the Update command (see Table 7.13 for values)
TableMappings	Gets the DataTableMapping that was sent through the Update command

Listing 7.13 gives examples of how to implement delegates that handle the RowUpdating and RowUpdated events. The listing uses the GetPackageDataAdapter method from Listing 7.11.

Listing 7.13 Handling the RowUpdating and RowUpdated events

 C# protected static void OnRowUpdating(object sender,   SqlCeRowUpdatingEventArgs args) {   MessageBox.Show("RowUpdating Event Status: " + args.Status); } protected static void OnRowUpdated(object sender,   SqlCeRowUpdatedEventArgs args) {   MessageBox.Show("RowUpdated Event Status: " + args.Status); } public static void UpdatePackageTable(DataSet dsPackages) {   string connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connstr)) {     conn.Open();     SqlCeDataAdapter daPackages = GetPackageDataAdapter(conn);     daPackages.RowUpdating +=       new SqlCeRowUpdatingEventHandler(OnRowUpdating);     daPackages.RowUpdated += new SqlCeRowUpdatedEventHandler(OnRowUpdated);     daPackages.Update(dsPackages);     daPackages.RowUpdating -=       new SqlCeRowUpdatingEventHandler(OnRowUpdating);     daPackages.RowUpdated -=       new SqlCeRowUpdatedEventHandler(OnRowUpdated);   } } VB Private Shared Sub _ OnRowUpdating(ByVal sender As Object, _               ByVal args As SqlCeRowUpdatingEventArgs)   MessageBox.Show("RowUpdating Event Status: " & args.Status) End Sub Private Shared Sub _ OnRowUpdated(ByVal sender As Object, _              ByVal args As SqlCeRowUpdatedEventArgs)   MessageBox.Show("RowUpdated Event Status: " & args.Status) End Sub Sub UpdatePackageTable(ByVal dsPackages As DataSet)   Dim connstr As String   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   Dim daPackages As SqlCeDataAdapter   daPackages = GetPackageDataAdapter(conn)   AddHandler daPackages.RowUpdating, AddressOf OnRowUpdating   AddHandler daPackages.RowUpdated, AddressOf OnRowUpdated   daPackages.Update(dsPackages)   RemoveHandler daPackages.RowUpdating, AddressOf OnRowUpdating   RemoveHandler daPackages.RowUpdated, AddressOf OnRowUpdated   conn.Close() End Sub