very strange bug

Discussion in 'ASP .Net' started by André Freitas, Feb 2, 2010.

  1. 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?
     
    André Freitas, Feb 2, 2010
    #1
    1. Advertising

  2. André Freitas

    pers Guest

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

    "André Freitas" <andrefreitas> wrote in message
    news:...
    >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, Feb 3, 2010
    #2
    1. Advertising

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


    >>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?


    Nop:

    private DbConnection CreateConnection(string connectionString)
    {
    DbConnection dbConnection = new SqlConnection(connectionString);
    return dbConnection;
    }
     
    André Freitas, Feb 3, 2010
    #3
  4. This behavior is by design. The DBparamater retains state information. You
    need to first call clear on it if you intend to reuse it.

    --
    Vapordan
    Shameless Author Plug
    ASP.NET 4 by Example only $20
    OWC Blackbook www.lulu.com/owc

    "André Freitas" <andrefreitas> wrote in message
    news:...
    > 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?
    >
     
    Alvin Bruney - ASP.NET MVP, Feb 14, 2010
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shawn

    Very very strange. Major bug?

    Shawn, Feb 3, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    348
    Shawn
    Feb 4, 2004
  2. Raymond Arthur St. Marie II of III

    very Very VERY dumb Question About The new Set( ) 's

    Raymond Arthur St. Marie II of III, Jul 23, 2003, in forum: Python
    Replies:
    4
    Views:
    483
    Raymond Hettinger
    Jul 27, 2003
  3. shanx__=|;-

    very very very long integer

    shanx__=|;-, Oct 16, 2004, in forum: C Programming
    Replies:
    19
    Views:
    1,629
    Merrill & Michele
    Oct 19, 2004
  4. Abhishek Jha

    very very very long integer

    Abhishek Jha, Oct 16, 2004, in forum: C Programming
    Replies:
    4
    Views:
    427
    jacob navia
    Oct 17, 2004
  5. Peter

    Very very very basic question

    Peter, Feb 8, 2005, in forum: C Programming
    Replies:
    14
    Views:
    518
    Dave Thompson
    Feb 14, 2005
Loading...

Share This Page