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
.
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
.
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)
{
DataSet dsData = GetDataSet();
String xmlData = ConvertDataTableToXML(dsData.Tables[0]);
SqlConnection conn = new SqlConnection
("Data Source=.;Initial Catalog=DBName;Integrated Security=SSPI;");
SqlCommand command = new SqlCommand
("sp_InsertData '" + xmlData + "'", conn);
conn.Open();
command.ExecuteNonQuery();
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.
CREATE PROCEDURE sp_InsertData
(@xmlString VARCHAR(MAX))
AS
BEGIN
DECLARE @xmlHandle INT
DECLARE @stagingTable TABLE
(
[Name] VARCHAR(50),
[Address] VARCHAR(50),
[Phone] VARCHAR(50)
)
EXEC sp_xml_preparedocument @xmlHandle output, @xmlString
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 INTO SampleData ([Name],
[Address],
[Phone])
(SELECT [Name] ,
[Address],
[Phone]
FROM @stagingTable)
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.