Xây dựng các đối tượng cơ sở dữ liệu với .NET Common Language Runtime (CLR) trong SQL 2005


Trong phiên bản 2005, môi trường thực thi của Microsoft .NET Framework (CLR) nay được chạy bên trong cơ sở dữ liệu. Môi trường thực thi này hỗ trợ việc việc xây dựng các đối tượng cơ sở dữ liệu dạng thủ tục, bao gồm hàm, thủ tục lưu trữ, trigger, được viết bằng các ngôn ngữ trên nền Microsoft.NET như C# hoặc VB.NET. Các ngôn ngữ trên nền .NET hỗ trợ nhiều đặc điểm mà trong ngôn ngữ T-SQL không có, cho phép xây dựng các đối tượng cơ sở dữ liệu phức tạp hơn. Các kiểu dữ liệu do người dùng định nghĩa và các aggregate (các tập hợp dữ liệu tổng hợp sẵn dùng trong Analysis Service) có thể được viết bằng một ngôn ngữ .NET để xây dựng các kiểu dữ liệu phức tạp hơn so với các phiên bản trước của SQL Server.

Thủ tục được lưu trữ (trong CSDL) (SP – Stored Procedure, gọi tắt trong bài này là thủ tục) là các thủ tục không thể dùng trong các biểu thức vô hướng. Không giống các hàm vô hướng, chúng có thể trả về các dữ liệu dạng bảng và các thông điệp cho client, gọi các phát biểu DDL (data definition language – ngôn ngữ định nghĩa dữ liệu) và DML (data manipulation language – ngôn ngữ xử lý dữ liệu), và trả về giá trị thông qua các tham số dạng output.

Các yêu cầu cho thủ tục CLR

Trong CLR, các thủ tục được xây dựng như các phương thức public static trên một lớp thuộc một assembly của Microsoft .NET Framework. Phương thức static (tĩnh) này có thể được khai báo để trả về kiểu void hay một số nguyên. Nếu nó trả về số nguyên, giá trị này sẽ được coi như giá trị trả về của thủ tục. Ví dụ:

EXECUTE @return_status = procedure_name

Biến @return_status sẽ chứa kết quả trả về cua phương thức. Nếu phương thức được khai báo là void, giá trị trả về sẽ là 0.

Nếu phương thức có nhận tham số, số lượng tham số được khai báo trong hàm .NET phải có cùng số lượng như số tham số được truyền vào trong phát biểu Transact -SQL goi thủ tục.

Các tham số được chuyển đến thủ tục lưu trữ CLR có thể là bất kỳ kiểu dữ liệu tự nhiên nào của SQL Server mà có kiểu tương tự trong .NET. Đối với phát biểu Transact-SQL dùng để tạo ra thủ tục, các kiểu của nó phải được chỉ định sao cho giống các kiểu dữ liệu tương ứng trong mã .NET nhất.

Bảng sau đây liệt kê ra các kiểu dữ liệu trong Microsoft SQL Server, kiểu dữ liệu tương ứng trong namespace System.Data.SqlTypes, và kiểu dữ liệu tự nhiên tương ứng trong Microsoft .NET Framework.

Kiểu dữ liệu SQL Server Kiểu dữ liệu CLR (SQL Server) Kiểu dữ liệu CLR (.NET Framework)
varbinary SqlBytes, SqlBinary Byte[]
binary SqlBytes, SqlBinary Byte[]
varbinary(1), binary(1) SqlBytes, SqlBinary byte, Byte[]
image Không có Không có
varchar Không có Không có
char Không có Không có
nvarchar(1), nchar(1) SqlChars, SqlString Char, String, Char[]
nvarchar SqlChars, SqlStringSQLChars tốt hơn cho việc truyền và truy cập dữ liệu, còn SQLString tốt hơn cho các thao tác xử lý chuỗi. String, Char[]
nchar SqlChars, SqlString String, Char[]
text Không có Không có
ntext Không có Không có
uniqueidentifier SqlGuid Guid
rowversion Không có Byte[]
bit SqlBoolean Boolean
tinyint SqlByte Byte
smallint SqlInt16 Int16
int SqlInt32 Int32
bigint SqlInt64 Int64
smallmoney SqlMoney Decimal
money SqlMoney Decimal
numeric SqlDecimal Decimal
decimal SqlDecimal Decimal
real SqlSingle Single
float SqlDouble Double
smalldatetime SqlDateTime DateTime
datetime SqlDateTime DateTime
sql_variant Không có Object
User-defined type(UDT) Không có Cùng lớp này phải được đưa vào cùng assembly hoặc trong một assembly phụ thuộc.
table Không có Không có
cursor Không có Không có
timestamp Không có Không có
xml SqlXml Không có

Trả kết quả về từ thủ tục CLR

Thông tin có thể được trả về từ các thủ tục .NET Framework theo một số cách bao gồm các tham số xuất (output parameter), các bảng, và các thông điệp.

Các tham số xuất và các thủ tục CLR

Cũng giống như với các thủ tục Transact-SQL, thông tin có thể được tả về từ thủ tục lưu trữ .NET Framework dùng các tham số xuất. Cú pháp Transact-SQL DML được dùng để tạo ra các thủ tục lưu trữ .NET Framework giống như tạo ra bằng Transact-SQL, va tham số tương ứng trong mã .NET Framework phải được khai báo truyền theo tham chiếu. Nhớ rằng VB không hỗ trợ các tham số xuất giống như cách của C#, bạn phải chỉ ra tham số được truyền theo tham chiếu và dùng thuộc tinh <Out()> để biểu diễn một tham số xuất, như sau:

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

Ví dụ sau biểu diễn một thủ tục trả về thông tin thông qua một tham số xuất:

