Chris said:
I've created a strongly typed dataset (to be used to populate a crystal
report) which I need to fill programmatically. How do I do this?
Filling a typed dataset works just like filling an untyped dataset. You
create a data adapter, assign the SelectCommand, and call Fill(). The
only real trick is to get the table and field names right.
A DataAdapeter will generate table names for each distinct result set
generated by the SelectCommand. The names will be "Table", "Table1",
"Table2", etc. If you call the overloaded Fill() method which takes a
table name parameter, the generated names will follow the same pattern,
except your table name will be used instead of "Table".
Once the data adapter has generated the table names, it will look for
matching table names in your data set. The data is then mapped to the
matching column names in the matching tables. As long as the
SelectCommand returns the proper column names, the data will populate as
you would expect.
Some things to beware of:
* You will *not* get errors if the table or column names are wrong. If
the Data Adapter can't find a given table, it would normally add a new
table to your dataset. For typed data sets, things are slightly
different. You won't get an exception in this case, but you also wont'
get your data. This includes both misnamed tables *and* misnamed
columns -- the data is simply discarded if it can't be fit into your schema.
* If your SelectCommand returns multiple result sets, you will almost
certainly need to map the resulting table names to those in your
dataset. The table names need to be mapped in the order they are
returned by the SelectCommand, and the code looks something like this
(assume the dataset has three tables: MyTable, MyOtherTable, MyThirdTable):
/**********/
SqlCommand cmd = new SqlCommand(
"multi_recordset_proc",
new SqlConnection(...)
);
cmd.CommandType = CommandType.StoredProcedure
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.TableMappings("MyTable1", "MyOtherTable");
da.TableMappings("MyTable2", "MyThirdTable");
da.Fill(dataset, "MyTable");
/**********/
* If you have relationships in your dataset, they will be enforced
during the Fill. I beleive this is done on a per-table basis, that is,
the contraints for a given table are disabled, data is populated, and
the constraints are enabled. If you populate a table before populating
parent tables, you will generate an error. You can get around this
problem by being careful of the order you populate tables.
Alternatively, you can do:
/**********/
ds.EnforceConstraints = false;
da.Fill(ds, "Table");
ds.EnforceConstraints = true;
/**********/
