How to Prevent ASP.NET Website From Multiple Database Request

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

CREATE PROCEDURE GetProductDetails

@ProductId bigint,

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)

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();
}
}

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

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 !

Author
Recent Posts

Alexia Pamelov

My name is Alexia Pamelov and I have spent the past 3 years building money making blogs review. Since then I have launched lots of successful nice blogs and after selling my survivalist blog I decided to teach other people how to do the same.

Latest posts by Alexia Pamelov (see all)