A managed data provider exposes four classes that enable a data consumer to access the provider's data source. Although these classes are specific to the provider, they derive from abstract ADO.NET classes:

Because these are abstract classes, the developer is responsible for specifying the vendor's specific implementation within the code. As we see next, the object names can be hard coded or provided generically by a provider factory class.

Each data provider registers a ProviderFactory class and a provider string in the machine.config file. The available providers can be listed using the static GetFactoryClasses method of the DbProviderFactories class. As this code shows, the method returns a DataTable containing four columns of information about the provider.

DataTable tb = DbProviderFactories.GetFactoryClasses();
foreach (DataRow drow in tb.Rows )
{
   StringBuilder sb = new StringBuilder("");
   for (int i=0; i<tb.Columns.Count; i++)
   {
      sb.Append((i+1).ToString()).Append(drow[i].ToString());
      sb.Append("
");
   }
   Console.WriteLine(sb.ToString());
}

Running this code for ADO.NET 2.0 lists four Microsoft written providers: Odbc, OleDb, OracleClient, and SqlClient. Figure 11-2 shows output for the SqlClient provider.

Figure 11-2. Data provider information returned by GetFactoryClasses()

To use these providers, your code must create objects specific to the provider. For example, the connection object for each would be an OdbcConnection, OleDbConnection, OracleConnection, or SqlConnection type. You can create the objects supplied by the providers directly:

SqlConnection conn = new SqlConnection();
SqlCommand cmd     = new SqlCommand();
SqlDataReader dr   = cmd.ExecuteReader();

However, suppose your application has to support multiple data sources. A switch/case construct could be used, but a better—and more flexible—approach is to use a class factory to create these objects dynamically based on the provider selected. ADO.NET provides just that—a DbProviderFactory class that is used to return objects required by a specific data provider. It works quite simply. A string containing the provider name is passed to the GetFactory method of the DbProviderFactories class. The method returns a factory object that is used to create the specific objects required by the provider. Listing 11-1 demonstrates using a factory.

// System.Data.Common namespace is required
DbProviderFactory factory ;
string provider = "System.Data.SqlClient";  // data provider
string connstr = "Data Source=MYSERVER;Initial Catalog=films;
           User Id=filmsadmin;Password=bogart;";
// Get factory object for SQL Server
factory = DbProviderFactories.GetFactory(provider);
// Get connection object. using ensures connection is closed.
using (DbConnection  conn = factory.CreateConnection())
{
   conn.ConnectionString = connstr;
   try
   {
      conn.Open();
      DbCommand cmd = factory.CreateCommand(); // Command object
      cmd.CommandText = "SELECT * FROM movies WHERE movie_ID=8" ;
      cmd.Connection = conn;
      DbDataReader dr;
      dr = cmd.ExecuteReader();
      dr.Read();
      MessageBox.Show((string)dr["movie_Title"]);
      conn.Close();
   }
   catch (DbException ex)
   { MessageBox.Show(ex.ToString()); }
   catch (Exception ex)
   { MessageBox.Show(ex.ToString()); }
   finally { conn.Close(); }
}

This approach requires only that a provider and connection string be provided. For example, we can easily switch this to an ODBC provider by changing two statements:

string provider= "System.Data.Odbc";
// The DSN (Data Source Name) is defined using an ODBC utility
string connstr = "DSN=movies;Database=films";

Note that the factory class provides a series of Create methods that returns the objects specific to the data provider. These methods include CreateCommand, CreateConnection, and CreateDataAdapter.

The connection string specifies the data source and necessary information required to access the data source, such as password and ID. In addition to this basic information, the string can include values for fields specific to a data provider. For example, a SQL Server connection string can include values for Connection Timeout and Packet Size (size of network packet).

Table 11-2 offers a representative list of commonly used connection strings.

"server=MYSERVER;
uid=filmsadmin;
pwd=bogart;
database=films;"

"Data Source=MYSERVER;User ID=filmsadmin;
password=bogart;Initial Catalog=films;"

"server=MYSERVER;
database=films;
Trusted_Connection=yes"

"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\movies.mdb;"

string path=
   Server.MapPath("/data/movies.mdb");
Data Source="+path+";"

The connection string is used to create the connection object. This is typically done by passing the string to the constructor of the connection object.

string cn= "Data Source=MYSERVER;Initial Catalog=films;
            User Id=filmsadmin;Password=bogart;";
SqlConnection conn = new SqlConnection(cn);
conn.Open();   // Open connection

A connection string can also be built using a safer, object-oriented manner using one of the ConnectionStringBuilder classes supplied by a managed data provider.^[2] As this code demonstrates, the values comprising the connection string are assigned to properties of this class. Internally, the object constructs a string from these properties and exposes it as a ConnectionString property.

SqlConnectionStringBuilder sqlBldr = new
      SqlConnectionStringBuilder();
scb.DataSource    = "MYSERVER";
// Or scp["Data Source"] = "MYSERVER";
sqlBldr.Password  = "bogart";
sqlBldr.UserID    = "filmsadmin";
sqlBldr.InitialCatalog = "films";
SqlConnection conn = new
SqlConnection(sqlBldr.ConnectionString);
conn.Open();

The ConnectionStringBuilder object is also useful for applications that input the connection string from a configuration file or other source. Setting the ConnectionString property to the connection string value exposes members that control the behavior of the connection. Table 11-3 lists selected properties of the SqlConnectionStringBuilder class.

Core Note

For demonstration purposes, the connection strings in these examples are shown as cleartext within the application's code. In reality, a connection string should be stored outside of an application's assembly. For Web applications, the Web.Config file is often a reasonable choice. As described in Chapter 17, “The ASP.NET Application Environment,” .NET includes a special configuration section to hold connection strings and supports techniques to encrypt the configuration information.

Desktop applications that access a central database can store the information on the client's machine (in the registry or a configuration file) or have it downloaded as part of an application's startup. The latter approach provides better scalability and security, particularly if the server returns a connection object rather than the string.

Creating a connection is a time-consuming process—in some cases taking longer than the subsequent commands take to execute. To eliminate this overhead, ADO.NET creates a pool of identical connections for each unique connection string request it receives. This enables future requests with that connection string to be satisfied from the pool, rather than by reconnecting to the server and performing the overhead to validate the connection.

There are several rules governing connection pooling that you should be aware of:

Under SQL Server, you control the behavior of connection pooling by including key-value pairs in the connection string. These keywords can be used to set minimum and maximum numbers of connections in the pool, and to specify whether a connection is reset when it is taken from the pool. Of particular note is the Lifetime keyword that specifies how long a connection may live until it is destroyed. This value is checked when a connection is returned to the pool. If the connection has been open longer than its Lifetime value, it is destroyed.

This code fragment demonstrates the use of these keywords for SqlClient:

cnString = "Server=MYSERVER;Trusted_Connection=yes;
      database=films;"          +
      "connection reset=false;" +
      "connection Lifetime=60;" +  // Seconds
      "min pool size=1;"        +
      "max pool size=50";           // Default=100
SqlConnection conn = new SqlConnection(cnString);

You can use one of its several constructors to create a command object directly, or use the ProviderFactory approach mentioned in Section 11.1.

This segment demonstrates how to create a command object and explicitly set its properties:

SqlConnection conn = new SqlConnection(connstr);
Conn.open();
string sql = "insert into movies(movie_Title,movie_Year,
        movie_Director) values(@title,@yr,@bestpicture)";
