即使出现错误也要执行insert
即使出现错误也要执行insert
提问于 2018-06-06 05:55:43
我正在从文本框和网格视图向两个表中插入数据；但是，当我收到错误时，数据仍在其中一个表中插入。我想要的是，一旦我得到了错误，那么数据就不应该插入。
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (DataRow dr in dt.Rows)
{
cmd.Parameters.AddWithValue("@loadSheetNum", lblSheet.Text);
cmd.Parameters.AddWithValue("@invoiceNum", dr["Invoice #"]);
cmd.Parameters.AddWithValue("@invoiceQty", dr["Invoice Qty"]);
cmd.Parameters.AddWithValue("@custName", dr["Customer Name"]);
cmd.Parameters.AddWithValue("@invoiceWeight", dr["Total Invoice Weight"]);
cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = 1;
}
cmd.ExecuteNonQuery();
}
using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection))
{
for (int i = 0; i < ContentPlaceHolder1.Controls.Count; i++)
{
Control ctrl = ContentPlaceHolder1.Controls[i];
if (ctrl is TextBox)
{
TextBox txt = (TextBox)ctrl;
//txt.TextMode = System.Web.UI.WebControls.TextBoxMode.Number;
value = txt.Text;
int parsedValue;
if (!int.TryParse(value, out parsedValue))
{
lblError.Text = "Please enter only numeric values for number";
return;
}
else
{
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@loadSheetNum", lblSheet.Text);
comm.Parameters.Add("@Number", SqlDbType.NVarChar).Value = value;
}
}
comm.ExecuteNonQuery();
}
}
}回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-06 06:27:07
当您的第二个命令通过使用事务出现问题时，您可以在第一个命令中实现不提交更改。
下面是一个例子：
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection, transaction))
{
// First command
}
using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection, transaction))
{
// Second command
// .. omitted
if(!int.TryParse(value, out parsedValue)){
lblError.Text = "Please enter only numeric values for number";
return; // Since we haven't committed the transaction, it will be rolled back when disposed.
}
// .. omitted
}
transaction.Commit(); // Both commands execute without error, commit the transaction.
}
}Stack Overflow用户
发布于 2018-06-06 06:17:57
你的代码中有很多问题。第一个是for循环中的AddWithValue。通过这种方式，您可以在每个循环中继续向命令添加参数，但只会使用存储过程所需的第一组参数，从而导致在每个循环中插入相同的值。
因此，您应该更改这两个循环，以便以这种方式处理参数：
using (SqlConnection connection = new SqlConnection(...))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@loadSheetNum", SqlDbType.NVarChar);
... add all the other parameters, but don't give them a value
foreach (DataRow dr in dt.Rows)
{
cmd.Parameters["@loadSheetNum"].Value = lblSheet.Text;
... set the value to all other parameters
cmd.ExecuteNonQuery();
}
}您应该对另一个命令执行相同的操作，但是当您确定要插入的值时，立即调用ExecuteNonQuery
using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection))
{
comm.CommandType = CommandType.StoredProcedure;
// This never changes inside the loop so keep it outside
comm.Parameters.Add("@loadSheetNum", SqlDbType.NVarChar).Value = lblSheet.Text);
// This changes inside the loop so set the value inside the loop
comm.Parameters.Add("@Number", SqlDbType.NVarChar)
for (int i = 0; i < ContentPlaceHolder1.Controls.Count; i++)
{
Control ctrl = ContentPlaceHolder1.Controls[i];
if (ctrl is TextBox)
{
TextBox txt = (TextBox)ctrl;
value = txt.Text;
int parsedValue;
if (!int.TryParse(value, out parsedValue))
{
lblError.Text = "Please enter only numeric values for number";
return;
}
else
{
comm.Parameters["@Number"] = value;
comm.ExecuteNonQuery();
}
}
}
}重要的是要知道，如果你的输入中有一个无效的数字，你是否想要避免整个插入代码。在这种情况下，您需要将事务应用于插入，并在出现错误时确认或回滚插入
只需添加以下内容
using (SqlConnection connection = new SqlConnection(...))
{
connection.Open();
using (SqlTransaction ts = connection.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand("PP_CreateSheet", connection, ts))
{
cmd.CommandType = CommandType.StoredProcedure;
.......
}
using (SqlCommand comm = new SqlCommand("PP_CreateNumber", connection, ts))
{
.....
if (!int.TryParse(value, out parsedValue))
{
lblError.Text = "Please enter only numeric values for number";
ts.Rollback();
return;
}
.....
}
// Before exiting from the SqlConnection using block call the
ts.Confirm();
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50709497
复制相关文章
相似问题