بازیابی Scalar Data با استفاده از Stored Procedure

[Aida]

بانک های اطلاعاتی دارای نقشی اساسی در اکثر برنامه های کامپیوتری بوده و برنامه های وب نیز از این قاعده مستثنی نمی باشند . اکثر برنامه های وب نوشته شده با استفاده از ASP.NET در سطوح متفاوتی از بانک های اطلاعاتی استفاده می نمایند . یکی از متداولترین بانک های اطلاعاتی که در برنامه های وب ASP.NET از آن در ابعاد بسیار گسترده ای استفاده می گردد ، SQL Server متعلق به شرکت مایکروسافت است . در بانک های اطلاعاتی رابطه ای نظیر SQL Server ، دستورات ( نظیر SELECT ,INSERT ,UPDATE و DELETE ) با استفاده از یک گرامر مبتنی بر SQL نوشته می گردند که عموما" در این رابطه از دو روش استفاده می گردد :

*

ad-hoc queries : یکی از روش های صدور دستورات SQL ، استفاده از query مورد نظر در متن برنامه است . به دستورات فوق ، ad-hoc queries گفته می شود . مهمترین چالش اینگونه query ها ، نگهداری و پشتیبانی آنان است . در صورت نیاز به تغییر query ، می بایست رشته موجود در برنامه را ویرایش ،‌ ترجمه و مجددا" آن را بکارگرفت .
*

Stored procedure ، توابع از قبل ترجمه شده ای می باشند که بر روی سرویس دهنده بانک اطلاعاتی مستقر و امکان فراخوانی آنان از طریق نام مربوطه فراهم می گردد . عملکرد stored procedures مشابه ماژولار نمودن قابلیت های برنامه نویسی در متدها می باشد . stored procedures علاوه بر این که دارای قابلیت های بمراتب بیشتری از بعد ویرایش نسبت به ad-hoc queries می باشند ، امکان استفاده از آنان در سایر برنامه ها نیز وجود خواهد داشت. مثلا" ممکن است در یک برنامه ASP.NET و سرویس وب با استفاده از چندین query از داده های موجود در یک بانک اطلاعاتی مشابه استفاده گردد . در صورتی که هر query مستقیما" در متن برنامه استفاده شده باشد ، برای اعمال تغییرات می بایست بر روی دو تقطه متمرکز گردید : محل استفاده از query در برنامه ASP.NET و مکانی که از query در سرویس وب استفاده شده است . پس از اعمال تغییرات لازم در هر یک از مکان های اشاره شده ، می بایست آنان را مجددا" ترجمه و بکار گرفت . در صورت استفاده از stored procedure ، صرفا" اعمال تغییرات مورد نظر در یک نقطه انجام خواهد شد .
stored procedure معمولا" resultset برمی گرداند ( نظیر نتایج حاصل از اجرای یک SELECT query ) . در برخی موارد لازم است که داده scalar از یک stored procedure برگردانده گردد . مثلا" ممکن است دارای یک stored procedure باشیم که صرفا" مانده حساب یک مشتری خاص را برگرداند و یا در بانک اطلاعاتی دانشجویان که نمرات یک درس خاص ذخیره شده است ، قصد بازیابی معدل کلاس را داشته باشیم . در مواردی که از یک stored procedure برای درج یک رکورد جدید درون یک جدول که دارای یک فیلد اطلاعاتی با ویژگی IDENTITY است، استفاده شده باشد ، می توان ID رکورد جدید را برگرداند .

برای بازیابی داده scalar از طریق stored procedure از روش های متعددی استفاده می گردد . در ادامه با برخی روش های موجود در این رابطه و نحوه استفاده از نتایج مربوطه در برنامه های ASP.NET آشنا خواهیم شد .

برگرداندن داده با استفاده از یک عبارت SELECT
معمولا" با استفاده از یک عبارت SELECT داده ها از یک stored procedure که عموما" به صورت یک resultset می باشد ، برگردانده می شوند . resultset ، شامل چندین فیلد و رکورد می باشد. مثلا" stored procedure زیر مشخصات کالاهای موجود در یک انبار را برمی گرداند :

CREATE PROCEDURE store_GetInventory AS

SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory

در صورت تمایل می توان یک مقدار scalar را با استفاده از یک stored procedure برگرداند . مثلا" فرض کنید که قصد بازیابی میانگین قیمت محصولات موجود در انبار را با استفاده از یک stored procedure داشته باشیم . بدین منظور می توان از یک عبارت SELECT به منظور برگرداندن مقدار داده مورد نظر استفاده نمود :

CREATE PROCEDURE store_GetAverageInventoryPrice AS

SELECT AVG(Price) AS AveragePrice
FROM store_Inventory

در صورتی که با استفاده از stored procedure یک رکورد جدید را در جدولی که دارای یک فیلد با ویژگی IDENTITY است اضافه نمائیم ، می توان ID رکورد جدید را با استفاده از تابع SCOPE_IDENTITY برگرداند :

CREATE PROCEDURE store_AddNewInventoryItem
(
@ProductName nvarchar(50),
@Price money
) AS

-- INSERT the new record
INSERT INTO store_Inventory(ProductName, Price)
VALUES(@ProductName, @Price)

-- Now return the InventoryID of the newly inserted record
SELECT SCOPE_IDENTITY()


