Partilhar via

Funções com valor escalar CLR

  • Artigo

Aplica-se a:SQL Server

Uma função com valor escalar (SVF) retorna um único valor, como uma cadeia de caracteres, inteiro ou valor de bit. Você pode criar funções definidas pelo usuário com valor escalar em código gerenciado usando qualquer linguagem de programação do .NET Framework. Essas funções são acessíveis a Transact-SQL ou outro código gerenciado. Para obter informações sobre as vantagens da integração CLR (Common Language Runtime) e escolher entre código gerenciado e Transact-SQL, consulte visão geral da integração CLR.

Requisitos para funções de valor escalar CLR

Os SVFs do .NET Framework são implementados como métodos em uma classe em um assembly do .NET Framework. Os parâmetros de entrada e o tipo retornado de um SVF podem ser qualquer um dos tipos de dados escalares suportados pelo SQL Server, exceto varchar, char, rowversion, texto, ntext, image, timestamp, tabelaou cursor. Os SVFs devem garantir uma correspondência entre o tipo de dados do 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 Mapear dados de parâmetros CLR.

Quando você implementa um .NET Framework SVF em uma linguagem .NET Framework, você pode especificar o atributo personalizado SqlFunction para incluir informações adicionais sobre a função. O atributo SqlFunction indica se a função acessa ou modifica dados, se é determinística e se a função envolve operações de ponto flutuante.

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

Observação

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

Parâmetros com valor de tabela

Os parâmetros com valor de tabela (TVPs), tipos de tabela definidos pelo usuário que são passados para um procedimento ou função, fornecem 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 integração mais estreita com o Transact-SQL. Eles também fornecem o potencial para um melhor desempenho.

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 para o servidor, como com uma lista de parâmetros escalares, os dados podem ser enviados para o 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 de, um procedimento armazenado gerenciado ou função em execução no processo do SQL Server. Para obter mais informações sobre TVPs, consulte Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados).

Exemplo de uma função com valor escalar CLR

Aqui está um 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();
        }
    }
}

A primeira linha de referências de código Microsoft.SqlServer.Server acessar atributos e System.Data.SqlClient acessar o namespace ADO.NET. (Este namespace contém SqlClient, o Provedor de Dados do .NET Framework para SQL Server.)

Em seguida, a função recebe o SqlFunction atributo personalizado, que é encontrado no namespace Microsoft.SqlServer.Server. O atributo custom indica se a função definida pelo usuário (UDF) usa ou não o provedor em processo para ler dados no servidor. O SQL Server não permite que UDFs atualizem, insiram ou excluam dados. O SQL Server pode otimizar a execução de um UDF que não usa o provedor em processo. Isso é indicado definindo DataAccessKind para 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 do 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 interface de programação de aplicativos (API) System.Transactions.

A maioria das linhas de código no corpo da função deve parecer familiar para os desenvolvedores que escrevem aplicativos cliente que usam os tipos encontrados no namespace System.Data.SqlClient.

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

O texto do comando apropriado é especificado inicializando o 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. Finalmente, a contagem de pedidos é devolvida ao chamador.

Se esse código for salvo em um arquivo chamado FirstUdf.cs, ele poderá ser compilado como assembly da seguinte maneira:

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

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

Objetos de banco de dados Visual C++ compilados com /clr:pure não têm suporte para execução no SQL Server. Por exemplo, esses objetos de banco de dados incluem funções com 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

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.

Conteúdo relacionado