znyc.recruitment.admin.api/Znyc.Recruitment.Admin.Commons/Core/DbContextCore/PostgreSQLDbContext.cs

using Microsoft.EntityFrameworkCore;
using Npgsql;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using Znyc.Recruitment.Admin.Commons.Encrypt;
using Znyc.Recruitment.Admin.Commons.Extensions;
using Znyc.Recruitment.Admin.Commons.IDbContext;

namespace Znyc.Recruitment.Admin.Commons.DbContextCore
{
    public class PostgreSQLDbContext : BaseDbContext, IPostgreSQLDbContext
    {
        public override DataTable GetDataTable(string sql, int cmdTimeout = 30, params DbParameter[] parameters)
        {
            return GetDataTables(sql, cmdTimeout, parameters).FirstOrDefault();
        }

        public override List<DataTable> GetDataTables(string sql, int cmdTimeout = 30, params DbParameter[] parameters)
        {
            List<DataTable> dts = new List<DataTable>();
            //TODO： connection 不能dispose 或者 用using，否则下次获取connection会报错提示“the connectionstring property has not been initialized。”
            DbConnection connection = Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            using (NpgsqlCommand cmd = new NpgsqlCommand(sql, (NpgsqlConnection)connection))
            {
                cmd.CommandTimeout = cmdTimeout;
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
                {
                    using (DataSet ds = new DataSet())
                    {
                        da.Fill(ds);
                        foreach (DataTable table in ds.Tables)
                        {
                            dts.Add(table);
                        }
                    }
                }
            }

            connection.Close();

            return dts;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            string conStringEncrypt = Configs.GetConfigurationValue("AppSetting", "ConStringEncrypt");
            isMultiTenant = Configs.GetConfigurationValue("AppSetting", "IsMultiTenant").ToBool();
            if (string.IsNullOrEmpty(dbConfigName))
            {
                dbConfigName = Configs.GetConfigurationValue("AppSetting", "DefaultDataBase");
            }

            string defaultSqlConnectionString = Configs.GetConnectionString(dbConfigName);
            if (conStringEncrypt == "true")
            {
                defaultSqlConnectionString = DEncrypt.Decrypt(defaultSqlConnectionString);
            }

            string dbType = dbConfigName.ToUpper();
            if (dbType.Contains("NPGSQL"))
            {
                optionsBuilder.UseNpgsql(defaultSqlConnectionString);
            }

            base.OnConfiguring(optionsBuilder);
        }
    }
}