Write Dataset to SQL Table

Question

I have converted a complex XML File to a Dataset that has 7 Tables and around 70 Columns I have created the tables in SQL Server to match the ones in the Dataset using the XML Schema. How can I easily write my Dataset to the SQL tables?

Answer 1

If you're using .NET 3/3.5, you could look into LINQ.

Answer 2

You probably want to look at DataAdapters and TypedDatasets.

These are all available via the VS designer, simply create a dataset, and drop the SQL tables in from the server explorer view. :) (or something like that, it's been a while)

Answer 3

Depending on how many rows you have in the DataSet, probably the best thing to do would be to use a SqlCommandBuilder, like so:

var connection = new SqlConnection("my connection string");
connection.Open();

// repeat for each table in data set
var adapterForTable1 = new SqlDataAdapter("select * from table1", connection);
var builderForTable1 = new SqlCommandBuilder(adapterForTable1);
adapterForTable1.Update(myDataSet, "Table1");

If you have complex relationships defined between the tables in the DataSet, I'm afraid you can't use the SqlCommandBuilder. What you'll need to do, instead, is define a data adapter for each table in your DataSet. Then, update the tables in the DataSet in dependency order (i.e., do the tables with no dependencies first, then the dependent tables).

Here's an example of a parent/child insert (note that you would do similar things for updates). Table1 is the parent, and has ParentId (the identity column), and an NVARCHAR field ParentValue. Table2 is the child, has its own identity column (ChildId), the foreign key field (ParentId), and its own value (ChildValue).

var myDataSet = new DataSet();

// ** details of populating the dataset omitted **

// create a foreign key relationship between Table1 and Table2.
// add a constraint to Table2's ParentId column, indicating it must
// existing in Table1.
var fk = new ForeignKeyConstraint("fk", myDataSet.Tables["Table1"].Columns["ParentId"], myDataSet.Tables["Table2"].Columns["ParentId"])
{
    DeleteRule = Rule.Cascade,
    UpdateRule = Rule.Cascade
};
myDataSet.Tables["Table2"].Constraints.Add(fk);
myDataSet.EnforceConstraints = true;

var connection = new SqlConnection("my connection string");
var adapterForTable1 = new SqlDataAdapter();
adapterForTable1.InsertCommand =
    new SqlCommand("INSERT INTO MasterTable (ParentValue) VALUES (@ParentValue); SELECT SCOPE_IDENTITY() AS ParentId", connection);
adapterForTable1.InsertCommand.Parameters.Add("@ParentValue", SqlDbType.NVarChar).SourceColumn = "ParentValue";
var adapterForTable2 = new SqlDataAdapter();
adapterForTable2.InsertCommand =
    new SqlCommand("INSERT INTO ChildTable (ParentId, ChildValue) VALUES (@ParentId, @ChildValue); SELECT SCOPE_IDENTITY() AS ChildId", connection);
adapterForTable2.InsertCommand.Parameters.Add("@ParentId", SqlDbType.Int).SourceColumn = "ParentId";
adapterForTable2.InsertCommand.Parameters.Add("@ChildValue", SqlDbType.NVarChar).SourceColumn = "ChildValue";

connection.Open();
adapterForTable1.Update(myDataSet, "Table1"); // insert rows in parent first
adapterForTable2.Update(myDataSet, "Table2"); // child second

Answer 4

If the DataTables and SQL Tables line up, then a fast way is SqlBulkCopy.