Procedimentos armazenados CLR

Os procedimentos armazenados são rotinas que não podem ser usadas em expressões escalares. Diferentemente das funções escalares, eles podem retornar resultados tabulares e mensagens para o cliente, invocar instruções DDL (linguagem de definição de dados) e DML (linguagem de manipulação de dados) e retornar parâmetros de saída. Para obter informações sobre as vantagens da integração do CLR e a escolha entre o código gerenciado e o Transact-SQL, consulte Visão geral da integração clr.

Requisitos dos procedimentos armazenados CLR

No CLR (Common Language Runtime), os procedimentos armazenados são implementados como métodos estáticos públicos em uma classe em um assembly do Microsoft.NET Framework. O método estático pode ser declarado como nulo ou retornar um valor inteiro. Se retornar um valor inteiro, o inteiro retornado será tratado como o código de retorno do procedimento. Por exemplo:

EXECUTE @return_status = procedure_name

A @return_status variável conterá o valor retornado pelo método . Se o método for declarado nulo, o código de retorno será 0.

Se o método usar parâmetros, o número de parâmetros na implementação .NET Framework deverá ser o mesmo que o número de parâmetros usados na declaração Transact-SQL do procedimento armazenado.

Os parâmetros passados para um procedimento armazenado CLR podem ser qualquer um dos tipos de SQL Server nativos que têm um equivalente no código gerenciado. Para que a sintaxe Transact-SQL crie o procedimento, esses tipos devem ser especificados com o tipo de SQL Server nativo mais apropriado equivalente. Para obter mais informações sobre conversões de tipo, consulte Mapeamento de dados de parâmetro CLR.

Parâmetros com valor de tabela

Os TVPs (parâmetros com valor de tabela), ou seja, tipos de tabela definidos pelo usuário transmitidos para um procedimento ou uma função, oferecem uma maneira eficiente de passar várias linhas de dados para o servidor. Os TVPs fornecem funcionalidade semelhante às matrizes de parâmetros, mas oferecem maior flexibilidade e maior integração com o Transact-SQL. Eles também fornecem o potencial para melhor desempenho. Os TVPs também ajudam a reduzir o número de viagens de ida e volta para o servidor. Em vez de enviar várias solicitações ao servidor, como com uma lista de parâmetros escalares, os dados podem ser enviados ao servidor como um TVP. Um tipo de tabela definido pelo usuário não pode ser passado como um parâmetro com valor de tabela para, ou ser retornado, um procedimento armazenado gerenciado ou uma função em execução no processo de SQL Server. Para obter mais informações sobre TVPs, consulte Usar parâmetros de Table-Valued (Mecanismo de Banco de Dados).

Retornando resultados de procedimentos armazenados CLR

As informações podem ser retornadas de .NET Framework procedimentos armazenados de várias maneiras. Isso inclui parâmetros de saída, resultados tabulares e mensagens.

Parâmetros OUTPUT e procedimentos armazenados CLR

Assim como acontece com os procedimentos armazenados do Transact-SQL, as informações podem ser retornadas de .NET Framework procedimentos armazenados usando parâmetros OUTPUT. A sintaxe DML do Transact-SQL usada para criar .NET Framework procedimentos armazenados é a mesma usada para criar procedimentos armazenados escritos no Transact-SQL. O parâmetro correspondente no código de implementação na classe .NET Framework deve usar um parâmetro pass-by-reference como o argumento . Observe que o Visual Basic não dá suporte a parâmetros de saída da mesma maneira que o C#. Você deve especificar o parâmetro por referência e aplicar o <atributo Out()> para representar um parâmetro OUTPUT, como no seguinte:

Imports System.Runtime.InteropServices  
...  
Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32)  

A seguir está um procedimento armazenado que retorna informações por um parâmetro OUTPUT:

using System;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void PriceSum(out SqlInt32 value)  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         value = 0;  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);  
         SqlDataReader reader = command.ExecuteReader();  
  
         using (reader)  
         {  
            while( reader.Read() )  
            {  
               value += reader.GetSqlInt32(0);  
            }  
         }           
      }  
   }  
}  

Imports System  
Imports System.Data  
Imports System.Data.Sql  
Imports System.Data.SqlTypes  
Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
Imports System.Runtime.InteropServices  
  
