How to Call a .NET Assembly From a SQL Server Scheduled Job

This is a step-by-step example of an unmanaged call, i.e. from a DTS Active X script, to a .NET assembly, using a SQL Server scheduled Job as the caller's context. Almost any class in .NET can be exposed to a COM client simply by exporting the information for its assembly and registering the assembly with COM. The class must have a default constructor and the methods, properties, fields, and events you want to expose must be public.

I'll now show you a step-by-step implementation of a COM client (a SQL Server scheduled Job) using .NET and C#.

Step 1. Update system path variable

Append to the System Path variable both .NET directory paths to run the .NET commands from any directory at the command prompt. The paths will vary according to the .NET Versions(1.0 or 1.1). These are the Version 1.0 paths that are used for this example on a Windows 2000 Professional system.

C:\WINNT\Microsoft.NET\Framework\v1.0.3705

C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin

• Go to the Desktop.
• Right-click on the My Computer icon.
• Click on the Properties item in the context menu.
  

The System Properties dialog box will pop up >> Click on the Advanced tab.

• Click on the Environment Variables... button.
• The Environment Variables dialog box will pop up.
• Highlight the Path item in the System variables panel and click on the Edit button.
  |

The Edit System Variable dialog box will pop up, as shown below.

In the Variable Value text box append both .NET path values to the value in the text box. First, insert a semi-colon (;) just after the last character in the text box, then append the .NET paths separated by a semi-colon to the value in the text box. See the semi-colons in red font below.

C:\WINNT\Microsoft.NET\Framework\v1.0.3705;C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin

Click OK to get out of the three dialogs.

Now, we can utilize the .NET command-line utilities from any directory of the command prompt.

Step 2. Create the assembly

For SQL Server to be able to get to the assembly at runtime and use it as a COM object, the assembly has to be registered with COM, and it has to be either placed in the same folder as the host executable or registered in the .NET global assembly cache (GAC) so that the runtime can find it. To add the assembly to the GAC, you will need to digitally sign it. To digitally sign it, you need a strong name key file and will need to compile that key file into your assembly.

So, let's see what it takes to do that. First, you create the strong name key file using the Strong Name Tool (sn.exe). You run sn.exe as follows to create a key file named createtxtkey.snk:

sn -k createtxtkey.snk

• Add a test folder anywhere on your C: drive. I will call mine NetComJob.
• Navigate to it through the command prompt.
• Type in the above sn command and the following output will be seen.
  
• Open up your text editor copy the code below into it and save it as createtxt.cs in the NetComJob directory. Note the use of a public no-arguments constructor. For this to be called from COM it has to have a default public no-arguments' constructor.

  using System;  
  using System.Reflection;  
  using System.IO;  
  namespace nscreatetxt {  
      public class clscreatetxt {  
          public clscreatetxt() {}  
          public void createtxt() {  
              StreamWriter SW;  
              SW = File.CreateText("c:\\MyTextFile.txt");  
              SW.WriteLine("Hello Mom");  
              SW.WriteLine("Hello GrandMom");  
              SW.Close();  
          }  
      }  
  } 

You could use sn.exe and the Assembly Linker tool (al.exe) to add the key file to your assembly after you have compiled it, but a much easier way is to use the AssemblyKeyFile attribute in your code to compile it at compile time. It must be only declared once, and be at assembly scope (i.e., outside the namespace declaration for your code). For our example,

Add the following two lines to the top of the createtxt.cs file, immediately beneath all the "using" namespace statements and above the nscreatetxt namespace declaration. The AssemblyVersion attribute will also be added to control our versioning process. Save the file.

The two lines are to be added. [assembly: System.Reflection.AssemblyKeyFile("createtxtkey.snk")]

[assembly: AssemblyVersion("1.0.0")]

Insert these lines as below.

using System;  
using System.Reflection;  
using System.IO;  
[assembly: System.Reflection.AssemblyKeyFile("createtxtkey.snk")]  
[assembly: AssemblyVersion("1.0.0")]  
namespace nscreatetxt {  
    public class clscreatetxt {  
        public clscreatetxt() {}  
        public void createtxt() {  
            StreamWriter SW;  
            SW = File.CreateText("c:\\MyTextFile.txt");  
            SW.WriteLine("Hello Mom");  
            SW.WriteLine("Hello GrandMom");  
            SW.Close();  
        }  
    }  
} 

We need to compile the code into a .NET assembly. From the command line, type the following command.

csc /t:library createtxt.cs /r:System.dll

The /t (or /target) switch tells the C# compiler to make the resulting assembly a class library (or DLL) instead of using the default packaging of an EXE. The /r (or /reference) switch tells the C# compiler to add references to the needed dlls. This output will follow.