Sql open connection just once for multiple readers

Question

0.00/5 (No votes)

See more:

I created a web site in C# asp.net. I use OOP a lot in my code. Because of this there is a reader in each method. The database is opened multiple times and it works fine but I know there should be a more efficient way I doing this. For this post I only show 1 of my methods just to keep it simple.

What I'm asking? Could someone please show me how to open the database once and use the connection in multiple methods.

Take a look at my LoadSchedule() in my page load. Then in my LoadSchedule method I have to open database for it work.

There has to be a better way of doing this, right?

Thanks in advanced.

What I have tried:

C#

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

public partial class PickAllPool : System.Web.UI.Page
{


    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;
        string connectionString = ConfigurationManager.ConnectionStrings["Name of connection string"].ConnectionString;
        conn = new SqlConnection(connectionString);
        comm = new SqlCommand("GetSchedule_v2", conn);
        comm.CommandType = CommandType.StoredProcedure;
        try
        {
            conn.Open();
            reader = comm.ExecuteReader();
            if (reader.Read())
            {
                DateTime localDate = DateTime.Now.AddHours(2);
                DateTime PoolStatusActive = Convert.ToDateTime(reader["PoolActive"]);
                DateTime PoolStatusDisabled = Convert.ToDateTime(reader["PoolDisabled"]);

                if (localDate > PoolStatusActive && localDate < PoolStatusDisabled)
                {
                    if (!IsPostBack)
                    {
                       
                        reader.Close();
                        LoadSchedule();                       
                    }

                }

                else
                {
                    poolStatus.Visible = false;
                    timerMsg.Visible = true;
                }
            }

        }

        catch (Exception ex)
        {

            System.Windows.Forms.MessageBox.Show("Database error message: " + ex.Message +
                            "<br>" + ex.Source +
                            "<br>" + ex.TargetSite +
                            "<br>" + ex.StackTrace);
        }
        finally
        {
            // comm.Dispose();
            conn.Close();
        }
    }


    protected void LoadSchedule()
    {
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;
        string connectionString = ConfigurationManager.ConnectionStrings["PhillyPickAllConnection"].ConnectionString;
        conn = new SqlConnection(connectionString);
        comm = new SqlCommand("GetSchedule_v2", conn);
        comm.CommandType = CommandType.StoredProcedure;

        try
        {
            conn.Open();
            reader = comm.ExecuteReader();
            myRepeater.DataSource = reader;
            myRepeater.DataBind();
            reader.Read();


            reader.Close();
        }
        catch (Exception ex)
        {
            System.Windows.Forms.MessageBox.Show("Database error message: " + ex.Message +
                            "<br>" + ex.Source +
                            "<br>" + ex.TargetSite +
                            "<br>" + ex.StackTrace);
        }
        finally
        {
            comm.Dispose();
            conn.Close();
        }
    }
}

Posted 7-Feb-19 14:27pm

Commish13

Updated 7-Feb-19 20:06pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2019-02-07T20:05:00

Solution 1

You have bigger problems than you think.
The use of

if (!IsPostBack)

and

protected void Page_Load(object sender, EventArgs e)

says "this is a website" - and that means your code will not work in production.

C# code is executed on the Server, never the Client - only Javascript code is ever executed on the client. So when you use MessageBox it shows a dialog on the Server, not the Client - where the user cannot see it. That means the execution of code on the Server will stop and wait for a use several thousand miles away to click an "OK" button that he can't see, and doesn't even know is there. He will not get any error message; all he gets is a frozen website...

It works in development because the Server and the Client are the same physical machine - so when the server shows a dialog you see it it on the Server and it looks like it works. But it doesn't, not realy - and as soon as you get to production, it fails catastrophically.

And given that this is a website, you should not be even trying to "share" an SqlConnection - you must create it it anew each time you use it because they are scarce resources and if you create one and hold it, it can last far too long - even if the client has turned off his machine and gone home! Given that websites are designed to be browsed but multiple users, this can exhaust the supply of connections to SQL pretty quickly, and then users get "Can't connect to server" messages. Or would, if your code didn't use MessageBoxes to display them ... :sigh:

