Helper
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(); } } /// <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 } }
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(); }