.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

}

}