Funções de valor escalar CLR

  • Artigo

Aplica-se a:SQL Server

Uma SVF (função de valor escalar) retorna um valor único como, por exemplo, uma cadeia de caracteres, um inteiro ou um valor de bit. Você pode criar funções definidas pelo usuário com valor escalar no código gerenciado usando qualquer linguagem de programação .NET Framework. Essas funções são acessíveis ao Transact-SQL ou a outro código gerenciado. 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 das funções de valor escalar do CLR

As SVFs do .NET Framework são implementadas como métodos em uma classe de um assembly do .NET Framework. Os parâmetros de entrada e o tipo retornados de um SVF podem ser qualquer um dos tipos de dados escalares compatíveis com SQL Server, exceto varchar, char, rowversion, text, ntext, image, timestamp, table ou cursor. Os SVFs devem garantir uma correspondência entre o tipo de dados SQL Server e o tipo de dados de retorno do método de implementação. Para obter mais informações sobre conversões de tipo, consulte Mapeamento de dados de parâmetro CLR.

Ao implementar uma SVF do .NET Framework em uma linguagem do .NET Framework, o atributo personalizado SqlFunction pode ser especificado para incluir informações adicionais sobre a função. O atributo SqlFunction indica se a função acessa ou modifica os dados, se é determinística e se envolve operações de ponto flutuante.

Funções de valor escalar definidas pelo usuário podem ser determinísticas ou não. Uma função determinística sempre retorna o mesmo resultado quando chamada com um conjunto de parâmetros de entrada específico. Uma função não determinística pode retornar resultados diferentes quando chamada com um conjunto de parâmetros de entrada específico.

Observação

Não marque uma função como determinística se ela sempre produzir os mesmos valores de saída, considerando os mesmos valores de entrada e o mesmo estado do banco de dados. A marcação de uma função como determinística, quando a função, de fato, não é pode resultar em exibições indexadas e colunas computadas danificadas. Você marca uma função como determinística definindo a propriedade IsDeterministic como true.

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).

Exemplo de uma função de valor escalar do CLR

Eis uma SVF simples que acessa dados e retorna um valor inteiro:

using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
  
public class T  
{  
    [SqlFunction(DataAccess = DataAccessKind.Read)]  
    public static int ReturnOrderCount()  
    {  
        using (SqlConnection conn   
            = new SqlConnection("context connection=true"))  
        {  
            conn.Open();  
            SqlCommand cmd = new SqlCommand(  
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
            return (int)cmd.ExecuteScalar();  
        }  
    }  
}  

Imports Microsoft.SqlServer.Server  
Imports System.Data.SqlClient  
  
Public Class T  
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _  
    Public Shared Function ReturnOrderCount() As Integer  
        Using conn As New SqlConnection("context connection=true")  
            conn.Open()  
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
            Return CType(cmd.ExecuteScalar(), Integer)  
        End Using  
    End Function  
End Class

A primeira linha de código referencia Microsoft.SqlServer.Server para acessar atributos e System.Data.SqlClient para acessar o namespace do ADO.NET. (Esse namespace contém o SqlClient, o provedor de dados .NET Framework para SQL Server.)

Em seguida, a função recebe o atributo personalizado SqlFunction , encontrado no namespace Microsoft.SqlServer.Server . O atributo personalizado indica se a UDF (função definida pelo usuário) usa ou não o provedor em processo para ler dados no servidor. SQL Server não permite que UDFs atualizem, insiram ou excluam dados. SQL Server pode otimizar a execução de uma UDF que não usa o provedor em processo. Isso é indicado com a definição de DataAccessKind como DataAccessKind.None. Na próxima linha, o método de destino é uma estática pública (compartilhada no Visual Basic .NET).

A classe SqlContext, localizada no namespace Microsoft.SqlServer.Server, pode acessar um objeto SqlCommand com uma conexão com a instância SQL Server que já está configurada. Embora não seja usado aqui, o contexto de transação atual também está disponível por meio da API System.Transactions .

A maior parte das linhas de código no corpo da função deve parecer familiar aos desenvolvedores que já criaram aplicativos cliente que usam os tipos encontrados no namespace System.Data.SqlClient .

[C#]

using(SqlConnection conn = new SqlConnection("context connection=true"))   
{  
   conn.Open();  
   SqlCommand cmd = new SqlCommand(  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);  
   return (int) cmd.ExecuteScalar();  
}

[Visual Basic]

Using conn As New SqlConnection("context connection=true")  
   conn.Open()  
   Dim cmd As New SqlCommand( _  
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)  
   Return CType(cmd.ExecuteScalar(), Integer)  
End Using

O texto de comando apropriado é especificado com a inicialização do objeto SqlCommand . O exemplo anterior conta o número de linhas na tabela SalesOrderHeader. Em seguida, o método ExecuteScalar do objeto cmd é chamado. Isso retorna um valor do tipo int com base na consulta. Por fim, a contagem da ordem retorna ao chamador.

Caso seja salvo em um arquivo chamado FirstUdf.cs, esse código pode ser compilado como assembly da seguinte forma:

[C#]

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs

[Visual Basic]

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

Observação

/t:library indica que uma biblioteca, e não um executável, deve ser produzida. Os executáveis não podem ser registrados em SQL Server.

Observação

Não há suporte para objetos de banco de dados do Visual C++ compilados com /clr:pure para execução em SQL Server. Por exemplo, entre esses objetos de banco de dados estão funções de valor escalar.

A consulta Transact-SQL e uma invocação de exemplo para registrar o assembly e o UDF são:

CREATE ASSEMBLY FirstUdf FROM 'FirstUdf.dll';  
GO  
  
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT   
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;   
GO  
  
SELECT dbo.CountSalesOrderHeader();  
GO

Observe que o nome da função conforme exposto no Transact-SQL não precisa corresponder ao nome do método estático público de destino.

Consulte Também

Mapeando dados de parâmetro CLR
Visão geral dos atributos personalizados da integração CLR
Funções definidas pelo usuário
Acesso aos dados dos objetos de banco de dados CLR