.NET & XML

Connecting a DataSet to the Database

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