Although I am a strong advocate of keeping stored procedures simple, I am also aware of scenarios where complex SQL cannot be eluded. Let us start with one such scenario and show how we can solve a complex issue using the SQL CLR enhancements in SQL Server.

Say, you’re designing and developing a database. You will be the architect of not only the tables but also the queries, functions and stored procedures required. Needless to say, you will also be responsible for the integrity of data inserted/updated in the database. Let’s assume that this database will be hit by several different applications and all these applications will be using your stored procedures for basic CRUD (Create Read Update Delete) operations.

This database that you are designing is of an e-shopping application. This means that the integrity and security of data is of utmost importance. Moreover, the same database will be used by applications such as the e-shopping website, a separate application for generating reports etc.

In this database, you create a table for containing Customer’s information. The table may, therefore, have the following columns:

1. Login ID
2. Password
3. Customer’s name
4. Address Line 1
5. Address Line 2
6. City Country
7. Phone Number
8. Email Address

At first glance, the table may seem simple enough. But, if you look closely, you’ll realize that things may not be this easy. As; for this table you will need to ensure the following:

1. Login ID may only contain alphabets and numbers and must be at least 8 characters long.
2. Password MUST contain both alphabets and number and the length must fall between the range of 6 and 15.
3. Customer’s name may contain only alphabets.
4. Phone Number and Email Address should be valid, in the sense that they should comply with the standard format.

These are just crude specifications. In real-life scenarios, a lot of other rules would apply. But imagining a stored procedure to validate even the few rules listed above gives a headache. It even seems impossible to implement a few of them in SQL. For example, we normally use regular expressions to validate phone numbers and email addresses but how on earth do we use regular expressions is SQL? And wait, don’t we need to encrypt the password as well. How do we do that?

We can ask the e-shopping application developers to validate data before inserting in database. But let’s say they don’t agree to that. They have too many other more important tasks to cater to than worrying about validation, and we expect other applications to hit the same database. We can’t possibly ensure that all of them will send clean data.

We can expose a webservice instead of stored procedures, but for the sake of this example, let’s say we are not allowed to do that.

So what are we left with? We need to expose stored procedures for data insertion and update. Additionally, we need these stored procedures to ensure correctness of data.

Our task seems daunting. But there is an easy solution. 

Here’s what we’ll do. All the rules that can be implemented in SQL will be incorporated in the stored procedures. For all other rules, the stored procedure will rely on an assembly that exposes particular functions/methods for validation. In this article, you will learn to develop this assembly using .Net Framework 3.5, C# and how to use that assembly from SQL Server 2008. I will be using Visual Studio 2010 for this purpose.

Let us start with the database. First, create a new database (TestDB), and create a table with the name of Customer and the following fields.

• Login ID, varchar(20), NOT NULL
• Password, varchar(50), NOT NULL
• Name, varchar(1000) NOT NULL
• Address Line 1, varchar(MAX)
• Address Line 2, varchar(MAX)
• City, varchar(50)
• Country, varchar(50)
• Phone Number, varchar(50)
• Email Address, varchar(100)

The length rules can be easily applied in SQL. However, we will need the C# code to encrypt the password and ensuring that certain fields follow a specified format. Instead of actually encrypting the password, we’ll just create a function that returns the reverse of the input string. This code can later be replaced with an encryption algorithm easily.

Follow the below steps:

1. Open Visual Studio 2010. (If you’re using Windows 7, right click on VS 2010 exe and select ‘Run as Administrator’, otherwise you won’t be able to debug if need be.)
2. Create a SQL database project. Click on File -> New Project. Under Installed Templates section on the left side of the “New Project” window, select Database and then SQL Server. Select Visual C# SQL CLR Database Project. Enter names for Project and Solution. Be sure to change the .Net Framework version from 4 to 3.5. Click on OK.
3. A new window will open up asking for database connection. Provide Server Name, Credentials and select your database.
4. Another window will open up requiring your consensus for SQL/CLR debugging. Click Yes.
5. The newly created project will have the following structure.

