Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:DataAdapters – Part 4

Posted by Ravi Varma Thumati on November 17, 2009

The DataAdapter class serves as a bridge between a disconnected ADO.NET objects and a data source. The DataAdapter retrieves data into a DataSet or a DataTable from a data source using the Fill( ) method. Schema information can be retrieved using the FillSchema( ) method. The DataAdapter updates any changes made to the DataSet or DataTable back to the data source using the Update( ) method.

In simple scenarios, the updating logic that the DataAdapter uses to reconcile changes made to the DataSet can be generated automatically from the query used to retrieve the data by using a CommandBuilder object. For more complex scenarios, custom update logic can be written to control the logic the DataAdapter uses when adding, deleting, and modifying records in the data source in response to changes made to the DataSet. In either case, the updating logic is used when the Update( ) method is called.

Once data is retrieved using the DataAdapter, no information about the connection, database, tables, columns, or any other details about the source of the data is available in the disconnected objects. The data can be persisted or passed between applications without the risk of exposing details about the location or structure of the data source or access credentials used. Figure 14-1 shows the structure of the DataAdapter and the contained classes.

Figure 14-1. The DataAdapter class

Creating DataAdapter Object

The overloaded constructor for the DataAdapter allows four different ways to create the data adapter, of which two are most commonly used. The following example creates a DataAdapter specifying the SELECT statement and connection string in the constructor.

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql = “SELECT * FROM Orders”;

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

While this approach is common, it is awkward when using parameterized queries or stored procedures. The following example creates a DataAdapter specifying a Command object for the SelectCommand property of the DataAdapter in the constructor:

// create the Connection

String connString = “Data Source = (local);Integrated security = SSPI;” +

“Initial Catalog = Northwind;”;

SqlConnection conn = new SqlConnection(connString);

// create a Command object based on a stored procedure

String selectSql = “MyStoredProcedure”;

SqlCommand selectCmd = new SqlCommand(selectSql, conn);

selectCmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(selectCmd);

It should be noted that there is no best way to create a DataAdapter, and it makes no real difference how it is created.

Retrieving Data from the Data Source

The Fill( ) method of the DataAdapter retrieves data from the data source into a DataSet or a DataTable. When the Fill( ) method for the data adapter is called, the select statement defined in the SelectCommand is executed against the data source and retrieved into a DataSet or DataTable. In addition to retrieving data, the Fill( ) method retrieves schema information for columns that don’t exist. This schema that it retrieves from the data source is limited to the name and data type of the column. If more schema information is required, the FillSchema( ) method, described later in this chapter, can be used. The following example shows how to use the Fill( ) method to retrieve data from the Orders table in the Northwind database:

// connection string and the select statement

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSQL = “SELECT * FROM Orders”;

SqlDataAdapter da = new SqlDataAdapter(selectSQL, connString);

// create a new DataSet to receive the data

DataSet ds = new DataSet();

// read all of the data from the orders table and loads it into the

// Orders table in the DataSet

da.Fill(ds, “Orders”);

A DataTable can also be filled similarly:

// … code to create the data adapter, as above

// create the DataTable to retrieve the data

DataTable dt = new DataTable(“Orders”);

// use the data adapter to load the data into the table Orders

da.Fill(dt);

Notice that a connection object is never opened and closed for the data adapter. If the connection for the data adapter isn’t open, the DataAdapter opens and closes it as required. If the connection is already open, the DataAdapter leaves the connection open.

The same set of records can be retrieved more efficiently using a stored procedure. Stored procedures have a number of benefits over SQL statements:

  • Stored procedures allow business logic for common tasks to be consistently implemented across applications. The stored procedure to perform a task can be designed, coded, and tested. It can then be made available to any client that needs to perform the task. The SQL statements to perform the task need to be changed in only one place if the underlying business logic changes. If the parameters for the stored procedure don’t change, applications using the stored procedure will not even need to be recompiled.
  • Stored procedures can improve performance in situations where a group of SQL statements are executed together with conditional logic. A stored procedure allows a single execution plan to be prepared for the SQL statements together with the conditional logic. Rather than having the client submit a series of SQL statements based on client-side conditional logic, both the SQL statements and conditional logic are executed on the server, requiring only one round trip. Additionally, when a stored procedure is executed, only the parameters need to be transmitted to the server rather than the entire SQL statement.
  • Stored procedures are more secure. Users can be granted permission to execute stored procedures that perform required business functions rather than having direct access to the database tables.
  • Stored procedures provide a layer of abstraction for the data, making performing business function more intuitive and, at the same time, hiding database implementation from the users.

