PDA

View Full Version : مقاله: چگونگی ساخت CLR Stored Procedure در Csharp و چگونگی استفاده در Sqlserver



r00tkit
یک شنبه 25 بهمن 1388, 15:23 عصر
امیدوارم فقط تکراری نباشه!!!!:خجالت:

همه ی ما می دونیم ا ز2005SQLserver به بعد sqlserver توانایی کار با CLR رو داره

این به این معنی هست که ما می تونیم کد هایی در محیط manage بنویسیم ودر sqlserver اجرا کنیم

اولین سوالی که به ذهن همه ی ما خطور می کنه اینه که اصلا چرا نیازی به clr در Sql هست؟؟

بعد اینکه در اینترنت گشتم و مقاله هایی رو خوندم به این نتایج رسیدم:

با استفاده از این ما می تونیم کد های پیچیده رو به راحتی بنویسیم مثلا استفاده از توابع ریاضی پیچیده و توایع باز گشتی

دلیل دیگه این که می تونیم از library های .net استفاده کنیم واز اونجایی که CLR stored procedure کامپایل میشن نسبت به T-SQLکه interpreted هستش سریع تر اجرا می شه ودر جا هایی کد چندین بار اجرا میشه مفید تر خواهد بود

واز همه مهم تر(به نظر من) دیگه از دست syntax سخت راحت می شیم من خیلی از Developer ها رو دیدم که در حد خفن با زبان مورد علاقشون کد می زنن اما در کار با T-SQL خلاقیتشون گم میشه.

من در این مقاله سعی می کنم با یه مثال ساده روش ساختن CLR stored procedure وچگونگی استفادشو توضیح بدم.


ویژال استادیو رو باز کنید روی بخش new project کلیک کنید سپس SQL Server project را از بخش C#‎‎‎‎ انتخواب کنید

و اسم ان رو به LearnCLRIntegrationتغییر بدید. روی پروژتون در بخش solution explorerراست کلیک کرده وسپس Add New Item را انتخواب کنید و یه stored procedure به پروژتون اضافه کنید.
این کار باعث ایجاد یه کلاس و کدهایی داخل ان می شه (code snippiest)






using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;


public class StoredProcedures

{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void StoredProcedure1()
{
// Connect through the context connection.
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();

SqlCommand command = new SqlCommand(
"SELECT * FROM t" , connection);

// Execute the command and send the results directly to the client.
SqlContext.Pipe.ExecuteAndSend(command);

}
}

}




شما می تونید علاوه بر CLR stored procedure این ها رو هم داشته باشین:SqlFunction, Sqltrigger, SqlMethod, SqlUserDefinedType ...

فکر کنم خودتون فهمیدید این کلاس چیه اما باز!!! این یه کلاسیه که مثل کلاس های دیگه می تونیم توش method های مختلف تعریف کنیم این attributes[Microsoft.SqlServer.Server.SqlProcedure] هست که نوع method رو مشخص می کنه مثلا برای ایجاد trigger از این attribute استفاده می کنیم [Microsoft.SqlServer.Server.SqlTrigger]

ما می تونیم از CLR stored procedure برایdeleteو update ... استفاده کنیم


فکر کنم نیاز به توضیح نداره [Microsoft.SqlServer.Server.SqlProcedure()]


باید از context connection = true به عنوان connection string استفاده کنید این به sqlserver می فهمونه که از همون connection که کد توش اجرا میشه استفاده کنه یعنی وابسته به خود database میشه!!!!!!



The context connection lets you execute Transact-SQL statements in the same context that your code was invoked in the first place. In order to obtain the context connection, you must use the "context connection" connection string keyword



SqlContext.Pipe.ExecuteAndSend(command);

این کد باعث اجرای Command وفرستادن نتیجه به سرویس گیرنده میشه (client)
برنامه رو کامپایل کنید و dll تواید شده رو به یه مسر راحت مثل D:\ کپی کنید (البته این کار دابخواهیه)
توانایی اجرای کدهای CLR به صورت پیش فرض در sqlserveroff هستش برای on کردنش کد زیر رو برای database خودتون اجرا کنید






