Using the CLR in SQL Server 2005

I’m using SQL Server 2005 on current project, and started looking at the new CLR support, where .net code can be directly loaded into SQL server.

This has already been mentioned on plenty of other blogs, but I figured I’ll repost here for my own reference.  To enable the CLR for a database, you need to the run the following:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

To create a simple stored procedure to return the values from multiple tables, use SqlContext.Pipe, as follows:

public partial class StoredProcedures
{
	[SqlProcedure]
	public static void GetItemById(int id)
	{
		StringBuilder SqlStatement = new StringBuilder();

		// Get the tables
		SqlStatement.AppendLine("SELECT * FROM ItemHeader WHERE (ItemHeaderId = " + id + ")";
		SqlStatement.AppendLine("SELECT * FROM ItemDetail WHERE (ItemHeaderId = " + id + ")";

		// Send the the command output to the client
		using (SqlConnection conn = new SqlConnection("context connection=true"))
		{
			using (SqlCommand command = conn.CreateCommand())
			{
				conn.Open();
				command.CommandText = SqlStatement.ToString();
				SqlContext.Pipe.ExecuteAndSend(command);
				conn.Close();
			}
		}
	}
}

You could easily achieve the same thing with T-SQL, but it’s a good way to get started with the CLR, though there might be a small performance overhead (though in this case, I would imagine it’s minimal).

This entry was posted in Uncategorized by Mun. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*