Multiple Connections in a .NET CLR Stored Procedure

This week I was working with .NET CLR stored procedures. In most cases I use a “context connection” to connect my CLR stored procedure to the database containing the stored procedure, but this week my stored procedures were very complex and needed multiple connections. Only one context connection is allowed at a time, so I decided to use standard database connections in my CLR stored procedure. Making this work correctly gave me fits for several hours. In order to make multiple non-context connections work in a CLR stored procedure I had to alter the underlying database Trustworthy property, and the Permission Level property of the CLR project.

Here’s a concrete example to make my ideas clear: a CLR stored procedure that uses a non-context connection to insert data. First I used the SQL Server Management Studio tool and created a dummy SQL Server 2008 database with a table to hold hypothetical employee information:

use master
go
create database dbDummy
go
use dbDummy
go

create table tblEmployees
(
[empID] int primary key,
[last] varchar(35) not null,
[first] varchar(35) null
)
go

I inserted some dummy data into the table:

insert into tblEmployees values(111, 'Adams', 'Abe')
insert into tblEmployees values(222, 'Baker', 'Bob')
insert into tblEmployees values(333, 'Chung', 'Cal')

Next I launched Visual Studio 2010 to make a CLR stored procedure. I chose the “Visual C# SQL CLR Database Project” template, and selected the .NET Framework 2.0 to sync with the .NET 2.0 on SQL Server 2008. I named the VS project clrDemo. VS gave me a dialog to select the database server machine, and the database (dbDummy). I also enabled debugging through a second dialog.

Next, in VS, I right-clicked on the clrDemo project name in the Solution Explorer window and selected Add | New Item, then Stored Procedure. I named the stored procedure clrDemo.cs (the same as the project), but a better name would have been cspInsertEmployee to indicate the type and purpose of the stored procedure.

I wrote simple code to insert an employee’s data:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void clrDemo(System.Data.SqlTypes.SqlInt32 empID,
  System.Data.SqlTypes.SqlString last,
  System.Data.SqlTypes.SqlString first)
{
  SqlConnection sqlConn = new
    SqlConnection("Server=(local);Database=dbDummy;" + 
    "Trusted_Connection=True");
  sqlConn.Open();
  int internalEmpID = (int)empID;
  string internalLast = (string)last;
  string internalFirst = (string)first;
  string insertString = "insert into tblEmployees values(" +
    internalEmpID + ",'" + internalLast + "','" +
    internalFirst + "')";
  SqlCommand insertCmd = new SqlCommand(insertString, sqlConn);
  insertCmd.ExecuteNonQuery();
  sqlConn.Close();
  return;
}

Then I wrote a short C# console application to call the CLR stored procedure:

class Program
{
  static void Main(string[] args)
  {
    SqlConnection sc = null;
    try
    {
      Console.WriteLine("Calling CLR stored procedure");
      string connString = "Server=(local);Database=dbDummy;" +
        "Trusted_Connection=True";
      sc = new SqlConnection(connString);

      SqlCommand cmd = new SqlCommand("clrDemo", sc);
      cmd.CommandType = System.Data.CommandType.StoredProcedure;

      SqlParameter sqlEmpID = cmd.Parameters.Add("@empID", 
        System.Data.SqlDbType.Int);
      sqlEmpID.Direction = System.Data.ParameterDirection.Input;
      sqlEmpID.Value = 444;

      SqlParameter sqlLast = cmd.Parameters.Add("@last",
        System.Data.SqlDbType.Text);
      sqlLast.Direction = System.Data.ParameterDirection.Input;
      sqlLast.Value = "Dunne";

      SqlParameter sqlFirst = cmd.Parameters.Add("@first",
        System.Data.SqlDbType.Text);
      sqlFirst.Direction = System.Data.ParameterDirection.Input;
      sqlFirst.Value = "Don";

      sc.Open();
      cmd.ExecuteNonQuery();

      if (sc != null && sc.State == ConnectionState.Open)
        sc.Close();

      Console.WriteLine("\nDone");
      Console.ReadLine();
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
      Console.ReadLine();
    }
  }
}

When I ran the console application the call to the CLR stored procedure failed with error message:

Request for the permission of type ‘System.Data.SqlClient.SqlClientPermission,
System.Data, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089′ failed

To enable a call to a CLR stored procedure that uses a non-context connection, you need to make two changes. You must first set the underlying database to accept an unsafe/external connection. In SQL Server Management Studio, the commands to do this are:

select is_trustworthy_on from sys.databases where name = 'dbDummy'
alter database dbDummy set trustworthy on
select is_trustworthy_on from sys.databases where name = 'dbDummy'

The second change is to modify the Permission Level of the CLR stored procedure. In VS, go to the CLR project’s properties, select the Database tab, then pick “External” from the Permission Level dropdown. After rebuilding the Solution, the demo program successfully called the CLR stored procedure using a non-context connection. And, you can have multiple non-context connections open for extremely complex stored procedures.

About these ads
This entry was posted in Software Test Automation. Bookmark the permalink.