十年河東,十年河西,莫欺少年窮
學無止境。精益求精
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)) { 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)) { 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)) { 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(string sql, params NpgsqlParameter[] ps) { using (NpgsqlConnection con = new NpgsqlConnection(ConnectionString)) { con.Open(); using (NpgsqlTransaction trans = con.BeginTransaction()) { try { using (NpgsqlCommand cmd = new NpgsqlCommand()) { cmd.Connection = con; cmd.Transaction = trans; cmd.CommandText = sql; if (ps != null) { cmd.Parameters.AddRange(ps); } int cc = cmd.ExecuteNonQuery(); trans.Commit(); con.Close(); return cc; } } catch (Exception ex) { trans.Rollback(); con.Close(); con.Dispose(); throw ex; } } } } #endregion } 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; } } }