The following example shows the stored procedure used to select records from the Orders table in the Northwind database. The stored procedure takes a CustomerID parameter that results in only orders for that customer being retrieved.

// the stored procedure

CREATE PROCEDURE GetOrders

@CustomerID nchar(5)

AS

SET NOCOUNT ON

SELECT * FROM Orders WHERE CustomerId=@CustomerID

RETURN

The code to retrieve the data using the stored procedure has some differences compared with the code using the SQL statements directly. The CommandText property of the SelectCommand is set to the name of the stored procedure rather than to a SQL statement. The CommandType is set to StoredProcedure rather than specifying or accepting the default value of Text. The following example illustrates retrieving orders for a specific customer using a stored procedure:

// connection string and the stored procedure

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql = “GetOrders”;

// create a DataSet to receive the data

DataSet ds = new DataSet();

SqlConnection conn = new SqlConnection(connString);

// create a command object based on the stored procedure

SqlCommand selectCmd = new SqlCommand(selectSql, conn);

selectCmd.CommandType = CommandType.StoredProcedure;

// create and set the CustomerID parameter for the stored procedure

selectCmd.Parameters.Add(“@CustomerID”, SqlDbType.NChar, 5);

selectCmd.Parameters[“@CustomerID”].Value = “VINET”;

// create and fill the DataSet

SqlDataAdapter da = new SqlDataAdapter(selectCmd);

da.Fill(ds, “Orders”);

The same result could be accomplished with a parameterized query, as shown in the following example:

// connection string and parameterized query

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql = “SELECT * FROM Orders WHERE CustomerID=@CustomerID”;

DataSet ds = new DataSet();

SqlConnection conn = new SqlConnection(connString);

// create a command object based on the SQL select statement

SqlCommand selectCmd = new SqlCommand(selectSql, conn);

// create and set the CustomerID parameter for the select statement

selectCmd.Parameters.Add(“@CustomerID”, SqlDbType.NChar, 5);

selectCmd.Parameters[“@CustomerID”].Value = “VINET”;

// create and fill the DataSet

SqlDataAdapter da = new SqlDataAdapter(selectCmd);

da.Fill(ds, “Orders”);

There are several options available to load more than one table into the same DataSet using a DataAdapter:

  • The Fill( ) method can be called several times on the same DataAdapter, specifying a different DataTable in the same DataSet. The SelectCommand is modified to select the records for a different table each time Fill( ) is called.
  • Multiple DataAdapter objects, each returning one table, can be created. Fill( ) is called on each DataAdapter, specifying the appropriate DataTable in the same DataSet.
  • Either a batch query or a stored procedure that returns multiple result sets can be used.

In the last option, the DataAdapter automatically creates the required tables and assigns them the default names Table, Table1, Table2, if a table name isn’t specified. If a table name is specified, for example MyTable, the DataAdapter names the tables MyTable, MyTable1, MyTable2, and so on. The tables can be renamed after the fill, or table mapping can map the automatically generated names to names of the underlying tables in the DataSet. The following example shows how to use a batch query with a DataAdapter to create two tables in a DataSet:

// connection string and batch query

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql = “SELECT * FROM Customers;” +

” SELECT * FROM Orders”;

// create the data adapter

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

// create and fill the DataSet

DataSet ds = new DataSet();

da.Fill(ds);

The DataSet is filled with two tables named Table and Table1, respectively, containing data from the Customers and the Orders tables in data source.

Finally, the DataAdapter provides an overloaded Fill( ) method that retrieves a subset of rows from the query and loads them into the DataSet. The starting record and maximum number of records are specified to define the subset. For example, the following code statement retrieves the first 10 records and inserts them into a DataTable named Categories:

