Click here to Skip to main content
15,895,084 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I do not want to open and close the database connection multiple times on my page. I'm making several calls to the database and running multiple stored procedures and it is slowing down my page load time.

As you can see I'm not showing every Method. There has to be a more efficient way of doing this?

What I have tried:

C#
protected void Page_Load(object sender, EventArgs e)
    {    
        if (!this.IsPostBack)
        {
            this.Method_1();
        }
    }
    private void Method_1()
    {
        string connString = ConfigurationManager.ConnectionStrings["***"].ConnectionString;
        using (conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand("GetStoredProc_1", conn))
            {
                using (SqlDataReader reader = comm.ExecuteReader())
                {
                    if (reader.Read())
                    {
                       Something here is true



                        if(Something here is true) 
                        {                      
                            reader.Close();
                            LoadSchedule();
                            LoadTeamRecord();
                        }
                        else
                        {
                            Response.Redirect("Some Page.aspx");
                        }
                    }
                }
            }
        }
    }
    private void LoadSchedule()
    {          
        string connString = ConfigurationManager.ConnectionStrings["***"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand("GetStoredProc_2", conn))
            {
                using (SqlDataReader reader = comm.ExecuteReader())
                {
                    myRepeater.DataSource = reader;
                    myRepeater.DataBind();

......
Posted
Updated 18-Aug-19 6:44am
v3
Comments
F-ES Sitecore 19-Aug-19 6:15am    
If your code is slow it is your sql commands\sps that are slow, not the opening of multiple connections. As mentioned in the solutions, ADO implements connection pooling so you're not actually opening and closing anything, simply getting existing connections from the pool and putting them back.

The connection cost should be pretty short, and it is good practice to hold a connection for as short a time as possible.

But ... if you are sure that is what is affecting your load times then create a global SqlConnection object and open it in your Page_)load event handler, and then reuse that in your other methods, remembering to close and Dispose the Connection at the end of the handler.

But make absolutely sure you close and dispose all DataReaders when you are finished with them: you cannot do anything else with a connection while you have and open Reader.

I don't think it will significantly improve your load times though. I''d take a good close look at your Stored Procedures instead.
 
Share this answer
 
Comments
Maciej Los 18-Aug-19 14:54pm    
Agree!
Commish13 19-Aug-19 20:47pm    
Thanks for your replies. Is there a way to find out what is slowing down my load time?
OriginalGriff 20-Aug-19 2:54am    
Depends on which version of VS you have, but start by googling for "VS Profiling" - it can give you a load of info if you use it right (Google will also explain how to do that).
If you don't have built in profiling, then you need to start gathering information. Use the Stopwatch class, and log how long each part of you code is taking. When you have identified the broad area, you can start adding more logging to narrow down to the area you need to concentrate on.

We can't do any of that for you: it needs you code running, complete with your data - and we have no access to either of those!
Commish13 20-Aug-19 8:34am    
Thanks. I will start looking into my stored Procedures and find out whats going on
Richard Deeming 22-Aug-19 9:32am    
"... create a global SqlConnection object ..."

NB: That won't work in ASP.NET - the SqlConnection class is not thread-safe, and having multiple requests trying to use the same global connection will lead to serious problems.

Trust me - having inherited a site that did precisely that some years ago, I know how bad it can get! :)
As SQL Server uses an optimization technique called connection pooling, the cost of reconnecting should not be a concern normally.
See: SQL Server Connection Pooling (ADO.NET) | Microsoft Docs[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900