Click here to Skip to main content
15,917,859 members
Articles / Web Development / ASP.NET

How to save data from a DataSet into a T-SQL table

Rate me:
Please Sign up or sign in to vote.
4.41/5 (17 votes)
5 Nov 2008CDDL2 min read 159.1K   1.6K   52   12
This article tells you how to save data from a DataSet into T-SQL tables.


This article will help you understand the procedure of saving data from a DataSet into T-SQL tables. This is often required when we pull data from external resources into a DataSet and want that data to be saved in our own database.


I have tried to explain the topic in detail, but a primary knowledge of C#, SQL, and XML is required to understand this article. In this article, we will do the following:

  1. Create a sample DataSet.
  2. Convert this DataSet into XML string.
  3. Send this XML string to a Stored Procedure, which will parse the XML and save the data into T-SQL tables.
  4. Analyse the T-SQL procedure which will perform step 3.

Create Sample Data

The SQL script for creating the SampleData table is given below. This script will create an empty table.

 [Name]  varchar(50) NOT NULL,
 [Address] varchar(50) NOT NULL,
 [Phone]  varchar(15) NOT NULL

The C# code below gets the sample data into a DataSet. We create three different columns named Name, Address, and Phone for the DataTable.

/// <summary>
/// This method is used to populate sample data.
/// Instead of this method, you can call a method which will
/// populate data from external data sources.
/// </summary>
/// <returns>DataSet with a sample data.</returns>
private static DataSet GetDataSet()
    DataSet ds = new DataSet();
    DataTable dt = new DataTable("Table");
    dt.Columns.Add("Name", Type.GetType("System.String"));
    dt.Columns.Add("Address", Type.GetType("System.String"));
    dt.Columns.Add("Phone", Type.GetType("System.String"));
    DataRow dr = dt.NewRow();
    dr["Name"] = "Sandeep Aparajit";
    dr["Address"] = "Redmond USA";
    dr["Phone"] = "425-000-0000";
    dr = dt.NewRow();
    dr["Name"] = "Anthony Gill";
    dr["Address"] = "Ohio USA";
    dr["Phone    "] = "625-000-0000";
    return ds    ;

Convert the DataTable (DataSet) into XML String

This is an important step. Here, we will actually convert the DataTable into its equivalent XML string. We make use of the DataSet.WriteXML() method for getting the XML string out of the DataSet.

/// <summary>
/// This method will convert the supplied DataTable 
/// to XML string.
/// </summary>
/// <param name="dtBuildSQL">DataTable to be converted.</param>
/// <returns>XML string format of the DataTable.</returns>
private static string ConvertDataTableToXML(DataTable dtData)
    DataSet dsData = new DataSet();
    StringBuilder sbSQL;
    StringWriter swSQL;
    string XMLformat;
        sbSQL = new StringBuilder();
        swSQL = new StringWriter(sbSQL);
        dsData.Merge(dtData, true, MissingSchemaAction.AddWithKey);
        dsData.Tables[0].TableName = "SampleDataTable";
        foreach (DataColumn col in dsData.Tables[0].Columns)
            col.ColumnMapping = MappingType.Attribute;
        dsData.WriteXml(swSQL, XmlWriteMode.WriteSchema);
        XMLformat = sbSQL.ToString();
        return XMLformat;
    catch (Exception sysException)
        throw sysException;

The Main Method

Here is the Main method, which will invoke the above mentioned methods for the conversion. Once the conversion of the DataSet to XML string is done, we will call the sp_InsertData Stored Procedure and pass this XML string as a parameter. This Stored Procedure is responsible for parsing the XML and inserting the data into the T-SQL table.

static void Main(string[] args)
    // Get the sample data into DataSet.
    DataSet dsData = GetDataSet();
    // Get the XML format of the data set.
    String xmlData = ConvertDataTableToXML(dsData.Tables[0]);
    // Create a SQLConnection object.
    // TODO: Specify the correct connection string as on you computer.
    SqlConnection conn = new SqlConnection
    ("Data Source=.;Initial Catalog=DBName;Integrated Security=SSPI;");
    // Create the SQlCommand object which will be used to insert the data
    // into T-SQL tables.
    SqlCommand command = new SqlCommand
    ("sp_InsertData '" + xmlData + "'", conn);
    // Open the SQL Connection.
    // Execute the stored procedure mentioned above .
    // Close the SQL Connection.

Stored Procedure: Sp_InsertData