da.Fill(ds, 0, 10, “Categories”);

It is important to realize that this method actually performs the original query and retrieves the full set of results. It then discards those records that aren’t in the specified range. As a result, this approach performs poorly when selecting from large result sets. A better approach is to limit the amount of data that must be transferred over the network and the work that must be performed by the data source by fine-tuning a SQL SELECT statement using a TOP n or WHERE clause.

Retrieving Schema Information from the Data Source

Schema information can be retrieved from a data source using the FillSchema( ) method, which retrieves the schema information for the SQL statement in the SelectCommand. The method adds a DataTable to the DataSet and adds DataColumn objects to that table. Finally, it configures the AllowDBNull, AutoIncrement, MaxLength, ReadOnly, and Unique properties of the DataColumn, based on the data source. While it configures the AutoIncrement property, it doesn’t set the AutoIncrementSeed and AutoIncrementStep properties. The FillSchema( ) method also configures the primary key and unique constraints for the DataTable. It doesn’t configure the DefaultValue property.

In addition to an argument specifying the DataSet argument, the FillSchema( ) method takes an argument specifying whether the schema is transformed by the table mappings for the data adapter. Mapping tables and columns is discussed in more detail later in this chapter

If the FillSchema( ) method is used with a table that already has schema defined, the original schema isn’t overwritten. Rather, new columns are added if they are part of the schema retrieved but don’t exist in the table.

Finally, if a query returning multiple result sets is specified in the SelectCommand, only the schema from the first result set is used. To fill schemas based on queries with multiple result sets, use the Fill( ) method with the MissingSchemaAction set to AddWithKey.

The following example demonstrates the FillSchema method:

// connection and select command strings

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql = “SELECT * FROM Orders”;

// create the data adapter

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

// create a new DataSet to receive the table schema

DataSet ds = new DataSet();

// read the schema for the Orders table from the data source and

// create a table in the DataSet called “Orders” with the same schema

da.FillSchema(ds, SchemaType.Source, “Orders”);

// create a new DataTable to receive the schema

DataTable dt = new DataTable(“Orders”);

da.FillSchema(dt, SchemaType.Source);

As with the Fill( ) method, the DataAdapter connection must be valid, but doesn’t have to be open. If it is closed when FillSchema( ) is called, it is automatically opened to retrieve the data and then closed. If it is open when FillSchema( ) is called, it is left open after the data is retrieved.

Updating the Data Source

The Update( ) method can submit DataSet changes back to the data source. It uses the statements in the DeleteCommand, InsertCommand, and UpdateCommand objects to attempt to update the data source with records that have been deleted, inserted, or updated in the DataSet. Each row is updated individually and not as part of a batch process. Furthermore, the order in which the rows are processed is determined by the indexes on the DataTable and not by the update type. Figure 14-2 illustrates how the DataAdapter is used both to reconcile changed data in the DataSet with the data source using the Update() method and to retrieve data from the data source using the Fill() method.

Figure 14-2. Retrieving and updating data using the DataAdapter

The delete, insert, and update statements can be automatically generated using the CommandBuilder object, but this is probably not the best approach for production systems. Alternatively, custom update logic can be used where the DeleteCommand, InsertCommand, and UpdateCommand are each defined. Compared with using the CommandBuilder, custom logic can significantly improve performance and can implement solutions to complex updating and conflict-resolution scenarios.

The following example demonstrates the Update( ) method. For simplicity, a CommandBuilder generates the update logic.

// connection and select command strings

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql=”SELECT * FROM Orders”;

// create a new DataSet to receive the data

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

// create the command builder

// this creates SQL statements for the DeleteCommand, InsertCommand,

// and UpdateCommand properties for the data adapter based on the

// select command that the data adapter was initialized with

SqlCommandBuilder cb = new SqlCommandBuilder(da);

// read all of the data from the orders table and load it into the

// Orders table in the DataSet

da.Fill(ds, “Orders”);

// … code to modify the data in the DataSet

// update the data in the Orders table in the DataSet to the data source

da.Update(ds, “Orders”);

