بازيابی Scalar Data با استفاده از Stored Procedure
اين مطلب از طريق سايت شرکت سخاروش در اختيار شما گذاشته شده است .

برگرداندن داده عددی با استفاده از  stored procedure

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

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

برای بازيابی داده 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  استفاده گردد .

منبع :   http://www.4guysfromrolla.com


استفاده از اين مطلب  با ذکر منبع و اهداف غيرانتفاعی بلامانع است .
http://www.srco.
ir