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



 ساير امکانات  

ساير




 

  

 

SAKHA RAVESH CO.

 ا مروز ◄

  پنجشنبه  4  خرداد  1396  2017  May  25   Thursday ToDay ►
صفحه اصلی |  مقالات |  نکته ها |  دايره المعارف |  خودآموزها | تازه ها |  خود آزمون ها    
  نسخه قابل چاپ  

    5 4 3 2 1 

 عنوان

 نويسنده

  مشاهده

 تعداد آراء

 امتياز

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

 مديريت وب

11592

31

4.5

با توجه به جایگاه داده در عصر حاضر و  لزوم نگاه جامع به این مقوله مهم ، بر آن شدیم تا محوریت فعالیت های خود را بر  روی این موضوع متمرکز نمائیم . از این رو گروه فابک با شعار فناوری اطلاعات برای کسب وکار شکل گرفت و  خدمات خود  را از طریق  سایت www.fabak.ir  به مخاطبان محترم عرضه می نماید

 

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

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

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



 جستجو
جستجو

مقالات                 
دايره المعارف       
دوره های آموزشی


 

 
مقالات
مشاهده گروه ها



              

 

 تهيه شده در شرکت سخا روش -  1382