CONVERTING DATA IN A DATATABLE TO SQLXML TO SEND TO THE DATABASE

Hi All,

Today I'm going to explain on how to convert the data in a DataTable to SqlXml. This would be helpful when you have lots of records in a DataTable, and you need to insert them at one go rather than inserting each row one at a time.

So I will create a small console based application to demonstrate this. I will show 2 ways on how this can be done. But I'm not sure about the performance in both the approaches.

First up I will create a DataTable and add some columns to it. Remember when you create the DataTable you have to give a name to it. Otherwise the XML cannot be created.

Creating the DataTable

I have named my DataTable as "SampleTable". Then I add some records to this.

Adding Records to the DataTable

So I inserted 2 records to the DataTable. To create the XML I will be using a stream. The stream that I'm going to use is a MemoryStream. DataTable has a method called WriteXml() which has 16 overloaded versions. I will use the overloaded version of the method which accepts a stream object.

Writing the data in the DataTable to the stream

Now we have the stream. I will explain 2 ways on how to convert this stream to SqlXml.

Method 1:

This is the most simplest way of doing it. I can create a object of type SqlXml by passing the stream object to the constructor. Remember to Flush and Close the stream once you have finished working with it.

Creating the SqlXml from the stream

Output :

Method 2 :

The next method is to use a XmlTextReader. Then from the XmlTextReader object I construct the SqlXml object.

Creating the SqlXml from the XmlTextReader

Output :

Both the outputs are the same. This XML can be sent into a stored procdeure in the database to insert the data to the tables.

Thats all for this post.
Happy Coding.