Recently working on an app that was uploading data from an Access database to remote SQL Server hosted by my clients website host.  The way the app was originally written just using inserts was taking well over 20 minutes to run and the amount of data was only growing and would therefore slow down even more.  I tasked myself with finding a better way.  I found some information on SQLXMLBULKLOADLib (although there's not much out there) and thought I'd give that route a try.

After some reading and trial and error, boy am I happy that I went that route...what  a difference it made... a 20 minute process is now under 2 minutes (and I could probably improve on that with some further optimization).  Code is simple..


            SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class jBulkLoad = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();

            jBulkLoad.ConnectionString = "put your connect string to SQL Server here"

            jBulkLoad.ErrorLogFile = @".\error-ci.log";

            jBulkLoad.Execute("CustomerInfo-schema.xml","CustomerInfo.xml");


The Execute function has two parameters, the first being the schema of the data, the second being XML representation of the data.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

 

  <xsd:element name="CustomerInfo" >

  <xsd:complexType>

    <xsd:sequence>

      <xsd:element name="CustomerID" type="xsd:integer" />

      <xsd:element name="CustomerName" type="xsd:string" />

      <xsd:element name="CustomerAddress1" type="xsd:string" />

    </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>


It just so happens my Access tables and SQL tables match exactly structure wise so this fell into place quite nicely.  I believe there is a way to translate the Access data into a differently named field in SQL, but I haven't tackled that yet.  I imagine it is just a simple change to the scheme XML. 

To generate the Data XML file I simply create a dateset with my data I want to upload and save it in XML format

        // save the XML file

        ds.WriteXml("CustomerInfo.xml");


The Execute method also accepts an XML Stream for the data XML instead of a file.

That's all there was to it....I'm sure it can be optimized more, but this is a huge improvement over what it was.  I'm sure I'll be coming back to this technique in the future.

As always I'm open to suggestions or anything constructive as I'm always trying to learn!