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






4.41/5 (14 votes)
This article tells you how to save data from a DataSet into T-SQL tables.
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:
- Create a sample
DataSet
. - Convert this
DataSet
into XML string. - Send this XML string to a Stored Procedure, which will parse the XML and save the data into T-SQL tables.
- 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 5th Nov 2008.