How to Pass XML data to SQL Server Stored Procedure

Valid for Environment: .NET 1.1, .NET 2.0, SQL Server 2000 or above

SQL Server 2000 supports XML. SELECT * FROM table FOR XML AUTO. Syntax sounds familiar, right? Yes, SQL Server 2000 supports XML. It not only supports returning the data in XML format, it also supports reading the XML string and parsing it. Before going to the implementation of Multiple Insert using this approach. To understand it a little bit, copy the code below and execute it in SQL Query Analyzer SQL Window :

DECLARE @intDocHandle int

DECLARE @xmlString varchar(4000)

SET @xmlString =’

<root>

<person PersonId=”1″ PersonName=”AA”/>

<person PersonId=”2″ PersonName=”BB”/>

<person PersonId=”3″ PersonName=”CC”/>

<person PersonId=”4″ PersonName=”DD”/>

</root>’

–Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlString

SELECT * FROM OPENXML(@intDocHandle,

‘/root/person’)

WITH

( PersonId INT,

PersonName VARCHAR(100)

)

– Remove the internal representation.

exec sp_xml_removedocument @intDocHandle

I am leaving further interpretation and understanding part up to you. It is quite easy to observe that it revolves around two important stored procs : sp_xml_preparedocument, sp_xml_removedocument and a key word OPENXML Let us now see how can we exploit this for Multiple Insert Scenario.

With the Logic mentioned above, the code for desired Stored Procedure looks like:

CREATE PROCEDURE sp_InsertByXML ( @strXML VARCHAR(4000) )

AS

BEGIN

DECLARE @intDocHandle int

–Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @strXML

INSERT INTO Person ( PersonId, PersonName )

SELECT * FROM OPENXML(@intDocHandle,

‘/PersonData/Person’, 2)

WITH

( PersonId INT,

PersonName VARCHAR(100)

)

– Remove the internal representation.

EXEC sp_xml_removedocument @intDocHandle

END

Now we need to form the XML at front end, which we can pass to this stored proc. I am sure, there can be various ways to do it. You can form by concatenating and forming XML or by using XMLDocument object of System.XML namespace and get the XML string out of it. Since most of times we play around DataSet and DataTables, I chose to get the XML out from the DataSet. First of all get the desired data in DataSet object. If you have trouble forming DataSet at runtime, refer to the “private string GetXml()” method in the sample code attached and then use the following code to get the string out of it :

System.IO.StringWriter sw = new System.IO.StringWriter ( );

dsPersonData.WriteXml (sw);

string strXml = sw.ToString();

With this I have the desired XML string. Now only job left is to call the stored procedure from my front end code, which is as follows:

private void btnInsertByXMLInput_Click(object sender, System.EventArgs e)

{

string strXml = GetXml();

SqlConnection connection = new SqlConnection(connectionString);

SqlCommand command = new SqlCommand(“sp_InsertByXML”, connection);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(“@strXML”,SqlDbType.VarChar, 4000);

command.Parameters[0].Value = strXml;

connection.Open();

int recordsInserted = command.ExecuteNonQuery();

connection.Close();

MessageBox.Show(“Number of records affected : ” + recordsInserted.ToString());

}

It also returns the message “Number of records affected : 4 “. Multiple records inserted, and Mission Accomplished yet again.

Key Notes:

1. XML is Case Sensitive. For example, in the OPENXML statement ‘/root/person’ is not equal to ‘/ROOT/person’.

2. You can form the Attribute Centric as well as Element Centric XML. In the code above, it is Element Centric XML thus we have “2” in OPEN XML syntax, else default is 1 which is used for Attribute Centric XML

3. In ADO.NET 2.0, you can get the XML out of DataTable itself. In such case modify the XPath in OPENXML appropriately.

4. SQL Server 2000 supports XML processing, but in SQL Server 2005 we have xml as datatype. It has got lot more ways to support DML and DDL for xml. Choose as per your need.

Use SQLBulkCopy of ADO.NET 2.0

Valid for Environment: NET 2.0 or above on SQL Server 2005 database or above

With ADO.NET 2.0 we got the programming interface for Bulk Copy which provides quite simple and straight forward mechanism to transfer the data from one SQL server instance to another, from one table to another, from DataTable to SQL Server 2005 database, from DataReader to SQL Server 2005 database and many more.

SqlBulkCopy belongs to System.Data.SqlClient namespace and it is as simple as ADO.NET Command object when it comes to programming it. Let us see it working:

private void btnSQLBulkCopyInsert_Click(object sender, EventArgs e)

{

// Get the DataTable

DataTable dtInsertRows = GetDataTable();

using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))

{

sbc.DestinationTableName = “Person”;

// Number of records to be processed in one go

sbc.BatchSize = 2;

// Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table

sbc.ColumnMappings.Add(“PersonId”, “PersonId”);

sbc.ColumnMappings.Add(“PersonName”, “PersonName”);

// Number of records after which client has to be notified about its status

sbc.NotifyAfter = dtInsertRows.Rows.Count;

// Event that gets fired when NotifyAfter number of records are processed.

sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);

// Finally write to server

sbc.WriteToServer(dtInsertRows);

sbc.Close();

}

}

void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)

{

MessageBox.Show(“Number of records affected : ” + e.RowsCopied.ToString());

}

The code above is very simple and quite self explanatory.

Key Notes :

1. BatchSize and NotifyAfter are two different properties. Former specify the number of records to be processed in one go while later specifies the number of records to be processed after which client needs to be notified.

Encrypt and Decrypt Passwords using Cryptostream

using System.Security.Cryptography;

Encryption

protected void EncryptIt(string Password)
{
byte[] data = System.Text.ASCIIEncoding.ASCII.GetBytes(Password);
byte[] rgbKey = System.Text.ASCIIEncoding.ASCII.GetBytes("56565656");
byte[] rgbIV = System.Text.ASCIIEncoding.ASCII.GetBytes("78787878");

//1024-bit encryption
MemoryStream memoryStream = new MemoryStream(1024);
DESCryptoServiceProvider desCryptoServiceProvider =
new DESCryptoServiceProvider();

CryptoStream cryptoStream = new CryptoStream(memoryStream,
desCryptoServiceProvider.CreateEncryptor(rgbKey, rgbIV),
CryptoStreamMode.Write);

cryptoStream.Write(data, 0, data.Length);
cryptoStream.FlushFinalBlock();

byte[] result = new byte[(int)memoryStream.Position];
memoryStream.Position = 0;
memoryStream.Read(result, 0, result.Length);

cryptoStream.Close();

string toDecrypt = System.Convert.ToBase64String(result);

DecryptIt(toDecrypt);
}

Decryption

protected void DecryptIt(string toDecrypt)
{
byte[] data = System.Convert.FromBase64String(toDecrypt);
byte[] rgbKey = System.Text.ASCIIEncoding.ASCII.GetBytes("56565656");
byte[] rgbIV = System.Text.ASCIIEncoding.ASCII.GetBytes("78787878");

MemoryStream memoryStream = new MemoryStream(data.Length);

DESCryptoServiceProvider desCryptoServiceProvider =
new DESCryptoServiceProvider();

CryptoStream cryptoStream = new CryptoStream(memoryStream,
desCryptoServiceProvider.CreateDecryptor(rgbKey, rgbIV),
CryptoStreamMode.Read);

memoryStream.Write(data, 0, data.Length);
memoryStream.Position = 0;

string decrypted = new StreamReader(cryptoStream).ReadToEnd();

cryptoStream.Close();
}