Connection.Open fails intermittently with OracleClient

[Ian Cox]

Hello.
I am using the following code to open a connection to an oracle
database.
using (OracleConnection cn = new OracleConnection())
{
cn.ConnectionString = connectionString;
cn.Open();

//call the overload that takes a connection in place of the connection
string
return ExecuteDataset(cn, commandType, commandText,
commandParameters);
}

(this code is actually from Tyler Jensen's OracleHelper class)

The code works great except it occasionally fails on the cn.Open()
line.
The error it gets is "Object reference not set to an instance of an
object". I can call the routine all day long and then it will fail and
keep failing until I restart IIS (??)

Can anybody help me get around this problem? I am about to go to
production with a system and this is really worrying.

Thanks in advance
Ian

 

[Angel Saenz-Badillos[MS]]

Do you have a fully working repro that I could run locally? Does the error
go away if you turn pooling off?

I see nothing wrong with the code as posted, and failing in cn.Open is not a
normal pooling bug.

 

[Ian Cox]

Thanks for your reply.

Do you have a fully working repro that I could run locally?

Here goes:
public static bool validateLogin(string Username, string Password){
string sqlName = "sp_SelectUserDetails";
bool returnVal = false;

// setup a parameter array of SqlParameter objects
OracleParameter[] sqlParams = new OracleParameter[4];
sqlParams[0] = new OracleParameter("i_UserName", OracleType.VarChar,
25);
sqlParams[0].Value = Username;
sqlParams[1] = new OracleParameter("i_Password", OracleType.VarChar,
50);
sqlParams[1].Value = Password;
sqlParams[2] = new OracleParameter("o_usercsr", OracleType.Cursor);
sqlParams[2].Direction = ParameterDirection.Output;
sqlParams[3] = new OracleParameter("o_groupcsr", OracleType.Cursor);
sqlParams[3].Direction = ParameterDirection.Output;

try
{
dsUserData = OraHelper.ExecuteDataset(getOraConnStr(),
CommandType.StoredProcedure, sqlName,sqlParams);

if (dsUserData.Tables[0].Rows.Count > 0)
{
returnVal = true;
}
else
{
returnVal = false;
}

}
catch(Exception e)
{
string errorMessage = e.Message + "\n" + e.Source + "\n";
throw(new Exception(errorMessage,e.InnerException));
}

return returnVal;
}

private static string getOraConnStr()
{
//GET THE DATABASE CONNECTION STRING
//e.g of what I have been using "Password=pwd1;User ID=user1;Data
Source=DBname;"
}

//This is the OraHelper.ExecuteDataset function
internal static DataSet ExecuteDataset(string connectionString,
CommandType commandType, string commandText, params OracleParameter[]
commandParameters)
{
//create & open a OracleConnection, and dispose of it after we
are done.
using (OracleConnection cn = new
OracleConnection(connectionString))
{
cn.Open(); //THIS IS WHERE IT FAILS
//call the overload that takes a connection in place of the
connection string
return ExecuteDataset(cn, commandType, commandText,
commandParameters);
}
}

Does the error
go away if you turn pooling off?

How do I do that?

The real problem is that I can't recreate the problem at will. It
hasn't happened at all today but happened twice yesterday. My worry is
that when we go to production (this weekend) the problem will appear
again. At the moment my only solution when it occurs is to restart
IIS.

Thanks in advance for your help
Cheers
Ian

 

[Angel Saenz-Badillos[MS]]

Ian, I am not sure that I have a good repro, but I have set something up and
it is currently running constantly I will let you know if I get a repro.
(doubtfull by looking at the code).

Can you post more information regarding your setup? what version of the
OracleClient provider are you using, version of Oracle Client installed in
your machine and Server.

To turn pooling off you just have to add Pooling=false to your connection
string.

As far as the code itself, it is good to use the "using" keyword, but the
most effective way to fill a dataset is to pass it a closed connection.When
the adapter sees that the connection is closed it will automatically open
the connection fill the dataset and close the connection for you as fast as
possible.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Thanks for your reply.

Do you have a fully working repro that I could run locally?

Here goes:
public static bool validateLogin(string Username, string Password){
string sqlName = "sp_SelectUserDetails";
bool returnVal = false;

// setup a parameter array of SqlParameter objects
OracleParameter[] sqlParams = new OracleParameter[4];
sqlParams[0] = new OracleParameter("i_UserName", OracleType.VarChar,
25);
sqlParams[0].Value = Username;
sqlParams[1] = new OracleParameter("i_Password", OracleType.VarChar,
50);
sqlParams[1].Value = Password;
sqlParams[2] = new OracleParameter("o_usercsr", OracleType.Cursor);
sqlParams[2].Direction = ParameterDirection.Output;
sqlParams[3] = new OracleParameter("o_groupcsr", OracleType.Cursor);
sqlParams[3].Direction = ParameterDirection.Output;

try
{
dsUserData = OraHelper.ExecuteDataset(getOraConnStr(),
CommandType.StoredProcedure, sqlName,sqlParams);

if (dsUserData.Tables[0].Rows.Count > 0)
{
returnVal = true;
}
else
{
returnVal = false;
}

}
catch(Exception e)
{
string errorMessage = e.Message + "\n" + e.Source + "\n";
throw(new Exception(errorMessage,e.InnerException));
}

return returnVal;
}

private static string getOraConnStr()
{
//GET THE DATABASE CONNECTION STRING
//e.g of what I have been using "Password=pwd1;User ID=user1;Data
Source=DBname;"
}

//This is the OraHelper.ExecuteDataset function
internal static DataSet ExecuteDataset(string connectionString,
CommandType commandType, string commandText, params OracleParameter[]
commandParameters)
{
//create & open a OracleConnection, and dispose of it after we
are done.
using (OracleConnection cn = new
OracleConnection(connectionString))
{
cn.Open(); //THIS IS WHERE IT FAILS
//call the overload that takes a connection in place of the
connection string
return ExecuteDataset(cn, commandType, commandText,
commandParameters);
}
}

Does the error
go away if you turn pooling off?

How do I do that?

The real problem is that I can't recreate the problem at will. It
hasn't happened at all today but happened twice yesterday. My worry is
that when we go to production (this weekend) the problem will appear
again. At the moment my only solution when it occurs is to restart
IIS.

Thanks in advance for your help
Cheers
Ian

 

[Ian Cox]

Can you post more information regarding your setup? what version of the
OracleClient provider are you using, version of Oracle Client installed in
your machine and Server.

For the OracleClient the file version is 1.0.1012.0
and .NET reports the version as 1.0.3300.0
I am using Oracle 8i client software talking to a Oracle 9i database
(maybe this is part of the issue?)

Next time the error occurs I will try turning pooling off to see what
happens.

Thanks again for your help
Ian

 

[Angel Saenz-Badillos[MS]]

I have not been able to repro the problem yet, but if you are using v1.0
there is one important Connection related QFE that you should definitelly
consider getting. Contact PSS for the fix for Q330126
http://support.microsoft.com/?id=330126

Under some circumstances (antivirus running on your web directories, process
unloads in asp.net) we were leaking connections.

 

[Ian Cox]

Ok. Thanks for that. We are using 1.1 though so I'm assumine we don't
need the fix.

It still hasn't happened since last Thursday, so hopefully, maybe it
was a temporary issue (last famous words).

Thanks for your help
Ian

 

[Angel Saenz-Badillos[MS]]

You are correct, this problem is fixed in 1.1. I still have not been able to
see the problem on my local repro. please let me know if you have any
thoughts on how to repro this.

Thanks,