I've ben messing with some ado.net code to load master page dynamically from database. Naturally I wanted to cache the masterpage file name so the database is not called every time the page is called.

I created a dataset, a datatable and datareader. When I step through the code in the debugger, they all do exactly the same thing.??? I don't get it. I thought using a reader to cache data was forbidden because it would leave the connection open. I think I'm not uderstanding this correctly. Could someone please explain why the following code is bad?

1. When the MasterPage name is not in the cache, the code goes and retrieves it from the database.

2. When the page is visited and the MasterPage name is in the cache, the code gets it from the cache and moves on.

3. I don't understant why this is supposed to be wrong. Can some one explain? Thank you.

protected void Page_PreInit(object sender, EventArgs e)
    {
        if (Cache["cachedMaster"] == null)
        {
            GetMasterPage();
        }
        else
        {
            string loadMasterFromCache = Cache["cachedMaster"].ToString();
            this.MasterPageFile = loadMasterFromCache;
        }
    }

try
        {
            SqlConnection conn;
            SqlCommand cmd;

            string cmdString = "SELECT masterPage FROM MasterPages WHERE (masterEnabled = 1) AND (masterCategoryName = 'Blog')";
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString);

            cmd = new SqlCommand(cmdString, conn);
            conn.Open();

            SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.SingleResult);

            if (myReader.Read())
            {
                string masterPageFileName = myReader["masterPage"] as string;
                Cache.Insert("cachedMaster", masterPageFileName, null, DateTime.Now.AddSeconds(300), System.Web.Caching.Cache.NoSlidingExpiration);
            }

            myReader.Close();
            conn.Close();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

You are caching the data, that is perfectly OK. The issue concerns when you put datareader instance itself to the Cache (when the need to cache a resultset occurs, it's better to put DataTable or custom object in cache) 

try this little experiment to prove a critical difference (replace with your data): 

        private void ConnectionTestReaderDataSet()
        {
            SqlConnection connReader;
            SqlConnection connAdapter;
            string connectionString = "Server=.\\SQLExpress;Database=AdventureWorks;Trusted_Connection=True;";
            SqlDataReader reader;
            SqlDataAdapter adapter = new SqlDataAdapter();

            // Initialize connection
            connReader = new SqlConnection(connectionString);
            connAdapter = new SqlConnection(connectionString);

            SqlCommand objSqlCommand = new SqlCommand(
                "SELECT * FROM Person.Contact WHERE FirstName LIKE @FirstName", connReader);
            objSqlCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar, 50);
            objSqlCommand.Parameters["@FirstName"].Value = "%A%";

            SqlCommand dsCommand = new SqlCommand(
                "SELECT * FROM Person.Contact WHERE FirstName LIKE @FirstName", connAdapter);
            dsCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar, 50);
            dsCommand.Parameters["@FirstName"].Value = "%A%";
            DataSet ds = new DataSet();

            try
            {
                // Open the connection
                connReader.Open();
                connAdapter.Open();

                adapter.SelectCommand = dsCommand;
                adapter.Fill(ds);

                reader = objSqlCommand.ExecuteReader();

                //close the connections
                connAdapter.Close();
                connReader.Close();

                if (ds.Tables[0].Rows.Count > 0)
                {
                    Response.Write("Dataset is still available");
                }

                if (reader.Read())
                {
                    Response.Write("Reader is still available");
                }
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                connReader.Dispose();
                connAdapter.Dispose();
            }
        }