การใช้งาน .NET CLR ใน MSSQL Server 2008 R2

Posted by on April 7, 2015

สวัสดีครับ,

ผมมีตัวอย่างการใช้ MSSQL Server 2008 R2 ในการเรียกใช้ Function ซึ่งเขียนในรูปแบบของ C# น่ะครับ (ต่อไปนี้จะเรียกว่า CLR Code ในรูปแบบของ SQL)

ข้อดี

  1. รองรับ arrays, collections, for-each loops, bit shifting, หรือ classes.
  2. รองรับ object-oriented capabilities เช่น encapsulation, inheritance, and polymorphism.
  3. เหมาะสำหรับ การคำนวนที่ซับซ้อน รวมถึงการจัดการกับตัวอักษร และ regular expressions.
  4. สามารใช้ built-in function ที่มีใน C# มาเสริมในกรณีที่ไม่สามารถเขียนด้วย SQL ได้โดยตรง

ข้อเสีย

  1. การทำงานจะช้ากว่าการเขียนด้วย Stored Procedure
  2. ไม่เหมาะสำหรับการใช้คำนวน จำนวนแถวที่มากๆ

เรามาเริ่มกันเลยน่ะครับ

ขั้นตอนที่ 1 : การสร้าง SQL CLR Database Project ใน Visual Studio

1.ไปที่  Visual Studio .Net 2008 or 2010, เลือกสร้าง project ‘Visual C# SQL CLR Database Project template’ โดย

1.1 เปิด VS 2010, เลือก “New Project”

1.2 เลือก Database —> SQL Server —> Visual C# SQL CLR Database Project template.

เพิ่มเติม:

SQL Server 2005 and SQL Server 2008 ต้องการ SQL CLR .NET framework. assemblies target ตั้งแต่ version 2.0, 3.0, or 3.5  

DB_CLR_1

1.3 สำหรับการระบุ .NET version targets, click ขวาที่ project name ใน Solution Explorer และเลือก Properties. (ในตัวอย่างนี้ Set ไว้ที่ ‘.Net Framework 3.5’)

DB_CLR_2

1.4 ทำการระบุชื่อ project, click OK
1.5 ทำการ ปรับค่าการติดต่อกับ database, ทดลองติดต่อ database, เมื่อติดต่อได้แล้ว, click OK
1.6 Click ขวา ที่ project และเลือก ‘add a user defined function.’

 

1.7 ตัวอย่างข้างล่างนี้จะเกี่ยวข้องกับการ แปลงเวลาที่ ต้องมี Daylight Saving Time (DST) เข้ามาเกี่ยวข้อง (MSSQL ไม่มี built-in functions ที่สามารถ แปลงเวลาโดยใช้ DST  )

1.7.1 แปลงจาก Paris time zone (GMT+1 with no DST, GMT+2 with DST) ไปเป็น UTC (GMT+0)
1.7.2 แปลงจาก UTC (GMT+0) ไปเป็น Paris time zone (GMT+1 with no DST, GMT+2 with DST)

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static DateTime ParisToUtc(DateTime ParisDateTime)

{

TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById(“Romance Standard Time”);

// “TimeZone for Paris (Europe/Paris)” it’s the ID regardless of daylight saving time

(i.e. no matters if right now is daylight saving time, the ID name remains the same)

 

DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(ParisDateTime, zone);

// it automatically takes care of daylight saving time

 

return utcTime;

}

 

[Microsoft.SqlServer.Server.SqlFunction]

public static DateTime UtcToParis(DateTime UTCDateTime)

{

TimeZoneInfo zone = TimeZoneInfo.FindSystemTimeZoneById(“Romance Standard Time”);

// “TimeZone for Paris (Europe/Paris)” it’s the ID regardless of daylight saving time

(i.e. no matters if right now is daylight saving time, the ID name remains the same)

 

DateTime utcTime = TimeZoneInfo.ConvertTimeFromUtc(UTCDateTime,zone);

// it automatically takes care of daylight saving time

 

return utcTime;

}

};

 

Capture

1.7.3 Click the ‘Build’ เพื่อ build the .Net script ไปเป็นไฟล์ ‘DLL’ สำหรับการ deploy ลง MSSQL server ในขั้นตอนถัดไปครับ…..

 

ขั้นตอนที่ 2 : การเรียกใช้ SQL CLR Database Project ใน MSSQL Server 2008R2

2.1 ไปที่ MSSQL Server 2008R2

2.2 เลือก database ที่ต้องการ

2.3 ทำการ กำหนดให้ database นั่นรองรับการเรียกใช้ CLR Code ด้วยคำสั่งดังต่อไปนี้…

— Reset the “allow updates” setting to the recommended 0

sp_configure ‘allow updates’,0;

RECONFIGURE WITH OVERRIDE

GO

 

sp_configure ‘clr enabled’, 1

RECONFIGURE

GO

Capture

2.4 ทำการ Deploy ไฟล์ DLL ที่เราสร้างจากขั้นตอนแรก ด้วยคำสั่งดังต่อไปนี้…

USE <your database name>

 

ALTER DATABASE <your database name> SET TRUSTWORTHY ON

 

— Use the command to change db owner to sa

EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false

 

— Deploy the DLL to be Assembly

CREATE ASSEMBLY TimeConvertor

FROM ‘D:\TimeConverting.dll’

WITH PERMISSION_SET = UNSAFE;

GO

 

— Create a function to convert Paris time to UTC by using the function from the DLL

CREATE FUNCTION [dbo].[ParisToUtc](@dt [datetime])

RETURNS [datetime] WITH EXECUTE AS CALLER

AS

EXTERNAL NAME TimeConvertor.UserDefinedFunctions.ParisToUtc

GO

 

— Create a function to convert UTC to Paris time by using the function from the DLL

CREATE FUNCTION [dbo].[UtcToParis](@dt [datetime])

RETURNS [datetime] WITH EXECUTE AS CALLER

AS

EXTERNAL NAME TimeConvertor.UserDefinedFunctions.UtcToParis

GO

Capture

2.5 ทำการทดลองเรียกใช้ฟังชั่นด้วยคำสั่งดังต่อไปนี้…

DECLARE @ParisTime AS DATETIME

DECLARE @UTCTime AS DATETIME

 

— Paris time with DST (GMT+2)

SET @ParisTime = ‘2012-04-15 10:00.000′

SELECT @ParisTime AS ParisTimeWithDayLightSaving

 

— Convert Paris time with DST (GMT+2) to UTC (GMT+0)

SET @UTCTime = dbo.ParisToUtc(@ParisTime)

SELECT @UTCTime AS UTCTime

 

—  Convert UTC (GMT+0) to Paris time with DST (GMT+2) again

SET @ParisTime = dbo.UtcToParis(@UTCTime)

SELECT @ParisTime AS ParisTimeWithDayLightSaving

Capture

 

 

จากตัวอย่างข้างต้นก็จะเห็นแล้วน่ะครับว่าเราสามารถทำการ แปลงเวลาที่เกี่ยวข้องกับ Daylight Saving ได้เรียบร้อย โรงเรียน CLR Code ค้าบบ : D

 

ขอบคุณที่เข้ามาอ่านบทความนี้น่ะคร้าบ หวังว่าบทความนี้จะมีประโยชน์กับผู้อ่านทุกท่านน่ะครับ ^ ^

 

นายป้องกัน