SqlCommand cmd = new SqlCommand();
// Assign connection object and sql query to command object
cmd.Connection = conn;
cmd.commandText = sql;
// Fill in parameter values in query
// This is recommended over concatenation in a query string
cmd.Parameters.AddWithValue ("@title", "Schindler's List");
cmd.Parameters.AddWithValue ("@yr", "1993");
cmd.Parameters.AddWithValue ("@bestpic", "Y");

In situations where multiple data providers may be used, a provider factory provides a more flexible approach. The factory is created by passing its constructor a string containing the data provider. The factory's CreateCommand method is used to return a command object.

string provider = "System.Data.SqlClient";
DBProviderFactory factory =
   DbProviderFactories.GetFactory(provider);
DbCommand cmd   = factory.CreateCommand();
cmd.CommandText = sql;     // Query or command
cmd.Connection  = conn;    // Connection object

Note that DbCommand is an abstract class that implements the IDbCommand interface. It assumes the role of a generic command object. This can eliminate the need to cast the returned command object to a specific provider's command object such as SqlCommand. However, casting is required if you need to access custom features of a provider's command class—for example, only SqlCommand has an ExecuteXmlReader method.

The SQL command assigned to the CommandText property is executed using one of the four command methods in Table 11-4.

cmd.CommandText = "DELETE movies WHERE movie_ID=220";
int ct = cmd.ExecuteNonQuery();

