Artykuł
07/22/2011

CLR oródwierszową funkcje

A funkcja zwracająca tabela jest funkcja zdefiniowana przez użytkownika, która zwraca tabela.

Począwszy od SQL Server 2005, SQL Server rozszerza funkcjonalność funkcja zwracająca tabelęs umożliwia zdefiniowanie funkcja zwracająca tabelę w dowolnym zarządzanych języka.Dane są zwracane z funkcja zwracająca tabelę przez IEnumerable lub IEnumerator obiektu.

Ostrzeżenie

Oródwierszową funkcji kolumny Typ zwrotu tabela nie może zawierać kolumny sygnatury czasowej lub kolumny Typ danych ciąg nieobsługujących kodu Unicode (takich jak char, varchar, i text).Ograniczenie NOT NULL nie jest obsługiwane.

Różnice między Transact-SQL i CLR oródwierszową funkcje

Transact-SQLFunkcje oródwierszową materialize wywołanie funkcja do pośrednich tabela wyniki.Ponieważ korzystają pośrednie tabela można obsługują one ograniczenia i unikatowe indeksy nad wyniki.Funkcje te mogą być bardzo użyteczne w dużych wyniki są zwracane.

W odróżnieniu CLR tabela-ważnych funkcji stanowią alternatywę strumieniowych.Nie istnieje wymóg że cały zestaw wyniki jest materialized w jednej tabela.IEnumerable Obiekt zwrócony przez funkcja zarządzanych bezpośrednio jest wywoływana przez plan wykonania kwerend, który wywołuje funkcja zwracająca tabelę, a wyniki są zużywane w sposób przyrostowy.Ten model strumieniowych zapewnia, że wyniki mogą być realizowane bezpośrednio po pierwszym wierszu jest dostępna, zamiast czekać na całą tabela, która ma zostać wypełniony.Jest także lepszym Jeśli masz bardzo dużej liczby wierszy zwracanych, ponieważ nie mają one materialized w pamięci jako całości.Na przykład, zarządzane funkcja zwracająca tabelę mogą być używane do analizowania pliku tekstowego i każdy wiersz jako wiersz zwrotu.

Implementowanie funkcji oródwierszową

Wdrożenie tabela-wyceniane jako metody klasy w funkcji Microsoft .NET Framework wirtualny plik dziennika.Z funkcja zwracająca tabelę kod musi implementować IEnumerable interfejs.IEnumerable interfejs jest zdefiniowany w.NET Framework.Typy reprezentujących tablice i zbiorów.NET Framework już zaimplementować IEnumerable interfejs.To ułatwia pisanie tabela-wycenione funkcje konwersji kolekcja lub tablicy w zestawie wyników.

Parametry oródwierszową

Parametry oródwierszową są typy zdefiniowane przez użytkownika tabela, które są przekazywane do procedury lub funkcja i skuteczny sposób przekazywania wielu wierszy danych do serwera.Parametry oródwierszową oferują podobne funkcje tablic parametrów, ale oferują większą elastyczność i ściślejszej integracja z Transact-SQL.Zapewniają także potencjału dla zwiększenia wydajności.Parametry oródwierszową również zmniejszyć liczbę niepotrzebnej do serwera.Zamiast wysyłać wiele żądań do serwera, takie jak z listą parametrów wartość skalarna, dane mogą być wysyłane do serwera jako tabela-parametr wycenione.Typ zdefiniowany przez użytkownika tabela nie przekazane jako parametr oródwierszową do zwracane z zarządzanych procedura składowana lub funkcja wykonywania w SQL Server procesu.Więcej informacji o tabela-wyceniane parametrów, zobacz Parametry oródwierszową (aparat bazy danych).

Parametry wyjściowe i oródwierszową funkcje

Informacje mogą być zwracane z wartościami przechowywanymi w tabela funkcji za pomocą parametrów wyjściowych.Odpowiedniego parametru w kodzie wykonania funkcja zwracająca tabelę należy użyć parametr przekazywany przez odwołanie jako argumentu.Należy zauważyć, że Visual Basic nie obsługuje parametrów wyjściowych w taki sam sposób, że Visual C#.Musisz zmiennoprzecinkową parametr odniesienie i zastosować <Out()> atrybut do reprezentowania parametru wyjściowego, jak w następujących:

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

Definiowanie funkcją oródwierszową w języku Transact-SQL

Składnia do definiowania CLR funkcja zwracająca tabelę jest podobny do Transact-SQL funkcja zwracająca tabelę, z dodaniem EXTERNAL NAME klauzula.Na przykład:

CREATE FUNCTION GetEmpFirstLastNames()
RETURNS TABLE (FirstName NVARCHAR(4000), LastName NVARCHAR(4000))
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;

Funkcje oródwierszową są używane do reprezentowania danych relacyjnych formularza do dalszego przetwarzania w kwerendach takich jak:

select * from function();
select * from tbl join function() f on tbl.col = f.col;
select * from table t cross apply function(t.column);

