最近的一个项目，在.net中使用MySQL数据库，背景是这样的：Sql Server要购买授权，每年100多万，领导说能省就省下这笔费用，可以慢慢往这方面发展。对于技术人员来说，这是个挑战，时间紧，技术上还有未知的风险。想想从实习到现在，四年多的.Net研发了，都是用的Sql Server数据库，这个mysql还是头一回用……
一、根据数据库生成EF的Model类
//表字段信息model
public class TableSchemaModel
{
/// <summary>
/// 表名
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 字段名
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// 是否可空：可空-YES,不可空-NO
/// </summary>
public string IsNullable { get; set; }
/// <summary>
/// 字段数据类型
/// </summary>
public string DataType { get; set; }
/// <summary>
/// 字符串长度
/// </summary>
public int? CharacterMaximumLength { get; set; }
/// <summary>
/// 字段描述
/// </summary>
public string ColumnComment { get; set; }
/// <summary>
/// 键标识：主键-PRI,外键-MUL
/// </summary>
public string ColumnKey { get; set; }
}
public class TableRefrenceModel
{
/// <summary>
/// 表名
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 字段名
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// 关联的表名
/// </summary>
public string ReferencedTableName { get; set; }
/// <summary>
/// 关联的表字段名
/// </summary>
public string ReferencedColumnName { get; set; }
}.Net Code：
//具体的处理方法，主要就是查询字段信息，循环生成Mode字段，就ok了。
--code begin
/// <summary>
/// 查询Mysql表字段信息
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public List<TableSchemaModel> GetTableSchemaData(string tableName)
{
List<TableSchemaModel> ret = null;
var tableSchema = dbContext.Database.Connection.Database;
string sql = string.Format("SELECT table_name TableName,column_Name ColumnName,is_nullable IsNullable,data_type DataType,character_maximum_length CharacterMaximumLength,column_comment ColumnComment,Column_Key ColumnKey FROM information_schema.COLUMNS WHERE table_schema='{0}' AND table_name = '{1}';", tableSchema, tableName);
ret = ExecuteQuerySql<TableSchemaModel>(sql);
return ret;
}
/// <summary>
/// 查询表中字段关联的所有外键表信息
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public List<TableRefrenceModel> GetTableRefrenceData(string tableName)
{
List<TableRefrenceModel> ret = null;
var tableSchema = dbContext.Database.Connection.Database;
string sql = string.Format("SELECT table_name TableName,column_name ColumnName,REFERENCED_TABLE_NAME ReferencedTableName,REFERENCED_COLUMN_NAME ReferencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_schema='{0}' AND table_name = '{1}';", tableSchema, tableName);
ret = ExecuteQuerySql<TableRefrenceModel>(sql);
return ret;
}
/// <summary>
/// 生成Model类文件中的非导航属性内容，到bin/Files 目录 的 tableName.cs文件
/// </summary>
/// <param name="tableName">表名，也是生成的Model类名</param>
public void GenerateModelJob(string tableName)
{
var data = taskRepository.GetTableSchemaData(tableName);
if (data != null)
{
var refrenceData = taskRepository.GetTableRefrenceData(tableName);
var priColumn = data.Where(o => o.ColumnKey == "PRI").First();
StringBuilder sb = new StringBuilder();
sb.Append(@"
namespace YCF.Stock.Core.Entities
{
using Abp.Domain.Entities;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using YCF.Stock.Core.Enum;
");
sb.AppendFormat(" [Table("{0}")]", tableName).AppendLine();
sb.AppendFormat(@" public partial class {0} : Entity<{1}>
", tableName, GetCSTypeFromDbType(priColumn.DataType)).Append(" {").AppendLine();
foreach (var field in data)
{
sb.AppendFormat(@"
/// <summary>
/// {0}
/// </summary>
", field.ColumnComment);
switch (field.DataType)
{
case "varchar":
case "nvarchar":
{
sb.AppendFormat(@" [StringLength({0})]
[MaxLength({0})]
", field.CharacterMaximumLength);
}
break;
case "date":
case "bit":
{
sb.AppendFormat(" [Column(TypeName = "{0}")]", field.DataType).AppendLine();
}
break;
}
if (field.ColumnKey == "PRI")
{
sb.AppendFormat(" [Column("{0}")]", field.ColumnName).AppendLine();
sb.AppendFormat(" public override&