Reusing SqlCommand?

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?

Solution 1:

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();

Solution 2:

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.