Funkcje oródwierszową można powrócić do tabela, gdy:

Utworzony z wartość skalarna argumenty wejściowe.Na przykład funkcja zwracająca tabelę , pobiera ciąg liczb rozdzielanych przecinkami i obracając je do tabela.
Generowane na podstawie danych zewnętrznych.Na przykład funkcja zwracająca tabela , odczytuje dziennika zdarzeń i udostępnia go jako tabela.

Uwagaa funkcja zwracająca tabelę można wykonać tylko dostęp do danych za pośrednictwem Transact-SQL kwerendy w InitMethod metoda, a nie w FillRow metoda.InitMethod Powinny być oznaczone SqlFunction.DataAccess.Read atrybut właściwość, jeśli Transact-SQL kwerenda jest wykonywana.

Funkcją oródwierszową próbki

Następujące funkcja zwracająca tabelę zwraca informacje z dziennika zdarzeń systemu.Funkcja przyjmuje argument jeden ciąg znaków zawierający nazwę zdarzenie dziennika do odczytu.

Przykładowy kod

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname).Entries;    }

    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Collections
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.Runtime.InteropServices

Public Class TabularEventLog
    <SqlFunction(FillRowMethodName:="FillRow")> _
    Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
        Return New EventLog(logname).Entries
    End Function

    Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Long)
        Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
        timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
        message = New SqlChars(eventLogEnTry.Message)
        category = New SqlChars(eventLogEnTry.Category)
        instanceId = eventLogEnTry.InstanceId
    End Sub
End Class

Deklarowanie i korzystania z funkcją oródwierszową próbki

Po próbki funkcja zwracająca tabelę został skompilowany, może być zadeklarowana w Transact-SQL podobnie do następującej:

use master
-- Replace SQL_Server_logon with your SQL Server user credentials.
GRANT EXTERNAL ACCESS ASSEMBLY TO [SQL_Server_logon] 
-- Modify the following line to specify a different database.
ALTER DATABASE master SET TRUSTWORTHY ON

-- Modify the next line to use the appropriate database.
CREATE ASSEMBLY tvfEventLog 
FROM 'D:\assemblies\tvfEventLog\tvfeventlog.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE 
(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)
AS 
EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod
GO

Należy zauważyć, że, począwszy od SQL Server 2005, nie można utworzyć typy zarządzane przez użytkownika, procedury przechowywane, funkcje, zagregowanych lub wyzwalaczy na SQL Server bazy danych z poziom zgodności "80".Aby skorzystać z tych funkcji integracja CLR z SQL Server, należy użyć sp_dbcmptlevel procedura składowana , aby ustawić poziom zgodności bazy danych "100".

Visual C++ skompilowany z/CLR obiektów z bazy danych: czysty nie są obsługiwane przez wykonanie na SQL Server 2005.Na przykład takie obiekty bazy danych to tabela-wycenione funkcji.

Aby przetestować próbki, spróbuj wykonać następujące Transact-SQL kodu:

-- Select the top 100 events,
SELECT TOP 100 *
FROM dbo.ReadEventLog(N'Security') as T
go

-- Select the last 10 login events.
SELECT TOP 10 T.logTime, T.Message, T.InstanceId 
FROM dbo.ReadEventLog(N'Security') as T
WHERE T.Category = N'Logon/Logoff'
go

Próbki: Zwracanie wyników SQL Server kwerendy

Następujący przykładowy pokazuje funkcja zwracająca tabelę kwerendy, SQL Server bazy danych.W tym przykładzie wykorzystano światła AdventureWorks2008R2 bazy danych.Zobacz https://www.codeplex.com/sqlserversamples więcej informacji na temat pobierania AdventureWorks2008R2.

