Introduction

In the previous article I showed how you can write stored procedures and functions in managed code and how to use them in T-SQL. What I didn't cover was how you can access database objects in managed stored procedures or functions. For this purpose SQL Server 2005 ships with a new managed ADO.NET provider called the SqlServer Data Provider (as opposed to the SqlClient Data Provider, which shipped since the .NET Framework v1.0).

This managed provider is an in-process provider that is used to directly communicate from the Common Language Runtime (CLR) to SQL Server. The SQL Server in-process provider can only connect to the SQL Server that hosts the CLR. It cannot be used to connect to any other SQL Server you might have running on the network. The SqlServer Data Provider is implemented in the System.Data.SqlServer namespace.

The SqlContext object

The main object of the managed SqlServer Data Provider is the SqlContext object. This object represents the current execution context of the managed stored procedure or function that runs inside SQL Server.

A first example of the usage of the SqlContext object is shown in the code below. This code is part of a .NET Class Library called NorthwindDAL.

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

namespace NorthwindDAL
{
	public class Northwind
	{
	
		[SqlFunction(DataAccess = DataAccessKind.Read)]
		public static SqlString GetProductNameByID(SqlInt32 productdID)
		{
			SqlCommand cmd = SqlContext.GetCommand();
			cmd.CommandText = "select ProductName " +
				"from Products where " +
				"ProductID = '" + productdID.ToString() + "'";
			return (string) cmd.ExecuteScalar();
		}
	}
}

The static GetCommand function of the SqlContext object is used to create a SqlCommand object. This SqlCommand object is initialized with the select statement we want to execute. On the last line the select statement is executed using the ExecuteScalar function and the result is returned to the caller.

Before the user-defined function can be made available, the assembly in which it resides must be registered in SQL Server. This is done using the CREATE ASSEMBLY statement as explained in the previous article.

CREATE ASSEMBLY NorthwindDAL
FROM 'C:\Articles\Sql2005\NorthwindDAL\bin\Debug\NorthwindDAL.DLL'

Next we need to register the user-defined function using the CREATE FUNCTION statement.

CREATE FUNCTION dbo.GetProductNameByID(@productId INT) 
RETURNS NVARCHAR(40)
AS EXTERNAL NAME  NorthwindDAL:[NorthwindDAL.Northwind]::GetProductNameByID

Once this is done the user-defined function can be executed

SELECT dbo.GetProductNameByID(1)

----------------------------------------
Chai

(1 row(s) affected)

Sending data to the client using the SqlPipe object

The second most important object of the SqlServer Data Provider is the SqlPipe object. This object is used to send resultsets and error messages back from the server to the client. A first example is shown in the following code snippet:

public static void HelloWorld()
{
	SqlPipe pipe = SqlContext.GetPipe();
	pipe.Send("Hello world from .NET");
}

Once the stored procedure is registered, it can be executed from the client and the resulting string 'Hello world from .NET' will be returned.

CREATE PROCEDURE dbo.HelloWorld 
AS EXTERNAL NAME NorthwindDAL:[NorthwindDAL.Northwind]::HelloWorld

EXEC HelloWorld

Hello world from .NET

To get an SqlPipe object, the static GetPipe method is first called on the SqlContext object. Once the SqlPipe object is obtained, the Send method can be used to transmit data to the calling client. The Send method has 4 overloaded versions that can be used to either send a String, SqlError, SqlDataReader or a SqlResultSet to the client.

public static void GetProductsByCategoryDataReader(SqlInt32 categoryID)
{
	SqlCommand cmd = SqlContext.GetCommand();
	cmd.CommandText = "select ProductID, ProductName  from products";
	SqlDataReader reader = cmd.ExecuteReader();
		
	SqlPipe pipe = SqlContext.GetPipe();
	pipe.Send(reader);
}

The previous example represents a stored procedure called GetProductsByCategoryDataReader that uses the Send method on the SqlCommand object to transmit a SqlDataReader to the calling client. The SqlDataReader behaves as a forward-only, read-only cursor that is created on top of the result of the query. This is the most lightweight cursor you can have in SQL Server.

Once the stored procedure is registered it can be executed using the T-SQL EXEC statement. The partial result of the executing is shown below.

CREATE PROCEDURE dbo.GetProductsByCategoryDataReader 
(@categoryID INT)
AS EXTERNAL NAME
NorthwindDAL:[NorthwindDAL.Northwind]::GetProductsByCategoryDataReader

GO

EXEC GetProductsByCategoryDataReader 1

ProductID   ProductName
----------- ----------------------------------------
17          Alice Mutton
3           Aniseed Syrup
40          Boston Crab Meat
60          Camembert Pierrot
...

