C# console app. SQLBulkCopy and quickly entering XML into a SQL Server DB

Question

C# with .net 2.0 with a SQL server 2005 DB backend.

I've a bunch of XML files which contain data along the lines of the following, the structure varies a little but is more or less as follows:

<TankAdvisory>
  <WarningType name="Tank Overflow">
    <ValidIn>All current tanks</ValidIn> 
    <Warning>Tank is close to capacity</Warning> 
    <IssueTime Issue-time="2011-02-11T10:00:00" /> 
    <ValidFrom ValidFrom-time="2011-01-11T13:00:00" /> 
    <ValidTo ValidTo-time="2011-01-11T14:00:00" /> 
  </WarningType>
</TankAdvisory>

I have a single DB table that has all the above fields ready to be filled.

When I use the following method of reading the data from the XML file:

DataSet reportData = new DataSet();
reportData.ReadXml("../File.xml");

It successfully populates the Dataset but with multiple tables. So when I come to use SQLBulkCopy I can either save just one table this way:

sbc.WriteToServer(reportData.Tables[0]);

Or if I loop through all the tables in the Dataset adding them it adds a new row in the Database, when in actuality they're all to be stored in the one row.

Then of course there's also the issue of columnmappings, I'm thinking that maybe SQLBulkCopy is the wrong way of doing this.

What I need to do is find a quick way of getting the data from that XML file into the Database under the relevant columns in the DB.

Jas · Accepted Answer · 2011-10-14 03:32:09Z

1

Ok, so the original question is a little old, but i have just came across a way to resolve this issue.

All you need to do is loop through all the DataTables that are in your DataSet and add them to the One DataTable that has all the columns in the Table in your DB like so...

DataTable dataTable = reportData.Tables[0];

//Second DataTable
DataTable dtSecond = reportData.Tables[1];

foreach (DataColumn myCol in dtSecond.Columns)
{

    sbc.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
    dataTable.Columns.Add(myCol.ColumnName);
    dataTable.Rows[0][myCol.ColumnName] = dtSecond.Rows[0][myCol];
}

//Finally Perform the BulkCopy

sbc.WriteToServer(dataTable);

answered Oct 14, 2011 at 3:32

Jas

711 silver badge1 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

animuson · Accepted Answer · 2011-12-21 20:47:36Z

1

foreach (DataColumn myCol in dtSecond.Columns)
{
    dataTable.Columns.Add(myCol.ColumnName);
    for (int intRowcnt = 0; intRowcnt <= dtSecond.Rows.Count - 1; intRowcnt++)
    {
        dataTable.Rows[intRowcnt][myCol.ColumnName] = dtSecond.Rows[intRowcnt][myCol];
    }
}

edited Dec 21, 2011 at 20:47

animuson♦

54.9k28 gold badges142 silver badges150 bronze badges

answered Dec 21, 2011 at 20:26

surya

111 bronze badge

Comments

John Leidegren · Accepted Answer · 2011-03-21 18:36:25Z

0

SqlBulkCopy is for many inserts. It's perfect for those cases when you would otherwise generate a lot of INSERT statements and juggle the limit on total number of parameters per batch. The thing about the SqlBulkCopy class though, is that it's a cranky. Unless you fully specify all column mappings for the data set it will throw an exception.

I'm assuming that your data is quite manageable since your reading it into a DataSet. If you where to have even larger data sets you could lift chunks into memory and then flush them to the database piece by piece. But if everything fits in one go, it's as simple as that.

The SqlBulkCopy is the fastest way to put data into the database. Just setup column mappings for all the columns, otherwise it won't work.

answered Mar 21, 2011 at 18:36

John Leidegren

61.3k21 gold badges139 silver badges158 bronze badges

2 Comments

scaryjones Over a year ago

Thanks for the reply but I'm not sure this addresses my issue. THe bulkcopy works fine when the dataset contains only one table, I can map the columns and it all works ok. The problem arises with the xml sections like 'IssueTime' which get added to the Dataset as different tables and end up being added to the db as distinct rows of info.

John Leidegren Over a year ago

Each SqlBulkCopy has one destination table. If you have a DataSet you use a SqlBulkCopy instance for each destination table in that DataSet.

Remus Rusanu · Accepted Answer · 2011-03-21 18:49:41Z

0

Why reinvent the wheel? Use SSIS. Read with an XML Source, transform with one of the many Transformations, then load it with an OLE Db Destination into the SQL Server table. You will never beat SSIS in terms of runtime, speed to deploy the solution, maintenance, error handling etc etc.

answered Mar 21, 2011 at 18:49

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

2 Comments

scaryjones Over a year ago

Hmm, hadn't heard of SSIS, but that link is for a product that works with SQL server 2008, is there a version for 2005?

Remus Rusanu Over a year ago

Go to that link, click on Versions, select SQL Server 2005.

Collectives™ on Stack Overflow

C# console app. SQLBulkCopy and quickly entering XML into a SQL Server DB

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related