Creating and Using DataAdapter Objects
DataAdapter Reference
The DataAdapter object offers properties, methods, and events to meet your every need. Let's meet them now.
Properties of the DataAdapter Object
You can divide the DataAdapter object's properties into two groups—those that control communication with the data store and those that control communication with the DataSet.
The properties that control communication with the data store are the ones that contain Command objects: the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties. These properties contain Command objects that the DataAdapter executes when you want to move information back and forth between a DataSet and your data store—to retrieve rows into your DataSet or to submit changes stored in a DataSet to your data store. Table 5-1 describes the properties.
| Property | Data Type | Description |
| AcceptChangesDuringFill | Boolean | Determines the RowState of the rows retrieved by the DataAdapter. (Default = True.) |
| ContinueUpdateOnError | Boolean | Controls whether the DataAdapter will continue to submit changes if it encounters an error. (Default = False.) |
| DeleteCommand | OleDbCommand | Command used to submit pending deletions. |
| InsertCommand | OleDbCommand | Command used to submit pending insertions. |
| MissingMappingAction | MissingMappingAction | Controls the DataAdapter object's behavior when fetching columns that do not appear in the TableMappings collection. (Default = Passthrough.) |
| MissingSchemaAction | MissingSchemaAction | Controls the DataAdapter object's behavior when fetching columns that do not appear in the DataTable object's Columns collection. (Default = Add.) |
| SelectCommand | OleDbCommand | Command used to query database and fetch results into a DataSet or DataTable. |
| TableMappings | DataTableMappingCollection | Collection of information the DataAdapter uses to map the results of the query to the DataSet. |
| UpdateCommand | OleDbCommand | Command used to submit pending updates. |
SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand
Each of these properties of the DataAdapter stores a Command object. The specific object type will depend on the .NET Data Provider you're using. For example, an OleDbDataAdapter object's SelectCommand property contains an OleDbCommand object, and a SqlDataAdapter object's SelectCommand property contains a SqlCommand object. (The Command object is discussed in more detail in Chapter 4.)
If you supply a query string in the constructor for the DataAdapter, this query string will become the CommandText property for the DataAdapter object's SelectCommand. If you supply a Command rather than just a query string, that Command will be assigned to the DataAdapter object's SelectCommand.
If you supply a Connection in the DataAdapter object's constructor, that Command will be assigned to the Connection property for the DataAdapter object's SelectCommand. If you supply a connection string, the DataAdapter will create a new Connection, sets its ConnectionString property to the string you supplied, and then assign the new Connection to the Connection property of the DataAdapter object's SelectCommand.
TableMappings Property
Earlier in the chapter, you learned that the DataAdapter and the DataSet are completely disconnected from each other in the ADO.NET object model. So how does the DataAdapter know how to communicate with the DataSet? For example, the OleDbDataAdapter and SqlDataAdapter can each accept a DataSet as a parameter in their Update methods. What if the DataSet contains multiple DataTable objects? How will the DataAdapter know which DataTable to examine?
The DataAdapter class has a TableMappings property that contains a collection of DataTableMapping objects. Each DataTableMapping object has a ColumnMappings property that returns a collection of DataColumnMapping objects. This hierarchy of objects corresponds to the collection of DataTable objects and DataColumn objects in your DataSet.
When the DataAdapter retrieves data from your data store, it uses the information in the TableMappings collection to determine where in your DataSet to store the results of your query. Generally speaking, if you fetch the results of the query
SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers
you'll want to create a DataTable named Customers that contains DataColumn objects whose names correspond to the columns in the results of the query. If you want to provide alternative names for the DataTable or any of its DataColumn objects, you'll want to populate the DataAdapter object's TableMappings collection with the desired mapping information.
The following code is an example of populating a DataAdapter object's TableMappings collection based on the query. With each TableMapping and ColumnMapping, the first string corresponds to the name of the item that the DataAdapter retrieves from the database, and the second string corresponds to the name of the item in the DataSet.
Visual Basic .NET
Dim strSQL, strConn As String strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers" strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim da As New OleDbDataAdapter(strSQL, strConn) With da.TableMappings.Add("Table", "Customers").ColumnMappings .Add("CustomerID", "CustomerID") .Add("CompanyName", "CompanyName") .Add("ContactName", "ContactName") .Add("Phone", "Phone") End With
Visual C# .NET
string strSQL, strConn; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers"; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.TableMappings.Add("Table", "Customers"); DataColumnMappingCollection cm = da.TableMappings[0].ColumnMappings; cm.Add("CustomerID", "CustomerID"); cm.Add("CompanyName", "CompanyName"); cm.Add("ContactName", "ContactName"); cm.Add("Phone", "Phone"); You can also use the AddRange method of the DataTableMappingCollection or DataColumnMappingCollection object to add multiple items to the collection in a single call, as shown here:
Visual Basic .NET
Dim cm As CommonDataColumnMapping = da.TableMappings(0).ColumnMappings cm.AddRange(New Common.DataColumnMapping() _ {New Common.DataColumnMapping("CustomerID", "CustomerID"), _ New Common.DataColumnMapping("CompanyName", "CompanyName"), _ New Common.DataColumnMapping("ContactName", "ContactName"), _ New Common.DataColumnMapping("Phone", "Phone")})
Visual C# .NET
DataColumnMappingCollection cm = da.TableMappings[0].ColumnMappings; cm.AddRange(new DataColumnMapping[] {new DataColumnMapping("CustomerID", "CustomerID"), new DataColumnMapping("CompanyName", "CompanyName"), new DataColumnMapping("ContactName", "ContactName"), new DataColumnMapping("Phone", "Phone")}); What if the query the DataAdapter executes contains information that does not appear in the DataAdapter object's TableMappings collection for the DataTable in your DataSet? By default, the DataAdapter will assume that you want to retrieve this information and store it in your table. In fact, you could execute the following code:
Visual Basic .NET
Dim strSQL, strConn As String strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers" strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet da.Fill(ds) Console.WriteLine(ds.Tables("Table").Rows(0)("CustomerID").ToString)
Visual C# .NET
string strSQL, strConn; strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers"; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet; da.Fill(ds); Console.WriteLine(ds.Tables["Table"].Rows[0]["CustomerID"].ToString());
MissingMappingAction and MissingSchemaAction Properties
Notice that we did not populate the DataAdapter object's TableMappings collection. In fact, we did not even create a DataTable in our DataSet; the DataAdapter did that automatically.
When the DataAdapter fetches the results of your query, it looks for corresponding tables and columns in its TableMappings collection. The MissingMappingAction property of the DataAdapter controls its behavior in situations where the DataAdapter retrieves tables or columns that don't correspond to entries in the TableMappingsCollection. By default, this property is set to Passthrough, but you can set it to the other values in the MissingMappingAction enumeration, which is in the System.Data namespace. Setting MissingMappingAction to Ignore will cause the DataAdapter to ignore tables and columns that do not appear in the TableMappings collection. If you set MissingMappingAction to Error, you'll receive an exception if the query contains tables or columns that do not appear in the DataAdapter object's TableMappings collection.
The DataAdapter also has a MissingSchemaAction property that controls the behavior of the DataAdapter if the tables or columns in the results of the query do not appear in the destination DataSet. By default, MissingSchemaAction is set to Add, which forces the DataAdapter to add the expected tables and columns to the DataSet. You can set the property to other values in the MissingSchemaAction enumeration in System.Data—such as AddWithKey, Ignore, and Error. Setting MissingSchemaAction to Ignore causes the DataAdapter to ignore tables and columns that do not appear in the DataSet, and setting the property to Error will generate an exception in the same scenario, just as the MissingMappingAction property does.
Setting MissingSchemaAction to AddWithKey will add the missing tables and columns to the DataSet but will also add key information for the table. This behavior is similar to calling the FillSchema method of the DataAdapter, a feature I'll cover shortly.
AcceptChangesDuringFill Property
When I worked as a support engineer helping developers who were having problems using ADO, I was amazed at how many developers were trying to use ADO as some sort of data synchronization tool. They would query one database and then point their Recordset at a different database and call Update, expecting that ADO would synchronize the tables in the two databases. ADO could not do that. But ADO.NET can...sort of.
The DataAdapter object has an AcceptChangesDuringFill property that accepts a Boolean value. This property, which is set to True by default, controls the RowState of the rows retrieved by the DataAdapter. If the property is set to True, the new DataRow objects will each have a RowState of Unchanged. Setting AcceptChangesDuringFill to False causes the new DataRow objects to have a RowState of New.
This means that if you set AcceptChangesDuringFill to False, you can query a table in one database and then pass the DataSet to a DataAdapter that's set to communicate with another database and insert all of the newly retrieved rows into this other database.
ContinueUpdateOnError Property
If you use a DataAdapter to submit updates to your database, you're relying on optimistic updating. If you fetch the contents of a row, modify that row in your DataSet, and then submit the pending change to the database using a DataAdapter, your update attempt might fail if another user has already changed the contents of the same row in your database. Don't worry; we'll discuss this functionality in depth in Chapter 10 and Chapter 11. For now, just know that when you're an optimist, things don't always work out the way you'd like.
The DataAdapter object's ContinueUpdateOnError property controls how the DataAdapter reacts when it detects that an attempt to submit the pending changes stored in a DataRow fails. By default, this property is set to False, which means that the DataAdapter will stop when it encounters a failed update attempt. If you want the DataAdapter to continue under such circumstances and try to submit the changes stored in the remaining pending DataRows, set this property to True.
Why would you want the DataAdapter to stop when it encounters a failed update attempt? Maybe the pending changes in your DataSet represent an order and the customer does not want to submit a partial order. It's an all-or-nothing proposition. So, you start a transaction before submitting the changes and if one update fails, you roll back the transaction. Under these circumstances, there's no reason to attempt to submit the rest of the changes if an error occurs while submitting changes for a prior row.
Methods of the DataAdapter Object
The DataAdapter has many properties, but it has just four methods, as described in Table 5-2.
| Method | Description |
| Fill | Executes the query stored in the SelectCommand and stores the results in a DataTable |
| FillSchema | Retrieves schema information for the query stored in the SelectCommand |
| GetFillParameters | Returns an array containing the parameters for the SelectCommand |
| Update | Submits changes stored in your DataSet (or DataTable or DataRows) to your database |
Fill Method
Calling the Fill method on a DataAdapter executes the query stored in the DataAdapter object's SelectCommand property and stores the results in a DataTable in your DataSet. The Fill method also returns a 32-bit integer that indicates the number of rows the DataAdapter retrieved.
This quick snippet of sample code shows how to use the Fill method:
Visual Basic .NET
Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() Dim intRowsRetrieved As Integer = da.Fill(ds)
Visual C# .NET
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); int intRowsRetrieved = da.Fill(ds);
The DataAdapter examines the contents of its TableMappings collection to determine which DataTable object(s) and DataColumn object(s) to use in the DataSet you supply. If the DataAdapter does not find the expected schema information in its TableMappings collection or within the DataSet, it checks its MissingMappingAction and MissingSchemaAction properties to determine how to react.
The DataAdapter object's Fill method is overloaded. You can supply a DataTable rather than a DataSet. Or, you can supply a DataSet and a string for the name of the DataTable you want to populate or create, as shown here:
Visual Basic .NET
Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() Dim intRowsRetrieved As Integer intRowsRetrieved = da.Fill(ds, "Customers") intRowsRetrieved = da.Fill(ds.Tables("Customers")) intRowsRetrieved = da.Fill(ds, 11, 10, "Customers")
Visual C# .NET
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); int intRowsRetrieved; intRowsRetrieved = da.Fill(ds, "Customers"); intRowsRetrieved = da.Fill(ds.Tables["Customers"]); intRowsRetrieved = da.Fill(ds, 11, 10, "Customers");
The DataAdapter also has a Fill method that can come in handy if you're building a Web application that has to support paging. Let's say you want to allow users to view the contents of your product catalog 10 items at a time. You can supply a starting row number and the number of rows to retrieve in the DataAdapter object's Fill method:
Visual Basic .NET
Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() Dim intStartingRow As Integer = 10 Dim intRowsToRetrieve As Integer = 10 Dim intRowsRetrieved As Integer intRowsRetrieved = da.Fill(ds, intStartingRow, _ intRowsToRetrieve, "Customers")
Visual C# .NET
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); int intStartingRow = 10; int intRowsToRetrieve = 10; int intRowsRetrieved = da.Fill(ds, intStartingRow, intRowsToRetrieve, "Customers");
The DataAdapter submits the query, and if you say that you want to start fetching at row 10 (as shown in the example), the DataAdapter will simply discard the first 10 rows. The DataAdapter will then fetch the number of rows you've requested. If the query (ignoring the discarded rows) does not contain the number of rows you've requested, the DataAdapter will simply fetch all remaining rows without throwing an exception.
Is this the best way to break up the results of a query into pages? No. Let's say your query returns 100 rows and you break it up into 10 pages of 10 rows each. When you fetch the first page, you simply fetch the first 10 rows. When you fetch the second page, you discard the first 10 rows and fetch the second set of 10 rows. Keep in mind that the database returns 20 rows in order for you to fetch the second page. When you fetch the tenth page, the database has to return all 100 rows. The DataAdapter simply discards the first 90. If that sounds inefficient, that's because it is. So why does the DataAdapter support this feature? Because it's simple.
A more complex but much more efficient way to achieve the same functionality is to store the key value(s) for the last row from the previous page. Say you fetch the first set of 10 rows using the following query:
SELECT TOP 10 CustomerID, CompanyName, ContactName, Phone FROM Customers ORDER BY CustomerID
If the tenth customer has a CustomerID of "BSBEV", the following query retrieves the next 10 rows:
SELECT TOP 10 CustomerID, CompanyName, ContactName, Phone FROM Customers ORDER BY CustomerID WHERE CustomerID > "BSBEV"
Using this type of architecture will improve the performance of your database because it will fetch fewer rows. Your data access code will run faster because it won't need to discard an initial set of rows to get to the desired rows and because the database will perform better.
The OleDbDataAdapter has two more overloaded Fill methods, which can help you leverage your preexisting ADO code. You can fetch the contents of an ADO Recordset into your ADO.NET DataSet, and you can supply a DataSet, a Recordset, and a table name or a DataTable and a Recordset. Here are examples of both syntaxes:
Visual Basic .NET
Dim rs As New ADODB.Recordset() rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient rs.Open(strSQL, strConn) Dim da As New OleDbDataAdapter() Dim ds As New DataSet() Dim intRowsRetrieved As Integer intRowsRetrieved = da.Fill(ds, rs, "Customers") intRowsRetrieved = da.Fill(ds.Tables("Customers"), rs)
Visual C# .NET
Recordset rs = new RecordsetClass(); rs.CursorLocation = CursorLocationEnum.adUseClient; rs.Open(strSQL, strConn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, (int) CommandTypeEnum.adCmdText); OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); int intRowsRetrieved; intRowsRetrieved = da.Fill(ds, rs, "Customers"); intRowsRetrieved = da.Fill(ds.Tables["Customers"], rs);
The DataAdapter provides a way to move data from an ADO Recordset to an ADO.NET DataSet, but there is no feature to move data from a DataSet back to a Recordset.
FillSchema Method
The FillSchema method lets you retrieve schema information about your query before executing it. Like the Fill method, the FillSchema method retrieves names and data types for each of the columns in your query. FillSchema also retrieves information on whether a column can accept Null values and sets the AllowDBNull property of the DataColumn objects it creates accordingly.
To help you determine how to update your data store, the FillSchema method also attempts to generate a primary key on your DataTable. The logic for this operation is somewhat complex, but I'll try to simplify it.
When you call FillSchema, the DataAdapter first asks the data store whether the table referenced in the query contains a primary key. If the table does not contain a primary key, the DataAdapter checks for a unique index.
Once the DataAdapter finds that the table contains a primary key (or unique index if there is no primary key), it checks the results of the query to locate the column(s) specified in the primary key (or unique index). If the DataAdapter locates the column(s), it sets that column or columns as the primary key in the DataTable. Otherwise, the DataAdapter does not create a primary key on the DataTable.
Using the FillSchema method is rather straightforward and will remind you of the Fill method, with one slight difference. As with the Fill method, you can supply a DataSet, a DataSet and a table name, or a DataTable. The slight difference is that the FillSchema method adds a parameter to let you control whether to retrieve schema information straight from the data source or apply the DataAdapter object's TableMappings to the schema information the DataAdapter retrieves.
You can specify either value from the SchemaType enumeration in System.Data—Source or Mapped. If you specify Source, the DataAdapter will generate schema information using just the column names retrieved from the data source. Specifying Mapped will force the DataAdapter to apply the contents of its TableMappings collection, the same way the DataAdapter maps the columns when you call the Fill method. Here are examples of all three ways to call FillSchema:
Visual Basic .NET
Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() 'Supply a DataSet and a SchemaType. da.FillSchema(ds, SchemaType.Source) 'Supply a DataSet, a SchemaType, and a table name. da.FillSchema(ds, SchemaType.Source, "Table") Dim tbl As New DataTable() 'Supply a DataTable and a SchemaType. da.FillSchema(tbl, SchemaType.Source)
Visual C# .NET
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); //Supply a DataSet and a SchemaType. da.FillSchema(ds, SchemaType.Source); //Supply a DataSet, a SchemaType, and a table name. da.FillSchema(ds, SchemaType.Source, "TableName"); DataTable tbl = new DataTable(); //Supply a DataTable and a SchemaType. da.FillSchema(tbl, SchemaType.Source);
The FillSchema method returns an array of DataTable objects that contain the DataTable objects that the FillSchema method populates.
You can call the FillSchema method and reference a DataTable that already exists. In this scenario, the DataAdapter will not overwrite the columns that already appear in the DataTable but will add new columns if the columns that the query returns do not already appear in the DataTable.
GetFillParameters Method
The GetFillParameters method acts as a shortcut to the Parameters collection of the DataAdapter object's SelectCommand, with one minor difference. GetFillParameters returns the parameter information as an array of IParameter objects rather than the Parameter type for the specific .NET Data Provider (such as OleDbParameter or SqlParameter). Unless you need to check or set the parameters' size, precision, or scale properties, you'll be able to access your parameters using the GetFillParameters method, as shown here:
Visual Basic .NET
Dim strSQL As String = "SELECT CustomerID, CompanyName " & _ "FROM Customers WHERE CustomerID LIKE ?" da = New OleDbDataAdapter(strSQL, strConn) da.SelectCommand.Parameters.Append("@CustomerID", _ OleDbType.VarWChar, 5) da.GetFillParameters(0).Value = "ALFKI"
Visual C# .NET
string strSQL = "SELECT CustomerID, CompanyName FROM Customers " + "WHERE CustomerID LIKE ?"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.SelectCommand.Parameters.Append("@CustomerID", OleDbType.VarWChar, 5); da.GetFillParameters[0].Value = "ALFKI"; We discussed Command objects and Parameter objects in further detail in Chapter 4.
Update Method
To submit the pending changes stored in a DataTable or DataSet to your data store, you use the DataAdapter object's Update method.
As with the Fill and FillSchema methods, you can pass a DataSet, a DataSet and a table name, or a DataTable to the Update method. The Update method offers another overloaded method—you can also pass an array of DataRow objects to the Update method. This option can come in handy if you want to pass a subset of rows in a table based on a filter or a relation.
The Update method returns an integer that contains the rows you successfully updated in your data store.
We'll discuss how the Update method submits changes to your data store in detail in Chapter 10.
Visual Basic .NET
Dim da As OleDbDataAdapter Dim ds As DataSet Dim intChangesSubmitted As Integer intChangesSubmitted = da.Update(ds) intChangesSubmitted = da.Update(ds, "TableName") intChangesSubmitted = da.Update(ds.Tables("TableName")) Dim aRows() As DataRow intChangesSubmitted = da.Update(aRows)
Visual C# .NET
OleDbDataAdapter da; DataSet ds; int intChangesSubmitted; intChangesSubmitted = da.Update(ds); intChangesSubmitted = da.Update(ds, "TableName"); intChangesSubmitted = da.Update(ds.Tables["TableName"]); DataRow[] aRows; intChangesSubmitted = da.Update(aRows);
Events of the DataAdapter Object
The DataAdapter object offers only three events, all of which are listed in Table 5-3.
| Event | Description |
| FillError | Fires when the DataAdapter encounters an error filling your DataSet or DataTable |
| RowUpdating | Fires before submitting a modified row to your database |
| RowUpdated | Fires after submitting a modified row to your database |
FillError Event
If the DataAdapter encounters an error when filling your DataSet or DataTable, you might be able to trap for that error using the FillError event, as shown in the following code snippet:
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT TOP 1 OrderID, CustomerID, EmployeeID FROM Orders" Dim da As New OleDbDataAdapter(strSQL, strConn) da.MissingSchemaAction = MissingSchemaAction.Error AddHandler da.FillError, AddressOf da_FillError Dim tbl As New DataTable("Orders") tbl.Columns.Add("OrderID", GetType(Integer)) tbl.Columns.Add("CustomerID", GetType(String)) da.Fill(tbl) Public Sub da_FillError(ByVal sender As Object, _ ByVal e As FillErrorEventArgs) Console.WriteLine(e.Errors.Message) e.Continue = True End Sub
Visual C#. NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT TOP 1 OrderID, CustomerID, EmployeeID FROM Orders"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.MissingSchemaAction = MissingSchemaAction.Error; da.FillError += new FillErrorEventHandler(da_FillError); DataTable tbl = new DataTable("Orders"); tbl.Columns.Add("OrderID", typeof(int)); tbl.Columns.Add("CustomerID", typeof(string)); da.Fill(tbl); static void da_FillError(object sender, FillErrorEventArgs e) { Console.WriteLine(e.Errors.Message); e.Continue = true; } As far as I can tell, you cannot use the FillError event to trap for situations where the data retrieved by the DataAdapter violates a constraint in your DataSet or DataTable.
RowUpdating and RowUpdated Events
The DataAdapter also fires events when submitting pending changes to your database via the DataAdapter.Update method. If you want to examine the pending changes in your row prior to submitting the change, use the RowUpdating event. If you want to execute code immediately after submitting a change, use the RowUpdated event.
The following code snippet demonstrates how to use both of the events.
Visual Basic .NET
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT TOP 1 OrderID, CustomerID, EmployeeID FROM Orders" Dim da As New OleDbDataAdapter(strSQL, strConn) AddHandler da.RowUpdated, AddressOf da_RowUpdated AddHandler da.RowUpdating, AddressOf da_RowUpdating Dim tbl As New DataTable("Orders") da.Fill(tbl) tbl.Rows(0)("EmployeeID") = CInt(tbl.Rows(0)("EmployeeID")) + 1 Dim cb As New OleDbCommandBuilder(da) da.Update(tbl) tbl.Rows(0)("EmployeeID") = CInt(tbl.Rows(0)("EmployeeID")) - 1 da.Update(tbl) Public Sub da_RowUpdating(ByVal sender As Object, _ ByVal e As OleDbRowUpdatingEventArgs) Console.WriteLine("RowUpdating Event: " & e.StatementType.ToString) Console.WriteLine(vbTab & "OrderID = " & e.Row("OrderID")) Console.WriteLine(vbTab & "EmployeeID from: " & _ e.Row("EmployeeID", DataRowVersion.Original)) Console.WriteLine(vbTab & "EmployeeID to : " & e.Row("EmployeeID")) Console.WriteLine() End Sub Public Sub da_RowUpdated(ByVal sender As Object, _ ByVal e As OleDbRowUpdatedEventArgs) Console.WriteLine("RowUpdated Event: " & e.StatementType.ToString) Console.WriteLine(vbTab & "OrderID = " & e.Row("OrderID")) If e.Status = UpdateStatus.ErrorsOccurred Then Console.WriteLine(vbTab & "Errors occurred") Else Console.WriteLine(vbTab & "Success!") End If Console.WriteLine() End Sub
Visual C#. NET
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT TOP 1 OrderID, CustomerID, EmployeeID FROM Orders"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); da.RowUpdated += new OleDbRowUpdatedEventHandler(da_RowUpdated); da.RowUpdating += new OleDbRowUpdatingEventHandler(da_RowUpdating); DataTable tbl = new DataTable("Orders"); da.Fill(tbl); tbl.Rows[0]["EmployeeID"] = (int) tbl.Rows[0]["EmployeeID"] + 1; OleDbCommandBuilder cb = new OleDbCommandBuilder(da); da.Update(tbl); tbl.Rows[0]["EmployeeID"] = (int) tbl.Rows[0]["EmployeeID"] - 1; da.Update(tbl); static void da_RowUpdating(object sender, OleDbRowUpdatingEventArgs e) { Console.WriteLine("RowUpdating Event: " + e.StatementType.ToString()); Console.WriteLine("\tOrderID = " + e.Row["OrderID"]); Console.WriteLine("\tEmployeeID from: " + e.Row["EmployeeID", DataRowVersion.Original]); Console.WriteLine("\tEmployeeID to : " + e.Row["EmployeeID"]); Console.WriteLine(); } static void da_RowUpdated(object sender, OleDbRowUpdatedEventArgs e) { Console.WriteLine("RowUpdated Event: " + e.StatementType.ToString()); Console.WriteLine("\tOrderID = " + e.Row["OrderID"]); if (e.Status == UpdateStatus.ErrorsOccurred) Console.WriteLine("\tErrors occurred"); else Console.WriteLine("\tSuccess!"); Console.WriteLine(); } 
EAN: 2147483647
Pages: 104