As with the Fill( ) and FillSchema( ) methods, opening and closing the connection are performed by the data adapter, if necessary.

Mapping Tables and Columns

By default, when you use the DataAdapter to fill a DataSet, the column names that are used in the DataSet correspond to the column names defined in the data source.

The data adapter has a collection of table-mapping objects that are accessed through the TableMappings property. A table-mapping object maps a table in the data source to a table with a different name in the DataSet. Table mappings are perhaps most commonly used to map default table names that are created as a result of filling a DataSet from a query that returns multiple result sets. When multiple result sets are added to a DataSet using the Fill( ) method of the DataAdapter, they are assigned the default names Table, Table1, Table2, and so on. Mapping the table names ensures that the data is updated to the correct tables in the data source. Of course, the table objects can also be renamed with the same result.

Each table-mapping object has a collection of column-mapping objects that are accessed through the ColumnMappings property. A column-mapping object maps a column in the data source to a column with a different name in the DataSet, within the table defined by the containing table-mapping object. Figure 14-3 shows how tables and columns in the DataSet and data source are mapped to each other using mapping classes in the DataAdapter.

Figure 14-3. Table and column mapping classes

Both table and column mappings can be used by the Fill( ) and FillSchema( ) methods when retrieving data, and by the Update( ) method when submitting DataSet changes back to the data source. The Fill( ) method always uses mapping information, if present. The FillSchema( ) method lets you choose whether to use mapping information.

The following example shows how to set up a table mapping and a column mapping:

SqlDataAdapter da;

// … code to set up the data adapter

// map the DataSet table MyOrders to the data source table Orders

DataTableMapping dtm = da.TableMappings.Add(“Orders”, “MyOrders”);

// map the DataSet column MyOrderID (in the DataSet MyOrders table)

// to the data source column OrderID (in the data source Orders table)

dtm.ColumnMappings.Add(“MyOrderID”, “OrderID”);

If incoming data source table and column names don’t match DataSet object names and mapping can’t be performed, the MissingMappingAction property of the DataAdapter determines what action is taken when data is retrieved using the Fill( ) method. The default value Passthrough results in the creation of the missing objects in the DataSet. The MissingMappingAction property can also be set to ignore missing objects or to raise an exception if missing objects are encountered.

Similarly, the MissingSchemaAction property of the DataAdapter determines what action is taken when the schema is retrieved using the FillSchema( ) or Fill( ) method. As with the MissingMappingAction property, options allow the missing objects to be added, ignored, or to cause an exception to be raised. Additionally, the MissingSchemaAction can be set to add the missing objects along with the primary key information.

AcceptChangesDuringFill

The AcceptChangesDuringFill( ) controls whether AcceptChanges( ) is implicitly called on new rows when they are added to a DataTable by the Fill( ) method. If AcceptChangesDuringFill is true, the rows added as a result of the Fill( ) have a RowState of Unchanged after they are added to the DataSet. If AcceptChangesDuringFill is false, the RowState of the newly added rows is Added. The following example demonstrates this:

// connection and select command strings

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

String selectSql = “SELECT * FROM Orders”;

// create a new DataSet to receive the data

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

da.Fill(ds, “Orders”);

// each row in the Orders table has RowState = Unchanged

ds.Tables[“Orders”].Clear();

da.AcceptChangesDuringFill = false;

da.Fill(ds, “Orders”);

// each row in the Orders table has RowState = Inserted

// manually call AcceptChanges

ds.AcceptChanges();

// each row in the Orders table has RowState = Unchanged

The default value for AcceptChangesDuringFill is true. Because the records already exist in the data source, the records retrieved during the Fill( ) operation should not be considered new records when the data source is eventually updated with the DataSet.

Setting AcceptChangesDuringFill to false can be useful, for example, to transfer data between data sources. Records retrieved from a data source are marked as New, and the DataSet can then insert these records into another data source using the Update( ) method of a DataAdapter.

ContinueUpdateOnError

