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.

Send()	Multiple rows from a reader, one record or some text
SendResultsStart()	Sends one row (SqlDataRecord) and configures the pipe for sending more rows
SendResultsRow()	Sends one row
SendResultsEnd()	Tells the Pipe we've finished sending 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.

Comments

Santosh Mahajan said:

It was very helpful.

Thanks a lot.

I would like to make a point here...

For adding data right from first row in the pipe, the above code could be changed to

                if (i == 0)

                {

                    SqlContext.Pipe.SendResultsStart(row);

                    SqlContext.Pipe.SendResultsRow(row);

                }

instead of only

                if (i == 0)

                    SqlContext.Pipe.SendResultsStart(row);

Thanks,

Santosh Mahajan

07/Jan/2008 13:10 PM

Mehnaz said:

Hi Paul,

Nice post.I am trying something similar but i am stuck.Appreciate if u help.

The clr SP i have written returns the results the same way u have done it.I call my clr SP from my application.The problem is how do i collect the results in my application.I wanna process those results in my application

12/Mar/2008 08:22 AM

GeekZilla