Reusing SqlCommand?

Question

I am not really sure if this is possible or not.

I am currently working on a college project and I have a function that uses stored procedures. I would like to know if it is possible to take the same SqlCommand instance and apply updated parameters to call into the stored procedure again within the same function.

Lets say i have something like this in my code:

myConStr = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
myConn = new SqlConnection(myConStr);
myCommand = new System.Data.SqlClient.SqlCommand("team5UserCurrentBooks3", myConn); 
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@book_id", bookID);
myCommand.Parameters.AddWithValue("@user_id", userID);

try
{
    myConn.Open();
    myCommand.ExecuteNonQuery();

Is it possible to update MyCommand's parameters and call the stored procedure again?

Stephen Wrighton · Accepted Answer · 2015-05-01T18:50:40.663

62

Yes. You'll want to make sure that you call myCommand.Parameters.Clear between each call in order to dump the parameters, but there's nothing stopping you from reusing the object. (I don't use C# often, so this may have an error or two in the text)

myConStr = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
myConn = new SqlConnection(myConStr);
myConn.Open();

myCommand = new System.Data.SqlClient.SqlCommand("team5UserCurrentBooks3", myConn); 
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@book_id", bookID);
myCommand.Parameters.AddWithValue("@user_id", userID);
myCommand.ExecuteNonQuery();

myCommand.Parameters.Clear();
myCommand.CommandText= "NewStoredProcedureName";
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@foo_id", fooId);
myCommand.Parameters.AddWithValue("@bar_id", barId);
mycommand.ExecuteNonQuery();

myCommand.Parameters.Clear();
myCommand.CommandText = " SELECT * FROM table1 WHERE ID = @TID;"
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@tid", tId);
SqlReader rdr;
rdr = myCommand.ExecuteReader();

edited May 01 '15 at 18:50

answered Mar 22 '09 at 02:02

Stephen Wrighton

36,783
6
67
86

4

Unlike reusing an instance where the text and parameters are kept the same, reusing an instance to clear everything before making it a "new" command has much less practical application (as opposed to actually creating a new command). This answer is worthwhile as a demonstration of the fact that you can do it, but please don't do this in production code unless a profiler shows the allocations are a hotspot, as it makes about as much sense as declaring a generic `int` named `i` and using it whenever you need an integer, regardless of what it happens to mean at any given point. – Jeroen Mostert Aug 24 '18 at 14:14
1

@JeroenMostert And what if you want to repeatedly execute a stored proc with different parameters? Is it sensible to clear the parameters on each iteration? – Maximilian Burszley Aug 29 '19 at 23:38
2

@TheIncorrigible1: no, because if you're executing the *same* stored procedure, its parameter list will not have changed. In this case, you can reuse the command, but you shouldn't clear the parameters -- just add them once and explicitly set all values that change in every iteration instead. The only case where it would make sense to clear the parameters is because you sometimes want to have the stored procedure apply the default value of a parameter, without knowing what that value is -- possible, but very unusual. – Jeroen Mostert Aug 30 '19 at 09:05

Joel Coehoorn · Answer 2 · 2018-08-24T14:01:45.273

Yes! You can definitely do that. Within a function you can re-use the same connection as well (I don't recommend re-using a connection object for larger scopes, but it is possible).

You could also do something like this:

myConStr = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
using (var cn = new SqlConnection(myConStr) )
using (var cmd = new SqlCommand("team5UserCurrentBooks3", cn) ) 
{
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.Add("@user_id", SqlDbType.Int).Value = userID;
    cmd.Parameters.Add("@book_id", SqlDbType.Int);
    cn.Open();

    for(int i = 0; i<10; i++)
    {
        cmd.Parameters["@book_id"].Value = i;
        cmd.ExecuteNonQuery();
    }
}

This will run the query 10 times and use the same user_id each time it executes, but change the book_id. The using block is just like wrapping your connection in a try/catch to make sure it's closed.

unfortunately Parameters.Add is obsolete now. But i Like you idea about using — Dmitris, Mar 22 '09 at 02:07
@Dmitris, Only the Add(string, object) overload is obsolete. All the other overloads of Add are fine to use - see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.add.aspx for more info. — LukeH, Mar 23 '09 at 13:07

Reusing SqlCommand?

2 Answers2

Linked