File Operations Made Easy

T-SQL is a great language for managing databases, but it’s lacking in some areas. Shortcomings in its file operations make it difficult to automate tasks such as having an extraction, transformation, and loading (ETL) process check whether a file exists, move a processed file, or check when a file was last modified. Another example is writing to files. When I was working on a project that required creation of XML files, I was surprised to discover that SQL Server's rich XML functionality lacks XML file export. You can easily import an XML file into SQL Server, but you can’t use SQL Server to create one.

Although difficult in T-SQL, these tasks are easily automated using SQL CLR. Tasks that require many lines of T-SQL code can often be handled with a single function call. I’ll show you how to augment T-SQL’s functionality in file operations using CLR.

CLR Primer

CLR was introduced in SQL Server 2005 to allow integration of Microsoft .NET Framework code into SQL Server. Initially, there was a lot of confusion about CLR’s purpose, with some people going as far as to predict the demise of T-SQL. There were also many horror stories circulating about performance, security, and other problems experienced by early adopters. When the dust settled, it turned out that, like many other tools, CLR can be quite useful when used sensibly. T-SQL will always be better at data manipulation, but CLR can be valuable in areas where T-SQL is lacking.

In a nutshell, you can use CLR to create a class by following these steps:

1. Create a project in Visual Studio.
2. Write some methods (.NET jargon for procedures) using C# or Visual Basic .NET.
3. Compile the methods into a DLL.
4. Create an assembly to deploy the DLL to your SQL Server database.
5. Create CLR stored procedures and functions for the methods.

After you’ve completed these steps, you can take advantage of the CLR class’s functionality in your T-SQL code.

Step1: Create the Project

To create a project, you need a version of Microsoft Visual Studio 2005 or later that includes C# or Visual Basic .NET—in other words, a full version and not Business Intelligence Development Studio (BIDS). For the examples here, I used Visual Studio 2008 and C#. Note that you need Visual Studio only if you want to create and modify projects. To deploy existing DLLs to SQL Server, you just need SQL Server Management Studio (SSMS).

To begin, create a new Windows class library project in C# in Visual Studio and name it FileCLR. This will create a class module with some prepopulated code, which Listing 1 shows. Rename the Class1.cs file to FileCLR.cs in Solution Explorer. Visual Studio will ask if you also want to change the class reference, which you should allow. You now have an empty FileCLR class library.

Next, change the target framework to .NET Framework 2.0, which is required by SQL Server. To do this, select FileCLR Properties on the Project menu, then select .NET Framework 2.0 in the Target Framework combo box on the Application tab. Finally, change the using directives in callout A in Listing 1 with the using directives in Listing 2. You’re now ready to start writing C# methods.

Step 2: Write the C# Methods

To get an idea of how to write C# methods, I’ll walk you through a few of the methods I’ve written.

DeleteFile. When you want to delete files, you can use the DeleteFile method. As Listing 3 shows, its code is pretty simple. The first line declares the method. It begins with the public static void statement, which is similar in function to T-SQL's CREATE PROCEDURE. The void keyword indicates that nothing is returned, so the method is a procedure and not a function. (Functions would specify the output’s data type instead of specifying void.) The public and static keywords are outside the scope of this discussion. If you’re curious about their purpose, you can check out MSDN’s C# Reference page.

The declaration continues by defining the method’s name and its two parameters (FilePath and ErrorMessage). In C#, you specify the parameter type (e.g., string) before the parameter name. The out keyword in C# is similar to the OUTPUT parameter in T-SQL.

In the second and last lines, notice the curly braces. In C#, curly braces are used to define blocks of statements, similar to the way that T-SQL uses the BEGIN and END keywords.

Let’s now jump to the heart of the code. The .NET Framework has a wealth of classes whose methods you can use to create powerful code. In this case, you call the File class’s Delete method in the .NET Framework's System.IO namespace. So, instead of building a long OS command string and passing it to xp_cmdshell, you can use a single line of code:

System.IO.File.Delete(FilePath);