CODEWITHSUNDEEP
c#xmldataset
MartGriff
MartGriff

Reputation: 2851

Write Dataset to SQL Table

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?

Upvotes: 5

Views: 25785

Answers (4)

MichaelGG
MichaelGG

Reputation: 10006

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

Upvotes: 3

Bryan Slatner
Bryan Slatner

Reputation: 1608

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

Upvotes: 7

leppie
leppie

Reputation: 117350

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)

Upvotes: 1

Ian P
Ian P

Reputation: 12993

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

Upvotes: -2

Related Questions