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
