C# class library DbHelper(Ado.net)

using EFCore.Sharding;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;

namespace Core.Util
{
    /// <summary>
    ///Description: Database operation abstract helper class
    /// </summary>
    public abstract class DbHelper
    {
        #regionThe constructor

        /// <summary>
        ///The constructor
        /// </summary>
        /// <param name="dbType">Database type</param>
        /// <param name="conString">Full connection string</param>
        public DbHelper(DatabaseType dbType, string conString)
        {
            _dbType = dbType;
            _conString = conString;
        }

        #endregion

        #regionPrivate members

        /// <summary>
        ///Database type
        /// </summary>
        protected DatabaseType _dbType;

        /// <summary>
        ///Connection string
        /// </summary>
        protected string _conString;

        /// <summary>
        ///Additional namespaces that entities need to reference
        /// </summary>
        protected string _extraUsingNamespace { get; set; } = string.Empty;

        /// <summary>
        ///Type mapping dictionary
        /// </summary>
        protected abstract Dictionary<string, Type> DbTypeDic { get; }

        #endregion

        #regionExternal interface

        /// <summary>
        ///The DataTable is returned by a database connection string and Sql statement query
        /// </summary>
        /// <param name="sql">The Sql statement</param>
        /// <returns></returns>
        public DataTable GetDataTableWithSql(string sql)
        {
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _conString;
                if(conn.State ! = ConnectionState.Open) { conn.Open(); }using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sql;

                    DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet table = new DataSet();
                    adapter.Fill(table);

                    return table.Tables[0]; }}}/// <summary>
        ///DataTable is returned via database connection string and Sql statement query, parameterized query
        /// </summary>
        /// <param name="sql">The Sql statement</param>
        /// <param name="parameters">parameter</param>
        /// <returns></returns>
        public DataTable GetDataTableWithSql(string sql, List<DbParameter> parameters)
        {
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _conString;
                if(conn.State ! = ConnectionState.Open) { conn.Open(); }using (DbCommand cmd = conn.CreateCommand())
                {

                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    if(parameters ! =null && parameters.Count > 0)
                    {
                        foreach (var item in parameters)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }

                    DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet table = new DataSet();
                    adapter.Fill(table);
                    cmd.Parameters.Clear();

                    return table.Tables[0]; }}}/// <summary>
        ///Returns a List from a database connection string and Sql statement query
        /// </summary>
        /// <typeparam name="T">Entity class</typeparam>
        /// <param name="sqlStr">The Sql statement</param>
        /// <returns></returns>
        public List<T> GetListBySql<T> (string sqlStr)
        {
            return GetDataTableWithSql(sqlStr).ToList<T>();
        }

        /// <summary>
        ///Return List via database connection string and Sql statement query, parameterized query
        /// </summary>
        /// <typeparam name="T">Entity class</typeparam>
        /// <param name="sqlStr">The Sql statement</param>
        /// <param name="param">Query parameters</param>
        /// <returns></returns>
        public List<T> GetListBySql<T> (string sqlStr, List<DbParameter> param)
        {
            return GetDataTableWithSql(sqlStr, param).ToList<T>();
        }

        /// <summary>
        ///Execute Sql statement with no return value
        /// </summary>
        /// <param name="sql">The Sql statement</param>
        public int ExecuteSql(string sql)
        {
            int count = 0;
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                conn.ConnectionString = _conString;
                if(conn.State ! = ConnectionState.Open) { conn.Open(); }using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    count = cmd.ExecuteNonQuery();

                    returncount; }}}/// <summary>
        ///Execute Sql statement with no return value
        /// </summary>
        /// <param name="sql">The Sql statement</param>
        /// <param name="paramters"></param>
        public int ExecuteSql(string sql, List<DbParameter> paramters)
        {
            int count = 0;
            DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
            using (DbConnection conn = dbProviderFactory.CreateConnection())
            {
                if(conn.State ! = ConnectionState.Open) { conn.Open(); }using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sql;

                    if(paramters ! =null && paramters.Count > 0)
                    {
                        foreach (var item in paramters)
                        {
                            cmd.Parameters.Add(item);
                        }
                    }
                    count = cmd.ExecuteNonQuery();

                    returncount; }}}/// <summary>
        ///Get all tables in the database
        /// </summary>
        /// <param name="schemaName">Pattern (Architecture)</param>
        /// <returns></returns>
        public abstract List<DbTableInfo> GetDbAllTables(string schemaName = null);

        /// <summary>
        ///Gets database table information by connection string and table name
        /// </summary>
        /// <param name="tableName">The name of the table</param>
        /// <returns></returns>
        public abstract List<TableInfo> GetDbTableInfo(string tableName);

        /// <summary>
        ///Convert the database type to the corresponding C# data type
        /// </summary>
        /// <param name="dbTypeStr">The data type</param>
        /// <returns></returns>
        public virtual Type DbTypeStr_To_CsharpType(string dbTypeStr)
        {
            string _dbTypeStr = dbTypeStr.ToLower();
            Type type = null;
            if (DbTypeDic.ContainsKey(_dbTypeStr))
                type = DbTypeDic[_dbTypeStr];
            else
                type = typeof(string);

            return type;
        }

        /// <summary>
        ///Generating physical files
        /// </summary>
        /// <param name="infos">Table field information</param>
        /// <param name="tableName">The name of the table</param>
        /// <param name="tableDescription">Table Description</param>
        /// <param name="filePath">File path (including file name)</param>
        /// <param name="nameSpace">Entity namespace</param>
        /// <param name="schemaName">Schema (pattern) name</param>
        public virtual void SaveEntityToFile(List<TableInfo> infos, string tableName, string tableDescription, string filePath, string nameSpace, string schemaName = null)
        {
            string properties = "";
            string schema = "";
            if(! schemaName.IsNullOrEmpty()) schema =$@", Schema = ""{schemaName}"" ";
            infos.ForEach((item, index) =>
            {
                string isKey = item.IsKey ? $@"
        [Key, Column(Order = {index + 1})]," : "";
                Type type = DbTypeStr_To_CsharpType(item.Type);
                string isNullable = item.IsNullable && type.IsValueType ? "?" : "";
                string description = item.Description.IsNullOrEmpty() ? item.Name : item.Description;
                string newPropertyStr =
$@"
        /// <summary>
        /// {description}
        /// </summary>{isKey}
        public {type.Name}{isNullable} {item.Name}{{ get; set; }}";
                properties += newPropertyStr;
            });
            string fileStr =
$@"using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
{_extraUsingNamespace}
namespace {nameSpace}
{{
    /// <summary>
    /// {tableDescription}
    /// </summary>
    [Table(""{tableName}""{schema})]
    public class {tableName}
    {{
{properties}}}}}";
            FileHelper.WriteTxt(fileStr, filePath, FileMode.Create);
        }

        #endregion}}Copy the code