CODEWITHSUNDEEP
c#xmldataset
Erik
Erik

Reputation: 225

C# - Insert DataSet into a SQL Table

I have a DataSet that I fill with values from a XML-file. I would like to insert the values then into a SQL table. How do I do that?

Here is how I fill my DataSet:

        DataSet dataset = new DataSet();

        dataset.ReadXml(xmlfile);
        customer.DataSource = dataset.Tables[0];

Upvotes: 0

Views: 7892

Answers (5)

Robert Rossney
Robert Rossney

Reputation: 96920

If all you're ever going to do is inserts, then the simplest way is to just loop through the rows in the DataTable and create and execute a DbCommand for each row. The specific syntax to use depends on the kind of database you're using; for SQL Server, it might look like this:

string sql = "INSERT INTO T (A, B, C) VALUES (@A, @B, @C)";
using (SqlConnection conn = new SqlConnection(connectionString))
{
   conn.Open();
   foreach (DataRow r in myTable.Rows)
   {
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = sql;
      cmd.Parameters.AddWithValue("@A", r["A"]);
      cmd.Parameters.AddWithValue("@B", r["B"]);
      cmd.Parameters.AddWithValue("@C", r["C"]);
      cmd.ExecuteNonQuery();
   }
}

This glosses over many, many possible complicating factors, e.g. exception handling, filtering out rows with the wrong DataRowState, calling AcceptChanges on the DataRow, updating identity columns with the value assigned by the database, and so on. There's a lot to understand in ADO.NET, and that stuff's in there for a reason. But if all you're going to do is insert rows, and the DataColumn objects in your DataTable have the right names and types and proper values for Size and AllowDbNull, and there are no foreign-key relations involved, and you're not going to encounter duplicate primary keys with existing data in the table, the above should work.

Upvotes: 2

RoughPlace
RoughPlace

Reputation: 1121

This completely depends on you existing Data access Layer.

If one doesn't exist i would look more towards LinqtoSQL rather than using Stored procedures, Stored procedures are more difficult to manage

Upvotes: 0

Reactor
Reactor

Reputation: 99

Simple,

Just use the DataSet.ReadXml() method.

http://msdn.microsoft.com/en-us/library/system.data.dataset.readxml.aspx

Now if your xml is different from your schema, then your most likely going to have to loop and fill your schema specific dataset, then save as needed.

Upvotes: -1

Jack Marchetti
Jack Marchetti

Reputation: 15754

I think you would need to loop through the dataset and perform inserts.

You can obviously do this with a sproc, and you just pass along the parameters, or with LINQ.

Either way should work.

Upvotes: 0

slandau
slandau

Reputation: 24102

My suggestion would be to create a stored procedure for your insert and then create a method that traverses the data set, and sets the parameters of your stored proc and then executes it.

Upvotes: 0

Related Questions