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 |
Đào Hải Nam 
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.
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ỳ.
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é.
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.
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
tôi cũng không hiểu data class mà bạn đang nói đến là gì 😀
dùng hàm này mà đổi convert(varchar(11),field,103) as ngay. 101 = mm/dd/yyy. 103 =dd/mm/yyyy.