sql中向主表插入数据的同时也向明细表插入数据(把主表ID插入明细中),怎么保证数据的完整性,和一致性。那就用事务。
protected void Button1_Click(object sender, EventArgs e)
{
string sqlconn = "server=.;uid=sa;pwd=123456;database=DB_Test";
SqlTransaction Transaction=null;
using (SqlConnection conn = new SqlConnection(sqlconn))
{
conn.Open();
//开启事务
Transaction = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Transaction = Transaction;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into Orders values('yangfeizai');select @@identity";
int OrderID=Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = "insert into OrdersDet values(" + OrderID + ",'yangfeizai1') ";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into OrdersDet values(" + OrderID + ",'yangfeizai2') ";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into OrdersDet values(" + OrderID + ",'yangfeizai3') ";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into OrdersDet values(" + OrderID + ",'yangfeizai4') ";
cmd.ExecuteNonQuery();
//事务提交
Transaction.Commit();
}
}
注意:"insert into Orders values('yangfeizai');select @@identity" Sql 语句获得主表插入数据的ID。事务回滚我这里就不多说了。