cmd.CommandText="SELECT * FROM movies
   WHERE movie_year > '1945';
SqlDataReader rdr= cmd.ExecuteReader();

cmd.CommandText="SELECT COUNT(movie_title)
   FROM movies";
int movieCt = (int) cmd.ExecuteScalar();

The ExecuteReader method is the most important of these methods. It returns a DataReader object that exposes the rows returned by the query. The behavior of this method can be modified by using its overload that accepts a CommandBehavior type parameter. As an example, the following statement specifies that a single row of data is to be returned:

rdr = cmd.ExecuteReader(sql, CommandBehavior.SingleResult);

Some data providers take advantage of this parameter to optimize query execution. The list of values for the CommandBehavior enumeration includes the following:

A stored procedure is a set of SQL code stored in a database that can be executed as a script. It's a powerful feature that enables logic to be encapsulated, shared, and reused among applications. ADO.NET supports the execution of stored procedures for OleDb , SqlClient, ODBC, and OracleClient data providers.

Executing a stored procedure is quite simple: set the SqlCommand.CommandText property to the name of the procedure; set the CommandType property to the enumeration CommandType.StoredProcedure; and then call the ExecuteNonQuery method.

cmd.CommandText = "SP_AddMovie";  // Stored procedure name
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();

When a stored procedure contains input or output parameters, they must be added to the command object's Parameters collection before the procedure is executed. To demonstrate, let's execute the stored procedure shown in Listing 11-3. This procedure allows records to be fetched from the movies table as pages containing 10 rows of data. Input to the procedure is the desired page; the output parameter is the total number of pages available. This code fragment illustrates how parameters are set and how the procedure is invoked to return the first page:

SqlCommand cmd  = new SqlCommand();
cmd.CommandText = "SPMOVIES_LIST";  // Stored procedure name
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(@PageRequest", SqlDbType.Int);
cmd.Parameters.Add(@TotalPages", SqlDbType.Int);
cmd.Parameters[0].Direction= ParameterDirection.Input;
cmd.Parameters[0].Value= 1;        // Retrieve first page
cmd.Parameters[1].Direction=ParameterDirection.Output;
cmd.CommandTimeout=10;  // Give command 10 seconds to execute
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()){
//   do something with results
}
rdr.Close();  // Must close before reading parameters
int totpages=  cmd.Parameters[1].Value;

This example uses the SqlClient data provider. With a couple of changes, OleDb can be used just as easily. The primary difference is in the way they handle parameters. SqlClient requires that the parameter names match the names in the stored procedure; OleDb passes parameters based on position, so the name is irrelevant. If the procedure sends back a return code, OleDB must designate the first parameter in the list to handle it. SqlClient simply adds a parameter—the name is unimportant—that has its direction set to ReturnValue.

CREATE PROCEDURE SPMOVIES_LIST
   @PageRequest int,
   @TotalPages int output
AS
   /*
      Procedure to return a resultset of movies ordered
      by title.
      Resultset contains 10 movies for the specified page.
   */
   SET NOCOUNT ON
   select @TotalPages = CEILING(COUNT(*)/10) from movies
   if @PageRequest = 1 or @PageRequest <1
   begin
      select top 10 * from movies order by movie_Title
      set @PageRequest = 1
      return 0
   end
   begin
      if @PageRequest > @TotalPages
         set @PageRequest = @TotalPages
      declare @RowCount int
      set @RowCount = (@PageRequest * 10)
      exec ('SELECT * FROM
      (SELECT TOP 10 a.* FROM
      (SELECT TOP ' + @RowCount + ' * FROM movies ORDER BY
          movie_Title) a
       ORDER BY movie_Title desc) b
       ORDER BY Movie_Title')

       return 0
   end

An earlier example (see “Creating a Command Object” on page 511) used this statement to create a SQL command to store a movie in the Films database:

string sql = "insert into movies(movie_Title,movie_Year,
      bestpicture) values(@title,@yr,@bestpic)";
// Parameters set values to be stored
cmd.Parameters.AddWithValue ("@title", "Schindler's List");
cmd.Parameters.AddWithValue ("@yr", "1993");
cmd.Parameters.AddWithValue ("@bestpic", "Y");

The alternative, which uses concatenation, looks like this:

string title = "Schindler''s List";  // Two single quotes needed
string yr = "1993";
string pic = "Y";
sql = "insert into movies(movie_Title,movie_Year,
      bestpicture) values";
