DataAdapter In C#

ADO.NET DataAdapter

How is ADO.NET like ADO? Like ADO, ADO.NET has a Connection object that we use to manage our connection to our data source. Like ADO, ADO.NET also has a Command object. The Command object is used to submit SQL statements or to execute stored procedures. Like the ADO Command object, the ADO.NET Command object accepts parameters and supports re-executing compiled commands. Things start to get different when we look at the ADO.NET DataSet object. In many ways, the DataSet object looks like a weird amalgam of all of the old DAO objects holding tables, relations, and columns.

The ADO Recordset object held a collection of records that we could scroll through. The ADO.NET DataSet can do the same but can hold several sets of records and the relationships between them. The ADO.NET DataSet is like a portable database containing tables and views along with the data description information that defines them. We can consider an ADO.NET DataSet to be an in-memory database that we hold in our application's memory space (see the sidebar, "The DataReader," for the one exception to ADO.NET's separation of client and server).

Theoretical overview of DataAdapter

The completely new object in the ADO.NET world is the DataAdapter. The purpose of the DataAdapter is embedded in its name: It performs the activities necessary to get the data from the data source on the server into the database that's held in the DataSet. To do that, the DataAdapter lets us specify the commands that should be carried out to retrieve and update data.

In ADO, if we used a client-side Recordset, our data was disconnected from the data source. This had lots of advantages, not the least of which was that we could close our application's connection to the data source and still work with the data. This would free up the connection so that it could be used by other applications, improving our application's scalability.

Disconnected Recordsets also made applications more scalable in other ways. In addition to reducing the load on the data server by reducing the number of active connections, the data server's load was also reduced because activities performed on disconnected data had no impact on the server. In order to transmit changes back to the data source, we used ADO's UpdateBatch method. The process was straightforward: We created a disconnected Recordset; we made changes to the data; we called the UpdateBatch method; and enchantment happened. If we had carefully crafted SQL statements or stored procedures, it didn't matter. UpdateBatch performed our updates any way that it saw fit. Some of Microsoft's documentation suggests that our original Recordset was re-created on the server, and the records scrolled through, reconciling the changes between the Recordset from the client and its half-sibling on the server, a very inefficient process.

The DataAdapter improves on this process. The object provides four properties that allow us to control how updates are made to the server: SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand. The four properties are set to Command objects that are used when data is manipulated.

For instance, when we call the DataAdapter's Fill method to retrieve data from a data source and pour it into a DataSet, the Command object in the SelectCommand property is used. The DataAdapter is the gatekeeper that sits between our DataSet and the data source. Instead of using Command objects directly with a Connection, the DataAdapter manages our Command objects as they interact with the data source.

DataAdapter.Fill Method

Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table".

public abstract int Fill    
(    
   DataSet dataSet;    
)

The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property. The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, and then closed. If the connection is open before Fill is called, it remains open.

The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys, and constraints are also created.

If the data adapter encounters duplicate columns while populating a DataTable, it generates names for the subsequent columns, using the pattern "columnname1", "columnname2", "columnname3", and so on. If the incoming data contains unnamed columns, they are placed in the DataSet according to the pattern "Column1", "Column2", and so on. When multiple result sets are added to the DataSet each result set is placed in a separate table. Additional result sets are named by appending integral values to the specified table name (for example, "Table", "Table1", "Table2", and so on.). Applications should use caution when using column and table names to ensure that conflicts with these naming patterns does not occur.

When the SELECT statement used to populate the DataSet returns multiple results, such as a batch SQL statements, if one of the results contains an error, all subsequent results are skipped and not added to the DataSet. We can use the Fill method multiple times on the same DataTable. If a primary key exists, incoming rows are merged with matching rows that already exist. If no primary key exists, incoming rows are appended to the DataTable.

Note - When handling batch SQL statements that return multiple results, the implementation of FillSchema for the OLE DB .NET Data Provider retrieves schema information for only the first result. To retrieve schema information for multiple results, use Fill with the MissingSchemaAction set to AddWithKey.

Populating a DataSet using DataAdapter

The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.

Each .NET data provider included with the .NET Framework has a DataAdapter object: the OLE DB .NET Data Provider includes an OleDbDataAdapter object and the SQL Server .NET Data Provider includes a SqlDataAdapter object. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET data provider to connect to a data source, and Command objects to retrieve data from and resolve changes to the data source.

The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet. The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.

The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema. Column types are created as .NET Framework types according to the tables in Mapping .Net Data Provider Data Types to .NET Framework Data Types. Primary keys are not created unless they exist in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey. If Fill finds that a primary key exists for a table, it will overwrite data in the DataSet with data from the data source for rows where the primary key column values match those of the row returned from the data source. If no primary key is found, the data is appended to the tables in the DataSet. Fill uses any TableMappings that may exist when populating the DataSet.

