Returning DataReader from separate class file (in App_Code folder) RRS feed

  • Question

  • User-903716854 posted

    I am trying to improve my web app code bu moving functions to a separate Class file which in contained in the App_Code folder.

    My first attempt which calls a method from my web form codebehind (including a string value), the method then calls a SQL Stored Procedure which uses the string value as an SP Parameter, the resulting DataReader (which may contain multiple data rows) is returned to the web form.

    Whilst this does work, I am concerned about the database connection. If I close it in the Method, then I get an error (as expected). I cannot seem to close the connection in the web form as it does not exist. What should I do? Maybe I should return something other than a DataReader?

    WebForm Code Behind 

    SqlDataReader myReader = new CSEmployee().EmployeeGetDetails(pmfkey.Text);
    if (myReader.HasRows)
       while (myReader.Read())
             // do something

    Class Code Page

        public SqlDataReader EmployeeGetDetails(string pmfkey)
            SqlCommand cmd = new SqlCommand("sp_Employee_GET_Details", dbEmpConn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@pmfkey", SqlDbType.NVarChar).Value = pmfkey;
            SqlDataReader oDataReader = null;
            oDataReader = cmd.ExecuteReader();
            //dbEmpConn.Close(); - This causes error (as expected)
            return oDataReader;

    Tuesday, August 20, 2013 11:33 AM


  • User1109032460 posted

    You have a couple of choices.

    The first is to pass in the CommandBehavior.CloseConnection value to ExecuteReader. You can then leave the connection open, and when you close the reader, it will close the connection for you. This is the simplest approach.

    Alternatively, you can radically change the API as follows

    void DoSomethingWithDatabase( Action<SqlDataReader> work )
      using( SqlConnection conn = new SqlConnection() )
        SqlCommand cmd = new SqlCommand();
        // the rest of the code to set up the command
        SqlDataReader reader = cmd.ExecuteReader();
        work( reader );

    when you use this, you can then do

    obj.DoSomethingWithDatabase( reader =>
        if( reader.HasRows )
          while( reader.Read() )

    Note how this works. The connection is declared and opened inside a using{} block, which ensures that the connection will be closed no matter what. By passing in an Action delegate, which is then invoked from inside the using block, you ensure that the work with the reader is done before the connection is closed.

    You could, of course, write a more generally useful method as

    static void DoSomethingWithDatabase( Action<SqlConnection> work )
      // information about connection string settings not shown
      using( SqlConnection conn = new SqlConnection() )
        work( conn );

    If you have this at the heart of your data access layer (assuming that you've decided an ORM is not for you), then you can guarantee that no code will be executed against a connection without it being inside a using{} block, thus ensuring that connections are always closed no matter what.

    Anyway, hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 21, 2013 3:58 AM