استفاده از Stored Procedure در LINQ to SQL

سلام با یکی دیگر از آموزش های LINQ to SQL در خدمت شما هستیم. در این جلسه قصد داریم به بررسی کاربرد Stored Procedure ها در مبحث LINQ بپردازیم.

تذکر: برای شروع این بخش از آموزش لازم است با مبحث Procedure ها در SQL Server آشنایی کافی داشته باشید.

قبل از شروع کد نویسی ابتدا باید یاد بگیریم که چگونه Procedure را به مدل اضافه کنیم برای این کار باید مراحل زیر را انجام دهیم:

1- ابتدا باید مدل اصلی را ایجاد کنید و جداول مورد نیاز را به مدل اضافه کنید که این آموزش را در این بخش در قسمت ایجاد پروژه و ساخت مدل بررسی کردیم.

2- Procedure مورد نظر را بنویسید و ذخیره کنید.

3-برای اضافه کردن Procedure به مدل باید آن را Drag و در جای مناسب در قسمت سفید رنگ مدل Drop کنید. (به شکل زیر توجه کنید)

درگ کردن Procedure

توجه : اگر از نرم افزار SQL Server برای نوشتن Procedure استفاده کرده اید و در صورتی که Procedure شما در قسمت Stored Procedure وجود نداشت بر روی آن راست کلید کرده و Refresh کنید.

با انجام مراحل بالا Procedure شما به مدل اضافه و قابل استفاده خواهد بود.

ساخت جدول

برای این که مطالب آموزش برای تمامی دوستان قابل درک باشد آموزش را با استفاده از یک جدول ساده شروع می کنیم، پس جدول زیر را ایجاد کنید و با ساخت مدل، آن را به پروژه اضافه کنید.

توجه داشته باشید فیلد ID را حتما به صورت Identity در نظر بگیرید و برای انجام مراحل بعدی، جدول را با اطلاعات کافی پر کنید.

نام جدول را Person و نام مدل آن را PersonModel انتخاب کنید.

حالا نوبت به استفاده از Procedure در محیط کد نویسی است اما قبل از آن لازم است بدانید که در این قسمت ما چهار رخداد مهم را بررسی خواهیم کرد:

Procedure هایی که نه پارامتر ورودی و نه پارامتر خروجی دارند
Procedure های که فقط پارامتر ورودی دارند
Procedure هایی که فقط پارامتر خروجی دارند
Procedure هایی که هم پارامتر خروجی و هم پارامتر ورودی دارند

که در ادامه با هر یک آشنا خواهیم شد.

حالت اول: بدون پارامتر

یکی از ساده ترین Procedure هایی که برای هر جدولی می توان نوشت عمل Select است که هیچ گونه پارامتری (ورودی و خروجی) ندارد.

Procedure زیر را نوشته و ذخیره کنید و به روشی که در بالا آموزش داده شد، آن را به مدل اضافه کنید.

CREATE PROCEDURE SelectAllPerson
as
SELECT * FROM Person

حالا برای استفاده از آن مانند کد زیر عمل می کنیم :

private void Form1_Load(object sender, EventArgs e)
        {
            PersonModelDataContext context = new PersonModelDataContext();
            var res =  context.SelectAllPerson();
            dataGridView1.DataSource = res;
        }

برای راحتی کار کد را در داخل Form1_Load نوشتیم تا به محض اجرا شدن برنامه نتیجه را مشاهده کنیم. در خط 3 یک شئ از مدل ایجاد کردیم تا با استفاده از آن به Procedure دست پیدا کنیم. در خط 4 توسط شی ساخته شده از مدل، Procedure را فراخوانی و نتیجه را در متغیر res ذخیره کرده ایم. در خط 5 هم توسط یک dataGridView اطلاعات به کاربر نمایش داده می شوند. برنامه را اجرا و خروجی را مشاهده کنید.

نکته: برای استفاده از Procedure ها لازم است با استفاده از شئ اصلی ساخته شده از مدل آن ها را فراخوانی کنیم.

مهم ترین موضوعی که احتمالا متوجه آن شده اید، روش استفاده از Procedure است که دقیقا مانند یک متد رفتار می کند و استفاده از آن بسیار ساده است.

حالت دوم: دارای پارامتر ورودی

زمانی را در نظر بگیرید که Procedure ما دارای پارامتر ورودی است مثلا حالتی که Procedure یک پارامتر ورودی از نوع int دارد که به وسیله ی آن رکورد مورد نظر را حذف می کنیم. اجازه دهید این مورد را در قالب مثال بررسی کنیم.

ابتدا Procedure زیر را نوشته و ذخیره کنید و مانند قبل به مدل اضافه کنید:

CREATE PROCEDURE DeleteItem
@id int
as
DELETE FROM Person WHERE ID=@id

همان طور که مشخص است این Procedure یک پارامتر ورودی از نوع int دارد.

پیاده سازی آن دقیقا مانند قبل است فقط در این حالت متد DeleteItem یک پارامتر ورودی از نوع int دارد که بر اساس آن یک رکورد از جدول را حدف می کند.

