Passing XML to Sql Server

One may want to pass an array of data to SQL Server. The most recent reason for me was so I could look for a specific record based on multiple criteria.

There are several ways to pass XML to SQL Server. One of the simplest ways to pass the data is as a string. However other options are available such as creating an SqlXML object from a list using the following C# function from

/**
* Get an SqlXml representation of the list
*
* @param IEnumerable list: The list to convert
* @return SqlXml
**/
public static SqlXml GetSqlXml(IEnumerable list)
{
    //We don't use 'using' or dispose or close the stream,
    //since it lives in the return variable
    MemoryStream stream = new MemoryStream();
    using (XmlWriter writer = XmlWriter.Create(stream))
    {
        writer.WriteStartElement("list");
        foreach (object obj in list)
        {
            writer.WriteElementString("item", obj.ToString());
        }
        writer.WriteEndElement();
        stream.Position = 0;
        return new SqlXml(stream);
    }
}

From: http://www.codeproject.com/Articles/20847/Passing-Arrays-in-SQL-Parameters-using-XML-Data-Ty

Another way to create the XML representation, in VB.net, is to use the following:

Dim xmlDoc As New System.Xml.XmlDocument
Dim table As System.Xml.XmlElement = xmlDoc.CreateElement("table")
For Each child As String In New String() {"s1", "s2", "s3"}
    Dim id As System.Xml.XmlElement = xmlDoc.CreateElement("id")
    id.innerText = child
    table.AppendChild(id)
Next
xmlDoc.AppendChild(table)
 

Then pass the xmlDoc as a string. Once you have the parameter set up, you can define a stored procedure to accept an XML parameter such as the following:

CREATE PROCEDURE [dbo].[StoredProcedure1] ( @Criteria XML = NULL )
AS
BEGIN
    ...
END
 

The last important piece is to parse the XML in SQL Server. If you have the following XML:

<table>
    <id>1</id>
    <id>2</id>
    <id>3</id>
</table>
 

You can access the “id” nodes by using the following:

SELECT r.id.value('.', 'varchar(50)') AS ID
FROM @Criteria.nodes('/table/id') AS r(id)

Simple Cache Class in C#

A simple class that caches Hashtables within a Session object in C#.

using System;
using System.Web;
using System.Web.SessionState;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;

public class Cache
{
	/**
	 * Delete the cache item from the cache by key
	 * 
	 * @param string CacheKey: The data to delete
	 * @return bool: Indicates the cache key was deleted
	 **/
	public static bool Delete(string CacheKey)
	{
		HttpContext.Current.Session[CacheKey] = null;
		return true;
	}

	/**
	 * Get the cache item by key
	 * 
	 * @param string CacheKey: The data to retrieve
	 * @return Hashtable: The data retrieved
	 **/
	public static Hashtable Get(string CacheKey)
	{
		if (HttpContext.Current.Session[CacheKey] != null)
		{
			Cache.Item item = (Cache.Item)HttpContext.Current.Session[CacheKey];
			if (item.Expiration >= DateTime.Now)
			{
				return item.Data;
			}
		}

		return null;
	}

	/**
	 * Set a cache item
	 * 
	 * @param string CacheKey: The key to set for the item
	 * @param Hashtable Data: The data to set
	 * @param int NumSecondsAlive: The number of seconds to set the cache for (defaults to 5 seconds)
	 * @return Hashtable: The data retrieved
	 **/
	public static bool Set(string CacheKey, Hashtable Data = null, int NumSecondsAlive = 5)
	{
		Cache.Delete(CacheKey);
		HttpContext.Current.Session[CacheKey] = new Cache.Item(Data, NumSecondsAlive);

		return true;
	}

	/**
	 * 
	 * Start Item Class
	 * 
	 **/
	private class Item
	{
		public Hashtable Data = null;
		public DateTime Expiration = new DateTime();

		public Item(Hashtable Data, int NumSecondsAlive)
		{
			this.Data = Data;
			this.Expiration = DateTime.Now.AddSeconds(NumSecondsAlive);
		}
	} // End Item Class
} // End Cache Class

You may want to consider performance and efficiency when implementing this on an actual site.