c#+sql事务处理

sql事务处理

[csharp]  view plain  copy

1. /// <summary>  
2.        /// 启用事务提交多条带参数的SQL语句  
3.        /// </summary>  
4.        /// <param name="mainSql">主表SQL</param>  
5.        /// <param name="mainParam">主表对应的参数</param>  
6.        /// <param name="detailSql">明细表SQL语句</param>  
7.        /// <param name="detailParam">明细表对应的参数</param>  
8.        /// <returns>返回事务是否成功</returns>  
9.        public static bool UpdateByTran(string mainSql, SqlParameter[] mainParam, string detailSql, List<SqlParameter[]> detailParam)  
10.        {  
11.            SqlConnection conn = new SqlConnection(connString);  
12.            SqlCommand cmd = new SqlCommand();  
13.            cmd.Connection = conn;  
14.            try  
15.            {  
16.                conn.Open();  
17.                cmd.Transaction = conn.BeginTransaction();//开启事务  
18.                if (mainSql != null && mainSql.Length != 0)  
19.                {  
20.                    cmd.CommandText = mainSql;  
21.                    cmd.Parameters.AddRange(mainParam);  
22.                    cmd.ExecuteNonQuery();  
23.                }  
24.                foreach (SqlParameter[] param in detailParam)  
25.                {  
26.                    cmd.CommandText = detailSql;  
27.                    cmd.Parameters.Clear();  
28.                    cmd.Parameters.AddRange(param);  
29.                    cmd.ExecuteNonQuery();  
30.                }  
31.                cmd.Transaction.Commit();//提交事务  
32.                return true;  
33.            }  
34.            catch (Exception ex)  
35.            {  
36.                if (cmd.Transaction != null)  
37.                {  
38.                    cmd.Transaction.Rollback();//回滚事务  
39.                }  
40.   
41.                //将异常信息写入日志   
42.                string errorInfo = "调用UpdateByTran(string mainSql,  SqlParameter[] mainParam,string detailSql ,List <SqlParameter []>detailParam)方法时发生错误，具体信息：" + ex.Message;  
43.                WriteLog(errorInfo);  
44.                throw ex;  
45.            }  
46.            finally  
47.            {  
48.                if (cmd.Transaction != null)  
49.                {  
50.                    cmd.Transaction = null;//清空事务  
51.                }  
52.                conn.Close();  
53.            }  
54.        }  

C#中使用

 

[csharp]  view plain  copy

1. //读者借书  
2.        public bool AddBorrowInfo(BorrowInfo objBorrowInfo)  
3.        {  
4.            //主表实现  
5.            string sqlMain = "insert into BorrowInfo(BorrowId, ReaderId, BorrowDate, AdminName_B)values(@BorrowId, @ReaderId, @BorrowDate, @AdminName_B) ";  
6.            SqlParameter[] param = new SqlParameter[]{  
7.             new SqlParameter ("@BorrowId",objBorrowInfo .BorrowId ),  
8.             new SqlParameter ("@ReaderId",objBorrowInfo .ReaderId ),  
9.             new SqlParameter ("@BorrowDate",objBorrowInfo .BorrowDate ),  
10.             new SqlParameter ("@AdminName_B",objBorrowInfo .AdminName_B )  
11.         };  
12.            //副表实现  
13.            string sqlDetail = "insert into BorrowDetail(BorrowId, BookId, BorrowCount, NonReturnCount, Expire)values(@BorrowId, @BookId, @BorrowCount, @NonReturnCount, @Expire)";  
14.            List<BorrowDetail> detailList = objBorrowInfo.DetailList;  
15.            List<SqlParameter[]> paramList = new List<SqlParameter[]>();  
16.            SqlParameter[] paramt = null;   
17.            foreach (BorrowDetail item in detailList)  
18.            {  
19.                paramt = new SqlParameter[]{  
20.             new SqlParameter ("@BorrowId",item.BorrowId  ),  
21.             new SqlParameter ("@BookId",item.BookId  ),  
22.             new SqlParameter ("@BorrowCount",item.BorrowCount ),  
23.             new SqlParameter ("@NonReturnCount",item.NonReturnCount ),  
24.             new SqlParameter ("@Expire",item.Expire  )  
25.              };  
26.                paramList.Add(paramt);  
27.            }  
28.            //添加借书  
29.            return SQLHelper.UpdateByTran(sqlMain, param, sqlDetail, paramList);  
30.        }