Filling typed dataset programmatically

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("your con. string"));

          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;

No comments:

Post a Comment