The "Proper" way to do DB access:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlDataAdapter da = new SqlDataAdapter("SELECT MyColumn1, MyColumn2 FROM myTable WHERE mySearchColumn = @SEARCH", con))
        {
        da.SelectCommand.Parameters.AddWithValue("@SEARCH", myTextBox.Text);
        DataTable dt = new DataTable();
        da.Fill(dt);
        myDataGridView.DataSource = dt;
        }
    }

Or

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT MyColumn1, MyColumn2 FROM myTable WHERE mySearchColumn = @SEARCH", con))
        {
        cmd.Parameters.AddWithValue("@SEARCH", myTextBox.Text);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            while (reader.Read())
                {
                int id = (int) reader["Id"];
                string desc = (string) reader["description"];
                ...
                }
            }
        }
    }

The using block ensures that the objects are Disposed automatically, regardless of what happens.

Posted 7-Feb-19 20:05pm

OriginalGriff

Comments

Commish13 9-Feb-19 10:49am

Thanks for your reply. Just wondering is it good practice to use a try, catch?

OriginalGriff 9-Feb-19 11:32am

Depends on what you are going to do with the catch: if you log it, report it to the user, or similarly handle it, then yes. If you swallow it, pass sit up, or ignore it, then no.

But you can use try...catch with using blocks as well. However you leave the using block, the appropriate Dispose will happen automatically.

Commish13 9-Feb-19 16:58pm

Hi, I'm trying to use your code but running into another issue. Whenever I put my repeater into the if statement it doesn't populate myRepeater. I need the if statement to display the pool at a certain date and time and not display after a certain date and time. Whenever I debug my code it steps through with any problems but it doesn't display my pool on the web page. Just to let you know the date and time are set up good so it will display the pool.

private void LoadSchedule()
{
string connString = ConfigurationManager.ConnectionStrings["
DatabaseConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand comm = new SqlCommand("GetSchedule_v2", conn))
{
using (SqlDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
DateTime localDate = DateTime.Now; ;
DateTime PoolStatusActive = Convert.ToDateTime(reader["PoolActive"]);
DateTime PoolStatusDisabled = Convert.ToDateTime(reader["PoolDisabled"]);

if (localDate > PoolStatusActive && localDate < PoolStatusDisabled)
{
myRepeater.DataSource = reader;
myRepeater.DataBind();
reader.Read();

}
}
}
}
}

Any suggestions what I should do or change?

Thanks

OriginalGriff 10-Feb-19 4:00am

Because you are doing two things wrong:
1) You are using Read twice: once in the "while" statement, and once in the "if". The second will cause it to throw away the record immediately after any that pass the if test.
2) You set the reader as the datasource for your repeater, but that will only be processed once the function exits - by which time you have read all the rows out of the reader and there is nothing to display. Since readers can't be rewound, the repeater is understandably empty. Build a collection of data you do want, and set it as the data source after the loop completes.

Commish13 10-Feb-19 12:02pm

I just want to thank you for your help but is there any way you could give me a code example. I'm trying to follow your comment above but no matter what I try it's not working.

OriginalGriff 10-Feb-19 12:12pm

What exactly did you try? Show us the code and explain what "it's not working" means!

Commish13 10-Feb-19 12:37pm

I kind of don't under stand when you say 'Build a collection of data you do want, and set it as the data source after the loop completes' because I can get each part of my code to work separately but not together.

private void LoadSchedule()
{
string connString = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand comm = new SqlCommand("GetSchedule_v2", conn))
{
using (SqlDataReader reader = comm.ExecuteReader())
{

while (reader.Read())
{
DateTime localDate = DateTime.Now.AddHours(2);
DateTime PoolStatusActive = Convert.ToDateTime(reader["PoolActive"]);
DateTime PoolStatusDisabled = Convert.ToDateTime(reader["PoolDisabled"]);

if (localDate > PoolStatusActive && localDate < PoolStatusDisabled)
{
MessageBox.Show("Good");
}

else
{
MessageBox.Show("Bad");
}
}
}
}
}
}

or

private void LoadSchedule()
{
string connString = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand comm = new SqlCommand("GetSchedule_v2", conn))
{
using (SqlDataReader reader = comm.ExecuteReader())
{

myRepeater.DataSource = reader;
myRepeater.DataBind();
}
}
}
}

OriginalGriff 11-Feb-19 3:49am

You know what a collection is, don't you? And you know how to add objects to a collection?