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

بانك هاي اطلاعاتي داراي نقشي اساسي در اكثر برنامه هاي كامپيوتري بوده و برنامه هاي وب نيز از اين قاعده مستثني نمي باشند . اكثر برنامه هاي وب نوشته شده با استفاده از 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 يك نمونه شي را برمي گرداند . با استفاده از متد فوق كد نوشته شده در مثال قبل به صورت زير خواهد بود :

در كد فوق مي بايست بررسي لازم در مواردي خاصي كه 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 ركورد جديد اضافه شده را به عنوان يك مقدار عددي برمي گرداند :

در كد فوق 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 استفاده گردد .
منبع: http://www.4guysfromrolla.com