6. For creating functions which can be used through SQL you’ll need to write User-Defined Functions. Right click on Project Name -> Add -> User-Defined Function.

Give the function the name of your choice. The user defined function will need to follow some conventions which are:

• It should have the SqlFunction attribute.

• The function should return and accept parameters of type SQL (SqlString, SqlBoolean, SqlChars, SqlInt32 etc.)

7. To verify that any string follows a certain format, write a function which accepts two inputs

• a string to be validated
• a string containing regular expression.

In our case, we can also write two functions, one to validate email address and one for validating phone number, but it is always better to write a generic function.

The function shown below is simple enough. Please note that the Value property of each SQL object converts the contained value into its equivalent data type. That is the value property of a SqlInt32 object will give the value in type Int.

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean MatchPattern(SqlChars StrToBeMatched, SqlString Pattern)
    {        
        Regex RegExpression = new Regex(Pattern.Value);        
        return RegExpression.IsMatch(new string(StrToBeMatched.Value));
    }

8. We’ll write another function to encrypt password. As stated before, this will be a dummy function which will return the reverse of the input string. The logic of the function can later be changed to encrypt the string.

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Encrypt(SqlString Password)
    {
        SqlString encrypted = string.Empty;
        char[] chars = Password.Value.ToCharArray();
        for(int i = chars.Length - 1; i >= 0; i--)
        {
            encrypted += chars.ToString();
        }
        return encrypted;
    }

9. We’re almost done now. Build the project. The build should succeed, if you’ve closely followed all steps.

10. After successfully building, right click on the solution and click on “Deploy Solution”.

The solution should deploy successfully.

11. Now you need to enable CLR integration, CLR is what lets you use functions from assemblies in SQL code and is disabled by default. To enable CLR, execute the following command:

sp_configure 'clr enabled', 1
reconfigure

12. To test, open new query window in SQL and select your database. Write a simple query to check if your assembly has in fact deployed successfully. (The SQL intellisense should start showing your function's name.)

SELECT dbo.MatchPattern('abc', 'abc');

This query should return 1.

SELECT dbo.MatchPattern('abc', 'def');

This query should return 0.

SELECT dbo.Encrypt('abc');

This query should return ‘cba’.

13. Now is the turn to write your stored procedure. Here is what it should look like. I’ve omitted all checks except the ones which actually show the usage of your assembly’s functions.

CREATE PROCEDURE [dbo].[InsertCustomer]
    (@inLoginID VARCHAR(20),
     @inPassword VARCHAR(50),
     @inName VARCHAR(1000),
     @inAddressLine1 VARCHAR(MAX),
     @inAddressLine2 VARCHAR(MAX),
     @inCity VARCHAR(50),
     @inCountry VARCHAR(50),
     @inPhoneNumber VARCHAR(50),
     @inEmailAddress VARCHAR(100))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    IF ((SELECT dbo.MatchPattern(@inEmailAddress, '^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,6})$')) = 1)
    BEGIN
        IF((SELECT dbo.MatchPattern(@inPhoneNumber, '^\d{10}$')) = 1)
        BEGIN
            
            INSERT INTO dbo.Customer
            (LoginID, Password, Name, AddressLine1, AddressLine2, City, Country, PhoneNumber, EmailAddress)
            VALUES
            (@inLoginID,
             (SELECT dbo.Encrypt(@inPassword)),
              @inName, @inAddressLine1, @inAddressLine2, @inCity, @inCountry, @inPhoneNumber, @inEmailAddress);            
        END
    END
END

Execute the stored procedure and see the results. Play with it a bit to understand the true potential of this technique.

The CLR integration with SQL makes creating a secure database easier than before. Using this technique you can not only ensure the integrity of data but also make sure that your database conforms to your company’s standards.

References

• http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
• http://www.codeproject.com/KB/string/SqlRegEx.aspx?display=PrintAll