The most
obvious way to create a DataSet
is to construct
each of its objects and add them to the appropriate collections.
First, create a new instance of DataSet
named
“AngusHardware.” The
DataSet
represents the entire database schema:
DataSet dataSet = new DataSet("AngusHardware");
Next, add a table named
“customers” to the
DataSet
. The DataTableCollection.Add(
)
method has several overloads; by passing a
string
parameter, you’re creating
a new DataTable
with the given name, and adding it
to the DataSet
’s
Tables
property. Add( )
returns
the newly created DataTable
, which
you’ll use to create columns:
DataTable customers = dataSet.Tables.Add("customers");
Next,
add a column to the “customers”
table. The DataColumnCollection.Add( )
method
returns the newly created DataColumn
, which
you’ll use in a minute to assign the primary key.
This Add( )
method, like the one on
DataTableCollection
, has several overloads. The
one used here simply takes the name of the database column and the
Type
of the data it contains:
DataColumn customersCustomerId = customers.Columns.Add("customer_id", typeof(long));
The process is similar for each column. Note that some columns are
nullable in the database and others are not; the
AllowDBNull
property indicates whether the column
is nullable:
customers.Columns.Add("name",typeof(string)).AllowDBNull = false; customers.Columns.Add("address1",typeof(string)).AllowDBNull = false; customers.Columns.Add("address2",typeof(string)); customers.Columns.Add("address3",typeof(string)); customers.Columns.Add("city",typeof(string)).AllowDBNull = false; customers.Columns.Add("state",typeof(string)).AllowDBNull = false; customers.Columns.Add("zip",typeof(string)).AllowDBNull = false;
The last step for the “customers”
table is to set the primary key, using the
customersCustomerId
DataColumn
created a minute ago. Although this table has a simple, one-column
primary key, the DataSet
allows for concatenated
primary keys via an array of DataColumn
objects:
customers.PrimaryKey = new DataColumn [ ] {customersCustomerId};
A very similar process creates the “coupons” table:
DataTable coupons = dataSet.Tables.Add("coupons"); DataColumn couponCouponCode = coupons.Columns.Add("coupon_code", typeof(string)); coupons.Columns.Add("discount_amount", typeof(Double)).AllowDBNull = false; coupons.Columns.Add("discount_type", typeof(int)).AllowDBNull = false; coupons.Columns.Add("expiration_date", typeof(DateTime)).AllowDBNull = false; coupons.PrimaryKey = new DataColumn [ ] {couponCouponCode};
And again for the “coupon_redemptions” table:
DataTable couponRedemptions = dataSet.Tables.Add("coupon_redemptions"); DataColumn couponRedemptionsCouponCode = couponRedemptions.Columns.Add("coupon_code", typeof(string)); couponRedemptions.Columns.Add("total_discount", typeof(Double)).AllowDBNull = false; couponRedemptions.Columns.Add("redemption_date", typeof(DateTime)).AllowDBNull = false; DataColumn couponRedemptionsCustomerId = couponRedemptions.Columns.Add("customer_id", typeof(long));
Now
that all the tables are created, it’s time to assign
the relations between them. The DataSet
has a
DataRelationCollection
, whose Add(
)
method has several overloads. The one used here takes the
parent DataColumn
and the child
DataColumn
. There are two relations in this
example; one between coupons.coupon_code
and
coupon_redemptions.coupon_code
, and one between
customers.customer_id
and
coupon_redemptions.customer_id
:
dataSet.Relations.Add(couponCouponCode, couponRedemptionsCouponCode); dataSet.Relations.Add(customersCustomerId, couponRedemptionsCustomerId);
Finally, this line writes an XML Schema document that describes the
DataSet
to a file:
dataSet.WriteXmlSchema("Coupons.xsd");
The XML Schema that this program saved in Coupons.xsd
is a normal XML Schema, and it
can be used to recreate the DataSet
in memory. The
code to read in a DataSet
’s
structure from a schema is very simple. In fact, it can be expressed
succinctly in two statements:
DataSet dataSet = new DataSet( ); dataSet.ReadXmlSchema("Coupons.xsd");
Example 11-4 shows the complete program that creates
the DataSet
for the coupon database, and saves an
XML Schema for it.
using System; using System.Data; public class CreateDataSet { public static void Main(string [ ] args) { DataSet dataSet = new DataSet("AngusHardware"); DataTable customers = dataSet.Tables.Add("customers"); DataColumn customersCustomerId = customers.Columns.Add("customer_id", typeof(long)); customers.Columns.Add("name",typeof(string)).AllowDBNull = false; customers.Columns.Add("address1",typeof(string)).AllowDBNull = false; customers.Columns.Add("address2",typeof(string)); customers.Columns.Add("address3",typeof(string)); customers.Columns.Add("city",typeof(string)).AllowDBNull = false; customers.Columns.Add("state",typeof(string)).AllowDBNull = false; customers.Columns.Add("zip",typeof(string)).AllowDBNull = false; customers.PrimaryKey = new DataColumn [ ] {customersCustomerId}; DataTable coupons = dataSet.Tables.Add("coupons"); DataColumn couponCouponCode = coupons.Columns.Add("coupon_code", typeof(string)); coupons.Columns.Add("discount_amount", typeof(Double)).AllowDBNull = false; coupons.Columns.Add("discount_type", typeof(int)).AllowDBNull = false; coupons.Columns.Add("expiration_date", typeof(DateTime)).AllowDBNull = false; coupons.PrimaryKey = new DataColumn [ ] {couponCouponCode}; DataTable couponRedemptions = dataSet.Tables.Add("coupon_redemptions"); DataColumn couponRedemptionsCouponCode = couponRedemptions.Columns.Add("coupon_code", typeof(string)); couponRedemptions.Columns.Add("total_discount", typeof(Double)).AllowDBNull = false; couponRedemptions.Columns.Add("redemption_date", typeof(DateTime)).AllowDBNull = false; DataColumn couponRedemptionsCustomerId = couponRedemptions.Columns.Add("customer_id", typeof(long)); dataSet.Relations.Add(couponCouponCode, couponRedemptionsCouponCode); dataSet.Relations.Add(customersCustomerId, couponRedemptionsCustomerId); dataSet.WriteXmlSchema("Coupons.xsd"); } }
18.223.170.223