Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Keep connection string open or not c#

If I have a few classes using the same connection string on the same page should I keep the connection open(); and close it once I'm finished or keep opening and closing.

example below

protected void Page_Load(object sender, EventArgs e)
{
    cs.Open();
    something ...
    cs.Close();    
}

protected void Button1_Click(object sender, EventArgs e)
{

    cs.Open();
    something .....
    cs.Close();
}

Or This

protected void Page_Load(object sender, EventArgs e)
{
    cs.Open();
    something ...
}

protected void Button1_Click(object sender, EventArgs e)
{
    something .....
    cs.Close();
    cs.Dispose();
}
like image 661
Beep Avatar asked Nov 28 '22 15:11

Beep


1 Answers

As a general rule for .NET all non managed resources such as a Database Connection should be opened as late as possible and closed as early as possible so the first option you showed is the right way to do it.

.NET offers a connection pool that make sure that you do not open more connections than needed so when you call Open or Close that is not what immediately happens. But instead, the pool manages all connections you have to keep a health ratio between opening and closing communication with the database.

Unless, of course, you have some other case where you need to keep a connection open such as when you are inside a transaction.

like image 120
tucaz Avatar answered Dec 11 '22 04:12

tucaz