To send the results of a query directly to the client, use one of the overloads of the Execute method on the SqlPipe object. This is the most efficient way to return results to the client, since the data is transferred to the network buffers without being copied into managed memory. For example:
[C#]
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ExecuteToClient()
{
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("select @@version", connection);
SqlContext.Pipe.ExecuteAndSend(command);
}
}
}
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub ExecuteToClient()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT @@VERSION", connection)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub
End Class
To send the results of a query that was executed previously through the in-process provider (or to pre-process the data using a custom implementation of SqlDataReader), use the overload of the Send method that takes a SqlDataReader. This method is slightly slower than the direct method described previously, but it offers greater flexibility to manipulate the data before it is sent to the client.
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
/// <summary>
/// Execute a command and send the resulting reader to the client
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendReaderToClient()
{
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("select @@version", connection);
SqlDataReader r = command.ExecuteReader();
SqlContext.Pipe.Send(r);
}
}
}
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub SendReaderToClient()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT @@VERSION", connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
SqlContext.Pipe.Send(reader)
End Using
End Sub
End Class
To create a dynamic result set, populate it and send it to the client, you can create records from the current connection and send them using SqlPipe.Send.
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class StoredProcedures
{
/// <summary>
/// Create a result set on the fly and send it to the client.
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendTransientResultSet()
{
// Create a record object that represents an individual row, including it's metadata.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));
// Populate the record.
record.SetSqlString(0, "Hello World!");
// Send the record to the client.
SqlContext.Pipe.Send(record);
}
}
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Create a result set on the fly and send it to the client.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub SendTransientResultSet()
' Create a record object that represents an individual row, including it's metadata.
Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )
' Populate the record.
record.SetSqlString(0, "Hello World!")
' Send the record to the client.
SqlContext.Pipe.Send(record)
End Sub
End Class
Here is an example of sending a tabular result and a message through SqlPipe.
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
}
[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub HelloWorld()
SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
SqlContext.Pipe.Send(reader)
End Using
End Sub
End Class
The first Send sends a message to the client, while the second sends a tabular result using SqlDataReader.
Note that these examples are for illustrative purposes only. CLR functions are more appropriate than simple Transact-SQL statements for computation-intensive applications. An almost equivalent Transact-SQL stored procedure to the previous example is:
CREATE PROCEDURE HelloWorld() AS
BEGIN
PRINT('Hello world!')
SELECT ProductNumber FROM ProductMaster
END
.gif) Note: |
|---|
|
Messages and result sets are retrieved differently in the client application. For instance, SQL Server Management Studio result sets appear in the Results view, and messages appear in the Messages pane.
|
If the above Visual C# code is saved in a file MyFirstUdp.cs and compiled with:
csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs
Or, if the above Visual Basic code is saved in a file MyFirstUdp.vb and compiled with:
vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb
| Note: |
|---|
|
Beginning with SQL Server 2005, Visual C++ database objects (such as stored procedures) compiled with /clr:pure are not supported for execution.
|
The resulting assembly can be registered, and the entry point invoked, with the following DDL:
CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll'
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld
EXEC HelloWorld
| Note: |
|---|
|
Beginning with SQL Server 2005, on a SQL Server database with a compatibility level of "80" you cannot create managed user-defined types, stored procedures, functions, aggregates, or triggers. To take advantage of these CLR integration features of SQL Server, you must use the sp_dbcmptlevel stored procedure to set the database compatibility level to "100".
|