A
Andrew Dowding
Hi Everybody,
I have been looking at problems with my Windows Forms C# application
and it's little Jet 4 (Access) database for the last few days. The
Windows Forms app implements a facade and implementation, data
abstraction layer. But because each data adapter in the implementation
layer has a connection object that opens and closes as needed, I found
I got several errors from the Jet engine when there were simultaneous
connections to the database. Errors such as:
1. Could not lock file.
2. The database has been placed in a state by user 'someuser' on
machine 'SOMEMACHINENAME' that prevents it from being opened or
locked.
I sort of stumbled across that this is a problem with the data adapter
opening and closing connections when it needed them. Which is usually
a good thing. But fo some reason, this behaviour caused the Jet
database to have problems locking the file etc. Probably because the
..ldb lock file is created and then deleted and then created etc on and
on and eventually something happens simultaneously and the database
and/or lock file has gone crazy, making the app very slow if not
locking it altogether. I noticed if I had my Server Explorer in Visual
Studio open with a connection to the database everything worked fine
for multiple instances of the app, connecting simultaneously. It looks
like Jet needed a connection the whole time to keep that lock file
there.
I added a connection to my main app and opened it on startup, then
closed it on exit. Everything was cool now. But I didn't want a
connection open to the database that's not even being used, so I wrote
a little connection pool/provider class which will keep one connection
always open to the database. The connection pool will serve
connections out to the implementation layer functions for their data
adapters and then those functions will return the connections. The
pool can serve out as many connections needed, it will also serve out
the connection always staying open and therefore that connection isn't
wasted.
While I was testing this connection pool class, I came across another
interesting fact. It seemed that even though I set the one connection
object each implementation layer class has to a connection from the
pool, the data adapters seem to have a clone of that one object, not a
reference. Therefore they weren't using the pool connection object at
all. I modified the connection pool to take a data adapter(s) as input
when getting a connection and reset them to the pool connection. Now
everything was running smoothly, plus the app and database were
communicating about 10 times faster - cool bonus.
Now you want to see the code huh?
Pool Class:
--------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Collections;
namespace DALApp.Connection
{
/// <summary>
/// Summary description for RiskConnectionPool.
/// The need for this connection pool has mainly arisen because after
/// it was found that JET 4.0 has several locking issues if a
connection
/// is not kept to the data source for the life of the application.
As, if
/// another user connects at the same time, dead locks will occur and
the
/// application will become unresponsive and usually throw a "Could
not lock file."
/// exception. This connection pool supports a root connection to
keep that
/// link with the data source at all times. It also allows the
application to
/// get as many connections as it needs. All connections returned
from
/// GetConnection() will be open.
/// AD.
/// </summary>
public class RiskConnectionPool : IDisposable
{
private ArrayList ConnectionList = null; // list of connections,
position 0 is always the root connection which should never be closed
private OleDbConnection RootConnection = null; // root connection,
needs to stay open for the application lifetime
private bool disposed = false; // is this class disposed
private static RiskConnectionPool instance = null; // singleton
instance
/// <summary>
/// Helper class for the connection pool.
/// </summary>
private class RiskConn : object
{
public OleDbConnection ConncetionbObject;
public bool CurrentlyUsed;
/// <summary>
/// Helper class constructor.
/// </summary>
/// <param name="Conn"></param>
/// <param name="CurrentlyUsed"></param>
public RiskConn(OleDbConnection Conn, bool CurrentlyUsed)
{
this.ConncetionbObject = Conn;
this.CurrentlyUsed = CurrentlyUsed;
}
}
/// <summary>
/// Get the singleton connnection pool object.
/// </summary>
/// <param name="ConnectionString"></param>
/// <returns></returns>
public static RiskConnectionPool getInstance(string
ConnectionString)
{
if(instance == null)
{
instance = new RiskConnectionPool(ConnectionString);
}
return(instance);
}
/// <summary>
/// Get the singleton connnection pool object.
/// </summary>
/// <param name="ConnectionString"></param>
/// <returns></returns>
public static RiskConnectionPool getInstance()
{
if(instance == null)
{
instance = new RiskConnectionPool(UtilApp.RegSettings.getValue(UtilApp.RegSettings.REGSETTING_RISK_USER_CONN_STRING));
}
return(instance);
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="ConnectionString"></param>
private RiskConnectionPool(string ConnectionString)
{
disposed = false;
ConnectionList = new ArrayList(1);
RootConnection = new OleDbConnection(ConnectionString);
ConnectionList.Add(new RiskConn(RootConnection, false));
Initialise();
}
/// <summary>
/// Event handler for root connection's state changed event.
/// Make sure that the root connection stays open
/// for the lifetime of the application. This is needed
/// because JET 4.0 created locking issues if there is
/// no connection open for the application lifetime.
/// </summary>
/// <param name="sender"></param>
/// <param name="sce"></param>
private void RootConnectionStateChangedEvent(object sender,
StateChangeEventArgs sce)
{
if(sce.CurrentState == ConnectionState.Broken)
{
RootConnection.Close();
RootConnection.Open();
}
else if(sce.CurrentState != ConnectionState.Open)
{
RootConnection.Open();
}
}
/// <summary>
/// Initialise the root connection.
/// </summary>
private void Initialise()
{
try
{
RootConnection.Open();
RootConnection.StateChange += new
StateChangeEventHandler(this.RootConnectionStateChangedEvent);
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// Get a free connection to use.
/// The connection will be open when returned.
/// </summary>
/// <returns></returns>
public OleDbConnection GetConnection(OleDbDataAdapter adp)
{
OleDbConnection c = this.GetConnection();
try
{
adp.SelectCommand.Connection = c;
}
catch{}
try
{
adp.InsertCommand.Connection = c;
}
catch{}
try
{
adp.UpdateCommand.Connection = c;
}catch{}
try
{
adp.DeleteCommand.Connection = c;
}
catch{}
return(c);
}
/// <summary>
/// Get a free connection to use.
/// The connection will be open when returned.
/// </summary>
/// <returns></returns>
public OleDbConnection GetConnection(OleDbDataAdapter[] adps)
{
OleDbConnection c = this.GetConnection();
for(int i=0; i < adps.Length; i++)
{
try
{
adps.SelectCommand.Connection = c;
}
catch{}
try
{
adps.InsertCommand.Connection = c;
}
catch{}
try
{
adps.UpdateCommand.Connection = c;
}
catch{}
try
{
adps.DeleteCommand.Connection = c;
}
catch{}
}
return(c);
}
/// <summary>
/// Get a free connection to use.
/// The connection will be open when returned.
/// </summary>
/// <returns></returns>
public OleDbConnection GetConnection()
{
RiskConn rc = findFreeConnection();
if(rc != null)
{
if(rc.ConncetionbObject.Equals(RootConnection))
{
rc.CurrentlyUsed = true;
return(rc.ConncetionbObject);
}
else
{
rc.CurrentlyUsed = true;
rc.ConncetionbObject.Open();
return(rc.ConncetionbObject);
}
}
else
{
// add a new connection and return it
OleDbConnection newConn = new
OleDbConnection(RootConnection.ConnectionString);
RiskConn nrc = new RiskConn(newConn, true);
ConnectionList.Add(nrc);
newConn.Open();
return(newConn);
}
}
/// <summary>
/// Find a free connection to use from the
/// list of connections.
/// </summary>
/// <returns></returns>
private RiskConn findFreeConnection()
{
for(int i=0; i < ConnectionList.Count; i++)
{
RiskConn rc = (RiskConn)ConnectionList;
if(!rc.CurrentlyUsed)
{
return(rc);
}
}
return(null);
}
/// <summary>
/// Find a connection object in the list of connections.
/// Uses the object.Equals() method to find.
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private RiskConn findConnection(OleDbConnection conn)
{
for(int i=0; i < ConnectionList.Count; i++)
{
RiskConn rc = (RiskConn)ConnectionList;
if(rc.ConncetionbObject.Equals(conn))
{
return(rc);
}
}
return(null);
}
/// <summary>
/// Return a connection to the pool.
/// If the connection does not belong
/// to the pool it is ignored.
/// </summary>
/// <param name="Conn"></param>
public void ReturnConnection(OleDbConnection Conn)
{
if(Conn == null)
{
return;
}
if(Conn.Equals(RootConnection))
{
((RiskConn)ConnectionList[0]).CurrentlyUsed = false;
}
else
{
RiskConn rc = findConnection(Conn);
if(rc != null)
{
rc.CurrentlyUsed = false;
if(rc.ConncetionbObject.State != ConnectionState.Closed)
{
rc.ConncetionbObject.Close();
}
}
}
}
/// <summary>
/// Dispose this connection pool object.
/// All connections contained will be
/// closed, disposed and nullified, including
/// the root connection.
/// </summary>
public void Dispose()
{
// detach event listener
RootConnection.StateChange -= new
StateChangeEventHandler(this.RootConnectionStateChangedEvent);
for(int i=1; i < ConnectionList.Count; i++) // loop through stack
except for the root connection
{
OleDbConnection conn =
((RiskConn)ConnectionList).ConncetionbObject;
if(conn != null)
{
try
{
conn.Close();
}
catch{}
finally
{
try
{
conn.Dispose();
}
catch{}
}
}
}
if(RootConnection != null)
{
try
{
// force root connection to close
RootConnection.Close();
RootConnection.Dispose();
}
catch{}
finally
{
try
{
RootConnection = null;
ConnectionList = null;
}
catch{}
}
}
disposed = true;
}
/// <summary>
/// Destructor for connection pool.
/// Calls dispose if this object
/// has not already been
/// disposed.
/// </summary>
~RiskConnectionPool()
{
if(!disposed)
{
this.Dispose();
}
}
}//\class
}//\namespace
Using the Pool Class:
--------------------------------------------------------------------------
/// <summary>
/// Get risk assessment data by risk ID
/// </summary>
/// <param name="intRiskID"></param>
/// <returns>DSAssess</returns>
internal RiskDALApp.Datasets.DSAssess getAssessmentForRisk(int
intRiskID)
{
try
{
oleDbConnection =
Connection.RiskConnectionPool.getInstance().GetConnection(new
OleDbDataAdapter[]{odaAssessByRiskID,odaAssessQueryByRiskID,odaNumTreatOption});
Datasets.DSAssess dsAssess = new Datasets.DSAssess();
odaAssessByRiskID.SelectCommand.Parameters["n_risk_ID"].Value =
intRiskID;
odaAssessByRiskID.Fill(dsAssess, "rk_assess");
odaAssessQueryByRiskID.SelectCommand.Parameters["n_risk_ID"].Value
= intRiskID;
odaAssessQueryByRiskID.Fill(dsAssess, "rk_assess_Query");
odaNumTreatOption.SelectCommand.Parameters["n_risk_ID"].Value =
intRiskID;
odaNumTreatOption.Fill(dsAssess, "rk_NumTreatOption_Query");
return dsAssess;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.RiskConnectionPool.getInstance().ReturnConnection(oleDbConnection);
oleDbConnection = null;
}
}
-----------------------------------
You'll have to change some things to get this working for you if you
want to use it. Hope it helps!
Andrew Dowding
Boring Legal Stuff
---
I, Andrew Dowding, grant you a nonexclusive copyright license to use
all programming code examples from which you can generate similar
function tailored to your own specific needs.
All sample code is provided for illustrative purposes only. These
examples have not been thoroughly tested under all conditions.
Therefore, cannot guarantee or imply reliability, serviceability, or
function of these programs.
All code contained herein are provided to you "AS IS" without any
warranties of any kind. The implied warranties of non-infringement,
merchantability and fitness for a particular purpose are expressly
disclaimed.
I have been looking at problems with my Windows Forms C# application
and it's little Jet 4 (Access) database for the last few days. The
Windows Forms app implements a facade and implementation, data
abstraction layer. But because each data adapter in the implementation
layer has a connection object that opens and closes as needed, I found
I got several errors from the Jet engine when there were simultaneous
connections to the database. Errors such as:
1. Could not lock file.
2. The database has been placed in a state by user 'someuser' on
machine 'SOMEMACHINENAME' that prevents it from being opened or
locked.
I sort of stumbled across that this is a problem with the data adapter
opening and closing connections when it needed them. Which is usually
a good thing. But fo some reason, this behaviour caused the Jet
database to have problems locking the file etc. Probably because the
..ldb lock file is created and then deleted and then created etc on and
on and eventually something happens simultaneously and the database
and/or lock file has gone crazy, making the app very slow if not
locking it altogether. I noticed if I had my Server Explorer in Visual
Studio open with a connection to the database everything worked fine
for multiple instances of the app, connecting simultaneously. It looks
like Jet needed a connection the whole time to keep that lock file
there.
I added a connection to my main app and opened it on startup, then
closed it on exit. Everything was cool now. But I didn't want a
connection open to the database that's not even being used, so I wrote
a little connection pool/provider class which will keep one connection
always open to the database. The connection pool will serve
connections out to the implementation layer functions for their data
adapters and then those functions will return the connections. The
pool can serve out as many connections needed, it will also serve out
the connection always staying open and therefore that connection isn't
wasted.
While I was testing this connection pool class, I came across another
interesting fact. It seemed that even though I set the one connection
object each implementation layer class has to a connection from the
pool, the data adapters seem to have a clone of that one object, not a
reference. Therefore they weren't using the pool connection object at
all. I modified the connection pool to take a data adapter(s) as input
when getting a connection and reset them to the pool connection. Now
everything was running smoothly, plus the app and database were
communicating about 10 times faster - cool bonus.
Now you want to see the code huh?
Pool Class:
--------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Common;
using System.Collections;
namespace DALApp.Connection
{
/// <summary>
/// Summary description for RiskConnectionPool.
/// The need for this connection pool has mainly arisen because after
/// it was found that JET 4.0 has several locking issues if a
connection
/// is not kept to the data source for the life of the application.
As, if
/// another user connects at the same time, dead locks will occur and
the
/// application will become unresponsive and usually throw a "Could
not lock file."
/// exception. This connection pool supports a root connection to
keep that
/// link with the data source at all times. It also allows the
application to
/// get as many connections as it needs. All connections returned
from
/// GetConnection() will be open.
/// AD.
/// </summary>
public class RiskConnectionPool : IDisposable
{
private ArrayList ConnectionList = null; // list of connections,
position 0 is always the root connection which should never be closed
private OleDbConnection RootConnection = null; // root connection,
needs to stay open for the application lifetime
private bool disposed = false; // is this class disposed
private static RiskConnectionPool instance = null; // singleton
instance
/// <summary>
/// Helper class for the connection pool.
/// </summary>
private class RiskConn : object
{
public OleDbConnection ConncetionbObject;
public bool CurrentlyUsed;
/// <summary>
/// Helper class constructor.
/// </summary>
/// <param name="Conn"></param>
/// <param name="CurrentlyUsed"></param>
public RiskConn(OleDbConnection Conn, bool CurrentlyUsed)
{
this.ConncetionbObject = Conn;
this.CurrentlyUsed = CurrentlyUsed;
}
}
/// <summary>
/// Get the singleton connnection pool object.
/// </summary>
/// <param name="ConnectionString"></param>
/// <returns></returns>
public static RiskConnectionPool getInstance(string
ConnectionString)
{
if(instance == null)
{
instance = new RiskConnectionPool(ConnectionString);
}
return(instance);
}
/// <summary>
/// Get the singleton connnection pool object.
/// </summary>
/// <param name="ConnectionString"></param>
/// <returns></returns>
public static RiskConnectionPool getInstance()
{
if(instance == null)
{
instance = new RiskConnectionPool(UtilApp.RegSettings.getValue(UtilApp.RegSettings.REGSETTING_RISK_USER_CONN_STRING));
}
return(instance);
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="ConnectionString"></param>
private RiskConnectionPool(string ConnectionString)
{
disposed = false;
ConnectionList = new ArrayList(1);
RootConnection = new OleDbConnection(ConnectionString);
ConnectionList.Add(new RiskConn(RootConnection, false));
Initialise();
}
/// <summary>
/// Event handler for root connection's state changed event.
/// Make sure that the root connection stays open
/// for the lifetime of the application. This is needed
/// because JET 4.0 created locking issues if there is
/// no connection open for the application lifetime.
/// </summary>
/// <param name="sender"></param>
/// <param name="sce"></param>
private void RootConnectionStateChangedEvent(object sender,
StateChangeEventArgs sce)
{
if(sce.CurrentState == ConnectionState.Broken)
{
RootConnection.Close();
RootConnection.Open();
}
else if(sce.CurrentState != ConnectionState.Open)
{
RootConnection.Open();
}
}
/// <summary>
/// Initialise the root connection.
/// </summary>
private void Initialise()
{
try
{
RootConnection.Open();
RootConnection.StateChange += new
StateChangeEventHandler(this.RootConnectionStateChangedEvent);
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// Get a free connection to use.
/// The connection will be open when returned.
/// </summary>
/// <returns></returns>
public OleDbConnection GetConnection(OleDbDataAdapter adp)
{
OleDbConnection c = this.GetConnection();
try
{
adp.SelectCommand.Connection = c;
}
catch{}
try
{
adp.InsertCommand.Connection = c;
}
catch{}
try
{
adp.UpdateCommand.Connection = c;
}catch{}
try
{
adp.DeleteCommand.Connection = c;
}
catch{}
return(c);
}
/// <summary>
/// Get a free connection to use.
/// The connection will be open when returned.
/// </summary>
/// <returns></returns>
public OleDbConnection GetConnection(OleDbDataAdapter[] adps)
{
OleDbConnection c = this.GetConnection();
for(int i=0; i < adps.Length; i++)
{
try
{
adps.SelectCommand.Connection = c;
}
catch{}
try
{
adps.InsertCommand.Connection = c;
}
catch{}
try
{
adps.UpdateCommand.Connection = c;
}
catch{}
try
{
adps.DeleteCommand.Connection = c;
}
catch{}
}
return(c);
}
/// <summary>
/// Get a free connection to use.
/// The connection will be open when returned.
/// </summary>
/// <returns></returns>
public OleDbConnection GetConnection()
{
RiskConn rc = findFreeConnection();
if(rc != null)
{
if(rc.ConncetionbObject.Equals(RootConnection))
{
rc.CurrentlyUsed = true;
return(rc.ConncetionbObject);
}
else
{
rc.CurrentlyUsed = true;
rc.ConncetionbObject.Open();
return(rc.ConncetionbObject);
}
}
else
{
// add a new connection and return it
OleDbConnection newConn = new
OleDbConnection(RootConnection.ConnectionString);
RiskConn nrc = new RiskConn(newConn, true);
ConnectionList.Add(nrc);
newConn.Open();
return(newConn);
}
}
/// <summary>
/// Find a free connection to use from the
/// list of connections.
/// </summary>
/// <returns></returns>
private RiskConn findFreeConnection()
{
for(int i=0; i < ConnectionList.Count; i++)
{
RiskConn rc = (RiskConn)ConnectionList;
if(!rc.CurrentlyUsed)
{
return(rc);
}
}
return(null);
}
/// <summary>
/// Find a connection object in the list of connections.
/// Uses the object.Equals() method to find.
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private RiskConn findConnection(OleDbConnection conn)
{
for(int i=0; i < ConnectionList.Count; i++)
{
RiskConn rc = (RiskConn)ConnectionList;
if(rc.ConncetionbObject.Equals(conn))
{
return(rc);
}
}
return(null);
}
/// <summary>
/// Return a connection to the pool.
/// If the connection does not belong
/// to the pool it is ignored.
/// </summary>
/// <param name="Conn"></param>
public void ReturnConnection(OleDbConnection Conn)
{
if(Conn == null)
{
return;
}
if(Conn.Equals(RootConnection))
{
((RiskConn)ConnectionList[0]).CurrentlyUsed = false;
}
else
{
RiskConn rc = findConnection(Conn);
if(rc != null)
{
rc.CurrentlyUsed = false;
if(rc.ConncetionbObject.State != ConnectionState.Closed)
{
rc.ConncetionbObject.Close();
}
}
}
}
/// <summary>
/// Dispose this connection pool object.
/// All connections contained will be
/// closed, disposed and nullified, including
/// the root connection.
/// </summary>
public void Dispose()
{
// detach event listener
RootConnection.StateChange -= new
StateChangeEventHandler(this.RootConnectionStateChangedEvent);
for(int i=1; i < ConnectionList.Count; i++) // loop through stack
except for the root connection
{
OleDbConnection conn =
((RiskConn)ConnectionList).ConncetionbObject;
if(conn != null)
{
try
{
conn.Close();
}
catch{}
finally
{
try
{
conn.Dispose();
}
catch{}
}
}
}
if(RootConnection != null)
{
try
{
// force root connection to close
RootConnection.Close();
RootConnection.Dispose();
}
catch{}
finally
{
try
{
RootConnection = null;
ConnectionList = null;
}
catch{}
}
}
disposed = true;
}
/// <summary>
/// Destructor for connection pool.
/// Calls dispose if this object
/// has not already been
/// disposed.
/// </summary>
~RiskConnectionPool()
{
if(!disposed)
{
this.Dispose();
}
}
}//\class
}//\namespace
Using the Pool Class:
--------------------------------------------------------------------------
/// <summary>
/// Get risk assessment data by risk ID
/// </summary>
/// <param name="intRiskID"></param>
/// <returns>DSAssess</returns>
internal RiskDALApp.Datasets.DSAssess getAssessmentForRisk(int
intRiskID)
{
try
{
oleDbConnection =
Connection.RiskConnectionPool.getInstance().GetConnection(new
OleDbDataAdapter[]{odaAssessByRiskID,odaAssessQueryByRiskID,odaNumTreatOption});
Datasets.DSAssess dsAssess = new Datasets.DSAssess();
odaAssessByRiskID.SelectCommand.Parameters["n_risk_ID"].Value =
intRiskID;
odaAssessByRiskID.Fill(dsAssess, "rk_assess");
odaAssessQueryByRiskID.SelectCommand.Parameters["n_risk_ID"].Value
= intRiskID;
odaAssessQueryByRiskID.Fill(dsAssess, "rk_assess_Query");
odaNumTreatOption.SelectCommand.Parameters["n_risk_ID"].Value =
intRiskID;
odaNumTreatOption.Fill(dsAssess, "rk_NumTreatOption_Query");
return dsAssess;
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.RiskConnectionPool.getInstance().ReturnConnection(oleDbConnection);
oleDbConnection = null;
}
}
-----------------------------------
You'll have to change some things to get this working for you if you
want to use it. Hope it helps!
Andrew Dowding
Boring Legal Stuff
---
I, Andrew Dowding, grant you a nonexclusive copyright license to use
all programming code examples from which you can generate similar
function tailored to your own specific needs.
All sample code is provided for illustrative purposes only. These
examples have not been thoroughly tested under all conditions.
Therefore, cannot guarantee or imply reliability, serviceability, or
function of these programs.
All code contained herein are provided to you "AS IS" without any
warranties of any kind. The implied warranties of non-infringement,
merchantability and fitness for a particular purpose are expressly
disclaimed.