Funciones escalares de CLR

Requisitos de las funciones escalares de CLR

Las SVF de .NET Framework se implementan como métodos en una clase de un ensamblado de .NET Framework. Los parámetros de entrada y el tipo devueltos de una SVF pueden ser cualquiera de los tipos de datos escalares admitidos por SQL Server, excepto varchar, char, rowversion, text, ntext, image, timestamp, table o cursor. Las SVF deben asegurar una coincidencia entre el tipo de datos SQL Server y el tipo de datos de retorno del método de implementación. Para obtener más información acerca de las conversiones de tipos de datos, vea Asignar datos de parámetros CLR.

Al implementar una SVF de .NET Framework SVF en un lenguaje .NET Framework, el atributo personalizado SqlFunction se puede especificar para incluir la información adicional de la función. El atributo SqlFunction indica tanto si la función obtiene acceso o modifica los datos como si no, si es determinista y si la función implica las operaciones de coma flotante.

Las funciones escalares definidas por el usuario pueden ser deterministas o no deterministas. Una función determinista siempre devuelve el mismo resultado cuando se llama con un conjunto concreto de parámetros de entrada. Una función no determinista puede devolver resultados distintos cuando se llama con un conjunto concreto de parámetros de entrada.

Parámetros con valores de tabla

Ejemplo de una función escalar de CLR

A continuación se muestra una SVF simple que tiene acceso a datos y devuelve un valor entero:

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

La primera línea de código hace referencia a Microsoft.SqlServer.Server para tener acceso a los atributos y a System.Data.SqlClient para tener acceso al espacio de nombres de ADO.NET. (Este espacio de nombres contiene SqlClient, el proveedor de datos de .NET Framework para SQL Server.)

Después, la función recibe el atributo personalizado SqlFunction, que se encuentra en el espacio de nombres Microsoft.SqlServer.Server. El atributo personalizado indica si la función definida por el usuario (UDF) usa o no el proveedor en proceso para leer los datos en el servidor. SQL Server no permite a las UDF actualizar, insertar o eliminar datos. SQL Server puede optimizar la ejecución de una UDF que no usa el proveedor en proceso. Esto se indica estableciendo DataAccessKind en DataAccessKind.None. En la línea siguiente, el método de destino es una estática pública (se comparte en Visual Basic .NET).

La clase SqlContext, ubicada en el espacio de nombres Microsoft.SqlServer.Server, puede tener acceso a un objeto SqlCommand con una conexión a la instancia SQL Server que ya está establecida. Aunque no se usa aquí, el contexto de transacción actual también está disponible a través de la interfaz de programación de aplicaciones (API) System.Transactions.

Los programadores que han escrito las aplicaciones cliente que usan los tipos situados en el espacio de nombres System.Data.SqlClient, deberían estar familiarizados con la mayoría de las líneas de código en el cuerpo de la función.

[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

El texto de comando adecuado se especifica inicializando el objeto SqlCommand. En el ejemplo anterior se cuenta el número de filas en la tabla SalesOrderHeader. Después, se llama al método ExecuteScalar del objeto cmd. Esto devuelve un valor de tipo int basado en la consulta. Por último, se devuelve Order Count al autor de la llamada.

Si este código se guarda en un archivo denominado FirstUdf.cs, puede estar compilado en un ensamblado como se muestra a continuación:

[C#]

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

[Visual Basic]

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

La consulta Transact-SQL y una invocación de ejemplo para registrar el ensamblado y una UDF son:

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 el nombre de función tal y como se expone en Transact-SQL no necesita coincidir con el nombre de método estático público de destino.

Vea también

Conceptos

Otros recursos