Using statment is closing the connection in my DAC RRS feed

  • Question

  • This is the code i had in my Data access component.  I dont know if i ever called this method but i did today from a WCF service i am trying to make.  I copied the code directly into the WCF service.  That didnt work but and once i moved the using on the connection it worked.  What is the proper way to handle this in the DAC?  I would retrun a data table but my WCF code is like :

    hist = new AuditHistory();
    hist.Comments = reader.GetString((int)History.Comments);
    hist.Date = reader.GetDateTime((int)History.Date);

       public static SqlDataReader ExecuteReader(string storedProcedureName, params SqlParameter[] arrParam)
                // Open the connection
                using (SqlConnection cnn = new SqlConnection(ConnectionString))

                    // Define the command
                    using (SqlCommand cmd = new SqlCommand())
                        cmd.Connection = cnn;
                        cmd.CommandTimeout = 0;//6/10/2010 mkerr to prevent execution from time out
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = storedProcedureName;

                        // Handle the parameters
                        if (arrParam != null && arrParam.Length != 0)
                            foreach (SqlParameter param in arrParam)
                        SqlDataReader reader = cmd.ExecuteReader();//CommandBehavior.CloseConnection);
                        return reader;

    • Moved by VMazurModerator Tuesday, June 14, 2011 11:01 AM (From:ADO.NET DataSet)
    Wednesday, June 1, 2011 12:19 AM


  • Once the using statement is done, the connection is closed. The SqlConnection class's Dispose method closes the connection (and the purpose of the using statement is to issue a Dispose when it's done).

    You'll need to structure your method differently so that it doesn't return the SqlDataReader, but returns data instead.

    ~~Bonnie Berent [C# MVP]
    Wednesday, June 1, 2011 4:36 AM