EasyHostingASP.NET | Best and cheap ASP.NET Hosting. t is not good to execute multiple db request for loading single page. Review your database code to see if you have request paths that go to the database more than once. Each of those round-trips decreases the number of requests per second your application can serve. By returning multiple resultsets in a single database request, you can cut the total time spent communicating with the database.
In order to improve performance you should execute single stored proc and bring multiple resultset in to single db request. In this article i will explain you how to avoid multiple database request and how to bring multiple resultset into single db request.
Consider a scenario of loading a Product Page, which displays
- Product Information and
- Product Review Information
In order to bring 2 database request in single db request, your sql server stored proc should be declared as below.
SQL Server Stored Proc
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE PROCEDURE GetProductDetails @ProductId bigint, AS SET NOCOUNT ON --Product Information Select ProductId, ProductName, ProductImage, Description, Price From Product Where ProductId = @ProductId --Product Review Information Select ReviewerName, ReviewDesc, ReviewDate From ProductReview Where ProductId = @ProductId |
Asp.net, C# Code to bring multiple db request into single db request
Code Inside Data Access Class Library (DAL)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | public DataSet GetProductDetails() { SqlCommand cmdToExecute = new SqlCommand(); cmdToExecute.CommandText = "GetProductDetails"; cmdToExecute.CommandType = CommandType.StoredProcedure; DataSet dsResultSet = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute); try { var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"]; string strConnString = conString.ConnectionString; SqlConnection conn = new SqlConnection(strConnString); cmdToExecute.Connection = conn; cmdToExecute.Parameters.Add(new SqlParameter("@ ProductId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 19, 0, "", DataRowVersion.Proposed, _productId)); //Open Connection conn.Open(); // Assign proper name to multiple table adapter.TableMappings.Add("Table", "ProductInfo"); adapter.TableMappings.Add("Table1", "ProductReviewInfo"); adapter.Fill(dsResultSet); return dsResultSet; } catch (Exception ex) { // some error occured. throw new Exception("DB Request error.", ex); } finally { conn.Close(); cmdToExecute.Dispose(); adapter.Dispose(); } } |
Code Inside Asp.net .aspx.cs page
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | protected void Page_Load(object sender, EventArgs e) { if (Request.QueryString[ProductId] != null) { long ProductId = Convert.ToInt64(Request.QueryString[ProductId].ToString()); DataSet dsData = new DataSet(); //Assuming you have Product class in DAL ProductInfo objProduct = new ProductInfo(); objProduct.ProductId = ProductId; dsData = objProduct.GetProductDetails(); DataTable dtProductInfo = dsData.Tables["ProductInfo"]; DataTable dtProductReviews = dsData.Tables["ProductReviewInfo"]; //Now you have data table containing information //Make necessary assignment to controls ..... ..... ..... ..... ..... } } |
Finish, Happy coding.
Best and Cheap ASP.NET Hosting in India Click Here !
- ASPHostPortal vs Site5 – Best Windows ASP.NET Hosting Comparison - September 29, 2017
- Best , Cheap Umbraco 7.7.1 Hosting Recommendation - September 28, 2017
- ASP.NET Web Forms Connection Resiliency and Command Interception - September 27, 2017