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

http://www.codeproject.com/KB/database/Load_DataSet_In_SQL_Table.aspx

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.

Collapse

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.

Collapse

/// <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.

Collapse

/// <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.

Collapse

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.

Collapse

/******************************************************************************
* 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

Convert DataReader to Dataset (C#)

http://netindonesia.net/blogs/rahmat.faisal/pages/10093.aspx

public static DataSet convertDataReaderToDataSet(SqlDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table

DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();

if ( schemaTable != null )
{
// A query returning records was executed

for ( int i = 0; i < schemaTable.Rows.Count; i++ )
{
DataRow dataRow = schemaTable.Rows[ i ];
// Create a column name that is unique in the data table
string columnName = ( string )dataRow[ "ColumnName" ]; //+ “<C” + i + “/>”;
// Add the column definition to the data table
DataColumn column = new DataColumn( columnName, ( Type )dataRow[ "DataType" ] );
dataTable.Columns.Add( column );
}

dataSet.Tables.Add( dataTable );

// Fill the data table we just created

while ( reader.Read() )
{
DataRow dataRow = dataTable.NewRow();

for ( int i = 0; i < reader.FieldCount; i++ )
dataRow[ i ] = reader.GetValue( i );

dataTable.Rows.Add( dataRow );
}
}
else
{
// No records were returned

DataColumn column = new DataColumn(“RowsAffected”);
dataTable.Columns.Add(column);
dataSet.Tables.Add( dataTable );
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add( dataRow );
}
}
while ( reader.NextResult() );
return dataSet;
}