C# 重用SqlCommand？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/670407/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reusing SqlCommand?
提问by Dmitris
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 SqlCommandinstance and apply updated parameters to call into the stored procedure again within the same function.
我目前正在研究一个大学项目，我有一个使用存储过程的函数。我想知道是否可以采用相同的SqlCommand实例并应用更新的参数在同一函数中再次调用存储过程。
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?
是否可以更新MyCommand参数并再次调用存储过程？
采纳答案by Stephen Wrighton
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)
是的。您需要确保在每次调用之间调用 myCommand.Parameters.Clear 以转储参数，但没有什么可以阻止您重用对象。（我不经常使用 C#，所以文中可能有一两个错误）
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();
回答by Joel Coehoorn
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_ideach time it executes, but change the book_id. The usingblock is just like wrapping your connection in a try/catch to make sure it's closed.
这将运行查询 10 次并在user_id每次执行时使用相同的查询，但更改book_id. 该using块就像将您的连接包装在 try/catch 中以确保它已关闭。
