sql中向主表插入数据同时也向明细表插入数据(把主表ID插入明细中)

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。事务回滚我这里就不多说了。

 

展开阅读全文

没有更多推荐了,返回首页