The Stored Procedure sp_InsertData is the heart of this operation, since it parses the XML string and inserts the data into the T-SQL table.

* Stored Procedure : sp_InsertData
* Author : Sandeep Aparajit 
* Description : This stored procedure will accept the data as 
* an XML data table. It will parse the data table and will
* insert the data into the SampleData table.
* Date : 05 Nov 08
* Revision :
(@xmlString VARCHAR(MAX))
    /* Initialize a handle for the XmlDocument */ 
    DECLARE @xmlHandle INT 
    Create a table variable to store the extract XmlData into Relational 
    Database Format. Unlike temporary tables, Table variables are 
    automatically removed from memory by SQL Server when the procedure 
    has completed. So, there is no need to try and drop them at the 
    end of the procedure. 
    DECLARE @stagingTable TABLE 
        [Name] VARCHAR(50), 
        [Address] VARCHAR(50), 
        [Phone] VARCHAR(50)

    Create the XmlDocument using the handle above and the Xml 
    string as parameters. If your stored procedure has an varchar input 
    parameter named @xmlString, it would look like this instead: 
    EXEC sp_xml_preparedocument @xmlHandle output,@xmlString 
    EXEC sp_xml_preparedocument @xmlHandle output, @xmlString 
    Use the OPENXML method to query the XmlDocument starting at 
    /NewDataSet/SampleDataTable node.
    INSERT INTO @stagingTable 
    SELECT   [Name] ,
    FROM OPENXML (@xmlHandle, '/NewDataSet/SampleDataTable',1) 
        WITH ([Name] varchar(50) '@Name', 
            [Address] varchar(50) '@Address', 
            [Phone] varchar(50) '@Phone'

    /*Insert the records into the table variable turning the XML structured 
    data into relational data. We are now free to query the table variable 
    just as if it were a regular table for use with data manipulation, cursors, etc... 
    It could also be used for generated reports and counts in ways that might 
    be simpler to code in SQL Server vs XSL.*/ 
    INSERT INTO SampleData ([Name], 
    (SELECT [Name] ,
    FROM @stagingTable)

    /* Remove the document from memory */ 
    EXEC sp_xml_removedocument @xmlHandle


Do visit my blog for more interesting articles :)


  • Initial version of article released on 5th Nov 2008.


This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)

Written By
Software Developer (Senior)
United States United States
Sandeep has 9+ yrs of IT experience. He is Microsoft Certified Technology Specialist and has been certified for Analyzing Requirements and Defining Microsoft .NET Solution Architectures.
He is an active member of:
1. MSDN Forums

My Blog
More Info

Comments and Discussions

QuestionVery Helpful Pin
frikrishna28-Sep-16 1:59
frikrishna28-Sep-16 1:59 
Stick to point, excellent article
QuestionPROCEDURE sp_InsertData Behaviour Pin
Member 112104045-Nov-14 16:19
Member 112104045-Nov-14 16:19 
GeneralMy vote of 5 Pin
Anupam Shukla 200913-Dec-11 2:02
Anupam Shukla 200913-Dec-11 2:02 
QuestionWhere were you 2 months ago? Pin
simdor12-Nov-08 3:17
simdor12-Nov-08 3:17 
GeneralLength of XML will be limited by varchar(max) Pin
Member 36628185-Nov-08 22:15
Member 36628185-Nov-08 22:15 
GeneralRe: Length of XML will be limited by varchar(max) Pin
Sandeep Aparajit6-Nov-08 4:08
Sandeep Aparajit6-Nov-08 4:08 
GeneralDbDataAdapter Pin
Marc Clifton5-Nov-08 12:48
mvaMarc Clifton5-Nov-08 12:48 
GeneralRe: DbDataAdapter Pin
Sandeep Aparajit5-Nov-08 13:05
Sandeep Aparajit5-Nov-08 13:05 
GeneralRe: DbDataAdapter Pin
HiMik20035-Nov-08 20:34
HiMik20035-Nov-08 20:34 
GeneralRe: DbDataAdapter Pin
Marc Clifton6-Nov-08 1:17
mvaMarc Clifton6-Nov-08 1:17 
GeneralRe: DbDataAdapter Pin
Sandeep Aparajit6-Nov-08 4:09
Sandeep Aparajit6-Nov-08 4:09 
GeneralRe: DbDataAdapter Pin
cwp4211-Nov-08 4:07
cwp4211-Nov-08 4:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.