private void Form1_Load(object sender, EventArgs e)
        {
            PersonModelDataContext context = new PersonModelDataContext();
            var res = context.DeleteItem(12);
            dataGridView1.DataSource = res;
        }

همان طور که مشاهده می کنید عدد 12 را به عنوان پارامتر ورودی در نظر گرفته ایم که به محض اجرای برنامه، رکورد مورد نظر حذف خواهد شد. پارامتر ورودی را می توان توسط یک TextBox یا کلیک بر روی رکورد در dataGridView یا هر روش دیگری از کاربر دریافت کرد و در متد DeleteItem از آن استفاده کرد.

حالت سوم: دارای پارامتر خروجی

یکی از ساده ترین مثال هایی که برای این قسمت می توان بررسی کرد استفاده از Procedure زیر است:

CREATE PROCEDURE GetRowCount
@i int out
as
SELECT @i=COUNT(*) FROM Person

می بینید که این Procedure دارای مقدار بازگشتی است که آن مقدار هم برابر با تعداد سطر های جدول شما است. روش به کارگیری آن بسیار ساده بوده و مطابق کد زیر است.

        private void Form1_Load(object sender, EventArgs e)
        {
            PersonModelDataContext context = new PersonModelDataContext();
            context.GetRowCount(ref count);
            MessageBox.Show(count.ToString());
        }

Procedure هایی که دارای مقدار بازگشتی هستند نوع پارامتر ورودی آن ها از نوع Reference Types است که در خط 4 از آن استفاده کردیم. لازم است که متغیر count در بالای برنامه به صورت int? count (خارج از بلوک های Form1_Load) تعریف شود. در آخر هم عدد گرفته شده توسط یک MessageBox به کاربر نمایش داده می شود.

تذکر بسیار مهم: در سی شارپ یک value type نمی تواند مقدار Null را در خود نگه دارد. گاهی اوقات نیاز است یک متغیر علاوه بر اینکه مقادیر واقعی در محدوده ی نوع خود را نگه دارد، بتواند مقدار Null را هم در خود ذخیره کند. در این مواقع از نوع های Nullable در سی شارپ استفاده می کنیم در این جا نیز ممکن است مقدار پارامتر بازگشتی از Procedure نیز Null باشد به همین دلیل لازم است کلیه ی متغیرهای عددی به صورت Nullable تعریف شوند مانند مورد بالا که متغیر count به صورت Nullable تعریف شده است.

حالت چهارم: دارای پارامتر ورودی و خروجی

ابتدا اجازه دهید حالتی را بررسی کنیم که Procedure ما یک پارامتر ورودی از نوع int و یک پارامتر خروجی از نوع string دارد پس Procedure زیر را به مدل اضافه کنید:

CREATE PROCEDURE GetItemByID
@id int
as
DECLARE @n nvarchar(50)
SELECT @n=Name FROM Person WHERE ID=@id
return @n

این Procedure یک int به عنوان ID دریافت می کند و نام متناظر با آن رکورد را به عنوان خروجی باز می گرداند.

روش استفاده از آن به صورت زیر است:

private void Form1_Load(object sender, EventArgs e)
        {
            PersonModelDataContext context = new PersonModelDataContext();
            context.GetNameByID(3,ref name);
            MessageBox.Show("Name= "+name);
        }

همان طور که در خط 4 مشاهده می کنید پارامتر اول از نوع int است که ID رکورد مورد نظر در جدول را مشخص می کند و پارمتر دوم از نوع string بوده که لازم است در بالای برنامه به صورت string name = string.Empty تعریف شود. به نحوه ی استفاده از کلمه ی ref در متد توجه کنید، در تمامی پارامتر های خروجی که در نوشتن Procedure به صورت out مشخص می شوند، در هنگام فراخوانی متد (Procedure) و وارد کردن پارامتر خروجی باید از کلمه ی ref استفاده شود.

در این مثال هدف من گرفتن نام یک رکورد ثبت شده در جدول با استفاده از ID بود. خروجی مورد نظر که در این مثال از نوع string است در متغیر name قرار می گیرد و به روش های مختلف قابل نمایش خواهد بود.

توجه: در مواردی که Procedure دارای پارامتر های ورودی و خروجی بوده، لازم است در زمان فراخوانی متد (Procedure) نوع پارامتر های ارسالی با نوع پارامتر های در نظر گرفته شده در زمان نوشتن Procedure یکسان باشد مثلا اگر نوع پارامتر خروجی در Procedure از نوع nvarchar بود لازم است از نوع داده ی string برای گرفتن آن استفاده کنیم. (مانند مثال بالا)

این قسمت از آموزش هم به پایان رسید در این بخش سعی شد با استفاده از یک جدول و Procedure های ساده آموزش را به پایان برسانیم. سعی کنید مثال های فراوانی با Procedure هایی که خودتان نوشته اید حل کنید.

موفق باشید.