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;
        }
    }
}