Server-side cursors and the SqlResultSet object

The result of query cannot only be send to the client by using a SqlDataReader objects. The SqlServer Data Providers also offers a SqlResultSet object which represents a server-side cursor. With a server-side cursor, the server manages the result set using resources provided by the server computer. The big advantage of a server-side cursor is that it returns only the requested data over the network. However, it is important to point out that a server-side cursor is-at least temporarily-consuming precious server resources for every active client. You must plan accordingly to ensure that your server hardware is capable of managing all of the server-side cursors requested by active clients. So using server-side cursors can have a negative impact on performance and scalability. Using a SqlResultSet is sometimes the least desirable way to access databases from the client.

public static void GetProductsByCategoryResultSet(SqlInt32 categoryID)
{
	SqlCommand cmd = SqlContext.GetCommand();
	cmd.CommandText = "select ProductID, ProductName from products";

	SqlResultSet rs = cmd.ExecuteResultSet(
	   ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
	SqlPipe pipe = SqlContext.GetPipe();

	pipe.Send(rs);
}

The ExecuteResultSet method returns a fully scrollable and updateable cursor to the client.

In the next example a stored procedure called UpdatePrices is created. This stored procedure will update the prices of certain products with an specific amount that is passed in as a parameter.

public static void UpdateProductPrices(SqlMoney amount)
{
	SqlCommand cmd = SqlContext.GetCommand();
	cmd.CommandText = "select * from products";
	SqlResultSet rs = cmd.ExecuteResultSet(
	   ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

	while(rs.Read())
	{
		//retrieve the ProductID column and 
		//if it is 1 update the price
		if (rs.GetInt32(3) == 1)
		{
			//Update the UnitPrice field
			rs.SetSqlMoney(5, rs.GetSqlMoney(5) + amount);
			//Update the database
			rs.Update();
		}
	}
					
	rs.Close();
}

Again we create a SqlResultSet that represent a scrollable and updatable cursor. We loop over every record that is in the resultset, and if necessary we use the SetSqlMoney method to update the UnitPrice field if necessary.

.NET User-defined functions and Security

All the managed stored procedures and user-defined functions we wrote until now accessed some database objects. But in managed code you can do much more than accessing database objects like tables. The following user-defined function for example will read the content of a file.

[SqlFunction(DataAccess=DataAccessKind.None)]
public static SqlString ReadFromFile(SqlString filename)
{
	StreamReader reader = File.OpenText(filename.ToString());
	string content = reader.ReadToEnd();
	reader.Close();

	return (SqlString)content;
}

If the assembly in which this user-defined function resides is registered using the default CREATE ASSEMBLY statement you will receive a security exception upon execution of the user-defined function.

CREATE FUNCTION dbo.ReadFromFile (@filename nvarchar(100)) 
RETURNS nvarchar(100)
AS EXTERNAL NAME NorthwindDAL:[NorthwindDAL.Northwind]::ReadFromFile

GO

EXEC dbo.ReadFromFile "c:\data.txt"

.Net SqlClient Data Provider: Msg 6522, Level 16, State 1, Procedure ReadFromFile, Line 0
A CLR error occurred during execution of 'ReadFromFile': 
    System.Security.SecurityException: Request failed.
at NorthwindDAL.Northwind.ReadFromFile(SqlString filename) +0.

This exception is generated because the default permission set under which all .NET code runs inside SQL Server is set to SAFE. This means that no external resources like files can be accessed. In order to correctly register our .NET assembly we need to execute the following T-SQL statement:

CREATE ASSEMBLY NorthwindDAL
FROM 'C:\Articles\Sql2005\NorthwindDAL\bin\Debug\NorthwindDAL.DLL'
WITH PERMISSION_SET = EXTERNAL_ACCESS

The EXTERNAL_ACCESS permission set grants the NorthwindDAL assembly access to external resources like the file system.

CREATE FUNCTION dbo.ReadFromFile(@filename nvarchar(100)) 
RETURNS nvarchar(100)
AS EXTERNAL NAME NorthwindDAL:[NorthwindDAL.Northwind]::ReadFromFile

GO

SELECT dbo.ReadFromFile('c:\data.txt')

Execution of the user-defined function returns the content of the file specified in the ReadFromFile call.

----------------------------------------------------------------------
Contents of a simple file...

(1 row(s) affected)

Conclusion

In this second article you saw how you can use the SQLServer Data Provider to access database objects in your managed user-defined functions and stored procedures. The SqlServer Data Provider runs inside the SQL Server 2005 database engine and provides direct access to all database objects.