sp_configure 'clr enabled',1
GO
Reconfigure
البته شما می تونید از SQL Server Surface Area Configurationنیز استفاد کنید
برای ایجاد assembly این کد هارو داخل sqlserver اجرا کنید






CREATE ASSEMBLY LearnCLRIntegration -- Assembly name, you can write which you wantFROM


'd:\your dll name.dll' -- write appropriate path and DLL name



WITH PERMISSION_SET = SAFE


GO




توضیح در کنار کد ها امده
البته با خود Management Studio هم میشه این کار رو کرد در داخل database مسیر زیر رو برید
Programmability=>assemblies=> add managed assembly


برای اجرا باید از assembly یه stored procedure بسازید با کد زیر :
کدش:





CREATE PROC clrsp


AS
EXTERNAL NAME ASSEMBLYNAME.StoredProcedures. StoredProcedure1-- ASSEMBLYNAME. Classname. Function name




ASSEMBLYNAME اسم اسمبلی که قبلا ساختیم
StoredProceduresاسم کلاس داخل اسمبلی


StoredProcedure1


هم اسم تابع ای هست که می خواد کار stored procedure رو انجام بده

حالا همه چیر ردیفِ




Exec clrsp
go

با این کد stored procedure اجرا میشه


به طور خلاصه برای ایجاد CLR Stored Procedure 3 مرحله داریم
1)ایجاد یه تابع static در داخل کلاسی به عنوان Stored Procedure
Register (2کردن اسمبلی با روش های گفته شده
3) ایجاد Stored Procedure از تابع داخل اسمبلی Registerشده و اجرا ان
برای اطلاعات بیشتر به MSDN مراجعه فر مایید

منبع:: :تشویق:wink !!Web

sia_2007
سه شنبه 27 بهمن 1388, 11:43 صبح
با سلام به همگی دوستان
اولین مسئله شناخت CLR در SQL Server است.
ببینید ؛ ما یک زبان داریم به نام T-SQL
این زبان فارغ از همه ی مسائل اعم از نحوه ترجمه و یا کندی یا تندی آن یک مسئله بسیار بسیار مهم دارد و آن هم این است که قادر به انجام هر کاری نیست.
برای مثال نمیتوان با T-SQL به انجام اموری مانند کار با شبکه یا اعمال متنوع روی فایلها پرداخت.
تا قبل از SQL Server 2005 ؛ راه حل زبانهای VBScript و یا C بود که هنوز هم هست.
و یا Shell Programming که کما بیش آشنا هستید.
اما در نسخه 2005 امکان استفاده از کدهای Managed به وجود آمد با چند مزیت مهم :
اولین مزیت امنیت بود.
ما در CLR که ربط 100 درصدی به CLR در Net Framework. ندارد؛ 3 ذسته اسمبلی داریم :
Safe ؛ UnSafe و میانه
اسمبلی های Safe کاملا معادل T-SQL هستند؛ و هیچ دستوری رو بالاتر از اون ندارند.
اسمبلی های میانه دارای امکاناتی مانند System.IO و ... هستند.
در مورد UnSafe ها هم بعدا صحبت میکنم.
بر این دسته بندی مکانیزم های امنیتی پیاده سازی میشود.
بحث دوم کارآیی Engine است.
در واقع اولویت بر Engine قرار داده شده است.
در معماری این سیستم ما دو App Domain داریم ؛ یکی Engine و دیگری CLR تا به هیچ وجه به سرویس رسانی Engine خللی وارد نشود ؛ اعم از کدهای خطرناک یا حلقه های بی پایان در CLR و ...
در مورد اسمبلی های Safe که معادل T-SQL هستند؛ تنها دستورات حاوی 2Cursor تا 7 برابر سریع تر از معادل T-SQL خود هستند و باقی از کندی بیشتر نسبت به T-SQL رنج میبرند.
چرا ؟
چون اطلاعات از Engine App Domain به CLR App Domain باید منتقل شوند.
البته آن دوستی که نحوه تفسیر T-SQL رو زیر سوال برده اند رو هم به خدا واگذار میکنم :چشمک:
البته اگر تمایل به کدنویسی آسان و روان و تسلط بر منطق برنامه دارید؛ بهتر است نیم نگاهی به Pipe بیاندازید؛ که دستور شما رو به Engine برده و در آن جا با سرعت بالایی اجرا میکند.

