The ADO.Net DataAdapter Object
The key to using the DataSet object to its full capability is the DataAdapter. You’ll recall that the DataSet is built usually by downloading data from tables in a dataset on the server. The connection to the server is then closed. The user then can manipulate and update or append data on the client, and at the appropriate time, he can upload those data changes to the server. It is the DataAdapter than that maintains the memory of the connection to the server, and correlates all of the interactive operations between the data on the client and the data on the server.
There are several DataAdapters. Because of the proprietary nature of the server databases, each database needs its own set of drivers to get the most out of that data manager. So there is a SqlDataAdapter, an OleDbDataAdapter, etc. In fact, for each future version of .Net classes which support a separate database manager on the server, there will be a DataAdapter for that database (such as Oracle, Sybase, etc.) For now there is the OleDbDataAdapter, a one-size-fits-all approach for generic server databases. The reason that a different adapter is preferable is because each database manager has a slightly different syntax and grammar and capabilities of manipulating data. For instance , SQL Server will use its T-SQL language to manage data commands, whereas other database managers will not recognize some of the T-SQL commands.
SqlDataAdapter Class Members
Visibility Constructor Parameters
public SqlDataAdapter ( )
public SqlDataAdapter ( SqlCommand selectCommand )
public SqlDataAdapter ( String selectCommandText , String selectConnectionString )
public SqlDataAdapter ( String selectCommandText , SqlConnection selectConnection )
Visibility Name Value Type Accessibility
public AcceptChangesDuringFill Boolean [ Get , Set ]
public Container IContainer [ Get ]
public ContinueUpdateOnError Boolean [ Get , Set ]
public DeleteCommand SqlCommand [ Get , Set ]
public InsertCommand SqlCommand [ Get , Set ]
public MissingMappingAction MissingMappingAction [ Get , Set ]
public MissingSchemaAction MissingSchemaAction [ Get , Set ]
public SelectCommand SqlCommand [ Get , Set ]
public Site ISite [ Get , Set ]
public TableMappings DataTableMappingCollection [ Get ]
public UpdateCommand SqlCommand [ Get , Set ]
Visibility Name Parameters Return Type
public fill (DataSetName, TableName) Void
public updatet (DataSetName, TableName) Integer
Multicast Name Type
multicast Disposed EventHandler
multicast FillError FillErrorEventHandler
multicast RowUpdated SqlRowUpdatedEventHandler
multicast RowUpdating SqlRowUpdatingEventHandler
A new instance of SqlDataAdapter can be constructed in four ways. This is accomplished by overloading the constructor function. The most verbose form is passed a SQL Select statement and a connection string. Variations can be passing a stored procedure instead of a SQL Select command, or the name of a connection object. Alternatively, one can create a new DataAdapter without arguments, and set the properties programmatically later.
Do not use the AcceptChanges method on the DataSet when using the Update method of the DataAdapter. AcceptChanges sets the rowstate indicator to Unchanged, and it is this property which marks rows which need to be reported back to the database on the server. If a row is market Unchanged, there will no way for the DataAdapter to know that its changes need to be reconciled with the server. By default, changes are processed in the order of their primary key. You can manipulate this somewhat by using the DataViewRowtate property and referencing it in the select method of the DataTable property:
dataadapter.Update(datatable.select(Nothing, Nothing, DataViewRowState.Deleted))
In an environment where only one user is updating a table, orm portion of a table, there will at all be no conflicts. As more and more users contend for the same data, the probability of conflicts increase. A conflict occurs when one user ‘checks out’ a updates a record and changes it, while another user does the same thing. When each users’ records are updated on the server, which changes prevail?
In the normal handling, when a table is updated through the dataadapter, all the rows are updated. If an error occurs (a conflict), all the remaining rows updating process is aborted. You need to control this by trapping these errors in a Try/Catch block. You can test the whole dataset using the DataTable’s HasChanges property, or by trapping on the RowUpdated event.
You can also set the DataAdapter’s ContinueUpdateOnErrors property to True to avoid stopping the update process when one row shows a conflict. However, ultimately you will have to resolve the conflicts either by inspection and manual intervention, or programmatically. You can display the conflicting rows using the GetChanges method of the DataTable dt.GetChanges().Rows.Count and dr.RowState. A more refined approach is to use a SELECT command.
When appropriate, you can control the conflict resolution on a row by row basis using program code and avoiding manual intervention, when the business logic of the application can be so defined. You can even enroll the updates as part of a transaction which can be rolled back in case one of the updates fails.
PPT Presentation on ADO.Net and DataAdapter
This resource provides comprehensive information on ADO.Net and DataAdapter.
• Tutorial: ASP. NET Tutorial
This tutorial discusses ASP.Net Database maintenance and Dataadapter in detail.
About the Author: Chris Kemp is a well knoen author who writes best quality articles on IT, Software, Programming, etc. For further details please visit the site www.paladn.com