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
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
Comments
//using System.Data.SqlServer;
using Microsoft.SqlServer.Server;
//SqlPipe objPipe;
//objPipe = SqlContext.GetPipe();
//objPipe.SendResultsStart(objRows, true);
SqlContext.Pipe.SendResultsStart(objRows);
//objPipe.SendResultsRow(objRows);
SqlContext.Pipe.SendResultsRow(objRows);
//objPipe.SendResultsRow(objRows);
SqlContext.Pipe.SendResultsRow(objRows);
//objPipe.SendResultsEnd();
SqlContext.Pipe.SendResultsRow(objRows);
Then create assembly fails with:
"CREATE ASSEMBLY for assembly 'ResultSetDemo' failed because assembly 'ResultSetDemo' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission."
Though I signed assembly with strong name key and verified all other conditions
Guennadi Vanine