Now that we have a basic understanding of SQL CLR and have an environment configured to support the code we want to write, we can move on to writing our first SQL CLR stored procedure. I am going to start out with a very simple piece of code, but one that solves a common problem in SQL Server: Matching Regular Expressions.
As I noted in a previous blog post, SQL Server does not have good native support for Regular Expressions, but when you are trying to sanitize data, Regular Expressions can save you a ton of processing time, development time, and effort. So, let’s fill this need right now, by using SQL CLR.
Open the “Visual C# SQL CLR Database” Project that we created earlier and add a “Stored Procedure” class to the project, if you did not already do so. I am going to call the file that will contain my class, “RegExTestMatch.cs”. When you open this class for editing, you will see code that looks similar to the following:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RegExTestMatch()
{
// Put your code here
}
}; |
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RegExTestMatch()
{
// Put your code here
}
};
Now, without delving too deeply into programming, I need to bring up just a few points of information, for those not familiar with high-level programming languages (HLPLs).
Libraries
To make programming languages easier to use and to encourage proper code reuse, most HLPLs incorporate some form of libraries. These are collections of classes, methods, and properties that can be used in other projects and classes. Programmers can also create their own libraries and include them in future projects or make them available to other developers, or even the general public. In C#, if a library is to be used within a particular set of code, that usage must first be communicated to the compiler, by way of the “using” statements at the top of the code listing. You will notice, the code that was auto-generated when we added our class to our project already had five “using” statements, meaning that our code is already able to utilize the objects defined inside those five libraries.
Return Types
A method is similar to a procedure within SQL Server. A method is generally a part of a class (not always) and defines some action that can be taken from within the specific class. In the code that was generated for us, our method is named “RegExTestMatch”. The word that directly preceeds that name, is called the return type. This defines the data type that any calling process should expect to return from this method. By default, this method’s return type is “void” meaning that nothing will return from this method. We can change this to suit our needs.
Object-Oriented Programming
For the sake of brevity, I will state that Object-Oriented Programming (OOP) is a style of programming that focuses on objects, which are usually classes (but certainly not always). A class is defined by a programmer and then can be reused many times either within the same project or in other projects that reference the library containing this object. For the purpose of this demonstration, the key point to understand is that we can create discrete instances of a specific class and that instance is what we use to perform whatever tasks we need. This entire subject would require a lot more discussion to flesh out completely.
To Solve Our Problem
Now that we have a basic understanding of a few points within our managed language, we can address the problem at hand. We are trying to create a CLR stored procedure that will accept a Regular Expression Pattern and a String to test for matching against said pattern. We will then want to respond to the calling process with either a true or a false.
We will need to first be able to use Regular Expressions within our code, so we need to include a reference to the Regular Expressions library found within .NET, so let’s add that “using” statement:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RegExTestMatch()
{
// Put your code here
}
}; |
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RegExTestMatch()
{
// Put your code here
}
};
Next, we need to decide what we are going to pass to the method and what we will return to the calling process. There are many different ways that we could handle this, but to keep things simple, I am going to pass in two strings, a pattern and a test string, and then return an int, which I will either set to “1” to indicate that the test string did indeed match the pattern, or a “0” to indicate that it did not match:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
{
// Put your code here
}
}; |
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
{
// Put your code here
}
};
After we have our method setup, we need to instantiate an instance of the Regex class (which is the class within the “System.Text.RegularExpressions” library we will be using). During our instantiation, we can go ahead and pass in the pattern we want to match, but we will have to first cast the pattern to the correct datatype, “string”. That code looks like this:
Regex patternTester = new Regex((string)regExPattern); |
Regex patternTester = new Regex((string)regExPattern);
The last part is to use our newly instantiated variable, which we named “patternTester”, to perform the test against our Test String. Once again, we must cast the variable to the correct datatype. Putting all of this together results in our final code, which looks like:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
{
Regex patternTester = new Regex((string)regExPattern);
if (patternTester.IsMatch((string)testString))
return 1;
else
return 0;
}
}; |
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int RegExTestMatch(SqlString regExPattern, SqlString testString)
{
Regex patternTester = new Regex((string)regExPattern);
if (patternTester.IsMatch((string)testString))
return 1;
else
return 0;
}
};
Now, we will need to have a brief look at security before we move on to deploying and using this code.
This post is part of a series of blog posts entitled SQL CLR: An Introduction, the other posts are as follows:
- The Origination and Uses of SQL CLR
- Infrastructure Requirements and Configuration
- Writing a Simple CLR Stored Procedure
- Security and Signing
- Deployment and Usage
- Table-Valued Functions and Other SQL CLR (Coming Soon)
- Incorporating Existing Libraries (Coming Soon)
