数据库:Oracle 817，OracleClient,net framework 1.1
以下是我的公共数据访问类（我想应该代码上没有什么不对的。用完后链接也都是释放的）
using System.Data.OracleClient;
public class ComFun
{
static public IDataReader ComFun_ExeReader(string Sql)
{
OracleConnection Conn=new OracleConnection(ConfigurationSettings.AppSettings["OracleConnectionString"]);
OracleCommand Cmd=new OracleCommand();
Cmd.CommandText=Sql;
Cmd.Connection=Conn;
Conn.Open();
OracleDataReader Reader=Cmd.ExecuteReader(CommandBehavior.CloseConnection);
return Reader;
}
static public DataSet ComFun_ExeDataset(string Sql)
{
OracleConnection Conn=new OracleConnection(ConfigurationSettings.AppSettings["OracleConnectionString"]);
OracleDataAdapter Ad=new OracleDataAdapter(Sql,Conn);
DataSet ds=new DataSet();
Conn.Open();
Ad.Fill(ds);
Conn.Close();
return ds;
}
static public Object ComFun_ExeScalar(string Sql)
{
OracleConnection Conn=new OracleConnection(ConfigurationSettings.AppSettings["OracleConnectionString"]);
OracleCommand Cmd=new OracleCommand();
Cmd.CommandText=Sql;
Cmd.Connection=Conn;
Conn.Open();
Object Obj=Cmd.ExecuteScalar();
Conn.Close();
return Obj;
}
}
在其他地方我是这么用这个公共类的
try{
string sql="select person_name,person_age from person";
IDataReader Reader=ComFun.ComFun_ExeReader(sql);
DropList.DataSource=Reader;
DropList.DataText.....="person_name";
DropList.DataBind();
Reader.Close();Reader.Dispose();
Session["Result"]="Success";
}
catch(Exception E)
{
Session["Result"]=E.Message;
}
finally
{
Response.Redirect("Result.aspx");
}
结果程序有时会出现"ORA-03114: not connected to ORACLE"的异常。
大多数时候又是运行好好的。没有规律。为什么？
传说当年Asp时代是每次用完后就释放掉的。第2次用时是重新登录数据库，然后接链接，所以不会有这个问题。难到是.net缓冲池的问题？
我在网上找到这个文章
http://www.dotnet247.com/247reference/msgs/49/248580.aspx
其中 Angel Saenz-Badillos[MS] (VIP)说
I am sorry this one is my fault and it is a very bad bug.!!!!!!!!!!!!!!!!!!!! 
难到这个是MS的BUG？他是写缓冲池代码的作者？
我应该怎么样才能避免出现这个错误。原文摘要如下
"
Michael Bachar
Hi,
I'm using ADO.Net in C# with Oracle9i Release 9.2.0.4.0
when loosing the connection with the Oracle database server the connections
doesn't recover and always returning the following exception even when the
connection has been restore:
"ORA-03114: not connected to ORACLE". I'm closing the connection properly in
that it will return to the connection pool. Here is an example code:
using(OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
using(OracleCommand cmd = new OracleCommand(sql, conn))
{
object obj = cmd.ExecuteScalar();
}
}
This should close the connection properly. In the MSDN it is mention that if
the connection pooler detect that the connection with the server has been
severed it will remove the connection from the connection pool. It appears
that this is not happening an I always get bad connections from the pool,
even after the connection with the database server has been restore. How can
I solve this? What is the proper way to recover from connection lost with
database server?
Thanks,
Michael.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Angel Saenz-Badillos[MS] (VIP)!!!!!!!!!!!
Michael,
I am sorry this one is my fault and it is a very bad bug.!!!!!!!!!!!!!!!!!!!! The problem here
is that our pooler fails to understand that a 03114 exception means we
should not put the connection back in the pool. We have a QFE that fixes
this problem by discarding the connection in the following Oracle
exceptions. Please let me know if you know of any other exceptions that also
result in a connection no longer being valid.
oracle exceptions checked
case 18: // max sessions exceeded,
case 19: // max session licenses exceeded
case 24: // server is in single process mode
case 28: // session has been killed
case 436: // oracle isn't licensed
case 1012 //not logged on error
case 1033: // startup/shutdown in progress
case 1034: // oracle not available
case 1075: // currently logged on
case 3113: // end-of-file on communication channel
case 3114: // not connected to ORACLE
case 12154: // can't resolve service name
case 12xxx //any error starting with 12 thousand
To get the fix please contact PSS directly and request QFE 830173
"
我不清楚如果每次都在用完
connect.close()后。再写上一句connect.dispose()。能不能把缓冲池里的链接给删掉？因为我宁可让它每次登录重新取个链接。因为稳定性比性能更重要。
今天最终在老外的指点下找到了这个贴子
http://support.microsoft.com/default.aspx?scid=kb;en-us;830173
晕倒。果然是MS的BUG。但是我搞不到那个HOTFIX文件呀。这下歇菜了。
我真弄不明白。MS怎么到了2004年6月8号才发布这个文章。：（
说起来现在还是2004年6月。唉。
中BUG也有吃螃蟹的。
http://support.microsoft.com/default.aspx?scid=kb;en-us;830173
FIX: A pooled connection is not disposed by Microsoft .NET Managed Provider for Oracle when an exception occurs
SYMPTOMS
When you try to use a database connection that is no longer valid to connect to an Oracle database, you experience unexpected behavior. For example, you might be trying to connect during server restart or during a connection break. Although the expected behavior to discard the bad connection and try to use a new physical connection from the pool, the actual behavior is that you can use the same bad connection that you were using before the session ended.RESOLUTION
A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that this article describes. Apply it only to systems that are experiencing this specific problem.To resolve this problem, contact Microsoft Product Support Services to obtain the hotfix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.Date Time Version Size File name ----------------------------------------------------------------------------- 24-May-2004 20:47 1.1.4322.1046 303,104 System.data.oracleclient.dll 26-May-2004 00:50 1.0.859.2574 1,126,192 Ndp1.1-kb830173-x86.exe 27-May-2004 22:54 5.4.15.0 465,648 Windowsserver2003-kb830173-x86-enu.exe 14-Oct-2003 16:55 1.1.4322.957 303,104 System.data.oracleclient.dll 15-Oct-2003 00:58 1.0.0.0 1,072,808 Ndp1.1-kb830173-x86.exe 17-Oct-2003 23:00 989,452 Ndp1.1-kb830173-x86.zip
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article.MORE INFORMATION
Steps to reproduce the behavior
The following series demonstrates that even after a .NET application is disconnected from the Oracle server, Microsoft .NET Managed Provider for Oracle does not dispose the connection from the connection pool:- Create an ADO.NET application that uses the Microsoft Oracle .NET Provider to access the Oracle back-end, compile, and then run the application.
- Connect to the Oracle Server by means of the ADO.NET application that you created in step 1.
- Close the Oracle connection either by stopping the Oracle server or by disconnecting the client from the physical network.
However, the actual behavior is that the connection that encounters the problem is returned to the Oracle client pool. This connection can later be retrieved from the pool for the .NET application the next time that the application tries to open a connection to the server.
REFERENCES
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:824684 Description of the Standard Terminology That Is Used to Describe Microsoft Software Updates
310985 How to use the ODBC .NET Managed Provider in Visual Basic .NET and connection strings
The information in this article applies to:
- Microsoft .NET Framework 1.1
| Last Reviewed: | 6/8/2004 (3.0) |
| Keywords: | kbDataPooling KbClientServer kbUser kbQuery kbconnectivity kbfix kbbug KB830173 kbAudDeveloper kbAudEndUser |