C#连接PostgreSql数据的操作帮助类
using Npgsql;
using System;
using NpgsqlTypes;
using System.Data;
using System.Collections.Generic;
using System.Reflection;
using System.Configuration;
using swapCommon;
namespace PgSqlHelper
{
public class PgSqlDbHelper
{
private static string ConnectionString = ConfigCommon.GetConfig("ConnectionStrings:postgerSqlDbContext");
/// <summary>
/// 将参数中null值转换为DBNull --OK
/// </summary>
/// <param name="param"></param>
public static void SetParmDBNull(NpgsqlParameter[] param)
{
foreach (var p in param)
{
if (p.Value == null)
{
p.Value = DBNull.Value;
}
}
}
/// <summary>
/// 查询 postgre 数据库，返回 DataTable 数据
/// </summary>
/// <param name="sqlText">sql查询语句</param>
/// <returns></returns>
public static DataTable ExecuteQuery(string sqlText)
{
return ExecuteQuery(sqlText, null);
}
/// <summary>
/// 查询 postgre 数据库，返回 DataTable 数据--OK
/// </summary>
/// <param name="sqlText">sql查询语句</param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public static DataTable ExecuteQuery(string sqlText, NpgsqlParameter[] param)
{
using (NpgsqlConnection con = new NpgsqlConnection(ConnectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand(sqlText, con))
{
if (param != null)
{
SetParmDBNull(param);
cmd.Parameters.AddRange(param);
}
con.Open();
NpgsqlDataReader reader = cmd.ExecuteReader();
DataTable datatable = new DataTable();
// 添加DataTable列
for (int i = 0; i < reader.FieldCount; i++)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = reader.GetFieldType(i);
myDataColumn.ColumnName = reader.GetName(i);
datatable.Columns.Add(myDataColumn);
}
// 添加DataTable数据
while (reader.Read())
{
//var n1 = reader.GetInt32(0); // 第一列值
//var n2 = reader.GetString(1); // 第二列值
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
myDataRow[i] = reader[i].ToString();
}
datatable.Rows.Add(myDataRow);
}
reader.Close();
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); // 关闭关联的connection
return datatable;
}
}
}
/// <summary>
/// 增删改 postgre 数据库，返回是否成功标识--ok
/// </summary>
/// <param name="sqlText"></param>
/// <param name="param">参数集合</param>
/// <returns></returns>
public static int ExecuteCommand(string sqlText, NpgsqlParameter[] param)
{
using (NpgsqlConnection con = new NpgsqlConnection(ConnectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand(sqlText, con))
{
if (param != null)
{
SetParmDBNull(param);
cmd.Parameters.AddRange(param);
}
con.Open();
int cc = cmd.ExecuteNonQuery();
con.Close();
return cc;
}
}
}
/// <summary>
/// 返回第一行第一列的值 object -- ok
/// </summary>
/// <param name="sql"></param>
/// <param name="ps"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params NpgsqlParameter[] param)
{
using (NpgsqlConnection con = new NpgsqlConnection(ConnectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, con))
{
if (param != null)
{
SetParmDBNull(param);
cmd.Parameters.AddRange(param);
}
con.Open();//打开数据库
object cc = cmd.ExecuteScalar();
con.Close();
return cc;
}
}
}
#region 事务
/// <summary>
/// 返回的是受影响的行数-事务
/// </summary>
/// <param name="sql">多个sql</param>
/// <param name="ps">指定类型的参数</param>
/// <returns></returns>
public static int ExecuteNonQueryTrans(List<PgTansSql> tansSqls)
{
using (NpgsqlConnection con = new NpgsqlConnection(ConnectionString))
{
con.Open();
using (NpgsqlTransaction trans = con.BeginTransaction())
{
try
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
int result = 0;
cmd.Connection = con;
cmd.Transaction = trans;
foreach (var item in tansSqls)
{
cmd.CommandText = item.sql;
if (item.param != null)
{
SetParmDBNull(item.param);
cmd.Parameters.AddRange(item.param);
result+= cmd.ExecuteNonQuery();
}
}
trans.Commit();
con.Close();
return result;
}
}
catch (Exception ex)
{
trans.Rollback();
con.Close();
con.Dispose();
throw ex;
}
}
}
}
#endregion
}
public class PgTansSql
{
public string sql { get; set; }
public NpgsqlParameter[] param { get; set; }
}
public class DataTableToListHelper
{
/// <summary>
/// DataTable转换List，反射机制
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static IList<T> DataTableToList<T>(DataTable dt) where T : new()
{
IList<T> list = new List<T>();// 定义集合
Type type = typeof(T); // 获得此模型的类型
var tempName = string.Empty;
foreach (DataRow dr in dt.Rows)
{
T t = new T();
PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
if (dt.Columns.Contains(tempName))
{
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
list.Add(t);
}
return list;
}
/// <summary>
/// 确认 DataTable 里面最多只有一行数据，转成 Object，反射机制
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static T DataTableToObject<T>(DataTable dt) where T : new()
{
T t = new T();
var tempName = string.Empty;
if (dt.Rows == null || dt.Rows.Count == 0)
{
return t;
}
DataRow dr = dt.Rows[0];
PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
if (dt.Columns.Contains(tempName))
{
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value)
pi.SetValue(t, value, null);
}
}
return t;
}
}
}
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using PgSqlHelper;
using System;
using Microsoft.Extensions.DependencyInjection;
using System.Collections.Generic;
using Npgsql;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
List<PgTansSql> tansSqls = new List<PgTansSql>();
NpgsqlParameter[] sqlPara = new NpgsqlParameter[2];
//pgsql 的参数与SQL SERVER 的格式不一样
sqlPara[0] = new NpgsqlParameter<DateTime>(":createtime", DateTime.Now); //注意：事务处理中，参数化的名字不能相同。下面采用的是createtime2
sqlPara[1] = new NpgsqlParameter<int>(":sex", -2);//注意：事务处理中，参数化的名字不能相同。下面采用的是 sex2
tansSqls.Add(new PgTansSql() { sql = "update student set sex=:sex,createtime=:createtime where sname='陈'", param= sqlPara });
NpgsqlParameter[] sqlPara2 = new NpgsqlParameter[3];
sqlPara2[0] = new NpgsqlParameter<int>(":sex2", 4); //注意：事务处理中，参数化的名字不能相同。上面采用的是 :sex
sqlPara2[1] = new NpgsqlParameter<string>(":sname","李四");
sqlPara2[2] = new NpgsqlParameter<DateTime>(":createtime2", DateTime.Now.AddDays(1));//注意：事务处理中，参数化的名字不能相同。上面采用的是 :createtime
tansSqls.Add(new PgTansSql() { sql = "update student set sex=:sex2,sname=:sname,createtime=:createtime2 where sname='李四'", param = sqlPara2 });
var dt = PgSqlDbHelper.ExecuteNonQueryTrans(tansSqls);
Console.WriteLine(JsonConvert.SerializeObject(dt));
Console.ReadLine();
}
}
}
查看更多关于C#连接PostgreSql数据的操作帮助类的详细内容...