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
SqlParameter
s 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");
It’s also possible to set the
InsertCommand
and DeleteCommand
properties, but since you’re only selecting and
updating rows in this example, it’s not necessary.
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"); } }
3.22.41.212