第一、sql中写事物
begin try
begin transaction
insert into shiwu (asd) values ('aasdasda');
commit transaction
end try
begin catch
select ERROR_NUMBER() as errornumber
rollback transaction
end catch
第二、c#中执行事物
SqlParameter[] paras = new SqlParameter[] { }; paras = list.ToArray(); Baitour.Utility.DBHelper db = Baitour.Utility.DBHelper.CreateMapping(); DbTransaction dbTran = db.CreateTran(); try { //在这里将插入得到的子订单号返回 object obj = db.ExecuteScalar(sql.ToString(), paras, dbTran); fsId = obj.ToString(); //int index = db.ExecuteNonQuery(sql.ToString(), paras, dbTran); if (!string.IsNullOrWhiteSpace(fsId)) { dbTran.Commit(); flag = true; } } catch (Exception ex) { flag = false; dbTran.Rollback(); //throw; } finally { db.CloseConn(); }
dbHelper
using System; using System.Collections.Specialized; using System.Configuration; using System.Data; using System.Data.Common; using System.Data.SqlClient; using Microsoft.Practices.EnterpriseLibrary.Data; using Microsoft.Practices.EnterpriseLibrary.Data.Sql; /* * 作 者: zjf * 创建时间: 2013-1-29 10:20:53 * 说明: */ namespace Baitour.Utility { /// <summary> /// 数据库访问类 /// </summary> public sealed class DBHelper { public static int ConnIndex = 0; public static DBHelper CreateMapping(string connStr = "") { DBHelper db = new DBHelper(connStr); return db; } /// <summary> /// 增删改的数据库连接字符串 /// </summary> string conString = ""; /// <summary> /// 查询的数据库连接字符串 /// </summary> string queryConString = ""; DBHelper(string connStr = "") { if (string.IsNullOrWhiteSpace(connStr)) { conString = ConfigurationManager.ConnectionStrings["DBConfig"].ToString(); //第一个链接字符串是ConfigurationManager.ConnectionStrings[0].Name LocalSqlServer不知道是谁，所以要从第二个取 //如果连接字符串的数量和当前索引+1相同 则从第一个索引开始取 NameValueCollection connStrs = new NameValueCollection(); int j = 0; for (int i = 0; i < ConfigurationManager.ConnectionStrings.Count; i++) { if (ConfigurationManager.ConnectionStrings[i].Name.StartsWith("QueryDB")) { connStrs.Add(j.ToString(), ConfigurationManager.ConnectionStrings[i].ToString()); j++; } } if (connStrs.Count > 0) { if (connStrs.Count == ConnIndex) { ConnIndex = 0; } queryConString = connStrs[ConnIndex].ToString(); ConnIndex++; } else { queryConString = conString; } } else { conString = connStr; queryConString = connStr; } DBMapping = new SqlDatabase(conString); QueryDBMapping = new SqlDatabase(queryConString); } #region 变量or属性 /// <summary> /// 增删改的企业库访问映射对象 /// </summary> internal Database DBMapping { get; set; } /// <summary> /// 查询的企业库访问映射对象 /// </summary> internal Database QueryDBMapping { get; set; } /// <summary> /// 增删改的数据库连接 /// </summary> internal DbConnection DBConnMapping { get; set; } /// <summary> /// 查询的数据库连接 /// </summary> internal DbConnection QueryDBConnMapping { get; set; } /// <summary> /// 数据库事务 /// </summary> internal DbTransaction DBTranMapping { get; set; } #endregion #region 方法 #region 准备方法 /// <summary> /// 创建数据库连接 /// </summary> void CreateDB() { DBMapping = new SqlDatabase(conString); QueryDBMapping = new SqlDatabase(queryConString); } /// <summary> /// 创建并打开连接 /// </summary> void CreateConn() { #region 增删改 if (DBMapping == null) { CreateDB(); } if (DBConnMapping == null) { DBConnMapping = DBMapping.CreateConnection(); DBConnMapping.Open();//打开连接 } //打开存在的连接 if (DBConnMapping != null && DBConnMapping.State != ConnectionState.Open) { DBConnMapping.Open();//打开连接 } #endregion #region 查询 if (QueryDBMapping == null) { CreateDB(); } if (QueryDBConnMapping == null) { QueryDBConnMapping = QueryDBMapping.CreateConnection(); QueryDBConnMapping.Open();//打开连接 } //打开存在的连接 if (QueryDBConnMapping != null && QueryDBConnMapping.State != ConnectionState.Open) { QueryDBConnMapping.Open();//打开连接 } #endregion } /// <summary> /// 关闭数据库连接 /// </summary> public void CloseConn() { if (DBConnMapping != null && DBConnMapping.State == ConnectionState.Open) { DBConnMapping.Close(); DBConnMapping.Dispose(); } if (QueryDBConnMapping != null && QueryDBConnMapping.State == ConnectionState.Open) { QueryDBConnMapping.Close(); QueryDBConnMapping.Dispose(); } } /// <summary> /// 创建并返回事务 /// </summary> public DbTransaction CreateTran() { if (DBMapping == null) { CreateDB(); } CreateConn(); DBTranMapping = DBTranMapping ?? DBConnMapping.BeginTransaction(); return DBTranMapping; } /// <summary> /// 创建命令对象 /// </summary> /// <param name="commandType">sql语句/存储过程</param> /// <param name="commandText">名称</param> /// <param name="commParameters">sql参数</param> /// <param name="isQuery">是否是创建查询命令</param> /// <returns></returns> DbCommand CreateComm(CommandType commandType, string commandText, DbParameter[] commParameters = null, bool isQuery = false) { DbCommand command = null; if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("sql语句/存储过程为空"); //创建命令对象 if (commandType == CommandType.StoredProcedure) command = isQuery ? QueryDBMapping.GetStoredProcCommand(commandText) : DBMapping.GetStoredProcCommand(commandText); else command = isQuery ? QueryDBMapping.GetSqlStringCommand(commandText) : DBMapping.GetSqlStringCommand(commandText); //清空参数 command.Parameters.Clear(); //填充参数 if (commParameters != null) { if (!string.IsNullOrWhiteSpace(ConfigurationManager.AppSettings["DBType"]) && ConfigurationManager.AppSettings["DBType"].ToLower() == "oracle") { foreach (DbParameter item in commParameters) { if (!item.ParameterName.StartsWith("!")) item.ParameterName = "!" + item.ParameterName; } } else { foreach (DbParameter item in commParameters) { if (!item.ParameterName.StartsWith("@")) item.ParameterName = "@" + item.ParameterName; } } if (commParameters.Length == 1) { command.Parameters.Add(commParameters[0]); } else { command.Parameters.AddRange(commParameters); } } return command; } #endregion #region 执行方法 #region sql、存储过程 /// <summary> /// 返回首列 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public object ExecuteScalar(string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { object obj = null; try { DbCommand command = CreateComm(cType, sql, paras, true); obj = DBMapping.ExecuteScalar(command); } catch (Exception ex) { } finally { CloseConn(); } return obj; } /// <summary> /// 返回首列 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public object ExecuteScalar(string sql, DbParameter[] paras, DbTransaction tran, CommandType cType = CommandType.Text) { object obj = null; try { DbCommand command = CreateComm(cType, sql, paras); obj = DBMapping.ExecuteScalar(command, tran); } catch (Exception ex) { } finally { } return obj; } /// <summary> /// 返回reader /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public IDataReader ExecuteReader(string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras, true); command.Connection = QueryDBMapping.CreateConnection(); command.Connection.Open(); return command.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { return null; } } /// <summary> /// 返回数据集 /// </summary> /// <returns></returns> public DataSet ExecuteDataSet(string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras, true); return QueryDBMapping.ExecuteDataSet(command); } catch (Exception ex) { return null; } finally { CloseConn(); } } public DataSet ExecuteDataSet(string sql) { try { DbCommand db = CreateComm(CommandType.Text, sql); return QueryDBMapping.ExecuteDataSet(CommandType.Text,sql); } catch { return null; } finally { CloseConn(); } } /// <summary> /// 得到影响行数 /// </summary> /// <returns></returns> public int ExecuteNonQuery(string sql, DbParameter[] paras, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras); return DBMapping.ExecuteNonQuery(command); } catch (Exception ex) { return -1; } finally { CloseConn(); } } /// <summary> /// 得到影响行数需要手动关闭连接 /// </summary> /// <returns></returns> public int ExecuteNonQuery(string sql, DbParameter[] paras, DbTransaction tran, CommandType cType = CommandType.Text) { try { DbCommand command = CreateComm(cType, sql, paras); return DBMapping.ExecuteNonQuery(command, tran); } catch (Exception ex) { return -1; } finally { } } /// <summary> /// 数据库分页获取DataSet对象 /// </summary> /// <param name="sTable_Name">表名/视图名</param> /// <param name="sSign_Record">显示的字段(以,分隔)/*表示全部字段</param> /// <param name="sFilter_Condition">查询条件</param> /// <param name="iPage_Size">每页显示条数</param> /// <param name="iPage_Index">第几页</param> /// <param name="sTaxisField">排序字段(以,分隔)</param> /// <param name="iTaxis_Sign">排序0升序1降序</param> /// <param name="iPageCount">返回总页数</param> /// <param name="iiRecord_Count">返回总记录数</param> /// <returns>DataSet对象</returns> public DataSet ExecutePageDataSet(string sTable_Name, string sSign_Record, string sFilter_Condition, int iPage_Size, int iPage_Index, string sTaxisField, int iTaxis_Sign, out int iPageCount, out int iiRecord_Count) { DataSet ds = new DataSet(); try { SqlParameter[] param = new SqlParameter[] { new SqlParameter("@TableName",sTable_Name), new SqlParameter("@Fields",sSign_Record), new SqlParameter("@OrderField",sTaxisField), new SqlParameter("@Taxis_Sign",iTaxis_Sign), new SqlParameter("@sqlWhere",sFilter_Condition), new SqlParameter("@pageSize",iPage_Size), new SqlParameter("@pageIndex",iPage_Index), new SqlParameter("@TotalPage", System.Data.SqlDbType.Int), new SqlParameter("@totalCount", System.Data.SqlDbType.Int) }; param[7].Direction = System.Data.ParameterDirection.Output; param[8].Direction = System.Data.ParameterDirection.Output; //执行 DbCommand command = CreateComm(CommandType.StoredProcedure, "up_ProcCustomPage2005", param); ds = QueryDBMapping.ExecuteDataSet(command); iPageCount = 0; iiRecord_Count = 0; try { iPageCount = Convert.ToInt32(command.Parameters["@TotalPage"].Value); iiRecord_Count = Convert.ToInt32(command.Parameters["@totalCount"].Value); } catch (Exception ex) { iPageCount = 0; iiRecord_Count = 0; } finally { command.Parameters.Clear(); } } catch (Exception ex) { ds = null; iPageCount = 0; iiRecord_Count = 0; } finally { } return ds; } #endregion #region 不加上查询参数的 /// <summary> /// 返回首列 /// </summary> /// <param name="sql"></param> /// <param name="paras"></param> /// <returns></returns> public object ExecuteScalar(string sql) { object obj = null; try { DbCommand command = CreateComm(CommandType.Text, sql, null, true); return DBMapping.ExecuteScalar(command); } catch (Exception ex) { } finally { CloseConn(); } return obj; } /// <summary> /// 返回reader /// </summary> /// <returns></returns> public IDataReader ExecuteReader(string sql) { try { DbCommand command = CreateComm(CommandType.Text, sql, null, true); return QueryDBMapping.ExecuteReader(command); } catch (Exception ex) { return null; } } /// <summary> /// 得到影响行数 /// </summary> /// <returns></returns> public int ExecuteNonQuery(string sql) { try { DbCommand command = CreateComm(CommandType.Text, sql, null, true); return DBMapping.ExecuteNonQuery(command); } catch (Exception ex) { throw; } } #endregion #endregion #endregion } }
