Sqlconnection object in website

  • Thread starter Thread starter Victor
  • Start date Start date
V

Victor

Hi Guys I have a problem here.
I want to improve the performance for a website. When I looked into the
system, I have found that the system made the "SqlConnection Object" static.
That mean only one SqlConnection object will be used.
Can someone tell me is that a correct way to do? Does that affect the system
performance?

Cheers
Victor
 
Here I have a short example how the code looks like
Class A
{
private static SqlConnection conn;

public static SqlConnection getDBAConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringA"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}


public static SqlConnection getDBBConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringB"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static SqlConnection getDBCConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringC"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static SqlConnection getDBDConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringD"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static void CloseConnection()
{
if (conn!=null)
{
if (conn.State == ConnectionState.Open) conn.Close();
}
}
}

Is that a good way to do?

Cheers
Victor
 
yes and no. there is nothing wrong with static methods to return a new
connection, but the code assume a close will be called before another
open. no locks are done, so its not thread safe.

you site most likely leaks connections especially under load.

you should remove the private static and change close to be passed a
connection. better yet use a using:

using A.getDBAConnection()
{
// my code here - no close required
}

-- bruce (sqlwork.com)


Here I have a short example how the code looks like
Class A
{
private static SqlConnection conn;

public static SqlConnection getDBAConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringA"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}


public static SqlConnection getDBBConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringB"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static SqlConnection getDBCConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringC"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static SqlConnection getDBDConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringD"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static void CloseConnection()
{
if (conn!=null)
{
if (conn.State == ConnectionState.Open) conn.Close();
}
}
}

Is that a good way to do?

Cheers
Victor


Victor said:
Hi Guys I have a problem here.
I want to improve the performance for a website. When I looked into the
system, I have found that the system made the "SqlConnection Object"
static. That mean only one SqlConnection object will be used.
Can someone tell me is that a correct way to do? Does that affect the
system performance?

Cheers
Victor
Click to expand...
Click to expand...
 
Your best bet for getting good practices code is to find the

Data Access Application Block Version 2.0

this one is 'sql server only'.

...

Your best bet is to fine tune the connection pool.
Using a connection pool, and the DAAB .. will start you on the right path.

the DAAB has a "open late, close early" mentality, which is a good one for
the web.

...

The other place you need to watch out for . is.... NOT closing datareaders.
You need to ~~~~ always close datareaders.




Victor said:
Here I have a short example how the code looks like
Class A
{
private static SqlConnection conn;

public static SqlConnection getDBAConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringA"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}


public static SqlConnection getDBBConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringB"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn; d> }

public static SqlConnection getDBCConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringC"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static SqlConnection getDBDConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringD"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static void CloseConnection()
{
if (conn!=null)
{
if (conn.State == ConnectionState.Open) conn.Close();
}
}
}

Is that a good way to do?

Cheers
Victor


Victor said:
Hi Guys I have a problem here.
I want to improve the performance for a website. When I looked into the
system, I have found that the system made the "SqlConnection Object"
static. That mean only one SqlConnection object will be used.
Can someone tell me is that a correct way to do? Does that affect the
system performance?

Cheers
Victor
Click to expand...
Click to expand...
 
hi
Here is my another question. So is that correct to set the connection
object to be static?


Cheers
Victor



sloan said:
Your best bet for getting good practices code is to find the

Data Access Application Block Version 2.0

this one is 'sql server only'.

..

Your best bet is to fine tune the connection pool.
Using a connection pool, and the DAAB .. will start you on the right path.

the DAAB has a "open late, close early" mentality, which is a good one for
the web.

..

The other place you need to watch out for . is.... NOT closing
datareaders.
You need to ~~~~ always close datareaders.




Victor said:
Here I have a short example how the code looks like
Class A
{
private static SqlConnection conn;

public static SqlConnection getDBAConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringA"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}


public static SqlConnection getDBBConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringB"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn; d> }

public static SqlConnection getDBCConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringC"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static SqlConnection getDBDConnection()
{
string _connStr;
_connStr =
ConfigurationSettings.AppSettings["ConnectionStringD"];
conn = new SqlConnection(_connStr);
conn.Open();
return conn;
}

public static void CloseConnection()
{
if (conn!=null)
{
if (conn.State == ConnectionState.Open) conn.Close();
}
}
}

Is that a good way to do?

Cheers
Victor


Victor said:
Hi Guys I have a problem here.
I want to improve the performance for a website. When I looked into the
system, I have found that the system made the "SqlConnection Object"
static. That mean only one SqlConnection object will be used.
Can someone tell me is that a correct way to do? Does that affect the
system performance?

Cheers
Victor
Click to expand...
Click to expand...
Click to expand...
 
Back
Top