[C#]

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

[Visual Basic]

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

Trả về dữ liệu dạng bảng và thông điệp

Trả về kết quả dạng bảng và các thông điệp đến client thông qua đối tượng SqlPipe, đối tượng này có thể lấy được bằng cách dùng thuộc tính Pipe của lớp SqlContext. Đối tượng SqlPipe có phương thức Send, bằng cách gọi hàm này, bạn có thể truyền dữ liệu đường ống (pipe) đến ứng dụng gọi.

Có một số hàm SqlPipe.Send khác nhau, bao gồm một cái cho phép gửi một đối tượng SqlDataReader và một cái đơn giản là gửi một chuối ký tự.

Trả về các thông điệp

Dùng SqlPipe.Send(string) để gửi một thông điệp đến ứng dụng client. Độ dài chuỗi ký tự được giới hạn tối đa 8000 ký tự, nếu nó dài hơn 8000, nó sẽ bị cắt bớt.

Trả về tập kết quả dạng bảng

Để gửi các kết quả truy vấn trực tiếp đến client, dùng một trong các phương thức Execute của đối tượng SqlPipe. Đây là cách hiệu quả nhất để trả kết quả về cho client, vì dữ liệu được chuyển thẳng vào vùng đệm mạng mà không cần chép vào bộ nhớ của môi trường CLR (managed memory). Ví dụ:

 [C#]

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

[Visual Basic]

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

Để gửi kết quả của một câu truy vấn vừa được thi hành thông qua in-process provider (hoặc để tiền xử lý dữ liệu dùng một custom implementation của SqlDataReader), dùng phương thức Send nhận tham số SqlDataReader. Phương thức này hơi chậm hơn phương pháp trực tiếp được mô tả ở trên, nhưng nó mềm dẻo hơn khi cần xử lý dữ liệu trước khi gửi đến client.

[C#]

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

[Visual Basic]

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

Để tạo ra một tập kết quả động, đưa dữ liệu vào và gửi nó cho client, bạn có thể tạo các bản ghi từ kết nối hiện tại và gứi nó bằng cách dùng SqlPipe.Send.

[C#]

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

[Visual Basic]

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

Tiếp theo là một ví dụ về gửi một tập kết quả dạng bảng và một thông điệp thông qua 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);
      }
   }
}

[Visual Basic]

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

Lời gọi hàm Send đầu tiên gửi một chuỗi thông điệp đến client, trong khi cái thứ hai gửi một tập kết quả dạng bảng dùng SqlDataReader.

Ghi nhớ là các ví dụ này chỉ dùng để minh họa. Các hàm CLR thích hợp hợp các hàm Transact-SQL đơn giản trong các công việc nặng về tính toán. Một thủ tục tương tự ví dụ trên được viết bằng Transact-SQL là:

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

Ghi chú:

Các thông điệp và các tập kết quả được lấy về theo những cách khác nhau trong các ứng dụng khách. Lây ví dụ cụ thể, SQL Server Management Studio hiển thị các tập kết quả trong cửa sổ Results, còn các thông điệp sẽ được hiển thị trong cửa sổ Messages.

Nếu đoạn mã C# trên được lưu trong file tên MyFirstUdp.cs và được dịch với lệnh:

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

Hoặc nếu như đoạn mã Visual Basic được lưu trong file MyFirstUdp.vb và được dịch với:

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

Assembly kết quả có thể được đăng ký, và sau đó được gọi với các lệnh DDL sau:

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

 

7 thoughts on “Xây dựng các đối tượng cơ sở dữ liệu với .NET Common Language Runtime (CLR) trong SQL 2005

  1. Liên quan tới Sql Server 2005 tôi muốn hỏi, làm thế nào để thực hiện Store procedure 1 cách tự động.
    Ví dụ : khoảng 5 phút thì thực hiện 1 sp_xxx q lần.

    Đào Hải Nam biết có thể chỉ giúp tôi.

    Thanhnk’s.

  2. Nếu dùng công cụ của SQL Server thì có thể tạo một SQL Server Agent Job, tạo một Step trong Job đó để thực hiện câu lệnh gọi đến thủ tục, và tao một schedule cho thủ tục job đã tạo được gọi định kỳ.

  3. Chào bác Nam!!

    Mấy hôm trước lan tháng trên web tìm thấy mấy bài viết của Bác hay quá, vào đọc. Sẵn đây cho Em hỏi tí:

    – Chắc Bác cũng đã từng làm về Store Procedure rồi phải không Bác. Em đang làm một số cái SP. Nhưng đang vướng ở chổ là trong SQL Server thì nó hiểu kiểu định dạng của datetime là “MM/dd/yyyy”, khi em show lên cho Khách xem thì phải định dạng là “dd/MM/yyyy” không biết phải dùng hàm gì trong C# để có thể chuyển đổi lại kiểu cần thiết đó mới có thể đưa vào SQL server Bác nhĩ.

    Cám ơn bác nhé.

  4. Nếu bạn dùng chuỗi mô tả ngày tháng thì bạn mới dùng ‘MM/dd/yyyy’, không rõ bạn show cho khách hàng bằng cách nào, công cụ gì.
    Nếu là dữ liệu được hiển thị trên chương trình của bạn thì bạn lấy về kiểu Datetime rồi định dạng theo cách mà ngôn ngữ lập trình cung cấp.

  5. Em moi buoc vao linh vuc lap trinh C#. nen cam thay kho khan qua.
    Em khong hieu lam ve data class. Bac Nam co the rut gon nhung van de don gian , de em de hieu dc khong a?. Em cam on nhieu

  6. dùng hàm này mà đổi convert(varchar(11),field,103) as ngay. 101 = mm/dd/yyy. 103 =dd/mm/yyyy.

Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s