Sqlconnection object in website

[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

[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
"Victor" <VictorDotShiAtnurseryroomDotComwrote in message
news:es**************@TK2MSFTNGP02.phx.gbl...

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

[bruce barker]

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)

Victor wrote:

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" <VictorDotShiAtnurseryroomDotComwrote in message
news:es**************@TK2MSFTNGP02.phx.gbl...

>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

[sloan]

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" <VictorDotShiAtnurseryroomDotComwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

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" <VictorDotShiAtnurseryroomDotComwrote in message
news:es**************@TK2MSFTNGP02.phx.gbl...

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

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

"sloan" <sl***@ipass.netдÈëÏûÏ¢
news:Oh*************@TK2MSFTNGP04.phx.gbl...

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" <VictorDotShiAtnurseryroomDotComwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

>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" <VictorDotShiAtnurseryroomDotComwrote in message
news:es**************@TK2MSFTNGP02.phx.gbl...

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