Nazwa użytkownika źródło kod pliku FindInvalidEmails.cs lub FindInvalidEmails.vb.

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {
   private class EmailResult {
      public SqlInt32 CustomerId;
      public SqlString EmailAdress;

      public EmailResult(SqlInt32 customerId, SqlString emailAdress) {
         CustomerId = customerId;
         EmailAdress = emailAdress;
      }
   }

   public static bool ValidateEmail(SqlString emailAddress) {
      if (emailAddress.IsNull)
         return false;

      if (!emailAddress.Value.EndsWith("@adventure-works.com"))
         return false;

      // Validate the address. Put any more rules here.
      return true;
   }

   [SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "FindInvalidEmails_FillRow",
       TableDefinition="CustomerId int, EmailAddress nvarchar(4000)")]
   public static IEnumerable FindInvalidEmails(SqlDateTime modifiedSince) {
      ArrayList resultCollection = new ArrayList();

      using (SqlConnection connection = new SqlConnection("context connection=true")) {
         connection.Open();

         using (SqlCommand selectEmails = new SqlCommand(
             "SELECT " +
             "[CustomerID], [EmailAddress] " +
             "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " +
             "WHERE [ModifiedDate] >= @modifiedSince",
             connection)) {
            SqlParameter modifiedSinceParam = selectEmails.Parameters.Add(
                "@modifiedSince",
                SqlDbType.DateTime);
            modifiedSinceParam.Value = modifiedSince;

            using (SqlDataReader emailsReader = selectEmails.ExecuteReader()) {
               while (emailsReader.Read()) {
                  SqlString emailAddress = emailsReader.GetSqlString(1);
                  if (ValidateEmail(emailAddress)) {
                     resultCollection.Add(new EmailResult(
                         emailsReader.GetSqlInt32(0),
                         emailAddress));
                  }
               }
            }
         }
      }

      return resultCollection;
   }

   public static void FindInvalidEmails_FillRow(
       object emailResultObj,
       out SqlInt32 customerId,
       out SqlString emailAdress) {
      EmailResult emailResult = (EmailResult)emailResultObj;

      customerId = emailResult.CustomerId;
      emailAdress = emailResult.EmailAdress;
   }
};

Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Partial Class UserDefinedFunctions
   Private Class EmailResult
      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString

      Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
         CustomerId = customerId__1
         EmailAdress = emailAdress__2
      End Sub
   End Class

   Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
      If emailAddress.IsNull Then
         Return False
      End If

      If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
         Return False
      End If

      ' Validate the address. Put any more rules here.
      Return True
   End Function

   <SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
   Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList()

      Using connection As New SqlConnection("context connection=true")
         connection.Open()

         Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
            Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
            modifiedSinceParam.Value = modifiedSince

            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
               While emailsReader.Read()
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               End While
            End Using
         End Using
      End Using

      Return resultCollection
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, ByRef customerId As SqlInt32, ByRef emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)

      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub
End ClassImports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Partial Class UserDefinedFunctions
   Private Class EmailResult
      Public CustomerId As SqlInt32
      Public EmailAdress As SqlString

      Public Sub New(customerId__1 As SqlInt32, emailAdress__2 As SqlString)
         CustomerId = customerId__1
         EmailAdress = emailAdress__2
      End Sub
   End Class

   Public Shared Function ValidateEmail(emailAddress As SqlString) As Boolean
      If emailAddress.IsNull Then
         Return False
      End If

      If Not emailAddress.Value.EndsWith("@adventure-works.com") Then
         Return False
      End If

      ' Validate the address. Put any more rules here.
      Return True
   End Function

   <SqlFunction(DataAccess := DataAccessKind.Read, FillRowMethodName := "FindInvalidEmails_FillRow", TableDefinition := "CustomerId int, EmailAddress nvarchar(4000)")> _
   Public Shared Function FindInvalidEmails(modifiedSince As SqlDateTime) As IEnumerable
      Dim resultCollection As New ArrayList()

      Using connection As New SqlConnection("context connection=true")
         connection.Open()

         Using selectEmails As New SqlCommand("SELECT " & "[CustomerID], [EmailAddress] " & "FROM [AdventureWorksLT2008].[SalesLT].[Customer] " & "WHERE [ModifiedDate] >= @modifiedSince", connection)
            Dim modifiedSinceParam As SqlParameter = selectEmails.Parameters.Add("@modifiedSince", SqlDbType.DateTime)
            modifiedSinceParam.Value = modifiedSince

            Using emailsReader As SqlDataReader = selectEmails.ExecuteReader()
               While emailsReader.Read()
                  Dim emailAddress As SqlString = emailsReader.GetSqlString(1)
                  If ValidateEmail(emailAddress) Then
                     resultCollection.Add(New EmailResult(emailsReader.GetSqlInt32(0), emailAddress))
                  End If
               End While
            End Using
         End Using
      End Using

      Return resultCollection
   End Function

   Public Shared Sub FindInvalidEmails_FillRow(emailResultObj As Object, customerId As SqlInt32, emailAdress As SqlString)
      Dim emailResult As EmailResult = DirectCast(emailResultObj, EmailResult)

      customerId = emailResult.CustomerId
      emailAdress = emailResult.EmailAdress
   End Sub
End Class

Kompilacja źródło kodu do biblioteki DLL i skopiować do katalogu głównego na dysku c biblioteki DLL.Następnie należy wykonać następujące Transact-SQL kwerendy.

use AdventureWorksLT2008
go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FindInvalidEmails')
   DROP FUNCTION FindInvalidEmails
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyClrCode')
   DROP ASSEMBLY MyClrCode
go

CREATE ASSEMBLY MyClrCode FROM 'C:\FindInvalidEmails.dll'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS
GO

CREATE FUNCTION FindInvalidEmails(@ModifiedSince datetime) 
RETURNS TABLE (
   CustomerId int,
   EmailAddress nvarchar(4000)
)
AS EXTERNAL NAME MyClrCode.UserDefinedFunctions.[FindInvalidEmails]
go

SELECT * FROM FindInvalidEmails('2000-01-01')
go

Zobacz także

Koncepcje

Opcja poziom zgodności bazy danych

Udostępnij za pośrednictwem