Exception when using SqlReader

Question

I tried calling this method from multiple threads, trying to get the identifier from the same string. I always get this exception in the line where I create the SqlDataReader:

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

I do not know where the problem is. I use the lock () operator, so I use the command only once, and then I recycle it. Its 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 pointless given that you are creating a new command in a method - no other code can block it.

However, you are using a connection between several teams. Do not do this - create a new SqlConnection (again, in the using statement) in each call. Don’t worry about the performance aspect - the connection pool will take care of a “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; } }