none
Using SqlBulkCopy class to write data to multiple tables. RRS feed

  • Question

  • Hi

    I am trying to read the following xml structure and insert records into my SQL DB tables Report, Product and Transactions:

    <Report>
    <Organization>Abc Inc. </Organization>
    <Year>2010</Year>
    <Quarter>1</Quarter>
    <Product>
    <Name>Benzene</Name>
    <Code>B-001</Code>
    <Transaction-Item type="Sales">
    <StartDate>2010-04-01</StartDate>
    <EndDate>2010-04-30</EndDate>
    <Amount>20000</Amount>
    <BusinessPartner>BOA</BusinessPartner>
    <Description>Sold Benzene</Description>
    </Transaction-Item>
    <Transaction-Item type="Indirect Sales">
    <StartDate>2010-04-01</StartDate>
    <EndDate>2010-04-30</EndDate>
    <Amount>25000</Amount>
    <BusinessPartner>BOA</BusinessPartner>
    <Description>Sold Benzene</Description>
    </Transaction-Item>
    </Product>
    <Product>
    <Name>Ethanol</Name>
    <Code>E-001</Code>
    <Transaction-Item type="Sales">
    <StartDate>2010-04-01</StartDate>
    <EndDate>2010-04-30</EndDate>
    <Amount>26000</Amount>
    <BusinessPartner>BOA</BusinessPartner>
    <Description>Sold Ethanol</Description>
    </Transaction-Item>
    <Transaction-Item type="Indirect Sales">
    <StartDate>2010-04-01</StartDate>
    <EndDate>2010-04-30</EndDate>
    <Amount>27000</Amount>
    <BusinessPartner>WF</BusinessPartner>
    <Description>Sold Ethanol</Description>
    </Transaction-Item>
    </Product>
    </Report>

    I am using the sqlbulk copy class to write the data to the tables and I am using a class which implements the IDataReader to read the data from xml.

    So my question is as follows:

    1. Is it possible to write data to three tables with foreign key relationships between them using SQLBulkCopy, since my reader will have 1 Product node at a time which will contain 1 or more transaction items. So I want to insert a row in Product table and also 1 or more rows in the Transaction table.
    2. Also as you can see from the XML structure, I have some header data like Organization name, year quarter. I would like to read this data as well and put it to a different table Report in my db using the sqlbulk copy class.

    Thanks for reading my post.

    Wednesday, May 26, 2010 6:04 PM

All replies

  • Given your requirements, SqlBulkCopy alone will not solve this.

    SqlBulkCopy will let you load data into a single target table.

    For your scenario, you can load the XML into a DataSet, then use SqlDataAdapter.Update to upload the data to multiple tables, this would be the simplest method I can think of.  You need to create one SqlDataAdapter per unique table, but the DataSet can hold the data for all the related tables.

     

    Monday, October 4, 2010 5:49 PM
    Moderator