ConnectionState问题 - 管理与Access数据库的连接 [英] ConnectionState problem - managing connections to an Access database
问题描述
我有一个asp.net应用程序，它使用Cassini从CD-ROM运行。如这样，它只是单个用户。
应用程序在加载时连接到Access数据库，并且
保持相同的连接一直打开（因为它的单个用户，这个
应该不是问题）。
有逻辑在代码中确保连接是第一次使用时自动打开连接并且连接是存储在Application变量中的
：
if（（cmd.Connection.State == ConnectionState.Closed）||
（cmd.Connection.State == ConnectionState.Broken））cmd.Connection.Open（）;
但是偶尔在IIS下测试我的应用程序时会出现这个错误消息：
系统.InvalidOperationException：
连接已经打开（状态=正在连接）。
或者这个：
ExecuteReader需要一个开放且可用的连接。
连接的当前状态是打开，执行。
但是如果状态是连接，你既不能打电话。打开也不能。 />
执行查询。那么如果没有发生这些
错误，我怎样才能保持开放式连接？
我不明白它为何会出现在Exectuting中
单个用户应用程序中的状态。是因为IIS多线程
并且它可能使用两个+线程来托管我的应用程序？
如果是这样，我怎么能阻止这些错误发生了什么？我不想打开
并在每次做某事时关闭一个新的连接，因为这太多了
慢（40ms），有些页面需要100个查询我没有时间在这里解释。使用单个连接 - 应用程序
表现非常好，但有这些错误。有没有办法可以获得两个世界中最好的
？
有人建议我禁用连接池但这没有
不同。
谢谢，
尼克...
-
请回复新闻组或者我没看到你的消息。
嗨尼克，
我知道您可能认为使用单个连接更好，但是
你真的想在.Net数据库连接上结束运行
型号。 .Net平台旨在有效利用数据库
Connections，并自行重用它们。它是通过
连接池的过程来完成的。
您可能会混淆连接类和数据库连接。在
的事实上，它们并不相同。 Connection类是一个提供
编程接口的类，用于处理数据库连接。数据库
Connection是一个软件实体，包含有关daabase的数据
连接（即客户端应用程序和数据库），以及
$的过程b $ b在客户端应用程序和数据库之间进行通信。这是一个重要的
区别。为什么？因为在.Net平台上，关闭一个连接
并不能实现杀死数据库连接。它将Connection
类与数据库Connection本身断开连接，该连接本身保存在Connection
池中，供具有相同连接字符串的任何Connection类重用。 br />
换句话说，通过坚持你的Connection（类），你不会完成任何.Net平台并不能完成的任何事情。本身
已经存在，这是为了保持实际的数据库连接。而且，事实上，正如我之前所说，你在$ .b
$ b周围进行了昂贵的终端运行
连接模型，更有可能导致问题比你要解决的任何事情（正如你所看到的那样）。
因此，我建议你采取微软的话来说明。 Net
平台可以运行，并按照微软的规定使用它。也就是说，在您需要的时间和地点创建您的
连接类，并尽快关闭和/或处理它们，以避免出现各种问题你现在正在体验
。
-
HTH，
Kevin Spencer
Microsoft MVP
..Net开发人员
歧义具有一定的品质。
" Nick Gilbert" <镍*** @ newsgroup.nospam>写在留言中
新闻：％2 **************** @ tk2msftngp13.phx.gbl ...
我有一个asp.net应用程序，它使用Cassini从CD-ROM运行。如此，它只是单个用户。
应用程序在加载时连接到Access数据库，并且
始终保持相同的连接打开（因为它'单个用户，这不应该是一个问题。
代码中有逻辑确保连接在第一次自动打开时自动打开使用并将连接存储在Application变量中：
if（（cmd.Connection.State == ConnectionState.Closed）||
（cmd.Connection.State = = ConnectionState.Broken））cmd.Connection.Open（）;
然而，偶尔在IIS下测试我的应用程序时，会出现这个错误消息：
System.InvalidOperationException：
连接已经打开（状态=连接）。
或者这个：
ExecuteReader需要一个开放且可用的连接。
连接的当前状态是打开，执行。
但是如果状态是连接，你既不能打电话。打开也不能执行查询。那么如何在没有这些错误的情况下保持开放式连接？
我不明白为什么它在单个用户中处于Exectuting状态应用。是因为IIS是多线程的吗？也许它正在使用两个+线程来托管我的应用程序？
如果是这样，我怎么能阻止这些错误的发生？我不想打开
并在每次做某事时关闭一个新的连接，因为这太慢了（40ms）而有些页面需要100个查询，原因我没有时间在这里解释一下。使用单个连接 - 应用程序执行得非常好但有这些错误。有没有办法可以获得两个世界中最好的？
有人建议我禁用连接池，但这没有什么区别。
谢谢，
尼克......
-
请回复新闻组，否则我看不到你的消息。
>换句话说，通过持久化你的Connection（类），你不是完成任何.Net平台本身都不做的事情，这就是坚持实际数据库连接。
如果是这种情况，为什么如果我将
缓存我的连接类，我的性能提升了5000％？为什么还要打电话。如果不是真的要再次打开
数据库连接，打开大约需要30ms
来执行它？
如果你不相信我 - 亲自试试吧。在SQL Server上调用Connection.Open关于
2000次几乎没有时间。在访问它几秒钟需要
。也许你假设.NET处理Access连接
与处理SQL Server连接的方式相同？
每次调用查询时调用.Open使我的一些页面无法使用... b $ b慢 - 而目前它们只需几分之一秒。我可以
重写页面以便它使用更少的查询（对我的项目来说不是很可行）或者尝试以另一种方式修复这个bug。
尼克......
我自己没有使用Access但是根据
http://msdn.microsoft.com/library/de .. .taprovider.asp
和其他来源，我的理解是汇集应该有效，因为这是由提供商处理的
。
Jet DB的格式是什么？ （Access 97,2000,2003等级？）你是否认为读取/写入设备的速度相同？
我可能会给这个一试。只是想知道池是否有效
与Jet ...例如一个有趣的测试是尝试禁用
池。如果它是相同的，那就是汇集不起作用。如果它慢了，
这是因为原来的慢速结果来自于其他东西而不是
汇集（权限检查或其他任何）......
-
Patrice
" Nick Gilbert" <镍*** @ newsgroup.nospam> aécritdansle message de
news：eq ************** @ TK2MSFTNGP09.phx.gbl ...换句话说，通过坚持你的Connection（类），你不会完成任何.Net平台本身不能完成的任何事情，这是保持实际的数据库连接。
如果是这种情况，如果我缓存连接类，为什么我的性能提高了5000％？为什么还要打电话。如果你不打算再次打开
数据库连接，打开每次打电话需要大约30ms
如果你不喜欢不相信我 - 亲自尝试一下。在SQL Server上调用Connection.Open约2000次几乎没有时间。在Access上需要几秒钟。也许你假设.NET处理Access连接
与处理SQL Server连接的方式相同？
每次调用查询时调用.Open会使我的某些页面无法使用
慢 - 而目前他们只在几分之一秒内呈现。我可以重写页面以便它使用更少的查询（对于我的项目来说不太可行）或尝试以另一种方式修复此错误。
尼克......
Hi,
I have an asp.net application which runs from a CD-ROM using Cassini. As
such, it is single user only.
The application connects to an Access database when it is loaded, and
keeps the same connection open all the time (as it''s single user, this
shouldn''t be a problem).
There is logic in the code to ensure that the connection is
automatically opened the first time it is used and the connection is
stored in an Application variable:
if ((cmd.Connection.State == ConnectionState.Closed) ||
(cmd.Connection.State == ConnectionState.Broken)) cmd.Connection.Open();
However occasionally while testing my application under IIS, it comes up
with this error message:
System.InvalidOperationException:
The connection is already Open (state=Connecting).
Or this:
ExecuteReader requires an open and available Connection. The
connection''s current state is Open, Executing.
But if the state is Connecting, you can neither call .Open nor can you
execute a query. So how can I maintain an open connection without these
errors occuring?
I don''t understand why it is ever in the "Exectuting"
state in a single user application. Is it because IIS is multithreaded
and perhaps it''s using two+ threads to host my application?
If so, how can I stop these errors from happening? I don''t want to open
and close a new connection each time I do something because this is too
slow (40ms) and some pages require 100 queries for reasons I don''t have
time to explain here. Using a single connection - the application
performs very well but has these errors. Is there a way I can get the
best of both worlds?
Someone suggested that I disable connection pooling but this made no
difference.
Thanks,
Nick...
--
Please reply to the newsgroup or I won''t see your message.
Hi Nick,
I understand that you may think it is better to use a single Connection, but
you''re really trying to make an end-run around the .Net database Connection
model. The .Net platform is designed to make efficient use of database
Connections, and to reuse them all by itself. it does this by the process of
Connection Pooling.
You may be confusing the Connection Class and a database Connection. In
fact, they are not the same. A Connection class is a class that provides a
programming interface for working with database Connections. A database
Connection is a software entity that contains data about the daabase
Connection (that is, the client app and the database), and process for
communicating between the client app and the database. This is an important
distinction to make. Why? Because in the .Net platform, closing a Connection
doesn''t actuall kill a database Connection. It disconnects the Connection
class from the database Connection itself, which is held in a Connection
Pool for re-use by any Connection class that has the same Connection String.
In other words, by persisting your Connection (class), you''re not
accomplishing anything that the .Net platform doesn''t do all by itself
already, which is to persist actual database Connections. And, in fact, you
are, as I said earlier, making an expensive end-run around the .Net database
Connection model, which is more likely to cause problems than it is to solve
any (as you have seen).
Therefore, I would recommend that you take Microsoft''s word for how the .Net
platform works, and use it as prescribed by Microsoft. That is, create your
Connection classes when and where you need them, and close and/or dispose
them as quickly as possible, in order to avoid the sorts of problems you''re
experiencing right now.
--
HTH,
Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.
"Nick Gilbert" <Ni***@newsgroup.nospam> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...Hi,
I have an asp.net application which runs from a CD-ROM using Cassini. As
such, it is single user only.
The application connects to an Access database when it is loaded, and
keeps the same connection open all the time (as it''s single user, this
shouldn''t be a problem).
There is logic in the code to ensure that the connection is
automatically opened the first time it is used and the connection is
stored in an Application variable:
if ((cmd.Connection.State == ConnectionState.Closed) ||
(cmd.Connection.State == ConnectionState.Broken)) cmd.Connection.Open();
However occasionally while testing my application under IIS, it comes up
with this error message:
System.InvalidOperationException:
The connection is already Open (state=Connecting).
Or this:
ExecuteReader requires an open and available Connection. The
connection''s current state is Open, Executing.
But if the state is Connecting, you can neither call .Open nor can you
execute a query. So how can I maintain an open connection without these
errors occuring?
I don''t understand why it is ever in the "Exectuting"
state in a single user application. Is it because IIS is multithreaded
and perhaps it''s using two+ threads to host my application?
If so, how can I stop these errors from happening? I don''t want to open
and close a new connection each time I do something because this is too
slow (40ms) and some pages require 100 queries for reasons I don''t have
time to explain here. Using a single connection - the application
performs very well but has these errors. Is there a way I can get the
best of both worlds?
Someone suggested that I disable connection pooling but this made no
difference.
Thanks,
Nick...
--
Please reply to the newsgroup or I won''t see your message.
> In other words, by persisting your Connection (class), you''re notaccomplishing anything that the .Net platform doesn''t do all by itself
already, which is to persist actual database Connections.
If this is the case, why do I get a 5000% performance improvement if I
cache my connection class? Why also does calling .Open take about 30ms
to execute each time you call it if it''s not really having to open the
database connection again?
If you don''t belive me - try it yourself. Calling Connection.Open about
2000 times on SQL Server takes almost no time. On Access it takes
several seconds. Perhaps you''re assuming .NET handles Access connections
in the same way it handles SQL Server connections?
Calling .Open every time I call a query makes some of my pages unusably
slow - whereas currently they render in a fraction of a second. I could
either rewrite the page so it uses fewer queries (not very feasable for
my project) or try and fix this bug another way.
Nick...
Not using Access myself but according to
http://msdn.microsoft.com/library/de...taprovider.asp
and other sources, my understanding is that pooling should work as this is
handled by the provider.
What is the format of the Jet DB ? (Access 97, 2000, 2003 level ?) Do you
see the same slowness on a read/write device ?
I''ll likely give this a try. Just curious to know if pooling works or not
with Jet... For example an interesting test would be to try to disable
pooling. If it''s the same this is that pooling doesn''t work. If it''s slower,
this is because the original slow result comes from something else than
pooling (permissions checking or whatever else)...
--
Patrice
"Nick Gilbert" <Ni***@newsgroup.nospam> a écrit dans le message de
news:eq**************@TK2MSFTNGP09.phx.gbl...In other words, by persisting your Connection (class), you''re not
accomplishing anything that the .Net platform doesn''t do all by itself
already, which is to persist actual database Connections.
If this is the case, why do I get a 5000% performance improvement if I
cache my connection class? Why also does calling .Open take about 30ms
to execute each time you call it if it''s not really having to open the
database connection again?
If you don''t belive me - try it yourself. Calling Connection.Open about
2000 times on SQL Server takes almost no time. On Access it takes
several seconds. Perhaps you''re assuming .NET handles Access connections
in the same way it handles SQL Server connections?
Calling .Open every time I call a query makes some of my pages unusably
slow - whereas currently they render in a fraction of a second. I could
either rewrite the page so it uses fewer queries (not very feasable for
my project) or try and fix this bug another way.
Nick...
这篇关于ConnectionState问题 - 管理与Access数据库的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
