public static int AddInSideTask(DevelopTask task, string[] employee)
{
int errorRow = 0;
SqlTransaction trans = null;
trans = DBHelper.Connection.BeginTransaction();//获得连接的同时已经打开
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = DBHelper.Connection; //指定连接
sqlCommand.Transaction = trans; //指定事务
try
{ //增加操作1
sqlCommand.CommandText = "Insert Into DevelopTask(Id,d1,d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,DemandInfoId,StateId) Values(@Id,@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,@d13,@d14,@d15,@d16,@DemandInfoId,@StateId)";;
SqlParameter[] ps1 ={
new SqlParameter("@Id",task.Id),
new SqlParameter("@d1",task.D1),
new SqlParameter("@d2",task.D2),
new SqlParameter("@d3",task.D3),
new SqlParameter("@d4",task.D4),
new SqlParameter("@d5",task.D5),
new SqlParameter("@d6",task.D6),
new SqlParameter("@d7",task.D7),
new SqlParameter("@d8",task.D8),
new SqlParameter("@d9",task.D9),
new SqlParameter("@d10",task.D10),
new SqlParameter("@d11",task.D11),
new SqlParameter("@d12",task.D12),
new SqlParameter("@d13",task.D13),
new SqlParameter("@d14",task.D14),
new SqlParameter("@d15",task.D15),
new SqlParameter("@d16",task.D16),
new SqlParameter("@DemandInfoId",DBNull.Value),
new SqlParameter("@StateId",task.State.Id)};
sqlCommand.Parameters.AddRange(ps1);
sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear(); string addRes = string.Empty;
//循环增加对应关联信息
for (int i = 0; i < employee.Length; i++)
{
addRes = "Insert Into User_Task(DevelopUserName,DevelopId) Values('" + employee[i] + "','" + task.Id + "')";
sqlCommand.CommandText = addRes;
sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
} trans.Commit();
}
catch (Exception)
{
try
{
trans.Rollback();
}
catch (Exception)
{
errorRow = -1;
}
errorRow = -1;
}
finally
{
DBHelper.Connection.Close();
}
return errorRow;
}代码如上:
1.先执行一个表的新增操作。
2.再循环执行另一个表的操作。(多个,差不多3个吧)逐步调试的时候,发现异常发生在trans.Commit();这一句。
异常提示信息是:此 SqlTransaction 已完成 它再也无法使用 System.Exception {System.InvalidOperationException}虽然事务提交的时候发生异常,但是,执行的更新操作都成功保存了。不知道原因是什么,请大家给解答下,先谢谢了。
sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
这个成功了
for (int i = 0; i < employee.Length; i++)
{
addRes = "Insert Into User_Task(DevelopUserName,DevelopId) Values('" + employee[i] + "','" + task.Id + "')";
sqlCommand.CommandText = addRes;
sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Clear();
}
这个有成功吗
{}
public static bool ExecuteNonQuery(string sql, CommandType cmdType, DbTransaction transaction, string connString, params DbParameter[] parameters)
{
DbConnection conn = DbFactory.CreateConnection();
conn.ConnectionString = connString;
DbCommand cmd = DbFactory.CreateCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.CommandType = cmdType;
AddParams(parameters, cmd);
try
{
Open(conn);
transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
int returnCount = 0;
returnCount = cmd.ExecuteNonQuery();
transaction.Commit();
if (returnCount > 0)
return true;
return false;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
Close(conn);
cmd.Dispose();
cmd.Parameters.Clear();
}
}
这个啊,
先试试。
这篇文章里面说的,不能把事务放在for循环里面,没错啊。
我的代码,事务是放在for循环外面的。