©2020-2024 ioDraw All rights reserved,
Privacy Policy
using EFCore.Sharding; using System; using System.Collections.Generic; using
System.Data; using System.Data.Common; using System.IO; namespace Core.Util {
/// <summary> /// 描述：数据库操作抽象帮助类 /// </summary> public abstract class DbHelper {
#region 构造函数 /// <summary> /// 构造函数 /// </summary> /// <param
name="dbType">数据库类型</param> /// <param name="conString">完整连接字符串</param> public
DbHelper(DatabaseType dbType, string conString) { _dbType = dbType; _conString =
conString; } #endregion #region 私有成员 /// <summary> /// 数据库类型 /// </summary>
protected DatabaseType _dbType; /// <summary> /// 连接字符串 /// </summary> protected
string _conString; /// <summary> /// 实体需要引用的额外命名空间 /// </summary> protected
string _extraUsingNamespace { get; set; } = string.Empty; /// <summary> ///
类型映射字典 /// </summary> protected abstract Dictionary<string, Type> DbTypeDic {
get; } #endregion #region 外部接口 /// <summary> /// 通过数据库连接字符串和Sql语句查询返回DataTable
/// </summary> /// <param name="sql">Sql语句</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> ///
通过数据库连接字符串和Sql语句查询返回DataTable,参数化查询 /// </summary> /// <param
name="sql">Sql语句</param> /// <param name="parameters">参数</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> /// 通过数据库连接字符串和Sql语句查询返回List /// </summary> /// <typeparam
name="T">实体类</typeparam> /// <param name="sqlStr">Sql语句</param> ///
<returns></returns> public List<T> GetListBySql<T>(string sqlStr) { return
GetDataTableWithSql(sqlStr).ToList<T>(); } /// <summary> ///
通过数据库连接字符串和Sql语句查询返回List,参数化查询 /// </summary> /// <typeparam
name="T">实体类</typeparam> /// <param name="sqlStr">Sql语句</param> /// <param
name="param">查询参数</param> /// <returns></returns> public List<T> GetListBySql<T>
(string sqlStr, List<DbParameter> param) { return GetDataTableWithSql(sqlStr,
param).ToList<T>(); } /// <summary> /// 执行无返回值的Sql语句 /// </summary> /// <param
name="sql">Sql语句</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> /// 执行无返回值的Sql语句 ///
</summary> /// <param name="sql">Sql语句</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> /// 获取数据库中的所有表
/// </summary> /// <param name="schemaName">模式（架构）</param> ///
<returns></returns> public abstract List<DbTableInfo> GetDbAllTables(string
schemaName= null); /// <summary> /// 通过连接字符串和表名获取数据库表的信息 /// </summary> ///
<param name="tableName">表名</param> /// <returns></returns> public abstract List<
TableInfo> GetDbTableInfo(string tableName); /// <summary> /// 将数据库类型转为对应C#数据类型
/// </summary> /// <param name="dbTypeStr">数据类型</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> /// 生成实体文件 /// </summary> /// <param
name="infos">表字段信息</param> /// <param name="tableName">表名</param> /// <param
name="tableDescription">表描述信息</param> /// <param
name="filePath">文件路径（包含文件名）</param> /// <param name="nameSpace">实体命名空间</param>
/// <param name="schemaName">架构（模式）名</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 } }