During the preparation for a sample for my upcoming book about SQL Service Broker I encountered a very interesting detail of the SQLCLR:
Let's assume that you want to instantiate a class through the design pattern Factory in SQLCLR. Have a look at the following code:

public interface ITask
{
   void Execute();
}

public class ConcreteTask : ITask
{
   public void Execute()
   {
      SqlContext.Pipe.Send("Hello world from ConcreteTask");
   }
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ManagedStoredProcedure(string TypeName)
{
   ITask task = InstantiateTask(TypeName);
}

The interesting thing is now how you implement the method InstantiateTask. The first try was that I used the method call Activator.CreateInstance and pass the assembly name and the class name for the requested type as a parameter. But when you do this you get the following error message from SQL Server 2005:

Msg 6522, Level 16, State 1, Procedure ProcessJobServerTasks, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'ProcessJobServerTasks':
System.IO.FileNotFoundException: Could not load file or assembly 'JobServer.Implementation' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Activator.CreateInstance(String assemblyName, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, Evidence securityInfo, StackCrawlMark& stackMark)
at System.Activator.CreateInstance(String assemblyName, String typeName)
at JobServer.Implementation.JobServerFactory.GetJobServerTask(String MessageType)
at JobServer.Implementation.JobServer.ProcessJobServerTasks(SqlString MessageType, SqlXml Message)

Finally I got a hint from John Mollman (http://blogs.msdn.com/mollman) from Microsoft. He suggested to use the method Type.GetType instead of Activator.CreateInstance. Here's the necessary code:

private static ITask InstantiateJobTask(string fqAssemblyName)
{
   if (null == fqAssemblyName || fqAssemblyName.Length == 0)
throw new ArgumentException("AssemblyName parameter cannot be null or empty", fqAssemblyName);
Type type = Type.GetType(fqAssemblyName);
if (null == type)
{
throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, "Requested type {0} not found, unable to load", fqAssemblyName), "fqAssemblyName");
}
ConstructorInfo ctor = type.GetConstructor(new Type[] { });
ITasktask = (ITGask)ctor.Invoke(new object[] { });
return task;
}

The parameter fqAssemblyName has for example the following format: JobServer.Implementation.DoNothingTask,JobServer.Implementation, Version=1.0.0.0,Culture=neutral, PublicKeyToken=neutral

As you can see from this sample you can do very interesting things with the SQLCLR in SQL Server 2005. You can write and host your own service logic programs with the SQLCLR in SQL Server 2005 and you also have the possibility to dynamically extend your service logic with patterns like the Factory design pattern described in this post.

-Klaus

My name is Klaus Aschenbrenner and I come from Vienna, Austria. I've worked with the .NET Framework and the SQL Server 2000 since the summer of 2000. For about 3 years I've worked and taught SQL Server 2005. In Austria I've also founded a user group called the .NET User Group Styria (http://www.csharp.at/). There we have monthly meetings on .NET topics. In the years 2004 & 2005 I was awarded with the Visual C# MVP award by Microsoft for my tremendous support ofthe local .NET community in Austria. I'm also the country lead of SQLPASS Austria, where we start in September with monthly meetings on SQL Server 2005 topics - stay tuned...

I'm employed as a Software Architect at ANECON (http://www.anecon.com/) in Vienna, where I'm leading several big .NET & SQL Server projects for several customers around Europe. It's a very nice place to work I'm also a regular writer on Asp Today and for the German .NET magazine DotNetPro.

In the last 2 years I have worked so much on SQL Server 2005, that I decided that I want to share this know-how with you, so that you can take advantage of it in your own SQL Server 2005 projects. In the beginning of this year I've also organized an event around SQL Server 2005 called the SQL Server 2005 Developers Summit. This was one intensive week about SQL Server 2005 where some collegues and I presented Hands-On-Labs on SQL Server 2005. All in all we had about 40 attendees, which is just great for Austria

In June my new book about SQL Server 2005 Service Broker is released by Apress: Pro SQL 2005 Service Broker. The whole book is about SQL Service Broker and how you can use the powerful messaging functionality of Service Broker in your own distributed SQL Server 2005 scenarios. So one aspect of this blog is also to give you more detailed information about my upcoming book and I will also discuss some topics and areas of the book directly here in my weblog, so that you can give me directly feedback to my opinions about this great topic!

Another big project that I've started with my girlfriend is building a house. I'll also cover about this great project, but  only in german, because there are so many specific terms that I don't know in english...

So stay tuned...

-Klaus

Welcome to my new blog on http://www.csharp.at
My weblog will cover posting from the following areas:

• .NET Framework 3.0 / SQL Server 2005
• All about flying
• Our house building project (only available in german language)

Happy reading and stay tuned!

-Klaus