Pgsql帮助类 netcore
十年河东，十年河西，莫欺少年穷
学无止境。精益求精
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(); } } }
