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