در زمان برگردان داده scalar با استفاده از یک عبارت SELECT ،‌ می توان با استفاده از روشی که یک resultset بازیابی می گردد ،‌داده های مورد نظر را بازیابی نمود . در چنین مواردی می توان از یک DataSet , DataTable و یا یک DataReader استفاده نمود ( نتایج برگردانده شده صرفا" شامل یک سطر و یک فیلد می باشد).
کد زیر با فراخوانی store_GetAverageInventoryPrice ، مقدار داده scalar را برمی گرداند :

Dim myConnection as New SqlConnection(connection string)
Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Read in the first record and grab the first column
Dim avgPrice as Decimal
If reader.Read() Then
avgPrice = Convert.ToDouble(reader("AveragePrice"))
End If


در مقابل استفاده از متد ExecuteReader می توان از متد ExecuteScalar استفاده نمود . متد ExecuteScalar یک نمونه شی را برمی گرداند . با استفاده از متد فوق کد نوشته شده در مثال قبل به صورت زیر خواهد بود :

Dim myConnection as New SqlConnection(connection string)
Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

Dim avgPriceObject as Decimal = Convert.ToDecimal(myCommand.ExecuteScalar())


در کد فوق می بایست بررسی لازم در مواردی خاصی که result مقدار NULL را می گرداند ، انجام شود .

استفاده از پارامترهای خروجی
یکی دیگر از روش های برگرداندن داده scalar از یک Stored procedure ( به جزء یک resultSet استاندارد ) ،‌ استفاده از یک و یا چندین پارامتر خروجی است . پارامتر خروجی ، پارامتری است که به Stored procedure ارسال و مقدار آن در Stored procedure مقداردهی می گردد . پارامتر فوق در ادامه توسط برنامه ای که Stored procedure را صدا می زند ،‌ خوانده شده و از آن استفاده می گردد .
برای استفاده از یک پارامتر خروجی ، می بایست نوع و ماموریت پارامتر مورد نظر را با استفاده از کلیدواژه OUTPUT مشخص نمود . کد زیر یک Stored procedure را نشان می دهد که کالاهای موجود در انبار با استفاده از یک عبارت SELECT برگردانده شده و در ادامه با استفاده از یک پارامتر خروجی ، قیمت میانگین کالاهای موجود در انبار محاسبه و برگردانده می شود .

CREATE PROCEDURE store_GetInventoryWithAveragePrice
(
@AveragePrice money OUTPUT
)
AS

SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory)

SELECT InventoryID, ProductName, Price, UnitsOnStock
FROM store_Inventory


برای دستیابی به مقدار یک پارامتر خروجی از طریق یک برنامه ASP.NET ، می بایست یک شی پارامتر را که خصلت Direction آن مقدار OutPut را دارد تعریف نمود . پس از فراخوانی Stored procedure ، مقدار پارامتر خروجی با استفاده از خصلت Value قابل دسترس خواهد بود :

Dim myConnection as New SqlConnection(connection string)
Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

'Create a SqlParameter object to hold the output parameter value
Dim avgPriceParam as New SqlParameter("@AveragePrice", SqlDbType.Money)

'IMPORTANT - must set Direction as Output
avgPriceParam.Direction = ParameterDirection.Output

'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(avgPriceParam)

'Call the sproc...
Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Now you can grab the output parameter's value...
Dim avgPrice as Decimal = Convert.ToDecimal(avgPriceParam.Value)


در کد فوق می بایست بررسی لازم در مواردی خاصی که result مقدار NULL را می گرداند ، انجام شود .
در زمان استفاده از Stored procedure صرفا" محدود به استفاده از یک پارامتر خروجی نخواهیم بود و می توان در چنین مواردی از چندین پارامتر ورودی و یا خروجی استفاده نمود .

استفاده از یک مقدار برگشتی
روش نهائی به منظور برگرداندن داده scalar از یک Stored procedure ، استفاده از مقادیر برگشتی است . پس از اتمام اجرای یک Stored procedure ، همواره یک مقدار برگردانده می شود که به صورت پیش فرض صفر است . در چنین مواردی می توان از عبارت RETURN برای برگرداندن یک مقدار عددی صحیح استفاده نمود . کد زیر ID رکورد جدید اضافه شده را به عنوان یک مقدار عددی برمی گرداند :

CREATE PROCEDURE store_AddNewInventoryItem
(
@ProductName nvarchar(50),
@Price money
) AS

-- INSERT the new record
INSERT INTO store_Inventory(ProductName, Price)
VALUES(@ProductName, @Price)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()

در کد فوق SCOPE_IDENTITY توسط RETURN برگردانده شده است . این در حالی است که در مثال قبل از یک عبارت select استفاده شده بود.
برای بازیابی مقدار برگردانده شده از یک Stored procedure ، از روش های مشابه پارامترهای خروجی استفاده می گردد . تنها تفاوت موجود در این رابطه ،‌استفاده از مقدار Direction مربوط به ReturnValue است:

Dim myConnection as New SqlConnection(connection string)
Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

'Create a SqlParameter object to hold the output parameter value
Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

'IMPORTANT - must set Direction as ReturnValue
retValParam.Direction = ParameterDirection.ReturnValue

'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(retValParam)

'Call the sproc...
Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Now you can grab the output parameter's value...
Dim retValParam as Integer = Convert.ToInt32(retValParam.Value)

جمع بندی
در این مقاله با سه روش متفاوت به منظور برگرداندن داده scalar از طریق یک Stored procedure آشنا شدیم: عبارت SELECT ، پارامترهای خروجی و مقدار برگردانده شده .
در مواردی که یک داده scalar با استفاده از یک عبارت SELECT برگردانده می شود ،‌ نتایج مورد نطر با استفاده از متد ExecuteScalar در دسترس و قابل استفاده خواهند بود . برای پارامترهای خروجی و مقادیر برگردانده شده ،‌ می بایست یک شی پارامتر با مقدار مناسب خصلت Direction ایجاد و پس از فراخوانی Stored procedure به منظور دستیابی به نتایج برگردانده شده از مقدار خصلت Value استفاده گردد .