Procedimentos Armazenados CLR

Procedimentos armazenados são rotinas que não podem ser usadas em expressões escalares. Ao contrário das funções escalares, elas podem retornar resultados e mensagens tabulares ao 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 clr e escolher entre código gerenciado e Transact-SQL, consulte Visão geral da integração clr.

Requisitos para 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 ele 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 usa parâmetros, o número de parâmetros na implementação do .NET Framework deve 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 nativos do SQL Server 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 nativo mais apropriado do SQL Server 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. As TVPs fornecem funcionalidade semelhante às matrizes de parâmetros, mas oferecem maior flexibilidade e integração mais próxima 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 do SQL Server. Para obter mais informações sobre TVPs, consulte Use Table-Valued Parameters (Mecanismo de Banco de Dados).

Retornando resultados de procedimentos armazenados CLR

As informações podem ser retornadas de procedimentos armazenados do .NET Framework 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 Transact-SQL procedimentos armazenados, as informações podem ser retornadas de procedimentos armazenados do .NET Framework usando parâmetros OUTPUT. A sintaxe DML Transact-SQL usada para criar procedimentos armazenados do .NET Framework é 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)  

O seguinte mostra um procedimento armazenado retornando informações por meio de 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 especifica 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 do 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, o SQL Server converte automaticamente o SqlInt32 tipo de dados CLR em um inttipo de dados do 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 e mensagens tabulares

O retorno de resultados tabulares e mensagens ao cliente é feito por meio do SqlPipe objeto, que é obtido usando a Pipe propriedade da SqlContext classe. O SqlPipe objeto tem um Send método. Ao chamar o Send método, você pode transmitir dados por meio do pipe para o aplicativo de chamada.

Essas são várias sobrecargas do SqlPipe.Send método, incluindo uma que envia um SqlDataReader e outro 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 8.000 caracteres. Se a mensagem exceder 8.000 caracteres, ela será truncada.

Retornando resultados tabulares

Para enviar os resultados de uma consulta diretamente ao cliente, use uma das sobrecargas do Execute método no SqlPipe objeto. Essa é a maneira mais eficiente de retornar resultados ao cliente, pois os dados são transferidos para os buffers de rede sem serem 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 por meio do provedor em processo (ou para pré-processar os dados usando uma implementação personalizada de SqlDataReader), use a sobrecarga do Send método que usa um SqlDataReader. Esse método é um pouco mais lento do que o método direto descrito anteriormente, mas oferece maior flexibilidade para manipular os dados antes de serem enviados ao 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âmico, preenchê-lo e enviá-lo para o cliente, você pode criar registros a partir 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   

Aqui está um exemplo de como enviar um resultado tabular e uma mensagem.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 usando SqlDataReader.

Observe que esses exemplos são apenas 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 de Transact-SQL quase equivalente ao exemplo anterior é:

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

Observação

Mensagens e conjuntos de resultados são recuperados de forma diferente no aplicativo cliente. Por exemplo, os conjuntos de resultados do SQL Server Management Studio aparecem no modo de exibição Resultados e as mensagens aparecem no painel Mensagens .

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

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

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

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

Observação

A partir do SQL Server 2005, os objetos de banco de dados do Visual C++ (como procedimentos armazenados) compilados não /clr:pure têm suporte para execução.

O assembly resultante pode ser registrado e o ponto de entrada invocado com o seguinte DDL:

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

Consulte Também

Funções de User-Defined CLR
Tipos de User-Defined CLR
Gatilhos CLR