Data Reader Error Please Help its Urgent

[Amit D.Shinde]

Hello

I have on clsGlobal.cs File in which i have created many functions of
Fetching, Inserting Data In Database.. Using Ado.net AsP.net C#.net
Those Functions returns String, Dataset etc.
But NO WHERE I AM USING DATAREADER IN WHOLE PROJECT.

But i Get the Error While at a time two users logs in i.e. more than
one user try to access my webapplication.

The error is as follows:

"There is already an open DataReader associated with this Connection
which must be closed first. "
I know why this error occurs .. .due to not closing of connection but
everywhere i am closing the connection ..

Please Help Me i am sending some samples where error occurss..


public static DataSet ExecuteSPDS(SqlCommand pCmd)
{
DataSet sResult;
try
{
SqlDataAdapter da = new SqlDataAdapter(pCmd);
DataSet ds = new DataSet();
da.Fill(ds,"RECORD");
sResult = ds;
}
catch(System.Exception ex)
{
sResult = null;

}
return sResult;
}



========================================================
public static string ExecuteScalarQuery1(string query)
{
string Result;
//try
{
SqlCommand cmd = new SqlCommand(query,m_Connection);
if(m_Connection.State==ConnectionState.Closed)
{
m_Connection.Open();
}
Result=Convert.ToString(cmd.ExecuteScalar());
if(m_Connection.State==ConnectionState.Open)
{
m_Connection.Close();
}
//catch(System.Exception ex)
{
}
return Result;
}
}
====================================================

 

[DalePres]

I am just guessing here but probably the DataSet.Fill method calls a
DataReader to do the work.

There are a few possibilities that come to my mind. First, you might create
a non-static method or class so that each user has their own instance rather
than a shared instance. If you must use a static instance, you might try
using a lock statement to block others until the first user is finished with
the code section.

You could wrap your functionality in a using statement, such as
SqlConnection sqlConn = new SqlConnection()

{

}

This makes sure that the connection object is disposed when the using
section is exited.

Also, when I test for connection state, I test with if (conn.ConnectionState
!= ConnectionState.Closed). There are other conditions the ConnectionState
can be other than Open that are still not Closed.

Hope this helps,

DalePres
MCAD, MCDBA, MCSE

 

[Dumitru Sbenghe]

The error you get is because you open a DataReader on a connection where it
is already opened a DataReader and not closed. It isn't because you didn't
close the connection.

The DataReader you have opened must be closed before open another
DataReader. It's a wellknown problem with the SqlClient provider (solved in
ADO.NET 2.0) : that you can have more than one DataReader opened for a
connection.

And yes, the DataSet.Fill method use inside it a DataReader to read the
data. The ExecuteScalar, also, use a DataReader to read the data. So watch
your steps: close the commands, the readers, connections ... correctly ...
use try/finally or using statement for that (you code is a good example of
"don't" on freeing the resources).

I hope you don't use a global connection object. This statement "But i Get
the Error While at a time two users logs in i.e. more than one user try to
access my webapplication." suggest me that.


Dumitru

 

[NuTcAsE]

This is classic synchronization problem. Each user is operating on a
different thread, but the are using the same connection instance. If
your m_Connection is static, there can be a synchronization problem.
Consider this:

1. Uer A logs in
2. User B logs in
3. User A executes ExecuteSPDS
4. Before User A's ExecuteSPDS is finished UserB executes
ExecuteScalarQuery1. KABOOOM. The connection is already open and being
used soUserB will get an error.

First suggestion dont use a global connection. You gain nothing by doin
that and its bad programming practice. Connections should be short
lived and disposed off as quickly as possible. If you have to use a
global connection use locking like DalePres suggested. Wrap your
function with a lock { } statement. But if you do go down this approach
be warned: if a long running function already has a lock on the
connection, all other connection will wait till that function is
finished, bringing your app's performance down.

NuTcAsE

 

[William \(Bill\) Vaughn]

I concur.
The Fill method does use the DataReader behind the scenes. And, no, you
can't use an open connection to execute Fill or anything else--not until ADO
2.0 when MARS (might) solve this issue. Shared connections don't work
anyway. The system needs to manage a separate context for each operation.
You need to open and close connections as they are used. Fill will do this
for you IF you have not already opened the connection when it's executed.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Amit D.Shinde]

Thanks For The help
What I got from the above discussion is I am using same connection for
each User. and not disposing the connection immediately.
This is the connection property i hav defined in my clsGlobal.cs File
which is called in each and every form of my application using
"clsGlobal.connection".

Is there any way to make some changes in above code so i need not
change it anywhere else.






static SqlConnection m_Connection;
internal static SqlConnection Connection
{
get
{
return m_Connection;
}
}
public static void CreateConnection()
{
//m_Connection = new SqlConnection("data source=IRIPL022;initial
catalog=AdityaDisha; persist security info=False;user id=sa;packet
size=4096");
m_Connection = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connString"]);
}

 

[DalePres]

You may need to modify your class so that it is not static, but rather has
to be instantiated for each user.

If you may have some functionality that you want to be available without
instantiating the class so you may find that you just split out the data
access portion to a separate class.

If ExecuteSPDS is called from an instance class and not a static class, you
can just create your connection there. Since you are already passing a
SqlCommand to your ExecuteSPDS method call, just create the new
SqlConnection before calling ExecuteSPDS. Set the Connection property of
the SqlCommand object that you pass in your call to ExecuteSPDS.

No matter what you do, using the global connection object is going to be a
problem. The only way to keep your code from breaking with a global
connection is going to be the lock method I mentioned before but as NuTcAsE
stated, all other users will block while waiting their turn at the global
object.

So, in summary, the answer to your question about how to modify the code
below to solve the problem, well the news isn't good. The modification is
to delete it.

HTH

DalePres
MCAD, MCDBA, MCSE