Stored procedures frequently need to return rowset data. You pass rowset data in different ways depending on whether you are producing the rowset data in your .NET code or simply returning database data from a SQL query. Let's start by producing the data.
Passing Data as a Produced Rowset
Let's add a stored procedure to our database project called GetWords. The procedure accepts a string of words and splits the string into a rowset with two columns. The first column contains the index number of the word, and the second column contains the word. The stored procedure code is as follows.
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure(Name:="GetVbWords")> _ Public Shared Sub GetWords(ByVal sentence As String) Dim rec As New SqlDataRecord( _ New SqlMetaData("Index", SqlDbType.Int), _ New SqlMetaData("Word", SqlDbType.NVarChar, 50)) SqlContext.Pipe.SendResultsStart(rec) Dim i As Integer = 0 For Each word As String In sentence.Split(" "c) rec.SetInt32(0, i) i += 1 rec.SetString(1, word) SqlContext.Pipe.SendResultsRow(rec) Next SqlContext.Pipe.SendResultsEnd() End Sub End Class
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetCsWords")] public static void GetWords(string sentence) { SqlDataRecord rec = new SqlDataRecord( new SqlMetaData("Index", SqlDbType.Int), new SqlMetaData("Word", SqlDbType.NVarChar, 50)); SqlContext.Pipe.SendResultsStart(rec); int i = 0; foreach (string word in sentence.Split(' ')) { rec.SetInt32(0, i++); rec.SetString(1, word); SqlContext.Pipe.SendResultsRow(rec); } SqlContext.Pipe.SendResultsEnd(); } }
This code introduces the SqlDataRecord object, which is used to define a tabular row of column data. The columns are SqlMetaData objects that consist of the column name and the data type. Strings must be defined in SQL Server terminology, so the string is defined as a Unicode variable character (NVarChar) field with a maximum length of 50 characters. Next, the SendResultsStart method is executed to open the stream and send the metadata down the pipe. In the loop that processes each row, rec is populated with current data consisting of the index number and the current word. Finally, the SendResultsEnd method is executed to close the stream.
Add the following script into the Test.sql script file and run the project.
Visual Basic
EXEC GetVbWords 'This is a test of the GetWords stored procedure'
C#
EXEC GetCsWords 'This is a test of the GetWords stored procedure'
When you look at the results in the output window, you see the following returned data.
You can use the SqlConnection object in the SQLCLR just as you would normally use the SqlConnection object, but for data within the current database you can set the connection string to be a context connection for better performance. The context connection does not carry the overhead of the network protocol stack, and it communicates directly with SQL Server without leaving the process, as shown in Figure 9-4. Would you use a regular connection in the SQLCLR? Sure. You'll often want to retrieve data that is on a different server, so you can simply use a regular connection to get the data.
Figure 9-4: The network protocol stack is eliminated when you communicate using a context connection.
As an example of using a context connection, someone once asked me to create something that looked like the following: A rowset containing a column called EmployeeId that represented the employee IDs in the Employees table, and another column called Last3Orders that represented the last three order IDs, comma separated in a single field. To do this without the SQLCLR, I might come up with a solution that implements a SQL cursor. Using the SQLCLR, the following stored procedure solves the problem.
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.SqlClient Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub LastEmployeeOrdersVb() Dim rec As New SqlDataRecord( _ New SqlMetaData("EmployeeID", SqlDbType.Int), _ New SqlMetaData("Last3Orders", SqlDbType.NVarChar, 50)) Dim employees As New DataTable("Employees") Using cn As New SqlConnection() cn.ConnectionString = "context connection=true" Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "Select EmployeeID From Employees" _ + " ORDER BY EmployeeId ASC" cn.Open() Using rdr As SqlDataReader = cmd.ExecuteReader() employees.Load(rdr) End Using End Using SqlContext.Pipe.SendResultsStart(rec) For Each dr As DataRow In employees.Rows Dim empId As Integer = CType(dr("EmployeeID"), Integer) Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = String.Format( _ "Select TOP 3 OrderID From Orders " _ + "WHERE EmployeeId = {0} " + _ "Order By OrderDate DESC", _ empId) Using rdr As SqlDataReader = cmd.ExecuteReader() Dim orders As String = "" While (rdr.Read()) If orders.Length > 0 Then orders += ", " orders += rdr(0).ToString() End While rec.SetInt32(0, empId) rec.SetString(1, orders) SqlContext.Pipe.SendResultsRow(rec) End Using End Using Next End Using SqlContext.Pipe.SendResultsEnd() End Sub End Class
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Data.SqlClient; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void LastEmployeeOrdersCs() { SqlDataRecord rec = new SqlDataRecord( new SqlMetaData("EmployeeID", SqlDbType.Int), new SqlMetaData("Last3Orders", SqlDbType.NVarChar, 50)); DataTable employees = new DataTable("Employees"); using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = "context connection=true"; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "Select EmployeeID From Employees" + " ORDER BY EmployeeId ASC"; cn.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { employees.Load(rdr); } } SqlContext.Pipe.SendResultsStart(rec); foreach (DataRow dr in employees.Rows) { int empId = (int)dr["EmployeeID"]; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = string.Format( "Select TOP 3 OrderID From Orders " + "WHERE EmployeeId = {0} " + "Order By OrderDate DESC", empId); using (SqlDataReader rdr = cmd.ExecuteReader()) { string orders = ""; while (rdr.Read()) { if (orders.Length > 0) orders += ", "; orders += rdr[0].ToString(); } rec.SetInt32(0, empId); rec.SetString(1, orders); SqlContext.Pipe.SendResultsRow(rec); } } } } SqlContext.Pipe.SendResultsEnd(); } }
This code retrieves the list of employee IDs into a DataTable object. The code then loops through the employee IDs and retrieves the last three order IDs for each employee. We can add code to Test.sql to execute the stored procedure, and the results can be viewed in the output window as shown here.
The context connection is very much like a regular connection, but with some limitations:
Multiple Active Result Sets (MARS) is not supported. In the previous example, the coding could have been somewhat simplified if MARS could have been used.
On a given connection to SQL Server from the client, otherwise known as a Server Process ID (SPID), only one context connection can be open at a time.
The SqlBulkCopy object is not supported.
Update batching is not supported.
SqlNotificationRequest is not supported.
SqlCommand.Cancel is not supported. A call to this method is silently ignored.
No other keywords can exist in the connection string with context connection=true.
Some of these restrictions might be limitations for the current release only, while other restrictions might be by design. So they will not change in future releases.
The only restrictions on a regular connection are the following:
Asynchronous access is not supported within the SQLCLR.
The SqlDependency framework is not supported within the SQLCLR.
Connection Credentials for Regular Connections
When you use a regular connection within the SQLCLR, if you use integrated authentication, the credentials used are those of the SQL Server service account, not the credentials you used to connect to SQL Server. This might be desirable in some scenarios, but at other times you probably want to use the same credentials you used to connect to SQL Server.
If the SQL Server service account is running using the "local system" account, the effective permissions for accessing anything on the local machine are similar to those of an administrator, but the "local system" account has no effective permissions for accessing anything on a remote server. If you attempt to access a remote server using the "local system" account, an exception is thrown that states "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
It is possible to get your credentials from your connection to SQL Server by using the SqlContext.WindowsIdentity property. You can use your credentials to impersonate your account while on the local machine, but Windows security keeps you from impersonating across the network. Impersonation across the network is called delegation; an administrator can enable delegation on an account-by-account basis, but most Windows domain administrators will not want to do this. Chapter 13, which covers security, covers delegation in more detail.
One solution is to use a Windows domain account for the SQL Server service and make sure the account has permission to access the remote database. Another solution is to use standard SQL Server security to access the remote database server, which means that you have a user name and password embedded in your connection string to the remote server.
Remember that you can impersonate to get access to local resources (as you'll see later in this chapter when we cover the streaming table valued functions).
Passing Data from a Database Rowset
You used the Pipe.Send method to return string data that can be viewed as InfoMessage objects. You have also used the Pipe.Send method to return a rowset that you produced. You can also return a rowset that contains database data by passing a SqlDataReader object to the .Pipe.Send method. The following code snippet gets the list of customers from the database and passes the SqlDataReader object to the Pipe.Send method.
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.SqlClient Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub GetCustomersVb() Using cn As New SqlConnection() cn.ConnectionString = "context connection=true" Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "Select * From Customers" cn.Open() Using rdr As SqlDataReader = cmd.ExecuteReader() SqlContext.Pipe.Send(rdr) End Using End Using End Using End Sub End Class
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Data.SqlClient; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void GetCustomersCs() { using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = "context connection=true"; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "Select * From Customers"; cn.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { SqlContext.Pipe.Send(rdr); } } } } }
When you run this stored procedure, you see the customer list in the output window. You can further simplify this code by using the Pipe.ExecuteAndSend method, as shown in the next code snippet.
Visual Basic
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.SqlClient Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub GetCustomers2Vb() Using cn As New SqlConnection() cn.ConnectionString = "context connection=true" Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "Select * From Customers" cn.Open() SqlContext.Pipe.ExecuteAndSend(cmd) End Using End Using End Sub End Class
C#
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Data.SqlClient; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void GetCustomers2Cs() { using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = "context connection=true"; using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "Select * From Customers"; cn.Open(); SqlContext.Pipe.ExecuteAndSend(cmd); } } } }
Notice that this code returns the customer list by simply passing the SqlCommand object to the Pipe.ExecuteAndSend method, which executes the command and passes the resulting SqlDataReader object to the Send method.
