共享变量中的数据库连接对象 [英] Database connection object in shared variable

问题描述

您好


让我们说因为几个原因我有一个ASP.Net应用程序

有一个数据库访问类存储一个

共享成员中的数据库连接：

公共类dataaccess

私有共享数据库as System.Data.SqlClient.SqlConnection


公共共享函数GetItems作为数据集

...

结束函数


public共享子InsertItems as dataset

...

end sub


end class

I明白我不应该这样做，而是尽可能晚地打开一个

连接并尽快关闭它事情

但在这种情况下我不能。


我也不认为将数据库连接对象放在共享中

变量可能会导致问题，因为对共享成员的访问不是线程安全。


现在......只有两次我有这样一个可预测的发生问题和

引发的异常是已经有一个与此连接相关的开放DataReader

，必须先关闭。


我尝试以编程方式通过调用

多个页面来执行复制，这些页面将执行包含

WAITFOR命令的存储过程或执行存储过程返回

数千条记录。

在这两种情况下，我都没有设法抛出异常。


哪个导致我的问题是：在哪种情况下，在共享成员中使用数据库连接对象的事实将导致

可预测的问题？


PS：我相信在我有异常的两次那里

已经开放......抛出的是当执行存储的

过程无法执行时因其他问题（例如执行权限）而导致

异常。 />
感谢您的帮助


Daniel

Hi there

Let''s say due to several reasons I have an ASP.Net application that
has a database access class that stores a database connection in a
shared member :
public class dataaccess

private shared DB as System.Data.SqlClient.SqlConnection

public shared function GetItems as dataset
...
end function

public shared sub InsertItems as dataset
...
end sub

end class
I understand that I shouldn''t be doing this and instead do the "open a
connection as late as possible and close it as soon as possible" thing
but in that case I can''t.

I also undestand that putting database connection object in a shared
variable can lead to problems because access to shared members is not
thread safe.

Now...Only two times I had such a "predictable" issue happening and
the exception thrown was "There is already an open DataReader
associated with this Connection which must be closed first.".

I tried to programmatically replicate this by either running calling
multiple pages that will execute a stored procedure containing a
WAITFOR command or that execute a stored procedure that returns
thousands of records.
In both cases I didn''t manage to get the exception thrown.

Which lead to my question which is: under which circumstances the fact
of using a database connection object in a shared member will lead to
predictable problems ?

PS: I believe that in the two times where I had the exception "There
is already an open..." thrown was when the execution of the stored
procedure couldn''t be performed and therefore resulted into an
exception due to other issues (execute permission for instance).
Thanks for your help

Daniel

推荐答案

您好Daniel，

我将不得不反对你的前提：

Hi Daniel,

I''m going to have to disagree with your premise:

我明白我不应该这样做，而是做 ;尽可能晚地打开
连接并尽快关闭它事情
但在那种情况下，我不能。


不能在任何程序员的词汇中都不应该是一个词。

当然你可以。你只是不知道怎么做。


根据纠正的前提，整个问题没有实际意义。我会

建议请求，如何使用设计使用的

的连接方式。


换句话说，它是什么似乎阻止你以设计使用的方式使用连接

？


-

HTH，

Kevin Spencer

..Net开发人员

微软MVP

我得到报酬的好钱

解决难题为生活


Daniel Fernandes <哒************** @ sesame.co.uk>在消息中写道

新闻：eb ************************ @ posting.google.com ...你好

让我们说由于几个原因，我有一个ASP.Net应用程序，它有一个数据库访问类，它在一个
共享成员中存储数据库连接：

公共类dataaccess
私有共享数据库as System.Data.SqlClient.SqlConnection

公共共享函数GetItems as dataset
...
结束函数

公共共享子InsertItems作为数据集
...
结束子
结束类

我明白我不应该这样做，而是尽可能晚地打开
连接并尽快关闭。事情
但在那种情况下我不能。

我也不认为将数据库连接对象放在共享的
变量中会导致问题，因为访问共享成员不是
线程安全。

现在......只有两次，我有这样一个可预测的发生的问题和抛出的异常是已经有一个与此连接关联的开放DataReader
必须先关闭。

我试图以编程方式复制此通过运行调用
多个页面来执行包含
WAITFOR命令的存储过程，或者执行一个返回数千条记录的存储过程。
在这两种情况下我都没有'设法让异常抛出。

导致我的问题是：在哪种情况下，在共享成员中使用数据库连接对象的事实将导致
可预测的问题？

PS：我相信在我有异常的两次那里
已经开放......抛出的是当执行存储的
程序无法执行时因其他问题（例如执行权限）而导致
异常。

感谢你的帮助

Daniel

I understand that I shouldn''t be doing this and instead do the "open a
connection as late as possible and close it as soon as possible" thing
but in that case I can''t.
"Can''t" is not a word that should be in any programmer''s vocabulary. Of
course you can. You just don''t know how to.

Based upon the corrected premise, the entire question is moot. I would
suggest asking instead, how you CAN work with a Connection in the way it was
designed to be used.

In other words, what is it that seems to prevent you from using a Connection
in the way it was designed to be used?

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

"Daniel Fernandes" <da**************@sesame.co.uk> wrote in message
news:eb************************@posting.google.com ... Hi there

Let''s say due to several reasons I have an ASP.Net application that
has a database access class that stores a database connection in a
shared member :
public class dataaccess

private shared DB as System.Data.SqlClient.SqlConnection

public shared function GetItems as dataset
...
end function

