Monday, September 19, 2005

Sending custom resultset -- SQL Server 2005

This article would explain in detail (with complete code sample) the way to return custom resultsets to the end user using CLR in SQL Server 2005.

Things to know before we get started:

1) SqlPipe() -- Its the job of the SqlPipe object to send results back to the stored proc or for that matter UDF etc.,

2) SqlDataRecord -- If we want to return resultset (more than one record) make use of this new object which is introduced in ASP.NET 2.0. For that we need to first create a schema using SqlMetaData objects.

With this small introduction lets get our hands dirty by writing a small SP which would return more than records.

1. Open VS.NET 2005
2. Create C# based Database project
3. Right click on the solution and choose Add >> Stored proceedure >> name the file as SPReturningResultSet

4. Copy paste the below code into that file

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.Data.SqlClient;

public partial class StoredProcedures
{
[SqlProcedure]
public static void SPReturningResultSet()
{
SqlMetaData[] objMetaDataCols = new SqlMetaData[3];
objMetaDataCols[0] = new SqlMetaData("Sno", SqlDbType.Int);
objMetaDataCols[1] = new SqlMetaData("FirstName", SqlDbType.VarChar, 50);
objMetaDataCols[2] = new SqlMetaData("EmailID", SqlDbType.VarChar, 50);

SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

SqlDataRecord objRows = new SqlDataRecord(objMetaDataCols);

objRows.SetInt32(0, 1);
objRows.SetString(1, "Vadivel Mohanakrishnan");
objRows.SetString(2, "vmvadivel@gmail.com");

// New result-set starts here
objPipe.SendResultsStart(objRows, true);

// In-between add as many rows as you want
objRows.SetInt32(0, 2);
objRows.SetString(1, "Maruthiraja");
objRows.SetString(2, "mars@gmail.com");
objPipe.SendResultsRow(objRows);

objRows.SetInt32(0, 3);
objRows.SetString(1, "Sriram");
objRows.SetString(2, "nilapenn@gmail.com");
objPipe.SendResultsRow(objRows);

// End of the result-set code
objPipe.SendResultsEnd();
}
};

Build the project (Ctrl + shift + B) in VS.NET 2005 and then open up SQL Server 2005 and do the following:

Create Assembly ResultSetDemoFrom 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll
'WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

Create Proc dbo.uspResultSetDemo
As External name ResultSetDemo.StoredProcedures.SPReturningResultSet
Go

Test our SP:

Exec dbo.uspResultSetDemo

If at all it throws an error like the one shown below .. then execute the execute the two lines of code beneath it:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

sp_configure 'clr enabled', 1
Reconfigure with override

Clean Up:

drop proc dbo.uspResultSetDemo
go

drop assembly ResultSetDemo
go

For more reading on this concept, I recommend you to go through this MSDN link >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.asp

Saturday, September 17, 2005

SP to rename a directory using CLR in SQL Server 2005

This article would explain in detail about the way to rename a physical directory using CLR in SQL Server 2005. The complete source code is attached with this article.

1. Open VS.NET 2005
2. Create C# based Database project
3. Right click on the solution and choose Add >> Stored proceedure
4. Copy paste the below code into that file

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.IO;

public partial class StoredProcedures
{
[SqlProcedure]
public static void RenameDirectory(String strOldName, String strNewName)
{
SqlPipe objPipe;
objPipe = SqlContext.GetPipe();

try
{
//Check for directory existance
//Note: Since it is for demo purpose I haven't done the complete validation
if (Directory.Exists(strNewName))
{
objPipe.Send("Specified directory already exists.");
}
else // Rename the Directory
{
//Directory.CreateDirectory(strNewName);
Directory.Move(strOldName, strNewName);
objPipe.Send("Directory has been successfully renamed.");
}
}
catch (Exception objExp)
{
objPipe.Send(objExp.Message.ToString());
}
}
};

After Building this database project in VS.NET 2005 open up SQL 2005 and do the following:

Create Assembly RenameDIRFrom 'C:\Documents and Settings\Administrator\My Documents\Visual Studio\Projects\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

The assemblies imported with EXTERNAL_ACCESS allows access to external sources such as file system using the .NET Framework class library. Since the above code needs to access the file system, we have set EXTERNAL_ACCESS permission while creating the assembly.

Create Proc dbo.RenamePhysicalDirectory
(
@strOldPath nvarchar(1024), @strNewPath nvarchar(1024)
)
As External name RenameDIR.StoredProcedures.RenameDirectory
Go

If we try to test our work by executing the below code:
Exec dbo.RenamePhysicalDirectory 'c:\vadivel', 'c:\vadivelTesting';

It would throw an error as follows:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

The work around execute the below code snippet and then try to test our work ..

sp_configure 'clr enabled', 1
Reconfigure with override