Print
 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, wher eas 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

February 11, 2008

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 thr 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.