.net mysql 类库_(精华)2020年6月27日 C#类库 MySqlHelper(Ado.net数据库封装)
using EFCore.Sharding;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data.Common;
namespace Core.Util
{
///
/// MySql数据库操作帮助类
///
public class MySqlHelper : DbHelper
{
#region 构造函数
///
/// 构造函数
///
/// 完整连接字符串
public MySqlHelper(string conString)
: base(DatabaseType.MySql, conString)
{
}
#endregion
#region 私有成员
protected override Dictionary DbTypeDic { get; } = new Dictionary
{
{ "boolean",typeof(bool)},
{ "bit(1)",typeof(bool)},
{ "tinyint unsigned",typeof(bool)},
{ "binary",typeof(byte[])},
{ "varbinary",typeof(byte[])},
{ "blob",typeof(byte[])},
{ "longblob",typeof(byte[])},
{ "datetime",typeof(DateTime)},
{ "double",typeof(double)},
{ "char(36)",typeof(Guid)},
{ "smallint",typeof(Int16)},
{ "int",typeof(Int32)},
{ "bigint",typeof(Int64)},
{ "tinyint",typeof(bool)},
{ "float",typeof(float)},
{ "decimal",typeof(decimal)},
{ "char",typeof(string)},
{ "varchar",typeof(string)},
{ "text",typeof(string)},
{ "longtext",typeof(string)},
{ "time",typeof(TimeSpan)}
};
#endregion
#region 外部接口
///
/// 获取数据库中的所有表
///
/// 模式(架构)
///
public override List GetDbAllTables(string schemaName = null)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
string dbName = string.Empty;
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
dbName = conn.Database;
}
string sql = @"SELECT TABLE_NAME as TableName,table_comment as Description
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @dbName";
return GetListBySql(sql, new List { new MySqlParameter("@dbName", dbName) });
}
///
/// 通过连接字符串和表名获取数据库表的信息
///
/// 表名
///
public override List GetDbTableInfo(string tableName)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
string dbName = string.Empty;
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
dbName = conn.Database;
}
string sql = @"select DISTINCT
a.COLUMN_NAME as Name,
a.DATA_TYPE as Type,
(a.COLUMN_KEY = 'PRI') as IsKey,
(a.IS_NULLABLE = 'YES') as IsNullable,
a.COLUMN_COMMENT as Description,
a.ORDINAL_POSITION
from information_schema.columns a
where table_name=@tableName and table_schema=@dbName
ORDER BY a.ORDINAL_POSITION";
return GetListBySql(sql, new List { new MySqlParameter("@tableName", tableName), new MySqlParameter("@dbName", dbName) });
}
///
/// 生成实体文件
///
/// 表字段信息
/// 表名
/// 表描述信息
/// 文件路径(包含文件名)
/// 实体命名空间
/// 架构(模式)名
public override void SaveEntityToFile(List infos, string tableName, string tableDescription, string filePath, string nameSpace, string schemaName = null)
{
base.SaveEntityToFile(infos, tableName, tableDescription, filePath, nameSpace, schemaName);
}
#endregion
}
}