Connection pooling (or not?) with Remote Access database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a Access Database that is connected by an OledbConnection. At this point the Access database is located on the same PC as the application that connects with the database, but it's going to be on a different PC. Regarding database connections I've found that there are a lot of different solutions. In a lot of cases it's said that connections should be opened "as late as possible" and closed immediately after the database operation is finished. Other solutions say that if you're using a remote database you should open one connection and open it and only close it if you close the application, because of overhead issues
Well, I did the last thing, but now I don't know if that was an intelligent action ;-). At this point I have three applications with each their own OledbConnection. So there are three connections with the Access Database. This connection is shared and within the application I assign this connection to the OledbCommands. This worked fine, but I recently discovered that sometimes commands aren't executed beacause the connection is "Open, Executing". This is very frustrating, because I cannot rely on database actions to be executed

Another thing that bugs me is the fact that in the Sub New I have to manually asign the connection to the commands (either "stand-alone" or from within a dataadapter). I think this is an ugly solution, as well as the fact that in a lot of Design-views I use a connection only for designing (because the re-assignment of the shared connection in New); the "Design-connection" is automatically added when a new dataadapter is added

That last point got me thinking that I'm doing something wrong or at least not in the way Microsoft thinks it should be done. On the other hand this mechanism works much faster then opening and closing seperate connections all the time. The last solution creates a lot of connections, that is, in every object that has a dataadapter/command it has a connection as well. And I had the idea that that solution used a lot of resources

So, in short: I have multiple PC's with applications that all want to connect to an Access database on a server. What kind of connection is the best to use and where I know that commands are actually executed

Thanks for your time

Best regards

Michiel Doeven
 
For a situation where there will be multiple users concurrently
connecting to an Access database, the first option will be best --
you'll want to keep connections and transactions as short as possible
to avoid blocking issues. Even in a single-user app, holding an open
connection object can cause problems. If I understand you correctly,
currently you are having problems in some situations where a
connection is still open from a previous command. You'll want to make
sure that you close the connection for all datareaders (use
CommandBehavior.CloseConnection or explicitly close the connection),
since failure to do so will cause subsequent commands to be blocked.

--Mary
 
Hello

Well, I changed everything (back) to the point that each control (etc.) has its own OledbConnection. The .NET Framework SHOULD now manage the connection with connection pooling. With every command I open and close the connection. Everything seemed to go very well. It took me a lot of time, but ok.. Now I implemented it and the system crashes randomly with the following exception: ExecutionEngineException
This is since I'm doing it the "Microsoft"-way. So opening the connection, executing the command, closing the connection. Or in other cases let the DataAdapter handle the opening and closing

I now remember this was one of the reasons to go another way: One connection for each application that is shared through the whole application by making it shared

Somebody has a solution, because I'm going crazy. I cannot rely on the code I write, because it creates mistery-exceptions

Thanks in advance

Kind regards

Michiel Doeven
 
Hello

I'm getting other strange exceptions and application-crahes as well
I'm having the exception InvalidComObjectException that says that the COM-object isn't connected with the RCW-object or something
I'm also having the InvalidOperationException that says that the connection is already Open, but if I step trough one row (to see the exception) the connection is closed
Another thing that was a bit strange is that the application sometimes hangs in a dataadapter.fill. There is no time-out, no exception, the only thing is that the debugger makes the dataadapter.fill-line green

Can somebody please give me a solution. Am I doing something wrong

Best regards

Michiel Doeven
 
Since you're having multiplte problems, you're going to have to tackle
them one at a time by stepping through your code and figuring out at
each failure point what the cause of each individual error is. It's
impossible for an outsider to come up with an answer or to generalize
with no code, no context and only an exception name to go by.

--Mary
 
What is the code doing when you get an InvalidComObjectException? It's
hard to diagnose what you might be doing right or wrong unless you
post a code sample or two, pinpointing the line on which the error(s)
occur.

--Mary
 
Well, the problem with this exception is, that there is no proper documentation ("This exception should not occur...") and there is no specific point in code, because there is no way to catch it
I was hoping for a Microsoft employee to explain to me where this exception is coming from

----- Mary Chipman wrote: ----

