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))
                {
                    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;
        }

    }

}