بازيابی 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