Since you're having multiplte problems, you're going to have to tackl
them one at a time by stepping through your code and figuring out a
each failure point what the cause of each individual error is. It'
impossible for an outsider to come up with an answer or to generaliz
with no code, no context and only an exception name to go by

--Mar

On Wed, 5 May 2004 02:26:10 -0700, "Michiel Doeven
 
Thanks for Mary's response!

Hi Michiel,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that exceptions were thrown when you're
trying to fill a DataSet using a Data Adapter. If there is any
misunderstanding, please feel free to let me know.

I agree with Mary's advice that it's better to install the latest version
of MDAC first. So that we can narrow down this issue to see if it is a
component error or code error. The latest version of MDAC (currently 2.8)
can be downloaded from the following link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en

If the update for MDAC doesn't work, could you please let me know the
version of VS.NET you're working on, so that I can do some further research
on this issue? Please also let me know the exception message. Thanks!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
I think I solved the problem
Actually, when you think of it, it's quit stupid, but because of the exceptions was hard to recognize
What I was doing: I have a couple of objects (all inherited from a base object). These objects need to perform some actions with the database. What I did was to make one object (a helper) that contained all database-acitons and passed that to all objects that needed those actions
What I think was going wrong is that this single object was called simultaneously by a coupleof objects. This is probably the reason why I got the exception that a connection was already open, while the debugger said it was closed. In worst cases I got the InvalidCOMException (see other message) or ExecutionEngineException
To make it worse, I use these objects remote in another application

So I think the problem was that a couple of threads were executing the same function at the same time
My solutions is to use my helper object as a local object and create it only when I needed, use it, and dispose it (well, that happens automaticaly)

What I'm wondering is (not really this newsgroup) is what the reason is to create a thread. I'm not creating these threads myself so I want to know why the framework creates these threads and how I can change the creation of threads (and give them a name, etc)

Another thing I want to know is why and when the ExecutionEngineException is thrown. There is very little documentation about this, except for "This exception should not occur"

Thanks Mary and Kevin for thinking along (and maybe others that read this), I think I solved it (no exceptions yet)

Best regards

Michiel Doeve

----- Kevin Yu [MSFT] wrote: ----

Thanks for Mary's response

Hi Michiel

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that exceptions were thrown when you're
trying to fill a DataSet using a Data Adapter. If there is any
misunderstanding, please feel free to let me know

I agree with Mary's advice that it's better to install the latest version
of MDAC first. So that we can narrow down this issue to see if it is a
component error or code error. The latest version of MDAC (currently 2.8)
can be downloaded from the following link

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d
b037-185d0506396c&DisplayLang=e

If the update for MDAC doesn't work, could you please let me know the
version of VS.NET you're working on, so that I can do some further research
on this issue? Please also let me know the exception message. Thanks

Kevin Y
======
"This posting is provided "AS IS" with no warranties, and confers no
rights.
 
Hi Michiel,

It was glad to know that you have had the problem resolved. Resource are
not guaranteed to be thread safe when multiple thread are trying to access
them simultaneousely. The ExecutionEngineException that is thrown when
there is an internal error in the execution engine of the common language
runtime. This class cannot be inherited.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Kevi

Thanks for your reply, but already know that. I was hoping for some more detailed information about
ExecutionEngineException. Why it is raised and how to avoid it (because you can't catch it and usually don't know where the exception occurs exactly
And I also want to know why threads are created when I'm not creating them myself. My application has approx. 15 threads, but why

Best regards

Michiel Doeve

P.S.: Sorry that the post is a bit off-topic

----- Kevin Yu [MSFT] wrote: ----

Hi Michiel

It was glad to know that you have had the problem resolved. Resource are
not guaranteed to be thread safe when multiple thread are trying to access
them simultaneousely. The ExecutionEngineException that is thrown when
there is an internal error in the execution engine of the common language
runtime. This class cannot be inherited

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community

Kevin Y
======
"This posting is provided "AS IS" with no warranties, and confers no
rights.
 
Hi Michiel,

Since the ExecutionEngineException was thrown from inside the CLR, it is
related to the implementation of CLR. We are not quite sure when this
occurs exception when execution engine has been corrupted or data is
missing. This should not occur under normal circumstances. If this occurs,
I think there might be something wrong with the .net framework on the
machine.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top