Updating the Microsoft SQL Server CE Database by Using the SqlCeDataAdapterOnce 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 databaseC# 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.
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 SqlDataAdapterC# 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 EventsWhen 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
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
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
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 eventsC# 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 |