Prosedur Tersimpan CLR

Prosedur tersimpan adalah rutinitas yang tidak dapat digunakan dalam ekspresi skalar. Tidak seperti fungsi skalar, mereka dapat mengembalikan hasil dan pesan tabular ke klien, memanggil pernyataan bahasa definisi data (DDL) dan bahasa manipulasi data (DML), dan mengembalikan parameter output. Untuk informasi tentang keuntungan integrasi CLR dan memilih antara kode terkelola dan Transact-SQL, lihat Gambaran Umum Integrasi CLR.

Persyaratan untuk Prosedur Tersimpan CLR

Dalam runtime bahasa umum (CLR), prosedur tersimpan diimplementasikan sebagai metode statis publik pada kelas dalam rakitan kerangka kerja Microsoft.NET. Metode statis dapat dideklarasikan sebagai batal, atau mengembalikan nilai bilangan bulat. Jika mengembalikan nilai bilangan bulat, bilangan bulat yang dikembalikan diperlakukan sebagai kode pengembalian dari prosedur. Contohnya:

EXECUTE @return_status = procedure_name

Variabel @return_status akan berisi nilai yang dikembalikan oleh metode . Jika metode dinyatakan batal, kode pengembalian adalah 0.

Jika metode mengambil parameter, jumlah parameter dalam implementasi .NET Framework harus sama dengan jumlah parameter yang digunakan dalam deklarasi Transact-SQL prosedur tersimpan.

Parameter yang diteruskan ke prosedur tersimpan CLR dapat berupa salah satu jenis SQL Server asli yang memiliki ekuivalen dalam kode terkelola. Agar sintaks Transact-SQL membuat prosedur, jenis ini harus ditentukan dengan jenis SQL Server asli yang paling sesuai yang setara. Untuk informasi selengkapnya tentang konversi jenis, lihat Memetakan Data Parameter CLR.

Parameter dengan Nilai Tabel

Parameter bernilai tabel (TVP), jenis tabel yang ditentukan pengguna yang diteruskan ke prosedur atau fungsi, menyediakan cara yang efisien untuk meneruskan beberapa baris data ke server. TVP menyediakan fungsionalitas serupa dengan array parameter, tetapi menawarkan fleksibilitas yang lebih besar dan integrasi yang lebih dekat dengan Transact-SQL. Mereka juga memberikan potensi performa yang lebih baik. TVP juga membantu mengurangi jumlah perjalanan pulang pergi ke server. Alih-alih mengirim beberapa permintaan ke server, seperti dengan daftar parameter skalar, data dapat dikirim ke server sebagai TVP. Jenis tabel yang ditentukan pengguna tidak dapat diteruskan sebagai parameter bernilai tabel ke, atau dikembalikan dari, prosedur tersimpan terkelola atau fungsi yang dijalankan dalam proses SQL Server. Untuk informasi selengkapnya tentang TVP, lihat Menggunakan Parameter Table-Valued (Mesin Database).

Mengembalikan Hasil dari Prosedur Tersimpan CLR

Informasi dapat dikembalikan dari prosedur tersimpan .NET Framework dengan beberapa cara. Ini termasuk parameter output, hasil tabular, dan pesan.

Parameter OUTPUT dan Prosedur Tersimpan CLR

Seperti halnya prosedur tersimpan Transact-SQL, informasi dapat dikembalikan dari prosedur tersimpan .NET Framework menggunakan parameter OUTPUT. Sintaks Transact-SQL DML yang digunakan untuk membuat prosedur tersimpan .NET Framework sama dengan yang digunakan untuk membuat prosedur tersimpan yang ditulis dalam Transact-SQL. Parameter yang sesuai dalam kode implementasi di kelas .NET Framework harus menggunakan parameter referensi pass-by sebagai argumen. Perhatikan bahwa Visual Basic tidak mendukung parameter output dengan cara yang sama seperti C#. Anda harus menentukan parameter berdasarkan referensi dan menerapkan <atribut Out()> untuk mewakili parameter OUTPUT, seperti dalam hal berikut:

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

Berikut ini menunjukkan prosedur tersimpan yang mengembalikan informasi melalui parameter 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