sql += "('"+title+"',"+yr+",'"+pic+"') ";

Not only is the parameterized version more readable and less prone to syntactical error, but it also provides a significant benefit: It automatically handles the problem of placing double single quotes ('') in a SQL command. This problem occurs when attempting to store a value such as O'Quinn, which has an embedded quote that conflicts with SQL syntax. Parameters eliminate the usual approach to search each string and replace an embedded single quote with a pair of single quotes.

A DataReader returns a single row from a resultset each time its Read method is executed. If no rows remain, the method returns false. The reader should be closed after row processing is completed in order to free system resources. You can check the DataReader.IsClosed property to determine if a reader is closed.

Although a DataReader is associated with a single command, the command may contain multiple queries that return multiple resultsets. This code fragment demonstrates how a DataReader processes the rows returned by two queries.

string q1 = "SELECT * FROM movies WHERE movie_Year < 1940";
string q2 = "SELECT * FROM movies WHERE movie_Year > 1980";
cmd.CommandText = q1 + ";" + q2;
DbDataReader rdr = cmd.ExecuteReader();
bool readNext = true;
while (readNext)
{
   while (rdr.Read())
   {
      MessageBox.Show(rdr.GetString(1));
   }
   readNext = rdr.NextResult(); // Another resultset?
}
rdr.Close();
conn.Close();

The two things to note are the construction of the CommandString with multiple queries and the use of the NextResult method to determine if results from another query are present.

Core Note

The DataReader has no property or method that provides the number of rows returned in its resultset. Because data is received one row at a time, the resultset could be altered by additions and deletions to the database as records are read. However, there is a HasRows property that returns true if the data reader contains one or more rows.

There are numerous ways to access data contained in the columns of the current DataReader row: as an array with column number (zero-based) or name used as an index; using the GetValue method by passing it a column number; and using one of the strongly typed Getxxx methods that include GetString, GetInt32, GetDateTime, and GetDouble. The following code segment contains an example of each technique:

cmd.CommandText="SELECT movie_ID, movie_Title FROM movies";
rdr = cmd.ExecuteReader();
rdr.Read();
string title;
// Multiple ways to access data in a column
title = rdr.GetString(1);
title = (string)rdr.GetSqlString(1); // SqlClient provider
title = (string)rdr.GetValue(1);
title = (string)rdr["movie_Title"];  // Implicit item
title = (string)rdr[1];              // Implicit item

The GetString method has the advantage of mapping the database contents to a native .NET data type. The other approaches return object types that require casting. For this reason, use of the Get methods is recommended. Note that although GetString does not require casting, it does not perform any conversion; thus, if the data is not of the type expected, an exception is thrown.

Many applications rely on a separate data access layer to provide a DataReader. In such cases, the application may require metadata to identify column names, data types, and other columnar information. Column names, which are useful for generating report headings, are readily available through the GetName method:

// List column names for a DataReader
DbDataReader rdr = GetReader();        // Get a DataReader
for (int k = 0; k < rdr.FieldCount; k++)
{
   Console.WriteLine(rdr.GetName(k));  // Column name
}
rdr.Close();

Complete column schema information is available through the GetSchemaTable method. It returns a DataTable in which there is one row for each field (column) in the resultset. The columns in the table represent schema information. This code segment demonstrates how to access all the column information for a resultset. For brevity, only three of the 24 columns of information are shown:

