如题:要向Access中的两个表(表A,表B)插入记录,其中表B的某字段引用表A的主键作为外键,请问如何编写C#事务???谢谢指点!!!
我试过在一个Transaction中同时插入记录,先插表A,再插表B,最后提交,提示错误:表B需要表A中的记录...不知该怎样处理.向高手请教!谢谢!
我试过在一个Transaction中同时插入记录,先插表A,再插表B,最后提交,提示错误:表B需要表A中的记录...不知该怎样处理.向高手请教!谢谢!
表B字段: B1, B2, A1 --A1外键C#事务:
public bool MyTransaction(A a,B b)
{
//假设已有连接 conn...
bool isSuccess = false;
OleDbCommand commA = new OleDbCommand();
OleDbCommand commB = new OleDbCommand();
OleDbTransaction trans = null;
//插入表A的SQL命令
string sqlA = "insert into A(A1,A2,A3) values (@a1,@a2,@a3)";
commA.CommandText = sqlA;
commA.Parameters.Add("@a1", OleDbType.VarChar, 20).Value = a.a1;
commA.Parameters.Add("@a2", OleDbType.VarChar, 20).Value = a.a2;
commA.Parameters.Add("@a3", OleDbType.VarChar, 20).Value = a.a3;
//插入表B的SQL命令
string sqlB = "insert into B(B1,B2,B3) values (@b1,@b2,@b3)";
commB.CommandText = sqlB;
commB.Parameters.Add("@b1", OleDbType.VarChar, 20).Value = b.b1;
commB.Parameters.Add("@b2", OleDbType.VarChar, 20).Value = b.b2;
commB.Parameters.Add("@b3", OleDbType.VarChar, 20).Value = b.b3; try
{
conn.Open(); //打开数据库连接
trans = conn.BeginTransaction(); //获取事务实例并开始事务
//指定Command的连接
commA.Connection = commB.Connection = conn;
//指定Command的事务
commA.Transaction = commB.Transaction = trans;
//执行更新操作
int a = commA.ExecuteNonQuery();
int b = commB.ExecuteNonQuery();
if (a == 1 & b == 1)
{
trans.Commit(); //如果全部成功,提交事务存入数据库
isSuccess = true;
}
else trans.Rollback(); //否则回滚事务.
}
catch (Exception ex)
{//发生异常回滚事务
trans.Rollback(); //发生异常回滚
throw ex;
}
finally
{
conn.Close();
}
return isSuccess;
}
string sqlB = "insert into B(B1,B2,A1) values (@b1,@b2,@a1)";
commB.CommandText = sqlB;
commB.Parameters.Add("@b1", OleDbType.VarChar, 20).Value = b.b1;
commB.Parameters.Add("@b2", OleDbType.VarChar, 20).Value = b.b2;
commB.Parameters.Add("@a1", OleDbType.VarChar, 20).Value = a.a1;
......