PostgreSQL 封装操作数据库方法

/// <summary>
/// 模块名：操作postgres数据库公共类
/// 作用：根据业务需求对数据库进行操作.
/// 注:系统中的公共方法,根据需要，逐一引入
/// 作者：东北大亨
/// 编写日期：2012-03-19
/// </summary>

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 获得系统设置参数
        /// <summary>
        /// 获得系统设置参数
        /// </summary>
        /// <param name="key1">查询参数（传进参数为字符串）</param>
        /// <returns>获得系统设置参数</returns>
        public static string GetConfigString()
        {
            return ConfigurationManager.AppSettings["ConnectionString"];
        }
        #endregion

        #region 获得系统设置参数
        /// <summary>
        /// 获得系统设置参数
        /// </summary>
        /// <param name="key1">查询参数（传进参数为字符串）</param>
        /// <returns>获得系统设置参数</returns>
        public static string GetConfigConnectString(string strKey)
        {
            return ConfigurationManager.ConnectionStrings[strKey].ConnectionString;
        }
        #endregion

        #region 创建数据库连接
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        protected NpgsqlConnection GetDBConnection()
        {
            NpgsqlConnection connect;
            connect = new NpgsqlConnection(GetConfigConnectString(""));
            connect.Open();
            return connect;
        }
        #endregion

        #region 根据参数和sql语句查询结果返回DataTable
        /// <summary>
        /// 执行SQL返回DataView
        /// </summary>
        /// <param name="StrText"></param>
        /// <param name="StrConnection"></param>
        /// <returns>返回Table</returns>
        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;
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="strSql">sql语句 </param>
        /// <param name="cmdParms">参数集合</param>
        /// <returns>dataTable</returns>
        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语句，返回影响的记录数
        /// <summary>
        /// 新增重载，执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">OleDbTransaction对象</param>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        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
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        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 参数赋值
        /// <summary>
        /// 参数赋值
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        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语句返回结果集
        /// <summary>
        /// 执行SQL返回Table
        /// </summary>
        /// <param name="strSqlText">查询T-SQL</param>
        /// <returns>返回Table</returns>
        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语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        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 执行存储过程
        /// <summary>
        ///  执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名字</param>
        /// <param name="parameters">参数</param>
        /// <param name="rowsAffected">返回参数</param>
        /// <returns></returns>
        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 对象(用来返回一个结果集，而不是一个整数值)
        /// <summary>
        /// 构建 OleDbCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OleDbCommand</returns>
        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语句，实现数据库事务。
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> 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;
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        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语句
        /// <summary>
        /// 批量执行SQL语句
        /// </summary>
        /// <param name="Sqlstr">SQL语句数组</param>
        /// <param name="param">SQL参数对象数组</param>
        /// <returns>返回成功与否</returns>
        public static int ExecuteSqls(String[] Sqlstr, List<NpgsqlParameter[]> 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 获取泛型集合
        /// <summary>
        /// 获取泛型集合(2013年09月修改和添加)
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="strSqlText">要查询的T-SQL</param>
        /// <returns></returns>
        public List<T> GetEntityList<T>(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<T>(ds, 0);
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }
        #endregion

        #region DataSetToList
        /// <summary>
        /// DataSetToList
        /// </summary>
        /// <typeparam name="T">转换类型</typeparam>
        /// <param name="dataSet">数据源</param>
        /// <param name="tableIndex">需要转换表的索引</param>
        /// <returns></returns>
        public List<T> DataSetToList<T>(DataSet dataSet, int tableIndex)
        {
            //确认参数有效
            if (dataSet == null || dataSet.Tables.Count <= 0 || tableIndex < 0)
                return null;

            DataTable _returnDt = dataSet.Tables[tableIndex];

            IList<T> list = new List<T>();

            for (int i = 0; i < _returnDt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                //获取对象所有属性
                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 执行数据库查询操作
        /// <summary>
        /// 执行数据库查询操作( 2013-11-12添加)
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        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<String> 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  执行一条计算查询结果语句，返回查询结果
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）---好用测试通过
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        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

    }
}

/// <summary>
/// 模块名：操作postgres数据库公共类
/// 作用：处理传入参数实体操作
/// 作者：东北大亨
/// 编写日期：2012-03-19
/// </summary>
using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using Npgsql;

namespace DBDHcms.DBUtility
{
    public enum EffentNextType
    {
        /// <summary>
        /// 对其他语句无任何影响 
        /// </summary>
        None,
        /// <summary>
        /// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务
        /// </summary>
        WhenHaveContine,
        /// <summary>
        /// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
        /// </summary>
        WhenNoHaveContine,
        /// <summary>
        /// 当前语句影响到的行数必须大于0，否则回滚事务
        /// </summary>
        ExcuteEffectRows,
        /// <summary>
        /// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
        /// </summary>
        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;
        }
    }
}