dq_ninh
02-06-2010, 11:53
Hôm nay, tôi muốn hướng dẫn các bạn cách nạp một hàm CLR, được viết bằng C#, vào SQL 2005/2008 (và những phiên bản sau này) server.
Tôi có cơ hội được thực hiện một dự án (project) nho nhỏ là gửi 55 triệu emails đến “khách hàng” có những email thuộc về hơn 170 ngàn ISP (nhà cung cấp email) khác nhau.
Việc phải làm trước khi gửi 55 triệu emails là thẩm định khuôn thức của 55 triệu emails để loại bỏ những email bất hợp lệ. Có hàng ngàn lỗi khác nhau có thể xảy ra trong 55 triệu emails. Thay vì xnguyen.yahoo.com, vì một lý do nào đó, có thể đã được lưu giữ trong CSDL là x?nguyen.yahoo.com, hoặc xnguyen.yaho.com, hoặc xnguyen.yahoo.co. Do đó, chuyện phân lọc những email có khuôn thức sai là chuyện bắt buộc phải thực hiện đầu tiên.
Người tiền nhiệm của tôi có một stored procedure dùng để thẩm định và phân lọc email. Nhưng stored procedure này không những rắc rối, mà còn tốn rất nhiều thời gian vận hành để có thể phân lọc 55 triệu emails.
Với một chút xíu kinh nghiệm lập trình nhỏ nhoi trong quá trình viết những phần mềm .NET, tôi biết rằng với một chút thời gian nho nhỏ, tôi có thể phát triển một hàm C# dùng lớp RegEx để thẩm định email. Mới google trên mạng chưa đầy 5 phút, tôi đã tìm ra được hàm C# sau đây, chỉ có 2 giòng, nhưng ít nhất, khỏi mất công viết:
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public static partial class UserDefinedFunctions
{
public static readonly RegexOptions Options =
RegexOptions.IgnorePatternWhitespace |
RegexOptions.Compiled | RegexOptions.Singleline;
[SqlFunction]
public static SqlBoolean RegexMatch( SqlChars input, SqlString pattern )
{
Regex regex = new Regex( pattern.Value, Options );
return regex.IsMatch( new string( input.Value ) );
}
}
Sau khi có hàm trên, chuyện kế tiếp là phải dung VS-2008 để tạo một project thuộc loại class library. Tôi đặt tên cho project là SqlFunctions. Khi tạo project, tôi chọn .NET 2.0, vì biết rằng SQL-2005 đã được phát hành cùng thời gian với .NET 2.0
Sau khi project SqlFunctions đã được thiết lập. Tôi đổi tên của tập tin class1.cs thành RegexMatch.cs cho phù hợp với tên của hàm trên, và sao chép nguyên văn hàm trên vào trong tập tin này.
Kế tiếp là dung VS-2008 để “build” project, tạo ra tập tin SqlFunctions.dll.
Bước kế tiếp là đưa tập tin SqlFunctions.dll vào SQL-2005/SQL-2008 server. Cách đưa vào như sau. Các bạn cần phải đăng nhập vào SQL server với System Admin:
Bước thứ 1 – cài đặt SQL Server để cho phép xử dụng những hàm CLR:
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Bước thứ 2 – đưa thông tin về SqlFunctions.dll vào master database:
CREATE ASSEMBLY SqlFunctions FROM 'F:\GCG\wndTaskManager\SqlFunctions\bin\Release\Sq lfunctions.dll' WITH PERMISSION_SET = SAFE;
Bước thứ 3 – ánh xạ hàm CLR vào một hàm SQL cục bộ:
CREATE Function RegexMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(2048))
RETURNS BIT EXTERNAL NAME SqlFunctions.UserDefinedFunctions.RegexMatch
Thế là xong. Dưới đây là cách dung hàm RegexMatch:
Emails hợp khuôn thức hồi báo giá trị = 1
SELECT dbo.RegexMatch('BillClinton@gardencitygroup.com', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
SELECT dbo.RegexMatch('Bill.Clinton@gardencitygroup.net', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
SELECT dbo.RegexMatch('Bill.Clinton@gardencitygroup.com.f r', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
Emails sai khuôn thức hồi báo giá trị = 0
SELECT dbo.RegexMatch('BillClinton@gardencitygroup.com ', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
SELECT dbo.RegexMatch('Bill/Clinton@gardencitygroup.com', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
Áp dụng hàm SQL trên vào bảng như nhau:
Sau cùng, đây là lệnh SELECT để phân lọc những bản ghi có email bất hợp lệ (giản dị, nhưng có tốc độ xử lý tối ưu):
SELECT * FROM BANG_EMAIL WHERE 0 = dbo.RegexMatch(email_address, '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
Ngoài ra, các bạn còn có thể dùng hàm RegexMatch() trên với những biểu thức khác để thẩm định những dữ liệu khác như số điện thoại, chứng minh thư nhân dân, số bằng lái xe, vân vân và vân vân.
Nói tóm lại, chỉ cần một chút xíu tư duy, là có thể tìm thấy một phương pháp tốt hơn để giải quyết một vấn đề.
Tôi có cơ hội được thực hiện một dự án (project) nho nhỏ là gửi 55 triệu emails đến “khách hàng” có những email thuộc về hơn 170 ngàn ISP (nhà cung cấp email) khác nhau.
Việc phải làm trước khi gửi 55 triệu emails là thẩm định khuôn thức của 55 triệu emails để loại bỏ những email bất hợp lệ. Có hàng ngàn lỗi khác nhau có thể xảy ra trong 55 triệu emails. Thay vì xnguyen.yahoo.com, vì một lý do nào đó, có thể đã được lưu giữ trong CSDL là x?nguyen.yahoo.com, hoặc xnguyen.yaho.com, hoặc xnguyen.yahoo.co. Do đó, chuyện phân lọc những email có khuôn thức sai là chuyện bắt buộc phải thực hiện đầu tiên.
Người tiền nhiệm của tôi có một stored procedure dùng để thẩm định và phân lọc email. Nhưng stored procedure này không những rắc rối, mà còn tốn rất nhiều thời gian vận hành để có thể phân lọc 55 triệu emails.
Với một chút xíu kinh nghiệm lập trình nhỏ nhoi trong quá trình viết những phần mềm .NET, tôi biết rằng với một chút thời gian nho nhỏ, tôi có thể phát triển một hàm C# dùng lớp RegEx để thẩm định email. Mới google trên mạng chưa đầy 5 phút, tôi đã tìm ra được hàm C# sau đây, chỉ có 2 giòng, nhưng ít nhất, khỏi mất công viết:
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public static partial class UserDefinedFunctions
{
public static readonly RegexOptions Options =
RegexOptions.IgnorePatternWhitespace |
RegexOptions.Compiled | RegexOptions.Singleline;
[SqlFunction]
public static SqlBoolean RegexMatch( SqlChars input, SqlString pattern )
{
Regex regex = new Regex( pattern.Value, Options );
return regex.IsMatch( new string( input.Value ) );
}
}
Sau khi có hàm trên, chuyện kế tiếp là phải dung VS-2008 để tạo một project thuộc loại class library. Tôi đặt tên cho project là SqlFunctions. Khi tạo project, tôi chọn .NET 2.0, vì biết rằng SQL-2005 đã được phát hành cùng thời gian với .NET 2.0
Sau khi project SqlFunctions đã được thiết lập. Tôi đổi tên của tập tin class1.cs thành RegexMatch.cs cho phù hợp với tên của hàm trên, và sao chép nguyên văn hàm trên vào trong tập tin này.
Kế tiếp là dung VS-2008 để “build” project, tạo ra tập tin SqlFunctions.dll.
Bước kế tiếp là đưa tập tin SqlFunctions.dll vào SQL-2005/SQL-2008 server. Cách đưa vào như sau. Các bạn cần phải đăng nhập vào SQL server với System Admin:
Bước thứ 1 – cài đặt SQL Server để cho phép xử dụng những hàm CLR:
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
Bước thứ 2 – đưa thông tin về SqlFunctions.dll vào master database:
CREATE ASSEMBLY SqlFunctions FROM 'F:\GCG\wndTaskManager\SqlFunctions\bin\Release\Sq lfunctions.dll' WITH PERMISSION_SET = SAFE;
Bước thứ 3 – ánh xạ hàm CLR vào một hàm SQL cục bộ:
CREATE Function RegexMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(2048))
RETURNS BIT EXTERNAL NAME SqlFunctions.UserDefinedFunctions.RegexMatch
Thế là xong. Dưới đây là cách dung hàm RegexMatch:
Emails hợp khuôn thức hồi báo giá trị = 1
SELECT dbo.RegexMatch('BillClinton@gardencitygroup.com', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
SELECT dbo.RegexMatch('Bill.Clinton@gardencitygroup.net', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
SELECT dbo.RegexMatch('Bill.Clinton@gardencitygroup.com.f r', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
Emails sai khuôn thức hồi báo giá trị = 0
SELECT dbo.RegexMatch('BillClinton@gardencitygroup.com ', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
SELECT dbo.RegexMatch('Bill/Clinton@gardencitygroup.com', '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
Áp dụng hàm SQL trên vào bảng như nhau:
Sau cùng, đây là lệnh SELECT để phân lọc những bản ghi có email bất hợp lệ (giản dị, nhưng có tốc độ xử lý tối ưu):
SELECT * FROM BANG_EMAIL WHERE 0 = dbo.RegexMatch(email_address, '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$')
Ngoài ra, các bạn còn có thể dùng hàm RegexMatch() trên với những biểu thức khác để thẩm định những dữ liệu khác như số điện thoại, chứng minh thư nhân dân, số bằng lái xe, vân vân và vân vân.
Nói tóm lại, chỉ cần một chút xíu tư duy, là có thể tìm thấy một phương pháp tốt hơn để giải quyết một vấn đề.