The following code example creates an instance of a DataAdapter that uses a Connection to the Microsoft SQL Server Northwind database and populates a DataTable in a DataSet with the list of customers. The SQL statement and Connection arguments passed to the DataAdapter constructor are used to create the SelectCommand property of the DataAdapter.

SqlClient example

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");  
SqlCommand selectCMD = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);  
selectCMD.CommandTimeout = 30;  
SqlDataAdapter customerDA = new SqlDataAdapter();  
customerDA.SelectCommand = selectCMD;  
nwindConn.Open();  
DataSet customerDS = new DataSet();  
customerDA.Fill(customerDS, "Customers");  
nwindConn.Close();

OleDb Example

OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +"Integrated Security=SSPI;Initial Catalog=northwind");  
OleDbCommand selectCMD = new OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn);  
selectCMD.CommandTimeout = 30;  
OleDbDataAdapter customerDA = new OleDbDataAdapter();  
customerDA.SelectCommand = selectCMD;  
DataSet customerDS = new DataSet();  
customerDA.Fill(customerDS, "Customers");

Note that the code does not explicitly open and close the Connection. The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it will also close the connection when Fill is finished. This can simplify our code when dealing with a single operation such as a Fill or an Update. However, if we are performing multiple operations that require an open connection, we can improve the performance of our application by explicitly calling the Open method of the Connection, performing the operations against the data source, then calling the Close method of the Connection. We should strive to keep connections to the data source open for a minimal amount of time to free up the resource to be used by other client applications.

Multiple Result Sets

If the DataAdapter encounters multiple result sets, it will create multiple tables in the DataSet. The tables will be given an incremental default name of TableN, starting with "Table" for Table0. If a table name is passed as an argument to the Fill method, the tables will be given an incremental default name of TableNameN, starting with "TableName" for TableName0.

Populating a DataSet from Multiple DataAdapters

Any number of DataAdapters can be used in conjunction with a DataSet. Each DataAdapter can be used to fill one or more DataTable objects and resolve updates back to the relevant data source. DataRelation and Constraint objects can be added to the DataSet locally, enabling we to relate data from multiple dissimilar data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed via OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication to each data source.

The following code example populates a list of customers from the Northwind database on Microsoft SQL Server 2000, and a list of orders from the Northwind database stored in Microsoft Access 2000. The filled tables are related with a DataRelation, and the list of customers is then displayed with the orders for that customer.

SqlConnection custConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind;");  
SqlDataAdapter customerDA = new SqlDataAdapter("SELECT * FROM Customers", ustConn);  
OleDbConnection orderConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\\Program Files\\Microsoft Office\\Office\\Samples\\northwind.mdb;");  
OleDbDataAdapter orderDA = new OleDbDataAdapter("SELECT * FROM Orders", orderConn);  
custConn.Open();  
orderConn.Open();  
DataSet customerDS = new DataSet();  
customerDA.Fill(customerDS, "Customers");  
orderDA.Fill(customerDS, "Orders");  
custConn.Close();  
orderConn.Close();  
DataRelation custOrderRel = customerDS.Relations.Add("CustOrders", customerDS.Tables["Customers"].Columns["CustomerID"], customerDS.Tables["Orders"].Columns["CustomerID"]);  
foreach(DataRow pRow in customerDS.Tables["Customers"].Rows)   
{  
    Console.WriteLine(pRow["CustomerID"]);  
    foreach(DataRow cRow in pRow.GetChildRows(custOrderRel))  
    Console.WriteLine("\t" + cRow["OrderID"]);  
}

SQL Server Decimal Type

The DataSet stores data using .NET Framework data types. For most applications, these provide a convenient representation of data source information. However, this representation may cause a problem when the data type in the data source is a SQL Server decimal. The .NET Framework decimal data type allows a maximum of 28 significant digits, while the SQL Server decimal data type allows 38 significant digits. If the SqlDataAdapter determines, during a Fill operation, that the precision of a SQL Server decimal field is greater than 28 characters, the current row will not be added to the DataTable. Instead the FillError event will occur, which enables you to determine if a loss of precision will occur, and respond appropriately. For more information about the FillError event, see Working with DataAdapter Events. To get the SQL Server decimal value, you can also use a SqlDataReader object and call the GetSqlDecimal method.

OLE DB Chapters