The ContinueUpdateOnError property controls whether an Update( ) continues with remaining rows or stops processing if an error is encountered during the updating. If ContinueUpdateOnError is true, and an error is encountered during the update, an exception isn’t raised, the RowError property of the DataRow causing the error is set to the error message that would have been raised, and the update continues processing the remaining rows. A well-designed application uses the RowError information to present the user with a list of the failed and possibly the current values in the data source for those rows. It also provides a mechanism to correct and resubmit the failed attempts, if required.

If ContinueUpdateOnError is false, the DataAdapter raises a DBConcurrencyException when a row update attempt fails. Generally, ContinueUpdateOnError is set to false when the changes made to the DataSet are part of a transaction and must be either completely applied to the data source or not applied at all. The exception handler rolls back the transaction.

DataAdapter Events

The FillError event is most commonly raised when the data being added violates a constraint in the DataSet or when the data being added can’t be converted to a .NET Framework data type without a loss of precision. When a FillError event occurs, the current row isn’t added to the DataTable. Handling the FillError event allows the error to be resolved and the row to be either added or ignored before resuming the Fill( ) operation with the next row.

The FillError event handler receives an argument of FillErrorEventArgs, which contains specific data about the event that can effectively respond to and handle the error. The Continue property of the FillErrorEventArgs argument determines whether an exception is thrown or processing continues because of the error.

The following example demonstrates handling the FillError event when filling a table containing three columns:

SqlDataAdapter da;

// … code to set up the data adapter

da.FillError += new FillErrorEventHandler(da_FillError);

DataSet ds = new DataSet();

da.Fill(ds, “MyTable”);

private void da_FillError(object sender, FillErrorEventArgs e)

{

// … code to identify and correct the error

// add the fixed row to the table

DataRow dr = e.DataTable.Rows.Add(new object[] {e.Values[0],

e.Values[1], e.Values[2]});

// continue the Fill with the rows remaining in the data source

e.Continue = true;

}

The RowUpdating event is raised before changes to a row are submitted to the data source. The RowUpdating event handler can modify update behavior, providing additional handling or canceling the update for the row. The RowUpdated event is raised after the update command is executed against the data source and is used to respond to exceptions that occur during the update.

The RowUpdating and RowUpdated event handlers receive arguments of SqlRowUpdatingEventArgs and SqlRowUpdatedEventArgs, respectively, containing data specific to each event. The arguments contain among other properties, a reference to the Command object that performs the update, a DataRow object containing the updated data, a StatementType property containing the type of update being performed, an Errors property containing any errors generated, and a Status property. The Status property returns a value of ErrorsOccurred if an error occurred while updating the row. The Status property can control the action to be taken with the current and remaining rows to be updated after an error; an error can be thrown, the current row can be skipped, or all remaining rows can be skipped by setting the Status property after an error.

The following code demonstrates handling the RowUpdating and RowUpdated events:

SqlDataAdapter da;

// … code to set up the data adapter

// add the event handlers

da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);

da.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);

DataSet ds = new DataSet();

// … code to fill the DataSet

// … code to modify the data in the DataSet

da.Update(ds, “Orders”);

private void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)

{

// Write the date, OrderID, and type of update to a log

System.IO.TextWriter tw = System.IO.File.AppendText(“update.log”);

tw.WriteLine(“{0}: Order {1} {2}.”, DateTime.Now, e.Row[“OrderID”,

DataRowVersion.Original], e.StatementType.ToString());

tw.Close();

}

private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)

{

if(e.Status == UpdateStatus.ErrorsOccurred)

{

// set the error information for the row

e.Row.RowError = e.Errors.Message;

// skip peocessing the current row and continue with the rest

e.Status = UpdateStatus.SkipCurrentRow;

}

}

An alternative to processing each error in response to the RowUpdated event as shown in this example is to set the DataAdapter ContinueUpdateOnError property to true, allowing all errors to be handled once the update of all rows is complete.

Advertisements

One Response to “ADO.NET Core Classes:DataAdapters – Part 4”

  1. There was clearly this time when i had been bored to death reading other people’s opinions and mind via their blogging. But just after a while when i got right here on your articles ., the love of reading and smiling on people’s daily post seem to come back, my personal curiosity has peaked again all thanks to you my good friend. I think you will continue this a long time. Excellent work my good friend.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: