Reusing SqlCommand

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Microsoft's documentation of SqlCommand says that you can reuse you
SqlCommand by this:

You can reset the CommandText property and reuse the SqlCommand object.
However, you must close the SqlDataReader before you can execute a new
or previous command.

How do you do this if you don't get a handle on the SqlDataReader
object? I'm using ExecuteNonQuery rather than ExecuteReader. I can
see that ExecuteNonQuery calls ExecuteReader, but I never get to touch
the SqlDataReader to close it. If I hold onto the SqlCommand object
and reuse it, only ever doing an ExecuteNonQuery, I start getting many
different errors(object not found from null ref, DataReader already
exists, connection is closed for DataReader object). If I create a new
SqlDataReader every time, the database access works fine.

Is there any way I can reuse my object or am I stuck via the
limitations of ADO.NET?

Code snippets:

Reuse:

int numberAffectedRows = 0;

if(myScAudit == null) {
myScAudit = CreateNewCommand();
}

try {
myScAudit.Parameters.Clear();
myScAudit.Parameters.Add(new SqlParameter("@FileID", theFileID));
myScAudit.Parameters.Add(new SqlParameter("@UserID", theUserID));
myScAudit.Parameters.Add(new SqlParameter("@Description",
theDescription));

using(SqlConnection conn = DBConnection()) {
myScAudit.Connection = conn;
conn.Open();
numberAffectedRows = myScAudit.ExecuteNonQuery();
}
}

if(numberAffectedRows <= 0) {
throw new Exception("No rows affected for audit logging.");
}
}
catch (Exception e) {
if(log.IsErrorEnabled) {
log.Error("Audit logging failed for fileID " + theFileID.ToString()
+ " with log: " + theDescription, e);
}
}

New:

int numberAffectedRows = 0;

try {
using(SqlCommand scAudit = SetDBLogging()) {
scAudit.Parameters.Clear();
scAudit.Parameters.Add(new SqlParameter("@FileID", theFileID));
scAudit.Parameters.Add(new SqlParameter("@UserID", theUserID));
scAudit.Parameters.Add(new SqlParameter("@Description",
theDescription));

using(SqlConnection conn = DBConnection()) {
scAudit.Connection = conn;
conn.Open();
numberAffectedRows = scAudit.ExecuteNonQuery();
}
}

if(numberAffectedRows <= 0) {
throw new Exception("No rows affected for audit logging.");
}
}
catch (Exception e) {
if(log.IsErrorEnabled) {
log.Error("Audit logging failed for fileID " + theFileID.ToString()
+ " with log: " + theDescription, e);
}
}

Steve
 
That is what I would have thought. But it doesn't appear to be the
case, as I'm forced to dispose my SqlCommand, otherwise I get
exceptions thrown all over the place.
 
Actually, what is wierd, is that I didn't notice this until I had
multiple threads running the same code. Each thread would get it's own
connection. If there was a single thread that would execute this code,
I wouldn't notice problems, even when trying to 10,000 writes in a row.
Our application occasionally gives us this error on our client's end.
But once I get 5 threads all executing the same code, I get this issue
consistently.
 
Hi Steve,

It is hard to tell what you are doing wrong actually.
Perhaps you could show us some code?
 
I'm really not sure what the problem is here and I can't post the
entire application as it is far too big. I have create a test project
using the same code and the behavior isn't the same. The exceptions
aren't thrown. I will continue looking.
 
Hi,

Perhaps you are messing something with multithreading?
Are you creating new instances of SqlConnection for every thread?
 
Our connection is never being passed, it is always being created. One
connection could be used on multiple threads, but that is only by using
connection pooling.
 
Ok, looks like I figured things out. The class I am using to log to
the database is a singleton. Having multiple threads that don't lock
the SqlCommand object causes problems. Two threads might try to set
the connection for a single SqlCommand object. Now, two threads that
each think they have their own connection actually are using a single
connection. So, this is why I am being forced to create a new
SqlCommand object every time, rather than reusing the same connection.

Thanks, just needed to have someone to try to prove what I was doing
but really just pushed myself to figure out what was really going on.
 
Back
Top