locked
Db.ExecuateReader and TransactionScope Class RRS feed

  • Question

  • User-2087761027 posted

    Hi guys


    I'm using Data Access Application Block 3.1.The code looks like below. 

    When it runs to Db.ExecuteReader(dbc2), it throws an exception "There is already an open DataReaderassociatd with this command which must be closed first"

    Examples I saw so far is that ExecuateNonQuery method is used within tranasactionsocpe. Can I just ExecuteReader here? My workround is to use connection.BeginTransaction to wrap up all the commad. To do this, I have to pass transaction object everywhere.
       using (TransactionScope scope = new
                            TransactionScope(TransactionScopeOption.RequiresNew))
    {
    ......
    Db.ExecuteReader(dbc1);
    Db.ExecuteReader(dbc2)
    ......
    }




    Monday, November 16, 2009 10:26 PM

All replies

  • User955742345 posted

    ExecuteReader returns a datareader.  You need to either close that instance first or use a new command object to execute the code at line 6, i.e., Db2.ExecuteREader(dbc2);

     

    If you are not returning any data or not reading any of the returned data, use ExecuteNonQuery, which executes the command and closes immediately.

    Monday, November 16, 2009 10:50 PM
  • User-2087761027 posted

    Should I close the reader? I thought DAAB shall be able to manage it by itself.

    Monday, November 16, 2009 11:12 PM
  • User955742345 posted

    I haven't used application blocks, but the standard with datareaders is that you have to close one before you can open another on the same command. 

    For speed, though, it would be better to use ExecuteNonQuery if you are not using any returned data.

    Monday, November 16, 2009 11:16 PM
  • User-2087761027 posted

    I  have a generic database access class which all my data service classes inherited from. In that generic class, there is a Db property

     public Database Db
            {
                get
                {
                    if (db == null)
                        return DatabaseFactory.CreateDatabase();
                    else
                        return this.db; ;
                }
            }


    My bussiness object structure is like:

       public class Employee
            {
                public int ID { get;set;}
                public string Name { get; set; }
                public Department Department { get;set;}
            }
    
            public class Department
            {
                public int ID { get;set;}
                public string Name { get;set;}
            }

    In my data service class, I call Db.ExecuteReader() to return an Employee object. Inside the Employee object, I make the other call Db.ExecuteReader to return Department object, which will throw an exception because reader is not close yet.The iss

    How shall I handle this situation?

    Thanks

    Monday, November 30, 2009 6:03 PM