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?
To answer your specific question, you can reuse a SqlConnection for each query. Just make sure to close your current query ( SqlDataReader , etc.) before you run another one, ie. wrap them in their own using blocks.
yes , it is necessary to dispose the sqlconnection and sqlcommand object after your piece of code gets executed.
A SqlCommand object allows you to specify what type of interaction you want to perform with a database. For example, you can do select, insert, modify, and delete commands on rows of data in a database table.
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();
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With