Issue with calling two methods in data access method

Question

I have this method:

public bool ActivateUser(string username, string key)
        {
            var user = this.GetUser(username, true);

            if (user != null)
            {
                if (user.NewEmailKey == key)
                {
                    string query = "usp_ActivateUser";
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand(query, conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@p_Username", username);
                            cmd.Parameters.AddWithValue("@p_LastModifiedDate", DateTime.Now);

                            conn.Open();

                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                cmd.ExecuteNonQuery();
                                return true;
                            }
                        }
                    }
                }
                else
                    return false;
            }
            else
                return false;

        }

As you can see I call GetUser() method first to get user and later use data for another database call. But something goes wrong.

There is already an open DataReader associated with this Command which must be closed first.

Here is the get user method:

public User GetUser(string username, bool nonMembershipUser)
        {
            string query = "usp_GetUser";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@p_Username", username);
                    conn.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {...

score 2 · Accepted Answer · answered Feb 23 '13 at 13:08

Your problem is here.

  using (SqlDataReader reader = cmd.ExecuteReader())
        {
          cmd.ExecuteNonQuery();
          return true;
        }

You are calling cmd.ExecuteNonQuery() but the command is already being used by the reader inside this using block.

Since your code doesn't really do anything meaningful with the reader why not remove the block entirely and call cmd.ExecuteNonQuery() ?

score 1 · Answer 2 · answered Feb 23 '13 at 13:09

Why do you do cmd.ExecuteReader() in the using statement, then cmd.ExecuteNonQuery(); on the very next line?

Why use the ExecuteReader() at all as you are simply returning from the database call without checking the result - ExecuteNonQuery will suffice for this.

score 1 · Answer 3 · answered Feb 23 '13 at 13:18

this is the problem, in ActivateUser:

using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                cmd.ExecuteNonQuery();
                                return true;
                            }

You can't open a Reader on an SqlCommand object and then execute another query on that command objct without first closing the Reader - which won't happen until that last "}". Actually I'm not sure you even need the Reader in this case - did you maybe copy/paste from your GetUser function? All you should need is

cmd.ExecuteNonQuery();
return true;

Also, I would consider wrapping code to execute readers,queries, etc, into some functions so you can re-use them. Here's what I normally use as a wrapper for readers:

public static DataTable ExecuteReader (string query,CommandType commType, params SqlParameter[] Paramerters)
{
   try
   {
      using (SqlConnection conn = new SqlConnection("your connection string here")
      {
          conn.Open();
          using (SqlCommand comm = new SqlCommand(conn,query))
          {
             conn.CommandType=commType;
             if (Parameters!=null) comm.Parameters.AddRange(Parameters);
             DataTable dt = new DataTable();
             using (SqlDataReader reader = comm.ExecuteReader())
             {
                dt.Load(reader);
             }
            return dt;
         }//end using command
     }//end using connection
}
 catch(Exception)
{
         throw;
}
}//end function

and you can write simple wrappers for nonquery, nonreader, etc, as well.

Issue with calling two methods in data access method

3 Answers3