/// 
/// 模块名：操作postgres数据库公共类/// 作用：根据业务需求对数据库进行操作./// 注:系统中的公共方法,根据需要，逐一引入/// 作者：东北大亨/// 编写日期：2012-03-19/// 
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;using System.Data;using Npgsql;using System.Reflection;namespace DBDHcms.DBUtility{    public class PostgreSqlHelp    {        #region 获得系统设置参数        /// 
        /// 获得系统设置参数        /// 
        /// 查询参数（传进参数为字符串）        /// 
     
      获得系统设置参数
             public static string GetConfigString()        {            return ConfigurationManager.AppSettings["ConnectionString"];        }        #endregion        #region 获得系统设置参数        /// 
        /// 获得系统设置参数        /// 
        /// 查询参数（传进参数为字符串）        /// 
     
      获得系统设置参数
             public static string GetConfigConnectString(string strKey)        {            return ConfigurationManager.ConnectionStrings[strKey].ConnectionString;        }        #endregion        #region 创建数据库连接        /// 
        /// 创建数据库连接        /// 
        /// 
             protected NpgsqlConnection GetDBConnection()        {            NpgsqlConnection connect;            connect = new NpgsqlConnection(GetConfigConnectString(""));            connect.Open();            return connect;        }        #endregion        #region 根据参数和sql语句查询结果返回DataTable        /// 
        /// 执行SQL返回DataView        /// 
        ///         ///         /// 
     
      返回Table
             public static DataView GetPostgreDataView(string strSql)        {            String strCon = GetConfigString();            DataView dv = new DataView();            using (NpgsqlConnection conn = new NpgsqlConnection(strCon))            {                conn.Open();                NpgsqlCommand cmd = new NpgsqlCommand(strSql, conn);                NpgsqlDataAdapter NpgDa = new NpgsqlDataAdapter(cmd);                DataSet ds = new DataSet();                NpgDa.Fill(ds);                if (ds.Tables.Count > 0)                {                    dv = ds.Tables[0].DefaultView;                }                return dv;            }        }        /// 
        /// 返回DataTable        /// 
        /// sql语句         /// 参数集合        /// 
     
      dataTable
             public static DataTable GetPostGreDataTable(string strSql, NpgsqlParameter[] cmdParms)        {            String strCon = GetConfigString();            NpgsqlConnection conn = new NpgsqlConnection(strCon);            DataTable dt;            try            {                conn.ConnectionString = strCon;                dt = new DataTable();                NpgsqlCommand cmd = new NpgsqlCommand();                PrepareCommand(cmd, conn, null, CommandType.Text, strSql, cmdParms);                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(strSql, conn);                adapter.SelectCommand = cmd;                adapter.Fill(dt);                return dt;            }            catch (NpgsqlException ex)            {                throw ex;            }            finally            {                conn.Close();            }        }        #endregion        #region 新增重载，执行SQL语句，返回影响的记录数        /// 
        /// 新增重载，执行SQL语句，返回影响的记录数        /// 
        /// SqlConnection对象        /// OleDbTransaction对象        /// SQL语句        /// 
     
      影响的记录数
             public static int ExecuteSql(NpgsqlConnection connection, NpgsqlTransaction trans, string SQLString, params NpgsqlParameter[] cmdParms)        {            using (NpgsqlCommand cmd = new NpgsqlCommand())            {                try                {                    PrepareCommand(cmd, connection, trans, SQLString, cmdParms);                    int rows = (int)cmd.ExecuteNonQuery();                    cmd.Parameters.Clear();                    return rows;                }                catch (NpgsqlException e)                {                    trans.Rollback();                    throw e;                }            }        }        #endregion        #region  执行查询语句，返回DataSet        /// 
        /// 执行查询语句，返回DataSet        /// 
        /// 查询语句        /// 
     
      DataSet
             public static DataSet Query(string SQLString, params NpgsqlParameter[] cmdParms)        {            //  创建数据库的连接            String strCon = GetConfigString();            using (NpgsqlConnection conn = new NpgsqlConnection(strCon))            {                NpgsqlCommand cmd = new NpgsqlCommand();                PrepareCommand(cmd, conn, null, SQLString, cmdParms);                using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))                {                    DataSet ds = new DataSet();                    try                    {                        da.Fill(ds, "ds");                        cmd.Parameters.Clear();                    }                    catch (NpgsqlException ex)                    {                        throw new Exception(ex.Message);                    }                    return ds;                }            }        }        #endregion        #region 参数赋值        /// 
        /// 参数赋值        /// 
        ///         ///         ///         ///         ///         ///         private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, CommandType cmdType,            string cmdText, NpgsqlParameter[] cmdParms)        {            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            if (trans != null)                cmd.Transaction = trans;            cmd.CommandType = cmdType;            if (cmdParms != null)            {                foreach (NpgsqlParameter parm in cmdParms)                    cmd.Parameters.Add(parm);            }        }        #endregion        #region 根据sql语句返回结果集        /// 
        /// 执行SQL返回Table        /// 
        /// 查询T-SQL        /// 
     
      返回Table
             public static DataTable GetPostgreTable(string strSqlText)        {            String strCon = GetConfigString();            using (NpgsqlConnection conn = new NpgsqlConnection(strCon))            {                conn.Open();                NpgsqlCommand cmd = new NpgsqlCommand(strSqlText, conn);                NpgsqlDataAdapter _NpgDa = new NpgsqlDataAdapter(cmd);                DataTable dt = new DataTable();                _NpgDa.Fill(dt);                return dt;            }        }        #endregion        #region  执行简单SQL语句        /// 
        /// 执行SQL语句，返回影响的记录数        /// 
        /// SQL语句        /// 
     
      影响的记录数
             public static int ExecuteSql(string SQLString)        {            String strCon = GetConfigString();            using (NpgsqlConnection connection = new NpgsqlConnection(strCon))            {                using (NpgsqlCommand cmd = new NpgsqlCommand(SQLString, connection))                {                    try                    {                        connection.Open();                        int rows = cmd.ExecuteNonQuery();                        return rows;                    }                    catch (NpgsqlException e)                    {                        connection.Close();                        throw e;                    }                }            }        }        #endregion        #region 执行存储过程        /// 
        ///  执行存储过程        /// 
        /// 存储过程名字        /// 参数        /// 返回参数        /// 
             public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)        {            String strCon = GetConfigString();            using (NpgsqlConnection conn = new NpgsqlConnection(strCon))            {                int result;                conn.Open();                NpgsqlCommand command = BuildQueryCommand(conn, storedProcName, parameters);                rowsAffected = command.ExecuteNonQuery();                result = (int)command.Parameters["@OUT_NUM"].Value;                conn.Close();                return result;            }        }        #endregion        #region 构建 OleDbCommand 对象(用来返回一个结果集，而不是一个整数值)        /// 
        /// 构建 OleDbCommand 对象(用来返回一个结果集，而不是一个整数值)        /// 
        /// 数据库连接        /// 存储过程名        /// 存储过程参数        /// 
     
      OleDbCommand
             private static NpgsqlCommand BuildQueryCommand(NpgsqlConnection connection, string storedProcName, IDataParameter[] parameters)        {            NpgsqlCommand command = new NpgsqlCommand(storedProcName, connection);            command.CommandType = CommandType.StoredProcedure;            foreach (NpgsqlParameter parameter in parameters)            {                if (parameter != null)                {                    // 检查未分配值的输出参数,将其分配以DBNull.Value.                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&                        (parameter.Value == null))                    {                        parameter.Value = DBNull.Value;                    }                    command.Parameters.Add(parameter);                }            }            return command;        }        #endregion        #region  执行多条SQL语句，实现数据库事务。        /// 
        /// 执行多条SQL语句，实现数据库事务。        /// 
        /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）        public static int ExecuteSqlTran(System.Collections.Generic.List
     
       cmdList)        {            //  创建数据库的连接            String ConnStr = GetConfigString();            using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr))            {                conn.Open();                using (NpgsqlTransaction trans = conn.BeginTransaction())                {                    NpgsqlCommand cmd = new NpgsqlCommand();                    try                    {                        int count = 0;                        //循环                        foreach (CommandInfo myDE in cmdList)                        {                            string cmdText = myDE.CommandText;                            NpgsqlParameter[] cmdParms = (NpgsqlParameter[])myDE.Parameters;                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);                            if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)                            {                                if (myDE.CommandText.ToLower().IndexOf("count(") == -1)                                {                                    trans.Rollback();                                    return 0;                                }                                object obj = cmd.ExecuteScalar();                                bool isHave = false;                                if (obj == null && obj == DBNull.Value)                                {                                    isHave = false;                                }                                isHave = Convert.ToInt32(obj) > 0;                                if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)                                {                                    trans.Rollback();                                    return 0;                                }                                if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)                                {                                    trans.Rollback();                                    return 0;                                }                                continue;                            }                            int val = cmd.ExecuteNonQuery();                            count += val;                            if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)                            {                                trans.Rollback();                                return 0;                            }                            cmd.Parameters.Clear();                        }                        trans.Commit();                        return count;                    }                    catch                    {                        trans.Rollback();                        throw;                    }                }            }        }        /// 
      
        ///         /// 
        /// 
              /// 
              /// 
              /// 
              /// 
              private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, string cmdText, NpgsqlParameter[] cmdParms)        {            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            if (trans != null)                cmd.Transaction = trans;            cmd.CommandType = CommandType.Text;            if (cmdParms != null)            {                // 多参数处理                foreach (NpgsqlParameter parameter in cmdParms)                {                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&                        (parameter.Value == null))                    {                        parameter.Value = DBNull.Value;                    }                    cmd.Parameters.Add(parameter);                }            }        }        #endregion        #region  批量执行SQL语句        /// 
      
        /// 批量执行SQL语句        /// 
        /// 
      SQL语句数组        /// 
      SQL参数对象数组        /// 
      
       返回成功与否
              public static int ExecuteSqls(String[] Sqlstr, List
      
        param)        {            String ConnStr = GetConfigString();            using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr))            {                using (NpgsqlCommand cmd = new NpgsqlCommand())                {                    NpgsqlTransaction tran = null;                    cmd.Transaction = tran;                    try                    {                        conn.Open();                        tran = conn.BeginTransaction();                        cmd.Connection = conn;                        cmd.Transaction = tran;                        int count = Sqlstr.Length;                        for (int i = 0; i < count; i++)                        {                            cmd.CommandText = Sqlstr[i];                            cmd.Parameters.AddRange(param[i]);                            cmd.ExecuteNonQuery();                        }                        tran.Commit();                        return 1;                    }                    catch                    {                        tran.Rollback();                        return 0;                    }                    finally                    {                        cmd.Dispose();                        conn.Close();                    }                }            }        }        #endregion        #region 获取泛型集合        /// 
       
        /// 获取泛型集合(2013年09月修改和添加)        /// 
        /// 
       
        类型
               /// 
       要查询的T-SQL        /// 
               public List
       
         GetEntityList
        
         (string strSqlText)        {            String strConnectStr = GetConfigString();            using (NpgsqlConnection conn = new NpgsqlConnection(strConnectStr))            {                using (NpgsqlDataAdapter sda = new NpgsqlDataAdapter(strSqlText, conn))                {                    DataSet ds = new DataSet();                    sda.Fill(ds);                    int iCount = ds.Tables[0].Rows.Count;                    if (0 < iCount)                    {                        return DataSetToList
         
          (ds, 0);                    }                    else                    {                        return null;                    }                }            }        }        #endregion        #region DataSetToList        /// 
          
 /// DataSetToList /// 
        /// 
          
           转换类型
                  /// 
          数据源        /// 
          需要转换表的索引        /// 
                  public List
          
            DataSetToList
           
            (DataSet dataSet, int tableIndex) { //确认参数有效 if (dataSet == null || dataSet.Tables.Count <= 0 || tableIndex < 0) return null; DataTable _returnDt = dataSet.Tables[tableIndex]; IList
            
              list = new List
             
              (); for (int i = 0; i < _returnDt.Rows.Count; i++) { //创建泛型对象 T _t = Activator.CreateInstance
              
               (); //获取对象所有属性 PropertyInfo[] _propertyInfo = _t.GetType().GetProperties(); for (int j = 0; j < _returnDt.Columns.Count; j++) { foreach (PropertyInfo _ProInfo in _propertyInfo) { //属性名称和列名相同时赋值 if (_returnDt.Columns[j].ColumnName.ToUpper().Equals(_ProInfo.Name.ToUpper())) { if (_returnDt.Rows[i][j] != DBNull.Value) { _ProInfo.SetValue(_t, _returnDt.Rows[i][j], null); } else { _ProInfo.SetValue(_t, null, null); } break; } } } list.Add(_t); } return list.ToList(); } #endregion #region 执行数据库查询操作 /// 
               
 /// 执行数据库查询操作( 2013-11-12添加) /// 
 /// 
                /// 
                /// 
                /// 
                /// 
                public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params NpgsqlParameter[] commandParameters) { NpgsqlCommand cmd = new NpgsqlCommand(); String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } #endregion public static int ExecuteSqlTran(List
               
                 SQLStringList) { String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { conn.Open(); NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; NpgsqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } finally { } } } public static object ExcuteScalar(string sql, params NpgsqlParameter[] pms) { String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { conn.Open(); NpgsqlCommand sqlcmd = new NpgsqlCommand(sql, conn); sqlcmd.CommandType = CommandType.Text; sqlcmd.CommandTimeout = 5000; if (pms != null && pms.Length > 0) sqlcmd.Parameters.AddRange(pms); object obj = sqlcmd.ExecuteScalar(); sqlcmd.Parameters.Clear(); return obj; } } public static DataSet ExcuteTableExpand(string sql, int intStartRecord, int intMaxRecord, string tableName) { DataSet ds = new DataSet(); String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { conn.Open(); NpgsqlCommand sqlcmd = new NpgsqlCommand(sql, conn); sqlcmd.CommandType = CommandType.Text; sqlcmd.CommandTimeout = 5000; NpgsqlDataAdapter sqladp = new NpgsqlDataAdapter(sqlcmd); sqladp.Fill(ds, intStartRecord, intMaxRecord, tableName); conn.Close(); return ds; } } #region 执行一条计算查询结果语句，返回查询结果 /// 
                
 /// 执行一条计算查询结果语句，返回查询结果（object）---好用测试通过 /// 
 /// 
                计算查询结果语句 /// 
                
                 查询结果（object）
                 public static object GetSingle(string SQLString, params NpgsqlParameter[] cmdParms) { // 创建数据库的连接 String ConnStr = GetConfigString(); using (NpgsqlConnection conn = new NpgsqlConnection(ConnStr)) { using (NpgsqlCommand cmd = new NpgsqlCommand()) { try { PrepareCommand(cmd, conn, null, SQLString, cmdParms); object obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (NpgsqlException e) { string strCode = e.Code; string ier = e.ErrorCode.ToString(); if ("23514".Equals(ier)) { // 主键重复 return 3; } else { // 未知错误 return 9; } } } } } private static void PrepareCommand1(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, string cmdText, NpgsqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text; if (cmdParms != null) { // 多参数处理 foreach (NpgsqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion }} /// 
                
 /// 模块名：操作postgres数据库公共类 /// 作用：处理传入参数实体操作 /// 作者：东北大亨 /// 编写日期：2012-03-19 /// 
 using System; using System.Collections.Generic; //using System.Linq; using System.Text; using Npgsql; namespace DBDHcms.DBUtility {     public enum EffentNextType     {         /// 
                
         /// 对其他语句无任何影响         /// 
         None,         /// 
                
         /// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务         /// 
         WhenHaveContine,         /// 
                
         /// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务         /// 
         WhenNoHaveContine,         /// 
                
         /// 当前语句影响到的行数必须大于0，否则回滚事务         /// 
         ExcuteEffectRows,         /// 
                
         /// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务         /// 
         SolicitationEvent     }     public class CommandInfo     {         public object ShareObject = null;         public object OriginalData = null;         event EventHandler _solicitationEvent;         public event EventHandler SolicitationEvent         {             add             {                 _solicitationEvent += value;             }             remove             {                 _solicitationEvent -= value;             }         }         public void OnSolicitationEvent()         {             if (_solicitationEvent != null)             {                 _solicitationEvent(this, new EventArgs());             }         }         public string CommandText;         public System.Data.Common.DbParameter[] Parameters;         public EffentNextType EffentNextType = EffentNextType.None;         public CommandInfo()         {         }         public CommandInfo(string sqlText, NpgsqlParameter[] para)         {             this.CommandText = sqlText;             this.Parameters = para;         }         public CommandInfo(string sqlText, NpgsqlParameter[] para, EffentNextType type)         {             this.CommandText = sqlText;             this.Parameters = para;             this.EffentNextType = type;         }     } }