Postgresql 数据库操作类

由于新开发的项目中，要求使用Postgresql数据库，我整理了一个数据库操作的方法类

首先从Nuget中添加Npgsql包

Postgresql帮助类

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Npgsql;
using System.Data.Common;
using DotNet.Utilities;

namespace DotNet.DataAccess
{
    ///<summary>
    ///有关数据库连接的方法
    /// 版本：2.0
    /// <author>
    ///		<name>LiWan</name>
    ///		<date>2018.04.24</date>
    /// </author>
    /// </summary>
    public class DbHelper : IDbHelper, IDisposable
    {
        //数据库连接字符串
        protected string connectionString = "";
        //数据库连接对象
        private NpgsqlConnection conn = null;
        /// <summary>
        /// 对象锁
        /// </summary>
        private static readonly Object locker = new Object();

        ///<summary>
        ///构造方法
        ///</summary>
        public DbHelper(string connstring)
        {
            DbCommon.ParamKey = ":";
            DbCommon.PlusSign = "+";
            DbCommon.GetDBNow = "now()";
            connectionString = connstring;
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        public NpgsqlConnection CreateConnect()
        {
            try
            {
                if (conn == null)
                {
                    return conn = new NpgsqlConnection(connectionString);
                }
                else
                {
                    lock (locker)
                    {
                        return conn;
                    }
                }
                // NpgsqlConnection conn = new NpgsqlConnection(connectionString);
                // return conn;
            }
            catch(Exception ex)
            {
                LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-CreateConnect()",ex.StackTrace,"","","");
                return null;
            }
        }
        #region 获取单一返回值
        /// <summary>
        /// 根据SQL执行，获取单一返回值
        /// </summary>
        /// <param name="cmdText">语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText)
        {
            object objValue = new object();
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
                    try
                    {
                        objValue = cmd.ExecuteScalar();                        
                    }
                    catch (Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-ExecuteScalar(string cmdText)",ex.StackTrace,"","","");
                        objValue= null;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return objValue;
        }

        /// <summary>
        /// 根据SQL执行，带参数，获取单一返回值
        /// </summary>
        /// <param name="cmdText">数据表名称</param>
        /// <param name="parameters">参数化</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, SqlParam[] parameters)
        {
            object objValue = new object();
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, parameters);
                    try
                    {
                        objValue = cmd.ExecuteScalar();                      
                    }
                    catch(Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-ExecuteScalar(string cmdText, SqlParam[] parameters)",ex.StackTrace,"","","");
                        objValue= null;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
             return objValue;
        }

        #endregion

        #region 返回受影响的行数
        /// <summary>
        /// 根据SQL执行，返回受影响的行数
        /// </summary>
        /// <param name="cmdText">执行语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText)
        {
            int rowsaffected=0;//受影响行数
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
                    try
                    {
                        rowsaffected= cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-ExecuteNonQuery(string cmdText)",ex.StackTrace,"","","");
                        rowsaffected= -1;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return rowsaffected;

        }

        /// <summary>
        /// 根据SQL执行，带参数，不带事务，返回受影响的行数
        /// </summary>
        /// <param name="cmdText">执行语句</param>
        /// <param name="parameters">参数化</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, SqlParam[] parameters)
        {
            int rowsaffected=0;//受影响行数
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, parameters);
                    try
                    {
                        cmd.Prepare();
                        rowsaffected = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    catch(Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-ExecuteNonQuery(string cmdText, SqlParam[] parameters)",ex.StackTrace,"","","");
                        rowsaffected=-1;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return rowsaffected;

        }


        /// <summary>
        /// 根据SQL执行，带参数，带事务，返回受影响的行数
        /// </summary>
        /// <param name="cmdText">执行语句</param>
        /// <param name="parameters">参数化</param>
        /// <returns></returns>
        public int ExecuteNonQueryTran(string cmdText, SqlParam[] parameters)
        {
            int rowsaffected=0;//受影响行数
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    using (NpgsqlTransaction tran = connection.BeginTransaction())
                    {
                        PrepareCommand(cmd, connection, tran, CommandType.Text, cmdText, parameters);
                        try
                        {
                            cmd.Prepare();
                            rowsaffected = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        catch(Exception ex)
                        {
                            LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-ExecuteNonQueryTran(string cmdText, SqlParam[] parameters)",ex.StackTrace,"","","");
                            rowsaffected=-1;
                        }
                        finally
                        {
                            connection.Close();
                            cmd.Dispose();
                            connection.Dispose();
                            tran.Dispose();
                        }
                    }
                }
            }
            return rowsaffected;

        }

        /// <summary>
        /// 批量执行SQL语句，不带事务，返回受影响的行数
        /// </summary>
        /// <param name="ListSql"></param>
        /// <returns></returns>
        public int BatchExecuteNonQuery(List<string> ListSql)
        {
            int rowsaffected=0;//受影响行数
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("begin;");
                    foreach (var sql in ListSql)
                    {
                        strSql.Append(sql + ";");
                    }
                    strSql.Append("end;");

                    PrepareCommand(cmd, connection, null, CommandType.Text, strSql.ToString(), null);
                    try
                    {
                        rowsaffected= cmd.ExecuteNonQuery();
                    }
                    catch(Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-BatchExecuteNonQuery(List<string> ListSql)",ex.StackTrace,"","","");
                        rowsaffected=-1;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return rowsaffected;
        }

        /// <summary>
        /// 使用事务批量执行SQL语句，返回受影响的行数
        /// </summary>
        /// <param name="ListSql"></param>
        /// <returns></returns>
        public int BatchExecuteNonQueryTrans(List<string> ListSql)
        {
            int rowsaffected=0;//受影响行数
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    using (NpgsqlTransaction tran = connection.BeginTransaction())
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("begin;");
                        foreach (var sql in ListSql)
                        {
                            strSql.Append(sql + ";");
                        }
                        strSql.Append("end;");

                        PrepareCommand(cmd, connection, tran, CommandType.Text, strSql.ToString(), null);
                        try
                        {
                            rowsaffected = cmd.ExecuteNonQuery();
                            tran.Commit();
                        }
                        catch(Exception ex)
                        {
                            tran.Rollback();
                            LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-BatchExecuteNonQueryTrans(List<string> ListSql)",ex.StackTrace,"","","");
                            rowsaffected=-1;
                        }
                        finally
                        {
                            connection.Close();
                            cmd.Dispose();
                            connection.Dispose();
                            tran.Dispose();
                        }
                    }
                }
            }
            return rowsaffected;
        }
        #endregion

        #region 根据SQL 返回DataSet数据集
        /// <summary>
        /// 执行查询语句，返回Dataset
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public DataSet ExcuteQuery(string cmdText)
        {
            DataSet ds = new DataSet();
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
                    try
                    {
                        cmd.Prepare();//预备语句，可以优化经核查那个使用的查询性能
                        NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                        da.Fill(ds, "ds");
                    }
                    catch(Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-ExcuteQuery(string cmdText)",ex.StackTrace,"","","");
                        ds= null;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行查询语句，返回Dataset(带参数)
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet ExcuteQuery(string cmdText, SqlParam[] parameters)
        {
            DataSet ds = new DataSet();
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, parameters);
                    try
                    {
                        cmd.Prepare();//预备语句，可以优化经核查那个使用的查询性能
                        NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch(Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-ExcuteQuery(string cmdText, SqlParam[] parameters)",ex.StackTrace,"","","");
                        ds= null;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return ds;

        }
        #endregion

        #region 根据存储过程 返回DataSet数据集
        /// <summary>
        /// 执行存储过程，返回数据信息
        /// </summary>
        /// <param name="ProcName">存储过程名称（需要使用双引号）</param>
        /// <returns></returns>
        public DataSet RubProc(string ProcName)
        {
            DataSet ds = new DataSet();
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.StoredProcedure, ProcName, null);
                    try
                    {
                        cmd.Prepare();//预备语句，可以优化经核查那个使用的查询性能
                        NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                        da.Fill(ds, "ds");
                    }
                    catch(Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-RubProc(string ProcName)",ex.StackTrace,"","","");
                        ds= null;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 执行存储过程，带参数，返回数据信息
        /// </summary>
        /// <param name="ProcName">存储过程名称（需要使用双引号）</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public DataSet RubProc(string ProcName, SqlParam[] parameters)
        {
            DataSet ds = new DataSet();
            using (NpgsqlConnection connection = CreateConnect())
            {
                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    PrepareCommand(cmd, connection, null, CommandType.StoredProcedure, ProcName, parameters);
                    try
                    {
                        cmd.Prepare();//预备语句，可以优化经核查那个使用的查询性能
                        NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch(Exception ex)
                    {
                        LogWebAPI.WriteAPILog("EX",ex.Message.ToString(),"DbHelper-RubProc(string ProcName, SqlParam[] parameters)",ex.StackTrace,"","","");
                        ds= null;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        connection.Dispose();
                    }
                }
            }
            return ds;

        }
        #endregion

        #region 执行命令
        /// <summary>  
        /// 生成要执行的命令  
        /// </summary>  
        /// <remarks>参数的格式：冒号+参数名</remarks>  
        private static void PrepareCommand(NpgsqlCommand cmd, NpgsqlConnection conn, NpgsqlTransaction trans, CommandType cmdType, string cmdText, SqlParam[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText.Replace("@", ":").Replace("?", ":").Replace("[", "\"").Replace("]", "\"");
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParam parmitem in cmdParms)
                {
                    NpgsqlParameter parm = new NpgsqlParameter();
                    parm.ParameterName = parmitem.FieldName.Replace("@", ":").Replace("?", ":");
                    parm.Value = parmitem.FiledValue;
                    parm.DbType = parmitem.DataType;//默认数据类型
                    cmd.Parameters.Add(parm);
                }
            }
        }
        #endregion

        public void Dispose()
        {

        }
    }
}

转载自：https://blog.csdn.net/liwan09/article/details/81774700