public shared sub InsertItems as dataset
...
end sub

end class
I understand that I shouldn''t be doing this and instead do the "open a
connection as late as possible and close it as soon as possible" thing
but in that case I can''t.

I also undestand that putting database connection object in a shared
variable can lead to problems because access to shared members is not
thread safe.

Now...Only two times I had such a "predictable" issue happening and
the exception thrown was "There is already an open DataReader
associated with this Connection which must be closed first.".

I tried to programmatically replicate this by either running calling
multiple pages that will execute a stored procedure containing a
WAITFOR command or that execute a stored procedure that returns
thousands of records.
In both cases I didn''t manage to get the exception thrown.

Which lead to my question which is: under which circumstances the fact
of using a database connection object in a shared member will lead to
predictable problems ?

PS: I believe that in the two times where I had the exception "There
is already an open..." thrown was when the execution of the stored
procedure couldn''t be performed and therefore resulted into an
exception due to other issues (execute permission for instance).
Thanks for your help

Daniel

当连接仍然有一个打开的对象附加到
$ b时会发生这种情况$ b它。这可能是一个长时间运行的命令，或者你正在谈论的问题

（打开Reader）。


你可以测试抛出的异常类型，但是，并确定它是否是一个

执行权限。检查System.Data中的异常对象。在

中，你可以在使用SqlClient时获取错误集合，并且

查询返回的错误（SqlErrorCollection类）。

- -


Gregory A. Beamer

MVP; MCP：+ I，SE，SD，DBA


***************************

在盒子外面思考！

*************************** >

Daniel Fernandes写道：

eIt will happen any time a connection still has an open object appended to
it. This could be a long running command, or the issue you are talking about
(open Reader).

You can test the type of Exception thrown, however, and determine if it is a
execution permission. Check the exception objects in System.Data. In
addition, you can pull the errors collection when you are using SqlClient and
query the errors returned (SqlErrorCollection class).
---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"Daniel Fernandes" wrote:

你好

让我们说因为几个原因我有一个ASP.Net应用程序
有一个数据库访问类将数据库连接存储在
共享成员中：

公共类dataaccess
私有共享数据库as System.Data.SqlClient.SqlConnection
...
结束函数

公共共享子InsertItems作为数据集
...
end sub

结束课

我明白我不应该这样做，而是尽可能晚地打开一个
连接并尽快关闭它可能"事情
但在那种情况下我不能。

我也不认为将数据库连接对象放在共享的
变量中会导致问题，因为访问共享成员不是
线程安全。

现在......只有两次，我有这样一个可预测的发生的问题和抛出的异常是已经有一个与此连接关联的开放DataReader
必须先关闭。

我试图以编程方式复制此通过运行调用
多个页面来执行包含
WAITFOR命令的存储过程，或者执行一个返回数千条记录的存储过程。
在这两种情况下我都没有'设法让异常抛出。

导致我的问题是：在哪种情况下，在共享成员中使用数据库连接对象的事实将导致
可预测的问题？

PS：我相信在我有异常的两次那里
已经开放......抛出的是当执行存储的
程序无法执行时因其他问题（例如执行权限）而导致
异常。

感谢你的帮助

Daniel

Hi there

Let''s say due to several reasons I have an ASP.Net application that
has a database access class that stores a database connection in a
shared member :
public class dataaccess

private shared DB as System.Data.SqlClient.SqlConnection

public shared function GetItems as dataset
...
end function

public shared sub InsertItems as dataset
...
end sub

end class
I understand that I shouldn''t be doing this and instead do the "open a
connection as late as possible and close it as soon as possible" thing
but in that case I can''t.

I also undestand that putting database connection object in a shared
variable can lead to problems because access to shared members is not
thread safe.

Now...Only two times I had such a "predictable" issue happening and
the exception thrown was "There is already an open DataReader
associated with this Connection which must be closed first.".

I tried to programmatically replicate this by either running calling
multiple pages that will execute a stored procedure containing a
WAITFOR command or that execute a stored procedure that returns
thousands of records.
In both cases I didn''t manage to get the exception thrown.

Which lead to my question which is: under which circumstances the fact
of using a database connection object in a shared member will lead to
predictable problems ?

PS: I believe that in the two times where I had the exception "There
is already an open..." thrown was when the execution of the stored
procedure couldn''t be performed and therefore resulted into an
exception due to other issues (execute permission for instance).
Thanks for your help

Daniel

2004年9月29日07:55:06 -0700， da ************** @ sesame.co.uk （Daniel

Fernandes）写道：

On 29 Sep 2004 07:55:06 -0700, da**************@sesame.co.uk (Daniel
Fernandes) wrote:

我明白我不应该这样做而是做打开一个
尽可能晚地连接并尽快关闭它事情
但在那种情况下我不能。

I understand that I shouldn''t be doing this and instead do the "open a
connection as late as possible and close it as soon possible" thing
but in that case I can''t.

嗨丹尼尔：


任何机会都是你试图将数据库的连接数限制为

？如果是这样，你可以在

连接字符串中调整连接池大小。


也许如果你告诉我们为什么你必须使用shaed连接我们可以

想到其他选择。我会不惜一切代价避免目前的实施

。线程错误往往是微妙的，有时会保持潜伏，直到最糟糕的时间。请问GE Energy！
http://www.securityfocus.com/news / 8412


-

Scott
http://www.OdeToCode.com/

这篇关于共享变量中的数据库连接对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！