ASP.NET 1.1 datareader already opened : conflict between users

[jason]

Hi,

{asp .net 1.1, sp 2)

I am facing problems with multi users accessing the same page. There is a
datagrid in the webform. The "datareader already opened error/ object
reference not found" errors will occur randomly when multi users are
clicking page number (paging), any page number.

The connection object is declared private and connection is closed
immediately after fill command. I am testing using two separate machines and
clicking the page number simultaneously.

Thanks.

Following is the snippet of the data access layer :

////////////////////////////////////////////////////////////
/////// base class - abstract

/////////////////////////////////////////////////////////////

public abstract class DbObject

{

private SqlConnection Connection;

private string connectionString;

public DbObject( string newConnectionString )

{

connectionString = newConnectionString;

Connection = new SqlConnection( connectionString );

}



protected DataSet RunProcedure(string storedProcName, IDataParameter[]
parameters, string tableName )

{

try

{

DataSet ds = new DataSet();

Connection.Open();

SqlDataAdapter sqlDA = new SqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );

sqlDA.Fill( dataSet, tableName );

return ds;

}

finally

{

if (Connection.State == ConnectionState.Open) Connection.Close();

}

}

/////////////////////////////////////////////////////////////////

The data class :

/////////////////////////////////////////////////////////////////

public class SqlProductController : DbObject,
Modules.Product.DAL.IProductController

{

private int appID;

public SqlProductController(string newConnectionString, int
appID):base(newConnectionString){ this.appID = appID;}





public DataSet GetProductList(string SearchText, int ID)

{

SqlParameter[] parameters = { new SqlParameter("@SearchText",
SqlDbType.NVarChar, 1000),

new SqlParameter("@PartnerId", SqlDbType.Int, 4) };

parameters[0].Value = SearchText;

parameters[1].Value = ID;


try

{

return RunProcedure("sp_SearchProdList", parameters, "ProductList");

}

catch (SqlException e)

{

throw new AppException("An error executing the Product_GetProdList
method", e );

}



}





}

 

[Islamegy®]

I'm sure you didn't close the reader. May be it throw exception before
closing the reader, always use try - catch - finally when use Datareader..
You can always Check connectionstate in the finally code block and close it.

 

[jason]

thank you Islamegy

As you can see in my code, I did use try except finally to close the
connection, and the connection is not shared. I am not using DataReader
directly either, I guess the DataAdapter is using DataReader internally to
access DB, correctly me if i am wrong.

Just couldn't figure out what went wrong, as this supposed to be a straight
forward code.

I'm sure you didn't close the reader. May be it throw exception before
closing the reader, always use try - catch - finally when use Datareader..
You can always Check connectionstate in the finally code block and close
it.

Hi,

{asp .net 1.1, sp 2)

I am facing problems with multi users accessing the same page. There is a
datagrid in the webform. The "datareader already opened error/ object
reference not found" errors will occur randomly when multi users are
clicking page number (paging), any page number.

The connection object is declared private and connection is closed
immediately after fill command. I am testing using two separate machines
and clicking the page number simultaneously.

Thanks.

Following is the snippet of the data access layer :

////////////////////////////////////////////////////////////
/////// base class - abstract

/////////////////////////////////////////////////////////////

public abstract class DbObject

{

private SqlConnection Connection;

private string connectionString;

public DbObject( string newConnectionString )

{

connectionString = newConnectionString;

Connection = new SqlConnection( connectionString );

}



protected DataSet RunProcedure(string storedProcName, IDataParameter[]
parameters, string tableName )

{

try

{

DataSet ds = new DataSet();

Connection.Open();

SqlDataAdapter sqlDA = new SqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );

sqlDA.Fill( dataSet, tableName );

return ds;

}

finally

{

if (Connection.State == ConnectionState.Open) Connection.Close();

}

}

/////////////////////////////////////////////////////////////////

The data class :

/////////////////////////////////////////////////////////////////

public class SqlProductController : DbObject,
Modules.Product.DAL.IProductController

{

private int appID;

public SqlProductController(string newConnectionString, int
appID):base(newConnectionString){ this.appID = appID;}





public DataSet GetProductList(string SearchText, int ID)

{

SqlParameter[] parameters = { new SqlParameter("@SearchText",
SqlDbType.NVarChar, 1000),

new SqlParameter("@PartnerId", SqlDbType.Int, 4) };

parameters[0].Value = SearchText;

parameters[1].Value = ID;


try

{

return RunProcedure("sp_SearchProdList", parameters, "ProductList");

}

catch (SqlException e)

{

throw new AppException("An error executing the
Product_GetProdList method", e );

}



}





}

 

[jason]

problem solved....

one of the base class controller was declared as static.

Thanks.

thank you Islamegy

As you can see in my code, I did use try except finally to close the
connection, and the connection is not shared. I am not using DataReader
directly either, I guess the DataAdapter is using DataReader internally to
access DB, correctly me if i am wrong.

Just couldn't figure out what went wrong, as this supposed to be a
straight forward code.

I'm sure you didn't close the reader. May be it throw exception before
closing the reader, always use try - catch - finally when use
Datareader..
You can always Check connectionstate in the finally code block and close
it.

Hi,

{asp .net 1.1, sp 2)

I am facing problems with multi users accessing the same page. There is
a datagrid in the webform. The "datareader already opened error/ object
reference not found" errors will occur randomly when multi users are
clicking page number (paging), any page number.

The connection object is declared private and connection is closed
immediately after fill command. I am testing using two separate machines
and clicking the page number simultaneously.

Thanks.

Following is the snippet of the data access layer :

////////////////////////////////////////////////////////////
/////// base class - abstract

/////////////////////////////////////////////////////////////

public abstract class DbObject

{

private SqlConnection Connection;

private string connectionString;

public DbObject( string newConnectionString )

{

connectionString = newConnectionString;

Connection = new SqlConnection( connectionString );

}



protected DataSet RunProcedure(string storedProcName, IDataParameter[]
parameters, string tableName )

{

try

{

DataSet ds = new DataSet();

Connection.Open();

SqlDataAdapter sqlDA = new SqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand( storedProcName, parameters );

sqlDA.Fill( dataSet, tableName );

return ds;

}

finally

{

if (Connection.State == ConnectionState.Open) Connection.Close();

}

}

/////////////////////////////////////////////////////////////////

The data class :

/////////////////////////////////////////////////////////////////

public class SqlProductController : DbObject,
Modules.Product.DAL.IProductController

{

private int appID;

public SqlProductController(string newConnectionString, int
appID):base(newConnectionString){ this.appID = appID;}





public DataSet GetProductList(string SearchText, int ID)

{

SqlParameter[] parameters = { new SqlParameter("@SearchText",
SqlDbType.NVarChar, 1000),

new SqlParameter("@PartnerId", SqlDbType.Int, 4) };

parameters[0].Value = SearchText;

parameters[1].Value = ID;


try

{

return RunProcedure("sp_SearchProdList", parameters, "ProductList");

}

catch (SqlException e)

{

throw new AppException("An error executing the
Product_GetProdList method", e );

}



}





}