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
Constructors
Visibility Constructor Parameters

public SqlDataAdapter ( )

public SqlDataAdapter ( SqlCommand selectCommand )

public SqlDataAdapter ( String selectCommandText , String selectConnectionString )

public SqlDataAdapter ( String selectCommandText , SqlConnection selectConnection )



Properties
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 ]


Methods
Visibility Name Parameters Return Type

public fill (DataSetName, TableName) Void

public updatet (DataSetName, TableName) Integer


Events
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))

Resolving Conflicts
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.
Resources
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