Introduction
As most things this idea started with a simple wish spoken out loud, “I wish I could make a change to a table in SQL and have all of the CRUD sprocs automatically update”. Everybody and their brother have created a sproc generating program of one type or another but it always has to be run as a separate step. I’m sure some adventurous DBA has even created a sproc that creates sprocs.
Background
I had heard about SQL CLR and thought it was the right software stack to solve this problem. I started doing some research around the web and found several articles that were little more than “Hello World” or “Look ma it compiles!” type of demos, I didn’t find a lot of information on real world applications. After a few long days of work I had a working solution, many of the problems I ran into were small things that took hours to solve. Since I haven’t seen many in depth articles about SQL CLR I thought I could fill in the gap and contribute back to http://CodeProject.com since I have learned so much from the other publishers on other topics. In fact if you haven’t created a CLR SQL Stored Procedure before, I highly recommend the “Hello World” version
ClrSp.aspx[
^] to get started.
The Setup
The Database Setup
The first step is to create a new empty database, the name is not important. Some of the later steps in my design will be deleting sprocs as well as creating them, so you have been warned. The following sproc is only intended for development databases, so security was not a consideration. Use a local instance of SQL Server, the Express edition works great for all of the steps in this article. Trying to use a remote SQL Server will make debugging impossible and complicate other steps.
Start by creating a new Database, using a local instance of SQL Server 2005 (or newer) or SQL Server Express 2005 (or newer), both should work just fine. The database I created for this example is creatively called ‘TestDB’ and it meets all of the requirements stated above. Once the database has been created run the following SQL Script in the new database.
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
This will enable the use of CLR (Common Language Runtime) compiled objects to be run in the database. Please note that it must be configured per database, NOT per Server.
Visual Studio 2008 Setup
Create a new Project of type ‘SQL Server Project’ it can be found under ‘Visual C# \ Database’ or ‘Database \ Microsoft SQL Server \ SQL CLR’. I called the Project ‘CLRSQLTrigger’.
Visual Studio now wants to know where your database is, this is why we created the database first. Click on the ‘Add New Reference…’ button and select the database you created earlier. If you need to change the database connection at any time in the future, it is in the Project Properties on the Database tab and has the same prompting you used here, so it’s pretty easy.
Right Click on the Project and select ‘Add\Trigger’. Most of the following advice will apply equally to stored procedures, triggers and functions. For this task select ‘Trigger’. The trigger name is ‘SprocGen’
Visual Studio gives us just a little shell of a program and, of course, the most important line is commented out. Start by un-commenting it so you can get the color coding of what is going on.
[Microsoft.SqlServer.Server.SqlTrigger(Name="SprocGen", Target="Table1",
Event="FOR UPDATE")]
In understanding this attribute line it’s clearly a trigger which we specified. So we move on to the ‘Name’ this is what it will be called when it’s deployed to SQL Server, the name can be any legitimate C#/SQL name and does not have to match the name of the method below but that is a good idea for the trigger name and the method name to be similar if not the same. The Target defaults to a generic table name and the Event defaults to Triggering on Table1 Update.
For our purposes, change the Target to ‘DATABASE’. Changing the Target also changes the options available for ‘Event’, so change the Event to ‘FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE’ This will cause our trigger to respond to the primary events that can happen to all tables in our new Database.
Also note that setting the Target=”All Server” will not be depolyable automatically from Visual Studio. I suggest waiting to create Server Level Triggers, until you are familiar with the scripting required to install the Assemblies manually, or create them at a Database Level where possible then change them to ‘All Server’.
The above link for ‘More’ also shows the treeview style relationship between the events, and shows off the groups that can be used to capture more events with simpler code.
Security and Limitations
The biggest thing that doesn’t seem to be talked about CLR SQL is the limitations. The main reason for these limitations is almost always security whether it’s File System permissions, or the ability to call external programs. Especially for production servers I have to agree with defaulting to minimum permissions and forcing the developer to enable what they need. The downside is it can be difficult to figure out why something isn’t working.
If something is failing silently it’s probably just not obvious what direction it’s shouting in. The first place I suggest looking is the Windows Event log. Start with the Application list of events then check the security list. Odds are the problem is there and hopefully has enough information to fix it.
One of the limitations appears to be the ability to add references to your project. Right clicking on the Projects References Folder and selecting “Add References …” Using .NET Framework version 2.0 I have 12 Assemblies available for Referencing directly. If you have done the same thing on any other application you may recall that the list is really long, I would guess around a hundred. The list that is displayed is actually from SQL Server. While SQL Server does have the .NET framework available it’s a very minimum installation. If the assembly is created by you add the project to the solution and it will be deployed seamlessly with your primary assembly. If the assembly is from a 3rd party use the ‘Create Assembly’, ‘Alter Assembly’ and ‘Drop Assembly’ SQL commands to add it to SQL Server and then it will be available to add to your project using the Add Reference dialog.
Debugging
The standard workflow to debug a CLR SQL assembly is the following…
- Right-Click the project and select ‘Deploy’
- Debug | Attach to Process…
- Select the Process ‘sqlservr.exe’ and make sure it is the one with a Type of ‘T-SQL, Managed, x86 or x64’
- Press the Attach Button
- Check that you have a break point set.
- Perform an Action (usually in ‘Microsoft SQL Server Management Studio’) that will cause your trigger to fire.
- If all went according to plan Visual studio should stop executing at your breakpoint.
I found that at this point I now had 30-60 seconds to step and debug my issue, after that time period the debug session would time out. The good news is simply pushing the ‘Continue/Play’ button and restart at step 6 would usually work.
What to do when the standard workflow doesn’t work.
There is an initial setting that is done for you behind the scenes that allows CLR SQL to be debugged; somewhere up to this point you probably were prompted with a message box that asked if you wanted to enable debugging. This works great if your code is always on the same machine. When I worked on this code on another machine I could not use the standard debugging technique, so I went looking.
The following are some options I considered and why they were or were not good choices.
- EventLog: security was too tight, Administrator privilege required to create an Event Source.
- SqlContext.Pipe: it would probably work well in a stored procedure, because the SQL Management Studio Message Pane is visible by default but doesn’t work well for Triggers which are called behind the scenes.
- Logging to a text File: security was tight (explanation below)
- Writing to a log table: If you think about it, your sproc could have trouble writing to any table why is your log table going to be any different?
After spending 15 minutes to an hour on each technique, I found an article listed in the references below that was a CLR SQL Stored Procedure that all it did was write to an External text file and he was kind enough to describe what it would take security wise. Novick’s article is also very detailed and step by step for anybody just starting out with CLR SQL.
First decide what folder on your hard drive you will be logging to, create it if you want to. It may sound easier to just dump it in to ‘C:\outfile.txt’ it doesn’t work. Especially with Windows Vista, Win7 and Windows 2008 Server it requires Administrator permissions to write to a Text File in the root of the drive and opening up that permission is dangerous. The following steps are for creating a new folder with the required permissions.
- Create a new folder called ‘C:\CLRSQLoutput’.
- Click Start | Run, type ‘services.msc’, press Enter
- Look down the list for ‘SQL Server(xxxxxx)’ where xxxxxx is the name instance of SQL Server you are using.
- By default the instance is probably SQLEXPRESS
- Double Click to open the properties for this service, click the ‘Log on’ tab.
- The account listed is probably ‘Network Service’ or ‘Local System’
- Go back to the new folder you created and right click on it and select the ‘Security’ tab
- The popup windows and such get really confusing here so you may want to find some detailed directions elsewhere, but the end result is make sure the account that SQL Server is running under (the one you found in Step 6) has Write permissions on the folder you created.
- Then include the following new variable and method in your stored procedure.
private const string outfilePath = @" C:\CLRSQLoutput\sqlout.txt";
internal void Log(string content)
{
File.AppendAllText(outfilePath, content);
}
- Next, in the Visual Studio Solution Explorer, right click on the Project and select ‘Properties’
- Select the ‘Database’ tab and change the ‘Permission Level’ to ‘External’
- In ‘Microsoft SQL Server Management Studio’, run the following SQL script
ALTER DATABASE testDB SET TRUSTWORTHY ON
EXEC sp_changedbowner sa
Remember this was all just to get some debugging output. The good news is when you are done debugging, start at step 10 to undo the relaxed security. The default Permission level was ‘Safe’ and DB owner was ‘machineName\username’.
Deploy
When the Assembly has been deployed it is visible in ‘Microsoft SQL Server Management Studio’.
- Connect to your local server
- Expand the database name that you are using
- Expand the Programmability Folder
- Each Project/Assembly can contain multiple Triggers and/or Stored Procedures
- The Triggers are located in the ‘Database Triggers’ Folder
- Stored Procedures are located in the ‘Stored Procedures’ Folder
- The Assembly is stored in the ‘Assemblies’ Folder
- Completely removing the Sprocs requires that all Stored Procedures and Triggers located in the Assembly be dropped/deleted before the assembly can be removed.
- Using the right-click Create menu on the Stored Procedure/Trigger/Functions/Assembly will generate Text that contains the Hex version of the compiled assembly and/or the original source or debug information. This will allow the CLR SQL object to be installed on another server with a single script.
The Real Code (not yet)
The next thing to understand is the environment or context that your code is running inside of. Add the following line of code inside the ‘SprocGen’ Method replacing the line ‘SqlContext.Pipe.Send("Trigger FIRED");’
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
There are two key properties on this context, they are ‘EventData’ which is a SqlXml Data Type that holds many other properties and ‘TriggerAction’ which tells us what happened to cause our trigger to fire. Let’s take the last one first and add the following code to our Trigger…
if (myTriggerContext.TriggerAction == TriggerAction.CreateTable)
{
}
else if (myTriggerContext.TriggerAction == TriggerAction.AlterTable)
{
}
else if (myTriggerContext.TriggerAction == TriggerAction.DropTable)
{
}
Now let’s look closer at the EventData Property it’s given to us in SqlXml which for our purposes is not a very useful data type. SqlXml is used because the values available are different for every type of event that occurs. There are close to 100 different combinations of about 100 different properties. Most combination expose between 8 and 12 properties. Below is a class that exposes the properties for the SprocGen trigger as well as a few more that show how to access a few of the other Property Types. It should be easy to copy and rename properties that are needed for your CLR SQL objects.
using System.Data.SqlTypes;
using System.Xml;
using System.Xml.Serialization;
namespace CLRSQLTrigger
{
public class SqlEventData
{
readonly XmlDocument document = new XmlDocument();
public SqlEventData(SqlXml sqlXml)
{
if (sqlXml != SqlXml.Null)
{
document.LoadXml(sqlXml.Value);
}
}
public static T SerializeToType<T>(SqlXml document)
{
XmlSerializer deserializer = new XmlSerializer(typeof(T));
using (XmlReader reader = document.CreateReader())
{
return (T)deserializer.Deserialize(reader);
}
}
[XmlElement]
public string EventType
{
get { return document.GetElementsByTagName("EventType")[0].InnerText; }
}
[XmlElement]
public string PostTime
{
get { return document.GetElementsByTagName("PostTime")[0].InnerText; }
}
[XmlElement]
public string SPID
{
get { return document.GetElementsByTagName("SPID")[0].InnerText; }
}
[XmlElement]
public string ServerName
{
get { return document.GetElementsByTagName("ServerName")[0].InnerText; }
}
[XmlElement]
public string LoginName
{
get { return document.GetElementsByTagName("LoginName")[0].InnerText; }
}
[XmlElement]
public string UserName
{
get { return document.GetElementsByTagName("UserName")[0].InnerText; }
}
[XmlElement]
public string DatabaseName
{
get { return document.GetElementsByTagName("DatabaseName")[0].InnerText; }
}
[XmlElement]
public string SchemaName
{
get { return document.GetElementsByTagName("SchemaName")[0].InnerText; }
}
[XmlElement]
public string ObjectName
{
get { return document.GetElementsByTagName("ObjectName")[0].InnerText; }
}
[XmlElement]
public string ObjectType
{
get { return document.GetElementsByTagName("ObjectType")[0].InnerText; }
}
[XmlElement]
public string TSQLCommand
{
get
{
XmlNodeList result = document.GetElementsByTagName("TSQLCommand");
if (result.Count > 0)
{
return result[0].InnerText;
}
return string.Empty;
}
}
[XmlElement]
public string AlterTableActionList
{
get {XmlNodeList actionList =
document.GetElementsByTagName("AlterTableActionList");
if (actionList.Count > 0)
{
return actionList[0].InnerText;
}
return string.Empty;
}
}
public override string ToString()
{
return document.OuterXml;
}
}
}
Many of the comments are in the code, but let’s highlight what they are. I was a little disappointed. I really wanted to use the SerializeToType method that is included, but Serialization uses Reflection which requires more permission than we have by default. I left the code in because it may be useful if the EventData is saved outside of the sproc. The class also has a custom ToString implementation that actually returns the Original SqlXml. This will probably be handy when writing debug text to the log file.
Add the following line in the middle because we want to be able to access this data in the ‘If’ and the ‘Else’.
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
SqlEventData eventData = new SqlEventData(myTriggerContext.EventData);
if (myTriggerContext.TriggerAction == TriggerAction.CreateTable)
{
If you are following along I suggest this to be the minimal point you should have a working trigger that can do something. Add the following code to the ‘If’ and the ‘Else’.
Log(eventData.ToString());
Right click the project and select ‘Deploy’ then use ‘Microsoft SQL Server Management Studio’ to create or modify a table in your TestDB. As a note I received some Permissions errors that appeared to be related to the ‘Read-Only’ attribute on the new folder. Changing Permissions must have modified the read-only attribute.
When everything is working correctly take a look at the contents of the text file. Of course, the data is not formatted nicely and with your own personal information instead of my modified information.
<EVENT_INSTANCE>
<EventType>ALTER_TABLE</EventType>
<PostTime>2011-08-25T16:47:42.830</PostTime>
<SPID>57</SPID>
<ServerName>MyComputer</ServerName>
<LoginName>James</LoginName>
<UserName>dbo</UserName>
<DatabaseName>TestDB</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Table_1</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>ALTER TABLE dbo.Table_1 ADD t nchar(10)
NULL</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
Linked to this article you will find the source code for my Sproc Generator so you can see my full working example. For this article I’m going to work on a little more of the Infrastructure to create a nice template for making these CLR SQL Triggers a little easier to write. This article is being released without the extra code samples because the body of the article is probably more valuable than the actual code and the samples embedded here are sufficient to get started.
Now the ‘Final’ code
SQLTrigger methods have to be static but everybody wants an encapsulated object that each does a single specific task. Let’s go in that direction and create a new Class called ‘SprocGenWorker’. One of the things I figured out early was that you are allowed only a single connection object, but it has to be available for any of your other methods to use. So we can’t really wrap it in a using statement inside of each method. So we will take a little bigger approach. Add an IDisposable implementation to the new class. Honestly a few other things got refactored at this point so lets go to the code.
using Microsoft.SqlServer.Server;
namespace CLRSQLTrigger
{
public class TriggerTemplate
{
[SqlTrigger(Name = "TriggerTemplate", Target = "DATABASE",
Event = "FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE")]
public static void TheTrigger()
{
using (TriggerTemplateBase templateBase = new TriggerTemplateBase())
{
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
if (myTriggerContext != null)
{
SqlEventData eventData = new SqlEventData(myTriggerContext.EventData);
if (myTriggerContext.TriggerAction == TriggerAction.CreateTable)
{
templateBase.WriteLog(eventData.ToString());
}
else if (myTriggerContext.TriggerAction == TriggerAction.AlterTable)
{
templateBase.WriteLog(eventData.ToString());
}
else if (myTriggerContext.TriggerAction == TriggerAction.DropTable)
{
templateBase.WriteLog(eventData.ToString());
}
}
}
}
}
}
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace CLRSQLTrigger
{
public class TriggerTemplateBase : IDisposable
{
private readonly SqlConnection connection =
new SqlConnection("context connection=true");
public SqlEventData EventInstance { get; set; }
public bool ExecuteSQL(string commandText)
{
bool result = false;
if (!string.IsNullOrEmpty(commandText))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (SqlCommand command = new SqlCommand(commandText, connection))
{
try
{
result = (command.ExecuteNonQuery() > 0);
}
catch (Exception exception)
{
#if DEBUG
WriteLog(exception.Message);
#endif
}
}
}
return result;
}
private string fileName = @"C:\CLRSQLoutput\sqlout.txt";
public string OutFileName
{
get { return fileName; }
set { fileName = value; }
}
public void WriteLog(string contents, SqlEventData eventData)
{
File.AppendAllText(OutFileName, eventData.ToString());
WriteLog(contents);
}
public void WriteLog(string contents)
{
File.AppendAllText(OutFileName, contents);
}
#region Implementation of IDisposable
public void Dispose()
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
}
#endregion
}
}
Conclusion
Using the 3 main code samples above you should be able to do some really cool things. If you have questions post them and I’ll see what I can do.
