Exception when using SqlReader

Question

I tried to call this method from multiple threads trying to get the ID from the same string. I am always getting this exception at the line where I create the SqlDataReader:

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

I don't know where the problem is. I am using a lock() statement so I use the command only once, then I dispose it. Kinda new to database programming so I don't know where my error is.

Thanks!

public int UsernameGetID(string username)
{
    using (var command = new SqlCommand("SELECT user_id FROM " + ServerConstants.Database.TableUserInformation + " WHERE username = @Username", connection))
    {
        lock (command)
        {
            SqlParameter param = new SqlParameter("@Username", SqlDbType.VarChar, username.Length);
            param.Value = username;
            command.Parameters.Add(param);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    return (int)reader[0];
                }
                else
                {
                    // username doesn't exists
                    return 0;
                }
            }
        }
    }
}

Answer 1

Locking on command is pointless, given that you're creating a new command within the method - no other code could lock on it.

However, you're sharing the connection between multiple commands. Don't do that - create a new SqlConnection (again, in a using statement) in each call. Don't worry about the efficiency aspect - the connection pool will take care of the "real" network connection.

So you want:

using (var connection = new SqlConnection(...))
using (var command = new SqlCommand(..., connection))
{
    connection.Open();
    ...
    using (var reader = command.ExecuteReader())
    {
        return reader.Read() ? (int) reader[0] : 0;
    }
}