DataTable schemaTable = rdr.GetSchemaTable();
int ict = 0;
foreach (DataRow r in schemaTable.Rows)
{
   foreach (DataColumn c in schemaTable.Columns){
      Console.WriteLine(ict.ToString()+"
                        "+c.ColumnName + ": "+r[c]);
      ict++;
   }
}
// Selected Output:
//       0 ColumnName: movie_ID
//       1 ColumnOrdinal: 0
//       12 DataType: System.Int32

The DataTable.Columns property exposes a collection of DataColumn objects that represent each data field in the DataTable. Taken together, the column properties produce the data schema for the table. Table 11-5 summarizes the most important properties.

DataTable columns are created automatically when the table is filled with the results of a database query or from reading an XML file. However, for applications that fill a table dynamically—such as from user input or real-time data acquisition—it may be necessary to write the code that defines the table structure. It's a worthwhile exercise in its own right that enhances a developer's understanding of the DataSet hierarchy.

The following segment creates a DataTable object, creates DataColumn objects, assigns property values to the columns, and adds them to the DataTable. To make things interesting, a calculated column is included.

DataTable tb = new DataTable("Order");
DataColumn dCol = new DataColumn("ID",
      Type.GetType("System.Int16"));
dCol.Unique = true;  // ID must be unique for each data row
dCol.AllowDBNull = false;
tb.Columns.Add(dCol);
dCol= new DataColumn("Price", Type.GetType("System.Decimal"));
tb.Columns.Add(dCol);
dCol=new DataColumn("Quan",Type.GetType("System.Int16"));
tb.Columns.Add(dCol);
dCol= new DataColumn("Total",Type.GetType("System.Decimal"));
dCol.Expression= "Price * Quan";
tb.Columns.Add(dCol);
// List column names and data type
foreach (DataColumn dc in tb.Columns)
{
   Console.WriteLine(dc.ColumnName);
   Console.WriteLine(dc.DataType.ToString());
}

Note that the ID column is defined to contain unique values. This constraint qualifies the column to be used as a key field in establishing a parent-child relationship with another table in a DataSet. To qualify, the key must be unique—as in this case—or defined as a primary key for the table. You assign a primary key to a table by setting its PrimaryKey field to the value of an array containing the column(s) to be used as the key. Here is an example that specifies the ID field a primary key:

DataColumn[] col = {tb.Columns["ID"]};
tb.PrimaryKey = col;

We'll see how to use a primary key to create table relationships and merge data later in this section.

Core Note

If a primary key consists of more than one column—such as a first and last name—you can enforce a unique constraint on these columns in three steps: by creating an array to hold the columns, creating a UniqueConstraint object by passing the array to its constructor; and adding the constraint to the data table's Constraints collection:

DataColumn[] cols = {tb.Columns["fname"]
                     tb.Columns["lname"]};
tb.Constraints.Add(new UniqueConstraint("nameKey", cols));

Data is added to a table by creating a new DataRow object, filling it with column data, and adding the row to the table's DataRow collection. Here is an example that places data in the table created in the preceding example.

DataRow row;
row = tb.NewRow();      // Create DataRow
row["Title"] = "Casablanca";
row["Price"] = 22.95;
row["Quan"] = 2;
row["ID"] = 12001;
tb.Rows.Add(row);       // Add row to Rows collection
Console.WriteLine(tb.Rows[0]["Total"].ToString()); // 45.90

A DataTable has methods that allow it to commit and roll back changes made to the table. In order to do this, it keeps the status of each row in the DataRow.RowState property. This property is set to one of five DataRowState enumeration values: Added, Deleted, Detached, Modifed, or Unchanged. Let's extend the preceding example to demonstrate how these values are set:

tb.Rows.Add(row);                    // Added
tb.AcceptChanges();                  // ...Commit changes
Console.Write(row.RowState);         // Unchanged
tb.Rows[0].Delete();                 // Deleted
// Undo deletion
tb.RejectChanges();                  // ...Roll back
Console.Write(tb.Rows[0].RowState);  // Unchanged
DataRow myRow;
MyRow = tb.NewRow();                 // Detached

The two DataTable methods AcceptChanges and RejectChanges are equivalent to the commit and rollback operations in a database. These apply to all changes made from the time the table was loaded or since AcceptChanges was previously invoked. In this example, we are able to restore a deleted row because the deletion is not committed before RejectChanges is called. Note that the changes are to the data table—not the original data source.

For each column value in a row, ADO.NET maintains a current and original value. When RejectChanges is called, the current values are set to the original values. The opposite occurs if AcceptChanges is called. The two sets of values can be accessed concurrently through the DataRowVersion enumerations Current and Original:

DataRow r = tb.Rows[0];
r["Price"]= 14.95;
r.AcceptChanges();
r["Price"]= 16.95;
Console.WriteLine("Current: {0} Original: {1} ",
      r["Price",DataRowVersion.Current],
      r["Price",DataRowVersion.Original]);
// output:  Current: 16.95  Original: 14.95

Keeping track of table row changes takes on added importance when the purpose is to update an underlying data source. We'll see later in this section how the DataAdapter updates database tables with changes made to DataTable rows.

A DataReader object can be used in conjunction with a DataSet or DataTable to fill a table with the rows generated by a query. This requires creating a DataReader object and passing it as a parameter to the DataTable.Load method:

cmd.CommandText = "SELECT * FROM movies WHERE movie_Year < 1945";
DBDataReader rdr =
      cmd.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dt = new DataTable("movies");
dt.Load(rdr);   // Load data and schema into table
Console.WriteLine(rdr.IsClosed);   // True

The DataReader is closed automatically after all of the rows have been loaded. The CloseConnection parameter ensures that the connection is also closed.

If the table already contains data, the Load method merges the new data with the existing rows of data. Merging occurs only if rows share a primary key. If no primary key is defined, rows are appended. An overloaded version of Load takes a second parameter that defines how rows are combined. This parameter is a LoadOption enumeration type having one of three values: OverwriteRow, PreserveCurrentValues, or UpdateCurrentValues. These options specify whether the merge operation overwrites the entire row, original values only, or current values only. This code segment illustrates how data is merged with existing rows to overwrite the current column values:

cmd.CommandText = "SELECT * FROM movies WHERE movie_Year < 1945";
DBDataReader rdr = cmd.ExecuteReader( );
DataTable dt = new DataTable("movies");
dt.Load(rdr);                    // Load rows into table
Console.Write(dt.Rows[0]["movie_Title"]);  // Casablanca

// Assign primary key so rows can be merged
DataColumn[] col = new DataColumn[1];
col[0] = dt.Columns["movie_ID"];
dt.PrimaryKey = col;
DataRow r = dt.Rows[0];           // Get first row of data
r["movie_Title"] = "new title";   // Change current column value
// Since reader is closed, must fill reader again
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Merge data with current rows. Overwrites current values
dt.Load(rdr, LoadOption.UpdateCurrentValues );
// Updated value has been overwritten
Console.Write(dt.Rows[0]["movie_Title"]);  // Casablanca

A DataAdapter object can be used to fill an existing table, or create and fill a new table, with the results from a query. The first step in this process is to create an instance of the DataAdapter for a specific data provider. As the following code shows, several constructor overloads are available:

// (1) The easiest: a query and connection string as arguments
String sql = "SELECT * FROM movies";
SqlDataAdapter da = new SqlDataAdapter(sql, connStr);

// (2) Assign a command object to the SelectCommand property
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection conn = new SqlConnection(connStr);
da.SelectCommand = new SqlCommand(sql,conn);

// (3) Pass in a query string and connection object
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter da = new SqlDataAdapter(sql, conn);

Of these, the first version is the simplest. It accepts two strings containing the query and connection. From these, it constructs a SqlCommand object that is assigned internally to its SelectCommand property. Unlike the other constructors, there is no need to write code that explicitly creates a SqlCommand or SqlConnection object.

In the overloads that accept a connection object as a parameter, the opening and closing of the connection is left to the DataAdapter. If you add a statement to explicitly open the connection, you must also include code to close it. Otherwise, the DataAdapter leaves it open, which locks the data in the database.

After the DataAdapter object is created, its Fill method is executed to load data into a new or existing table. In this example, a new table is created and assigned the default name Table:

DataSet ds = new DataSet();
// Create DataTable, load data, and add to DataSet
// Could use da.Fill(ds,"movies") to specify table name.
int numrecs = da.Fill(ds);  // Returns number of records loaded

For an existing table, the behavior of the Fill command depends on whether the table has a primary key. If it does, those rows having a key that matches the key of the incoming data are replaced. Incoming rows that do not match an existing row are appended to the DataTable.

A CommandBuilder object generates the commands necessary to update a data source with changes made to a DataSet. It's amazingly self-sufficient. You create an instance of it by passing the related DataAdapter object to its constructor; then, when the DataAdapter.Update method is called, the SQL commands are generated and executed. The following segment shows how changes to a DataTable are flushed to the database associated with the DataAdapter:

DataTable dt= ds.Tables["movies"]; // Shortcut to reference table

// (1) Use command builder to generate update commands
SqlCommandBuilder sb = new SqlCommandBuilder(da);

// (2) Add movie to table
DataRow drow = dt.NewRow();
drow["movie_Title"] = "Taxi Driver";
drow["movie_Year"] = "1976";
dt.Rows.Add(drow);

// (3) Delete row from table
dt.Rows[4].Delete();

// (4) Edit Column value
dt.Rows[5]["movie_Year"] = "1944";

// (5) Update underlying Sql Server table
int updates = da.Update(ds, "movies");
MessageBox.Show("Rows Changed: " +updates.ToString());  // 3

There are a couple of restrictions to be aware of when using the CommandBuilder: The Select command associated with the DataAdapter must refer to a single table, and the source table in the database must include a primary key or a column that contains unique values. This column (or columns) must be included in the original Select command.

Core Note

You can create your own update commands without using a CommandBuilder. Although it can be a lengthy process, it can also yield more efficient commands. For applications that require considerable database updates, you may want to consult an ADO.NET book for details on coding update logic directly.

As demonstrated in this example, the use of a DataAdapter simplifies and automates the process of updating a database—or any data store. However, there is a rock in this snowball: the problem of multi-user updates. The disconnected model is based on optimistic concurrency, an approach in which the rows of the underlying data source are not locked between the time they are read and the time updates are applied to the data source. During this interval, another user may update the data source. Fortunately, the Update method recognizes if changes have occurred since the previous read and fails to apply changes to a row that has been altered.

There are two basic strategies for dealing with a concurrency error when multiple updates are being applied: roll back all changes if a violation occurs, or apply the updates that do not cause an error and identify the ones that do so they can be reprocessed.

SqlDataAdapter da = new SqlDataAdapter();
SqlCommandBuilder sb = new SqlCommandBuilder(da);
SqlTransaction tran;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();      // Must open to use with transaction
// (1) Create a transaction
SqlTransaction tran = conn.BeginTransaction();
// (2) Associate the SelectCommand with the transaction
da.SelectCommand = new SqlCommand(sql, conn, tran);
DataSet ds = new DataSet();
da.Fill(ds, "movies");
//
// Code in this section makes updates to DataSet rows
try
{
   int updates = da.Update(ds, "movies");
   MessageBox.Show("Updates: "+updates.ToString());
}
// (3) If exception occurs, roll back all updates in transaction
catch (Exception ex)
{
   MessageBox.Show(ex.Message);   // Error updating
   if (tran != null)
   {
      tran.Rollback();      // Roll back any updates
      tran = null;
      MessageBox.Show("All updates rolled back.");
   }
}
finally
{
// (4) If no errors, commit all updates
   if (tran != null)
   {
       tran.Commit();
       MessageBox.Show("All updates successful. ");
       tran = null;
   }
}
conn.Close();

// SqlDataAdapter da loads movies table
da.ContinueUpdateOnError = true;
DataSet ds = new DataSet();
try
{
   da.Fill(ds, "movies");
   DataTable dt = ds.Tables["movies"];
   SqlCommandBuilder sb = new SqlCommandBuilder(da);
   // ... Sample Update operations
   dt.Rows[29].Delete();                      // Delete
   dt.Rows[30]["movie_Year"] = "1933";        // Update
   dt.Rows[30]["movie_Title"] = "King Kong";  // Update
   dt.Rows[31]["movie_Title"] = "Fantasia";   // Update
   DataRow drow = dt.NewRow();
   drow["movie_Title"] = "M*A*S*H";
   drow["movie_Year"] = "1970";
   dt.Rows.Add(drow);                         // insert
   // Submit updates
   int updates = da.Update(ds, "movies");
   // Following is true if any update failed
   if (ds.HasChanges())
   {
      // Load rows that failed into a DataSet
      DataSet failures = ds.GetChanges();
      int rowsFailed = failures.Rows.Count;
      Console.WriteLine("Update Failures: "+rowsFailed);
      foreach (DataRow r in failures.Tables[0].Rows )
      {
         string state = r.RowState.ToString());
         // Have to reject changes to show deleted row
         if (r.RowState == DataRowState.Deleted)
               r.RejectChanges();
         string ID= ((int)r["movie_ID"]).ToString();
         string msg= state + " Movie ID: "+ID;
         Console.WriteLine(msg);
      }
   }

When a relationship is defined between two tables, it has the effect of adding a ForeignKeyConstraint to the Constraints collections of the child DataTable. This constraint determines how the child table is affected when rows in a parent table are changed or deleted. In practical terms, this means that if you delete a row in the parent table, you can have the related child row(s) deleted—or optionally, have their key value set to null. Similarly, if a key value is changed in the parent table, the related rows in the child can have their key value changed or set to null.

The rule in effect is determined by the value of the DeleteRule and UpdateRule properties of the constraint. These can take one of four Rule enumeration values:

This code segment illustrates how constraints affect the capability to add a row to a child table and delete or change a row in the parent table. The tables from the preceding example are used.

// (1) Try to add row with new key to child table
DataRow row = child.NewRow();
row["movie_directorID"] = 999;
child.Rows.Add(row);   // Fails – 999 does not exist in parent
// (2) Delete row in parent table
row = parent.Rows[0];
row.Delete();      // Deletes rows in child having this key
// (3) Relax constraints and retry adding row
ds.EnforceConstraints = false;
row["movie_directorID"] = 999;
child.Rows.Add(row);   // Succeeds
ds.EnforceConstraints = true;    // Turn back on
// (4) Change constraint to set rows to null if parent changed
((ForeignKeyConstraint)child.Constraints[0]).DeleteRule =
       Rule.SetNull ;

Note that setting the EnforceConstraints property to false turns off all constraints—which in database terms eliminates the check for referential integrity. ^[3] This allows a movie to be added even though its movie_DirectorID column (foreign key) does not have a corresponding row in the directors table. It also permits a director to be deleted even though a movie by that director exists in the movies table. This clearly compromises the integrity of the database and should be used only when testing or populating individual tables in a database.

The XML file used in the preceding example has a simple structure that is easily transformed into a single table: The <movies> tag (refer to Listing 11-8) represents a row in a table, and the elements contained within it become column values. Most XML is more complex than this example and requires multiple tables to represent it. Although ReadXml has limitations (it cannot handle attributes), it can recognize nested XML and render multiple tables from it. As an example, let's alter the oscarwinners.xml file to include a <director> tag within each <movies> block.

<films>
  <movies>
    <movie_ID>5</movie_ID>
    <movie_Title>Citizen Kane </movie_Title>
    <movie_Year>1941</movie_Year>
    <director>
      <first_name>Orson</first_name>
      <last_name>Welles</last_name>
    </director>
    <bestPicture>Y</bestPicture>
    <AFIRank>1</AFIRank>
  </movies>
  ... more movies here
</films>

Next, run this code to display the contents of the table(s) created:

DataSet ds = new DataSet();
ds.ReadXml("c:\oscarwinnersv2.xml");
foreach (DataTable dt in ds.Tables)
   ShowTable(dt);

Figure 11-7 depicts the DataSet tables created from reading the XML file. It creates two tables, automatically generates a movies_ID key for each table, and assigns values to this key, which link a row in the movies table to an associated row in the director table.

Figure 11-7. DataSet tables and relationship created from XML