using EFCore.Sharding; using System; using System.Collections.Generic; using
System.Data; using System.Data.Common; using System.IO; namespace Core.Util {
/// <summary> /// describe ： Abstract help class for database operation /// </summary> public abstract class DbHelper {
#region Constructor /// <summary> /// 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 #region Private members /// <summary> /// Database type /// </summary>
protected DatabaseType _dbType; /// <summary> /// Connection string /// </summary> protected
string _conString; /// <summary> /// Additional namespace that entities need to refer to /// </summary> protected
string _extraUsingNamespace { get; set; } = string.Empty; /// <summary> ///
Type mapping dictionary /// </summary> protected abstract Dictionary<string, Type> DbTypeDic {
get; } #endregion #region External interface /// <summary> /// Through the database connection string and Sql Statement query return DataTable
/// </summary> /// <param name="sql">Sql sentence </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> ///
Through the database connection string and Sql Statement query return DataTable, Parameterized query /// </summary> /// <param
name="sql">Sql sentence </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> /// Through the database connection string and Sql Statement query return List /// </summary> /// <typeparam
name="T"> Entity class </typeparam> /// <param name="sqlStr">Sql sentence </param> ///
<returns></returns> public List<T> GetListBySql<T>(string sqlStr) { return
GetDataTableWithSql(sqlStr).ToList<T>(); } /// <summary> ///
Through the database connection string and Sql Statement query return List, Parameterized query /// </summary> /// <typeparam
name="T"> Entity class </typeparam> /// <param name="sqlStr">Sql sentence </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 the Sql sentence /// </summary> /// <param
name="sql">Sql sentence </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(); return count; } } } /// <summary> /// Execute the Sql sentence ///
</summary> /// <param name="sql">Sql sentence </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(); return count; } } } /// <summary> /// Get all the tables in the database
/// </summary> /// <param name="schemaName"> pattern （ framework ）</param> ///
<returns></returns> public abstract List<DbTableInfo> GetDbAllTables(string
schemaName= null); /// <summary> /// Get the information of database table by connection string and table name /// </summary> ///
<param name="tableName"> Table name </param> /// <returns></returns> public abstract List<
TableInfo> GetDbTableInfo(string tableName); /// <summary> /// Convert database type to corresponding C# data type
/// </summary> /// <param name="dbTypeStr"> 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 entity files /// </summary> /// <param
name="infos"> Table field information </param> /// <param name="tableName"> Table name </param> /// <param
name="tableDescription"> Table description information </param> /// <param
name="filePath"> File path （ Include file name ）</param> /// <param name="nameSpace"> Entity namespace </param>
/// <param name="schemaName"> framework （ 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 } }
Technology
Daily Recommendation