I haven’t yet shown you how to actually connect the DataSet to an actual database. This is achieved using the IDataAdapter interface, which serves as an intermediate layer between the database table and the DataSet. You specify the SQL commands to select, insert, update, and delete from each table, and then use the Fill( ) method to fill the DataSet with data from the database, or the Update( ) method to update the database with data from the DataSet.

The first step is create a database connection, a SqlDataAdapter, and an AngusHardware DataSet:

SqlConnection connection = new SqlConnection(
  "Initial Catalog=AngusHardware; User ID=sa");
SqlDataAdapter adapter = new SqlDataAdapter( );
AngusHardware dataSet = new AngusHardware( );

After that, you can create the select command for the SqlDataAdapter. This is the SqlCommand that will be used to populate the DataSet with data from the database:

adapter.SelectCommand = new SqlCommand("SELECT coupon_code, " +
  "discount_amount, discount_type, expiration_date FROM coupons", 
  connection);

Because you’ll be updating some of the data in this example and you would like those changes to be reflected in the database, the next step is to set the SqlDataAdapter’s UpdateCommand property. Again, this is a normal SqlCommand, but unlike the SelectCommand it is necessary to add SqlParameters so that any updates get mapped to the correct columns:

adapter.UpdateCommand = new SqlCommand(
  "UPDATE coupons SET coupon_code = @couponCode, discount_amount = " +
  "@discountAmount, discount_type = @discountType, expiration_date = " +
  "@expirationDate WHERE coupon_code = @couponCode", connection);
adapter.UpdateCommand.Parameters.Add("@couponCode",
  SqlDbType.Char,10,"coupon_code");
adapter.UpdateCommand.Parameters.Add("@discountAmount",
  SqlDbType.Float,8,"discount_amount");
adapter.UpdateCommand.Parameters.Add("@discountType",
  SqlDbType.TinyInt,1,"discount_type");
adapter.UpdateCommand.Parameters.Add("@expirationDate",
  SqlDbType.DateTime,8,"expiration_date");

With the SqlDataAdapter all set up, the Fill( ) method is used to fill the DataSet with data from the database using the SelectCommand. The second parameter to Fill( ) tells the SqlDataAdapter the name of the DataTable to fill with data; this name can differ from the name of the database table:

adapter.Fill(dataSet, "coupons");

Updating a row of data is a simple matter of locating the row of interest and setting its properties. Here we set the expiration date to the current date and time:

dataSet.coupons[0].expiration_date = DateTime.Now;

Since some of the data were changed, the SqlDataAdapter.Update( ) method causes the database to be updated with the changes currently in the DataSet:

adapter.Update(dataSet, "coupons");

Note that although in this case the DataSet was filled, modified, and updated within the span of a single database session, the operation could just as easily have spanned a larger time. The DataSet is a disconnected view of the database, which means that a connection need not be maintained while the data are modified.

Example 11-8 shows the complete program.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

public class FillDataSet {
  
  public static void Main(string [ ] args) {
    
    SqlConnection connection = new SqlConnection(
      "Initial Catalog=AngusHardware; User ID=sa");
    SqlDataAdapter adapter = new SqlDataAdapter( );
    AngusHardware dataSet = new AngusHardware( );
    
    adapter.SelectCommand = new SqlCommand("SELECT coupon_code, " +
      "discount_amount, discount_type, expiration_date FROM coupons", 
      connection);

    adapter.UpdateCommand = new SqlCommand(
      "UPDATE coupons SET coupon_code = @couponCode, discount_amount = " +
      "@discountAmount, discount_type = @discountType, expiration_date = " +
      "@expirationDate WHERE coupon_code = @couponCode", connection);
    adapter.UpdateCommand.Parameters.Add("@couponCode",
      SqlDbType.Char,10,"coupon_code");
    adapter.UpdateCommand.Parameters.Add("@discountAmount",
      SqlDbType.Float,8,"discount_amount");
    adapter.UpdateCommand.Parameters.Add("@discountType",
      SqlDbType.TinyInt,1,"discount_type");
    adapter.UpdateCommand.Parameters.Add("@expirationDate",
      SqlDbType.DateTime,8,"expiration_date");

    adapter.Fill(dataSet, "coupons");

    dataSet.coupons[0].expiration_date = DateTime.Now;

    adapter.Update(dataSet, "coupons");
  }
}