Hierarchical rowsets, or chapters (OLE DB type DBTYPE_HCHAPTER, ADO type adChapter) can be used to fill the contents of a DataSet. When the DataAdapter encounters a chaptered column during a Fill operation, a DataTable is created for the chaptered column, and that table is filled with the columns and rows from the chapter. The table created for the chaptered column is named using both the parent table name and the chaptered column name in the form "ParentTableNameChapteredColumnName". If a table already exists in the DataSet that matches the name of the chaptered column, the current table is filled with the chapter data. If there is no column in an existing table that matches a column found in the chapter, a new column is added.

Before the tables in the DataSet are filled with the data in the chaptered columns, a relation is created between the parent and child tables of the hierarchical rowset by adding an integer column to both the parent and child table, setting the parent column to auto-increment and creating a DataRelation using the added columns from both tables. The added relation is named using the parent and child column names in the form "ParentColumnName_ChildColumnName". Note that the related column only exists in the DataSet. Subsequent fills from the data source will result in new rows being added to the tables rather than changes being merged into existing rows.

Remember also that, if you use the DataAdapter.Fill overload that takes a DataTable, only that table will be filled. An auto-incrementing integer column will still be added to the table, but no child table will be created or filled, and no relation will be created.

The following example uses the MSDataShape Provider to generate a chapter column of orders for each customer in a list of customers. A DataSet is then filled with the data.

OleDbConnection nwindConn = new OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" +  
"Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");  
OleDbDataAdapter customerDA = new OleDbDataAdapter("SHAPE {SELECT CustomerID, CompanyName FROM Customers} " +  
" APPEND ({SELECT CustomerID, OrderID FROM Orders} AS Orders " +  
" RELATE CustomerID TO CustomerID)", nwindConn);  
DataSet customerDS = new DataSet();  
customerDA.Fill(customerDS, "Customers");

Updating the Database using DataAdapter and the DataSet

The Update method of the DataAdapter is called to resolve changes from a DataSet back to the data source. The Update method, like the Fill method, takes as arguments an instance of a DataSet, and an optional DataTable object or DataTable name. The DataSet instance is the DataSet that contains the changes that have been made, and the DataTable identifies the table from which to retrieve the changes.

When you call the Update method, the DataAdapter analyzes the changes that have been made and executes the appropriate command (INSERT, UPDATE, or DELETE). When the DataAdapter encounters a change to a DataRow, it uses the InsertCommand, UpdateCommand, or DeleteCommand to process the change. This allows you to maximize the performance of your ADO.NET application by specifying command syntax at design-time and, where possible, through the use of stored procedures. You must explicitly set the commands before calling Update. If Update will call and the appropriate command does not exist for a particular update, than an exception will be thrown (for example, no DeleteCommand for deleted rows).

Command parameters can be used to specify input and output values for an SQL statement or stored procedure for each modified row in a DataSet. For more information, see Using Parameters with a DataAdapter. If your DataTable maps to or is generated from a single database table, you can take advantage of the CommandBuilder object to automatically generate the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter. For more information, see Automatically Generated Commands.

The Update method will resolve your changes back to the data source, however other clients may have modified data at the data source since the last time you filled the DataSet. To refresh your DataSet with current data, use the DataAdapter and Fill the DataSet again. New rows will be added to the table, and updated information will be incorporated into existing rows.

To handle exceptions that may occur during an Update, you can use the RowUpdated event to respond to row update errors when they occur or you can set DataAdapter.ContinueUpdateOnError to true before calling Update, and respond to the error information stored in the RowError property of a particular row when the Update is completed. Calling AcceptChanges on the DataSet, DataTable, or DataRow will cause all Original values for a DataRow to be overwritten with the Current values for the DataRow. If the field values have been modified that identify the row as unique, after calling AcceptChanges the Original values will no longer match the values in the data source.

The following examples demonstrate how to perform updates to modified rows by explicitly setting the UpdateCommand of the DataAdapter. Notice that the parameter specified in the WHERE clause of the UPDATE statement is set to use the Original value of the SourceColumn. This is important, because the Current value may have been modified and may not match the value in the data source. The Original value is the value that was used to populate the DataTable from the data source.

SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);   
catDA.UpdateCommand = new SqlCommand("UPDATE Categories SET CategoryName = @CategoryName " +  
"WHERE CategoryID = @CategoryID" , nwindConn);  
catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");  
SqlParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID",   
qlDbType.Int);  
workParm.SourceColumn = "CategoryID";  
workParm.SourceVersion = DataRowVersion.Original;  
DataSet catDS = new DataSet();  
catDA.Fill(catDS, "Categories");   
DataRow cRow = catDS.Tables["Categories"].Rows[0];  
cRow["CategoryName"] = "New Category";  
catDA.Update(catDS);

In this article, we learned about ADO.NET Data Adapter and how C# DataAdapter class is used to work with a database connection and a DataSet to get data.

ADO.NET DataView represents a view of data. Learn next >> ADO,NET DataView Control in C#