Procedimientos almacenados de CLR

Use SqlPipe.Send(string) para enviar mensajes a la aplicación cliente. El texto del mensaje está limitado a 8000 caracteres. Si el mensaje supera los 8000 caracteres, se truncará.

Para enviar los resultados de una consulta directamente al cliente, use una de las sobrecargas del método Execute en el objeto SqlPipe. Se trata del modo más eficaz de devolver resultados al cliente, puesto que los datos se transfieren a los búferes de red sin copiarse en la memoria administrada. Por ejemplo:

[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

Para enviar los resultados de una consulta previamente ejecutada a través del proveedor en proceso (o para preprocesar los datos mediante una implementación personalizada de SqlDataReader), use la sobrecarga del método Send que toma SqlDataReader. Este método es algo más lento que el método directo descrito anteriormente, pero ofrece mayor flexibilidad para manipular los datos antes de enviarlos al cliente.

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

Para crear un conjunto de resultados dinámico, rellénelo y envíeselo al cliente; puede crear registros de la conexión actual y enviarlos mediante 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 

A continuación se muestra un ejemplo del envío de un mensaje y un resultado tabular a través de 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 

El primer Send envía un mensaje al cliente, mientras que el segundo envía un resultado tabular mediante SqlDataReader.

Tenga en cuenta que se trata de ejemplos meramente ilustrativos. Las funciones CLR resultan más apropiadas que las instrucciones Transact-SQL simples para las aplicaciones que requieren un uso intensivo de los recursos para realizar cálculos. Un procedimiento almacenado Transact-SQL casi equivalente al ejemplo anterior es el siguiente:

CREATE PROCEDURE HelloWorld() AS
BEGIN
PRINT('Hello world!')
SELECT ProductNumber FROM ProductMaster
END

Si el código de Visual C# anterior se guarda en un archivo MyFirstUdp.cs y se compila con:

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs 

O bien, si el código de Visual Basic anterior se guarda en un archivo MyFirstUdp.vb y se compila con:

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb 

Es posible registrar el ensamblado resultante y el punto de entrada invocado con el siguiente DDL:

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll'
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld
EXEC HelloWorld