CLR Stored Procedure for searching files

I was recently asked to write a CLR stored procedure which would process a text file and return a row for each line in the file that contained text matching our search criteria.

I'd never written a CLR stored proc before so it was an interesting learning excersise. This article details the steps I went through to get to the end result.

Pre-requesits

• Visual Studio 2005
• SQL Server 2005

The SQL project

From Visual studio, create a new C# database project. The new project wizard will ask you to specify a database for your project. In this instance, this is only used when you deploy the stored procedure.

Once the project is created, add a new stored procedure to the project by right clicking the project and choosing Add New Item...

Specify the input parameters for the stored procedure in the auto generated function header. For this example:

public static void sp_Readerrorlog(int logSequenceNumber, 
                                      string logFilename, 
                                      bool returnAllOccurances, 
                                      string searchCriteria)

The file processor

The following code processes a text file and builds up a list of occurances.

FileStream logFileStream = new FileStream(logFilename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
StreamReader logFileReader = new StreamReader(logFileStream);
List<string> occuances = new List<string>();

// Get all occurances
while (!logFileReader.EndOfStream)
{
    string line = logFileReader.ReadLine();
    if (line.Contains(searchCriteria))
    {
        occuances.Add(line);
    }

}

// Clean up
logFileReader.Close();
logFileStream.Close();

Returning the results

The SqlContext.Pipe object is the key to communicating with SQL. There are four methods on the Pipe object which can be used for returning data.

To return a single row you need to create a SqlDataRecord. Below is an example of creating a SqlDataRecord with one varchar column.

SqlMetaData meta = new SqlMetaData("errorlog", SqlDbType.VarChar,2000);
SqlDataRecord row = new SqlDataRecord(meta);
row.SetValue(0, "hello");

If we wanted to only return one row we'd use send.. as below

SqlContext.Pipe.Send(row);

The finised stored procedure

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void sp_Readerrorlog(int logSequenceNumber, string logFilename, bool returnAllOccurances, string searchCriteria)
    {
        FileStream logFileStream = new FileStream(logFilename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); 
        StreamReader logFileReader = new StreamReader(logFileStream);
        List<string> occuances = new List<string>();

        // Get all occurances
        while (!logFileReader.EndOfStream)
        {
            string line = logFileReader.ReadLine();
            if (line.Contains(searchCriteria))
            {
                occuances.Add(line);
            }

        }

        // Clean up
        logFileReader.Close();
        logFileStream.Close();

        SqlMetaData meta = new SqlMetaData("errorlog", SqlDbType.VarChar, 2000);

        // If we have rows, return them
        if (occuances.Count > 0)
        {

            if (returnAllOccurances)
            {
                // return all rows
                for (int i = 0; i < occuances.Count; i++)
                {
                    SqlDataRecord row = new SqlDataRecord(meta);
                    row.SetValue(0, (string)(occuances[i]));

                    if (i == 0)
                    {
                        SqlContext.Pipe.SendResultsStart(row);
                    }
                    else
                    {
                        SqlContext.Pipe.SendResultsRow(row);
                    }
                }
                SqlContext.Pipe.SendResultsEnd();
            }
            else
            {
                // return just one row
                SqlDataRecord row = new SqlDataRecord(meta);
                row.SetValue(0, (string)(occuances[occuances.Count - 1]));
                SqlContext.Pipe.Send(row);
            }
        }
        else
        {
            // should we return nothing here?
           
        }
    }
};

Deploying the Stored Procedure

Deploying the stored procedure is easy, or so it seemed. Right click the project in Solution Explorer, choose Deploy. This worked, unfortunately this stored procedure would like to access a file, this requires the EXTERNAL_ACCESS permission.

EXTERNAL_ACCESS

To mark the stored proc as requiring EXTERNAL_ACCESS you need to bring up the properties window for the project. On the Database tab, change the Permission Level to "External".

TRUSTWORTHY

One last thing, before you can deploy an EXTERNAL_ACCESS stored procedure, you will need to ensure that the target database is marked as TRUSTWORTHY. Use the following T-Sql to do this.

ALTER DATABASE PaulsTestArea SET TRUSTWORTHY ON;
GO

Lets deploy

Once you've ensured the EXTERNAL_ACCESS and TRUSTWORTH settings are correct, try deploying the stored proc. It should deploy with no problems.. (I was connected to the database as SA when I tried)

Calling the Stored Procedure

exec sp_Readerrorlog 0, 'C:\errorlog.txt',true,'Error: 100008'

Summary

Creating CLR stored procedures is easy. Creating CLR stored procedures which need access to external objects like files, web services etc is also pretty easy once you know what to do about security issues.

Further Reading

http://www.devx.com/codemag/Article/31193/0/page/5

http://blogs.msdn.com/lcris/archive/2005/09/26/474202.aspx