Why Does Demo 006 (SL) even work?

Jav posted on Saturday, June 12, 2010

I get an error when I try to emulate the same in my  own project.  What I'm talking about is in the Order folder, the OrderEdit - LineItems - LineItemEdit - Details - Detail scenario.  In fact Rocky demonstrates the technique at length in SL Video 0106 DataAccess.

I have RootObject - RootCategories - RootCategory - Categories - Category, and I get a run time error:

{"There is already an open DataReader associated with this Command which must be closed first."} System.Exception {System.InvalidOperationException}

The error occures when the second DataReader is opened in the Categories class.  I have looked at the code quite carefully and mine seems to following the same path as Rocky's.  His code works, mine does't.

Here is my code

ROOTOBJECT
 private void DataPortal_Fetch(Criteria criteria)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("ConnectionString"))
            {
                using (var cm = ctx.Connection.CreateCommand())
                {
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.CommandText = "StoredProc1";
                    using (var dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        dr.Read();
                        LoadProperties;
                        if (dr.NextResult())
                        {
                            LoadProperty(RootCategoriesProperty, DataPortal.FetchChild<RootCategoryList>(dr));
                        }
                    }
                }
            }
        }

ROOTCATEGORIES
        private void Child_Fetch(SafeDataReader dr)
        {
             while (dr.Read())
            {
                Add(DataPortal.FetchChild <RootCategory>(dr));
            }
        }

ROOTCATEGORY
        private void Child_Fetch(SafeDataReader dr)
        {
            LoadProperties;
 
            Categories = DataPortal.FetchChild <CategoryList>(RootCategoryKey);
        }

CATEGORIES
        private void Child_Fetch(Int32 rootCategoryKey)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("EnvEmrConnectionString"))
            {
                using (var cm = ctx.Connection.CreateCommand())
                {
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.AddWithValue("@RootCategoryKey", rootCategoryKey);
                    cm.CommandText = "StoredProc2";
                    using (var dr = new SafeDataReader(cm.ExecuteReader()))                        // This causes error
                    {
                        while (dr.Read())
                        {
                            Add(DataPortal.FetchChild<Category>(dr));
                        }
                    }
                }
            }
        }

CATEGORY
       private void Child_Fetch(SafeDataReader dr)
        {
            LoadProperties;
          }

Jav

RockfordLhotka replied on Sunday, June 13, 2010

It is possible (and I haven't looked) that I enabled MARS on the connection - that's the easy answer for cases where you want to have multiple open datareaders on a connection, since it is just a tweak on the database connection string.

Jav replied on Monday, June 14, 2010

That's what it is.  I didn't think of looking before.

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|DemoDb.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>

Thanks

Jav