473,320 Members | 2,122 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

C# Windows Forms -> Jet 4.0 Access = Slow Performance/File Lock Errors -- Possible Solution

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(Ut ilApp.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.RootConnectionStateCh angedEvent);
}
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[i].SelectCommand.Connection = c;
}
catch{}
try
{
adps[i].InsertCommand.Connection = c;
}
catch{}
try
{
adps[i].UpdateCommand.Connection = c;
}
catch{}
try
{
adps[i].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[i];
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[i];
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.RootConnectionStateCh angedEvent);
for(int i=1; i < ConnectionList.Count; i++) // loop through stack
except for the root connection
{
OleDbConnection conn =
((RiskConn)ConnectionList[i]).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().GetCon nection(new
OleDbDataAdapter[]{odaAssessByRiskID,odaAssessQueryByRiskID,odaNumTr eatOption});
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().Return Connection(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.
Nov 16 '05 #1
0 3146

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: rubixxcube | last post by:
Randomly my application throws the following exception, and of course it only happens in production and i can't reproduct it in Testing. Does anyone know what causes the following exception: An...
2
by: Greg Bacchus | last post by:
Hi, I'm getting an exception that really has me stumped. It's sporadic at best, it's only happened a handful of times. This particular time it happened when the user pressed 'Alt-S' to save the...
5
by: RAJ | last post by:
hi plz tell me how to know "how window is going to close"... i have to right code for X button of forms... plz telll me thanks bye
15
by: Wiktor Zychla | last post by:
today we've found a critical issue regarding the ListView from Windows.Forms. it was confirmed on several machines with Win2K and XP. here's the problem: create a ListView with about 50000 rows....
6
by: Ayende Rahien | last post by:
Excetremely annoying problem, I've an application with a long startup time. So I created another form with my logo in it to as a splash screen. The splash screen is run from another thread and is...
2
by: Raed Sawalha | last post by:
i have a windows form(Main) with listview, when click an item in listview i open other window form (Sub) which generate the selected item from parent window in as treeview items when click any item...
7
by: Tyler Foreman | last post by:
Hello, I have a strange problem that occurs every so often in my application. It usually takes place when I hide one form and activate another. What happens is I get the following exception:...
0
by: SMichal | last post by:
Hi, I've got a simple problem when I try to start a new process from my application. I'm starting a new process like this: Process proc = new Process(); proc.StartInfo.Arguments = "";...
4
by: Mo | last post by:
Hi, I am writing a console application to send a key sequence to an old clunky application on a regular interval using the windows scheduler. I can get it to work if it is a windows form...
21
by: Dan Tallent | last post by:
In my application I have a form (Customer) that I want to be able to open multiple copies at once. Within this form I have other forms that can be opened. Example: ZipCode. When the user enters...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.