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

• Download source code - 16.9 KB

Introduction

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.

Background

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.

CREATE TABLE SampleData
(
 [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";
    dt.Rows.Add(dr);
    
    dr = dt.NewRow();
    dr["Name"] = "Anthony Gill";
    dr["Address"] = "Ohio USA";
    dr["Phone    "] = "625-000-0000";
    dt.Rows.Add(dr);
    
    ds.Tables.Add(dt);
    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;
    try
    {
        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.
    conn.Open();
    
    // Execute the stored procedure mentioned above .
    command.ExecuteNonQuery();
    
    // Close the SQL Connection.
    conn.Close();
}

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 :
******************************************************************************/
CREATE PROCEDURE sp_InsertData
(@xmlString VARCHAR(MAX))
AS
BEGIN
    /* 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] ,
        [Address],
        [Phone] 
    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], 
            [Address], 
            [Phone]) 
    (SELECT [Name] ,
        [Address],
        [Phone]
    FROM @stagingTable)



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

Others

Do visit my blog for more interesting articles :)

History

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