very strange bug

[André Freitas]

I got a namespace, with a class, with a constructor, who have the follow
method:

public DataTable ExecuteDataTable(string transactSQL, bool isProcedure,
List<DbParameter> parameterList)
{
DbConnection dbConnection = CreateConnection();
dbConnection.Open();
DbCommand dbcommand = dbConnection.CreateCommand();
dbcommand.CommandText = transactSQL;
if (isProcedure)
{
dbcommand.CommandType = CommandType.StoredProcedure;
}
if (parameterList != null)
{
dbcommand.Parameters.AddRange(parameterList.ToArray());
}
DbDataReader dbDataReader = dbcommand.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(dbDataReader);
dbDataReader.Close();
dbDataReader.Dispose();
dbcommand.Dispose();
dbConnection.Close();
dbConnection.Dispose();
return dataTable;
}

In a .cs page, I have a instance, calling the method:

Database database = new Database("databasename");
List<DbParameter> list = new List<DbParameter>();
list.Add(database.CreateParameter("storeId", 154));
DataTable departaments = database.ExecuteDataTable("SELECT a FROM b WHERE
lojaid = @storeId", false, list);
DataTable sections = database.ExecuteDataTable("SELECT a FROM c WHERE lojaid
= @storeId", false, list);

I got a bug, telling me "another SqlParameterCollection already have a
SqlParameter", when the method is called for the second time. (???)
After some deployment, i discover the error here:

dbcommand.Parameters.AddRange(parameterList.ToArray());

I think its very strange, beucase im using a istance. Some time later,
recreating list before each execute, it works well.
A hour later I found a solution:

dbcommand.Parameters.Clear();
dbcommand.Dispose();

It means, when I dispose the dbcommand, something hapens with the list.
More: in debug mode, the list seems very well in the second time.

Anyone knows why?

 

[pers]

Check your dbConnection.CreateCommand();
I think CreateCommand returns same reference for all.

 

[André Freitas]

Check your dbConnection.CreateCommand();

I think CreateCommand returns same reference for all.

Nop:

private DbConnection CreateConnection(string connectionString)
{
DbConnection dbConnection = new SqlConnection(connectionString);
return dbConnection;
}

 

[Alvin Bruney - ASP.NET MVP]

This behavior is by design. The DBparamater retains state information. You
need to first call clear on it if you intend to reuse it.