sqlContext.Pipe.ExecuteAndSend(myCmd); // Cmd is SQL Command

دلیل دیگه استفاده از CLR علاوه بر Cursor Programming ؛ بحث UDT ها هستش ؛ مثلا یک Type برای تاریخ شمسی با اعتبار سنجی و متدهای مورد نیاز !!! :تشویق:


و هم چنین گذاشت پا فراتر از Min و Max و Sum و ... و نوشتن Agg Fun های خودمان ؛ که البته من تابع خاصی به نظرم نرسید.
خب این از مفاهیم

ببینید دوستان آن Connection String و دیتابیسی که در ابتدای پروژه انتخاب میکنید؛ محل Deploy شدن اسمبلی خروجی پروژه است و نه هیچ چیز دیگری !
Context Connection به این معنی است که مثلا برای sp ؛ با مجوز و Connection String کسی که دارد sp را صدا میزند اجرا شو؛ که اولین و اصلی ترین مولفه برای Safe به شمار رفتن اسمبلی شماست.
این عین متن انگلیسی است که دوست عزیزمون هم نوشتن و من برای قضاوت اون رو این جا کپی میکنم :


The context connection lets you execute Transact-SQL statements in the same context that your code was invoked in the first place


با زدن دکمه F5 تمام مسائلی رو که دوست عزیز برای Deploy کردم اسمبلی بیان کرده اند خودکار اتفاق میافتد؛ و فقط نیاز به فعال سازی CLR است.

مفهوم Pipe رو هم که توضیح دادم.
با این حال دوستمون مطالبی رو در مورد Pipe گفتند که واقعا جای تعجب داره !
اگر این دستور کاری را که شما گفته اید انجام میدهد؛ پس روش معمولی اجرای Command چیست ؟
دستورات T-SQL که ما آنرا در Command.CommandText مینویسیم؛ همیشه در Engine اجرا میشه؛ اما با دو روش :
یک روش معمولی : که دستور از کلاینت به CLR ( چون اون Command Text شما اغلب اوقات از کلاینت پارامتر میگیرد ) و از CLR به Engine و پس از اجرا نتیجه به CLR و سپس از CLR به کلاینت میرود.
اما با استفاده از Pipe ؛ دستور از کلاینت به CLR ؛ و از CLR به Engine و از Engine به کلاینت ارسال میشود.
بدیهی است این روش بسیار سریع تر است؛ هر چند مقداری Exception Handling آن سخت تر است.
ببینید من نمیخواهم تند صحبت کنم و زیاد عادت به این کار ندارم؛ اما این آموزش شما باعث میشه فراگیرنده اون رو امتحان کنه و سپس ببینه CLR کنده؛ و بگه گور بابای CLR
در حالی که CLR علاوه بر کارهایی که با T-SQL امکان پذیر نیست؛ حتی در مبحث T-SQL نیز در Cursor و UDT و Agg Fun ها نیز سرآمد است.
و در نهایت؛
حتما کد تولید شده تون رو با معادل T-SQL اون در صورت امکان مقایسه کنید و سرعت اون رو بسنجید.
حتما در این زمینه کتاب بخوانید چون واقعا مبحث جالبیه و مخچه آدم رو به کار میگیره !!! :چشمک:
اگر دوستان کمکی خواستند من در خدمتم
موفق باشید