Setelah rakitan yang berisi prosedur tersimpan CLR di atas telah dibuat dan dibuat di server, Transact-SQL berikut digunakan untuk membuat prosedur dalam database, dan menentukan jumlah sebagai parameter 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

Perhatikan bahwa jumlah dinyatakan sebagai int jenis data SQL Server, dan bahwa parameter nilai yang ditentukan dalam prosedur tersimpan CLR ditentukan sebagai SqlInt32 jenis data CLR. Ketika program panggilan menjalankan prosedur tersimpan CLR, SQL Server secara otomatis mengonversi SqlInt32 jenis data CLR ke intjenis data SQL Server. Untuk informasi selengkapnya tentang jenis data CLR mana yang dapat dan tidak dapat dikonversi, lihat Memetakan Data Parameter CLR.

Mengembalikan Hasil dan Pesan Tabular

Mengembalikan hasil tabular dan pesan kepada klien dilakukan melalui SqlPipe objek, yang diperoleh dengan menggunakan Pipe properti SqlContext kelas . Objek SqlPipe memiliki Send metode . Dengan memanggil metode , Send Anda dapat mengirimkan data melalui pipa ke aplikasi panggilan.

Ini adalah beberapa kelebihan beban SqlPipe.Send metode, termasuk yang mengirim SqlDataReader dan yang lain yang hanya mengirim string teks.

Mengembalikan Pesan

Gunakan SqlPipe.Send(string) untuk mengirim pesan ke aplikasi klien. Teks pesan dibatasi hingga 8000 karakter. Jika pesan melebihi 8000 karakter, pesan akan dipotong.

Mengembalikan Hasil Tabular

Untuk mengirim hasil kueri langsung ke klien, gunakan salah satu kelebihan beban Execute metode pada SqlPipe objek. Ini adalah cara paling efisien untuk mengembalikan hasil ke klien, karena data ditransfer ke buffer jaringan tanpa disalin ke dalam memori terkelola. Contohnya:

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

Untuk mengirim hasil kueri yang dijalankan sebelumnya melalui penyedia dalam proses (atau untuk melakukan pra-proses data menggunakan implementasi SqlDataReaderkustom ), gunakan kelebihan muatan Send metode yang mengambil SqlDataReader. Metode ini sedikit lebih lambat daripada metode langsung yang dijelaskan sebelumnya, tetapi menawarkan fleksibilitas yang lebih besar untuk memanipulasi data sebelum dikirim ke klien.

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

Untuk membuat tataan hasil dinamis, isi dan kirim ke klien, Anda dapat membuat rekaman dari koneksi saat ini dan mengirimnya menggunakan 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

Berikut adalah contoh pengiriman hasil tabular dan pesan melalui 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

Yang pertama Send mengirim pesan ke klien, sementara yang kedua mengirim hasil tabular menggunakan SqlDataReader.

Perhatikan bahwa contoh-contoh ini hanya untuk tujuan ilustrasi. Fungsi CLR lebih tepat daripada pernyataan Transact-SQL sederhana untuk aplikasi intensif komputasi. Prosedur tersimpan Transact-SQL yang hampir setara dengan contoh sebelumnya adalah:

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

Nota

Pesan dan tataan hasil diambil secara berbeda dalam aplikasi klien. Misalnya, kumpulan hasil SQL Server Management Studio muncul di tampilan Hasil , dan pesan muncul di panel Pesan .

Jika kode Visual C# di atas disimpan dalam file MyFirstUdp.cs dan dikompilasi dengan:

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

Atau, jika kode Visual Basic di atas disimpan dalam file MyFirstUdp.vb dan dikompilasi dengan:

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

Nota

Dimulai dengan SQL Server 2005, objek database Visual C++ (seperti prosedur tersimpan) yang dikompilasi dengan /clr:pure tidak didukung untuk eksekusi.

Rakitan yang dihasilkan dapat didaftarkan, dan titik masuk dipanggil, dengan DDL berikut:

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

Lihat Juga

Fungsi User-Defined CLR
Jenis User-Defined CLR
Pemicu CLR

Last updated on 2017-04-27

Bagikan melalui