I have a Connection class as shown below. This class is working fine for stand-alone desktop applications but when I used the same class in ASP.NET in a high concurrent environment, few users reported application malfunctioning.
It was showing user X records on user Y's screen and also user X's old cached objects of other pages. Someone just pointed out that since the class is Static, all users are sharing a common connection, which is a bug.
public static class dbConnection
{
public static SqlConnection cn = new SqlConnection();
public static void OpenConnection()
{
try
{
cn.ConnectionString = ConfigurationManager.ConnectionStrings["cnCustomer"].ToString();
if (cn.State == ConnectionState.Closed)
cn.Open();
}
catch (Exception)
{
throw;
}
}
public static void CloseConnection()
{
try
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
catch (Exception)
{
throw;
}
}
I want to know how to refine this class so that it works safely in a concurrent environment?
You need to scope your connection object to your request. The place to keep objects of such scope is HttpContext.Items.
You could bind an event handler to your applications to create the connection object or create your "dbConnection" class (make it not static) and put it into the HttpContext.Items dictionary.
This is however a bit of a poor mans solution. I would go with a dependency injection based solution. If you are using ASP.Net MVC you could install the nuget packages for AutoFac and the AutoFac mvc integration. There is sample code on their website on
how to wire it up.
Class one :get the connection string from web config
=========================================================
public class ConnectionString
{
public static string MYConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["CnCustomer"].toString();
}
}
}
Class TWO : DataConnection Helper
================================================
public sealed class dbConnection
{
public static SqlConnection objConnection;
public dbConnection()
{
CreateConnection();
}
public void CreateConnection()
{
objConnection = new SqlConnection(ConnectionString.MYConnectionString);
In the same class I discussed above, I have a method: CloseConnection() in which I am closing the connection. I want to know whether it is absolutely necessary to Dispose the connection after closing it or not?
Its not absolutely necessary as the GC should clean it up (provided it's not static). However if ur creating a lot of these it's advisable to displose of them as soon as ur done with it.
-- Sam Critchley
"Wise man say 'forgiveness is divine, but never pay full price for late pizza." - TMNT
As said Static is not be used? You are initializing a static method in a static class. Will it get disposed when used with "Using" or if I add simple "connection.Dispose()"?
rpk2006
Member
631 Points
629 Posts
How to design a common connection class that is suitable for concurrent environment?
Feb 24, 2012 11:06 AM|LINK
I have a Connection class as shown below. This class is working fine for stand-alone desktop applications but when I used the same class in ASP.NET in a high concurrent environment, few users reported application malfunctioning.
It was showing user X records on user Y's screen and also user X's old cached objects of other pages. Someone just pointed out that since the class is Static, all users are sharing a common connection, which is a bug.
public static class dbConnection { public static SqlConnection cn = new SqlConnection(); public static void OpenConnection() { try { cn.ConnectionString = ConfigurationManager.ConnectionStrings["cnCustomer"].ToString(); if (cn.State == ConnectionState.Closed) cn.Open(); } catch (Exception) { throw; } } public static void CloseConnection() { try { if (cn.State == ConnectionState.Open) cn.Close(); } catch (Exception) { throw; } }I want to know how to refine this class so that it works safely in a concurrent environment?
worldspawn[]
Contributor
6081 Points
1336 Posts
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 24, 2012 11:49 AM|LINK
You need to scope your connection object to your request. The place to keep objects of such scope is HttpContext.Items.
You could bind an event handler to your applications to create the connection object or create your "dbConnection" class (make it not static) and put it into the HttpContext.Items dictionary.
This is however a bit of a poor mans solution. I would go with a dependency injection based solution. If you are using ASP.Net MVC you could install the nuget packages for AutoFac and the AutoFac mvc integration. There is sample code on their website on how to wire it up.
http://msdn.microsoft.com/en-us/library/system.web.httpcontext.items.aspx
http://code.google.com/p/autofac/wiki/MvcIntegration
http://code.google.com/p/autofac/wiki/AspNetIntegration
"Wise man say 'forgiveness is divine, but never pay full price for late pizza." - TMNT
software development
asyed4u
Participant
1307 Points
268 Posts
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 24, 2012 11:57 AM|LINK
Hi,
Try with following code , u can alter the
Class one :get the connection string from web config
=========================================================
public class ConnectionString
{
public static string MYConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["CnCustomer"].toString();
}
}
}
Class TWO : DataConnection Helper
================================================
public sealed class dbConnection
{
public static SqlConnection objConnection;
public dbConnection()
{
CreateConnection();
}
public void CreateConnection()
{
objConnection = new SqlConnection(ConnectionString.MYConnectionString);
try
{
objConnection.Open();
objConnection.Close();
}
catch (Exception ex)
{
string strError = ex.Message.ToString();
}
}
public static void DBClose()
{
try
{
if (objConnection.State == ConnectionState.Open)
objConnection.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static void DBOpen()
{
try
{
if (objConnection.State != ConnectionState.Open)
objConnection.Open();
}
catch (Exception ex)
{
throw ex;
}
}
}
=========================
rpk2006
Member
631 Points
629 Posts
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 24, 2012 12:03 PM|LINK
In the same class I discussed above, I have a method: CloseConnection() in which I am closing the connection. I want to know whether it is absolutely necessary to Dispose the connection after closing it or not?
worldspawn[]
Contributor
6081 Points
1336 Posts
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 24, 2012 11:32 PM|LINK
Its not absolutely necessary as the GC should clean it up (provided it's not static). However if ur creating a lot of these it's advisable to displose of them as soon as ur done with it.
"Wise man say 'forgiveness is divine, but never pay full price for late pizza." - TMNT
software development
pbromberg
Contributor
3886 Points
530 Posts
MVP
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 25, 2012 12:17 AM|LINK
public static class DbConnection
{
public static SqlConnection GetOpenConnection( string connectionName)
{
try
{
string connectionString = ConfigurationManager.ConnectionStrings[connectionName];
SqlConnection conn = new SqlConnection (connectionString);
conn.Open();
return conn;
}
catch (Exception)
{
throw;
}
}
}
// usage:
/*
using( SqlConnection conn = DbConnection.GetOpenConnection("myConnectionStringName") )
{
// do your work with connection here
} // when the closing brace of the using statement is hit, the connection will automatically be closed and disposed
*/
Eggheadcafe.com
rpk2006
Member
631 Points
629 Posts
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 25, 2012 05:02 AM|LINK
@pbromberg:
As said Static is not be used? You are initializing a static method in a static class. Will it get disposed when used with "Using" or if I add simple "connection.Dispose()"?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 26, 2012 12:14 AM|LINK
Hello rpk2006：）
In my mind，you can use using statement+SqlConnection instead of the static connection——
using (SqlConnection con = new SqlConnection(…………)) { …………………… }pbromberg
Contributor
3886 Points
530 Posts
MVP
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 26, 2012 12:17 AM|LINK
There is nothing wrong with using a static method as long as no state is held. The connection will be disposed as indicated in my reply.
Eggheadcafe.com
rpk2006
Member
631 Points
629 Posts
Re: How to design a common connection class that is suitable for concurrent environment?
Feb 27, 2012 04:33 AM|LINK
@pbromberg:
I am going to change code as suggested in production but it will take some time.
At present, I have modified the code as below. Is it going to work?
public static void CloseConnection() { try { if (cn.ConnectionState == ConnectionState.Open) cn.Close(); } catch (Exception ex) { // code here } finally { cn.dispose(); } }