'The Partial modifier is only required on one class definition per project.  
Partial Public Class StoredProcedures   
    ''' <summary>  
    ''' Executes a query and iterates over the results to perform a summation.  
    ''' </summary>  
    <Microsoft.SqlServer.Server.SqlProcedure> _  
    Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)  
  
        Using connection As New SqlConnection("context connection=true")  
           value = 0  
           Connection.Open()  
           Dim command As New SqlCommand("SELECT Price FROM Products", connection)  
           Dim reader As SqlDataReader  
           reader = command.ExecuteReader()  
  
           Using reader  
              While reader.Read()  
                 value += reader.GetSqlInt32(0)  
              End While  
           End Using  
        End Using          
    End Sub  
End Class  

Depois que o assembly que contém o procedimento armazenado CLR acima tiver sido criado e criado no servidor, o Transact-SQL a seguir será usado para criar o procedimento no banco de dados e especificará a soma como um parâmetro OUTPUT.

CREATE PROCEDURE PriceSum (@sum int OUTPUT)  
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum  
-- if StoredProcedures class was inside a namespace, called MyNS,  
-- you would use:  
-- AS EXTERNAL NAME TestStoredProc.[MyNS.StoredProcedures].PriceSum  

Observe que a soma é declarada como um int tipo de dados SQL Server e que o parâmetro de valor definido no procedimento armazenado CLR é especificado como um SqlInt32 tipo de dados CLR. Quando um programa de chamada executa o procedimento armazenado CLR, SQL Server converte automaticamente o SqlInt32 tipo de dados CLR em um inttipo de dados SQL Server. Para obter mais informações sobre quais tipos de dados CLR podem e não podem ser convertidos, consulte Mapeamento de dados de parâmetro CLR.

Retornando resultados tabulares e mensagens

O retorno dos resultados tabulares e mensagens para o cliente é executado através do objeto SqlPipe, que é obtido usando a propriedade Pipe da classe SqlContext. O objeto SqlPipe tem um método Send. Chamando o método Send, você pode transmitir dados pelo pipe para o aplicativo de chamada.

Há várias sobrecargas do método SqlPipe.Send, incluindo uma que envia um SqlDataReader e outra que simplesmente envia uma cadeia de caracteres de texto.

Retornando mensagens

Use SqlPipe.Send(string) para enviar mensagens para o aplicativo cliente. O texto da mensagem é limitado a 8000 caracteres. Se a mensagem ultrapassar os 8000 caracteres, ela será truncada.

Retornando resultados tabulares

Para enviar os resultados de uma consulta diretamente para o cliente, use uma das sobrecargas do método Execute no objeto SqlPipe. Essa é a maneira mais eficiente de retornar os resultados para o cliente, porque os dados são transferidos para os buffers de rede sem ser copiados para a memória gerenciada. Por exemplo:

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);  
      }  
   }  
}  

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 os resultados de uma consulta que foi executada anteriormente através do provedor interno ao processo (ou pré-processar os dados usando uma implementação personalizada de SqlDataReader), use a sobrecarga do método Send que assume um SqlDataReader. Esse método é ligeiramente mais lento do que o método direto descrito anteriormente, mas oferece maior flexibilidade para manipular os dados antes de serem enviados para o 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);  
      }  
   }  
}  

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 criar um conjunto de resultados dinâmicos, preenchê-lo e enviá-lo para o cliente, você pode criar registros da conexão atual e enviá-los usando 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);  
   }  
}  

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 seguir está um exemplo de envio de um resultado tabular e uma mensagem atravé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);  
      }  
   }  
}  

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   

O primeiro Send envia uma mensagem para o cliente, enquanto o segundo envia um resultado tabular que usa SqlDataReader.

Observe que esses exemplos são meramente para fins ilustrativos. As funções CLR são mais apropriadas do que instruções Transact-SQL simples para aplicativos com uso intensivo de computação. Um procedimento armazenado transact-SQL quase equivalente ao exemplo anterior é:

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

Se o código Visual C# anterior for salvo em um arquivo MyFirstUdp.cs e compilado com:

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

Ou então, se o código Visual Basic anterior for salvo em um arquivo MyFirstUdp.vb e compilado com:

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

O assembly resultante pode ser registrado, e o ponto de entrada invocado, com o DDL a seguir:

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

Consulte Também

Funções do CLR definidas pelo usuário
Tipos definidos pelo usuário de CLR
Gatilhos de CLR