.Net初使用MySql数据库生成Model类并与SqlServer简单对比

最近的一个项目，在.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; }
    }

//具体的处理方法，主要就是查询字段信息，循环生成Mode字段，就完了。

        /// <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 {0} Id ", GetCSTypeFromDbType(field.DataType)).Append("{ get; set; }").AppendLine();
                    }
                    else
                    {
                        sb.AppendFormat("        public {0}{1} {2} ", GetCSTypeFromDbType(field.DataType), field.IsNullable == "YES" ? "?" : "", field.ColumnName).Append("{ get; set; }").AppendLine();
                    }
                }
                foreach (var refrence in refrenceData)
                {
                    sb.AppendFormat("        public virtual {0} {0} ", refrence.ReferencedTableName).Append("{ get; set; }").AppendLine();
                }
                sb.Append(@"
    }
}
");
                var str = sb.ToString();
                string saveDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files\\");
                if (!Directory.Exists(saveDir)) Directory.CreateDirectory(saveDir);
                string saveFilePath = System.IO.Path.Combine(saveDir, tableName + ".cs");
                System.IO.File.AppendAllText(saveFilePath, str, Encoding.UTF8);
            }
        }

        /// <summary>
        /// Mysql数据库字段类型转化为C#字段类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        private string GetCSTypeFromDbType(string dbType)
        {
            string ret = dbType;
            switch (dbType)
            {
                case "nvarchar":
                case "text":
                case "varchar": ret = "string"; break;
                case "datetime":
                case "date": ret = "DateTime"; break;
                case "bit": ret = "bool"; break;
                case "bigint": ret = "long"; break;
            }
            return ret;
        }

二、与Sql Server简单对比

 1、从上面可以看到，查询MySql表结构的语句非常简单，全部的信息全部可以从information_schema.COLUMNS 这个对象里面查，还有外键表字段等等关联关系，全部都可以从INFORMATION_SCHEMA.KEY_COLUMN_USAGE对象查出来，只要传入指定的数据库名和表名就行了，相对 来说，Sql Server就复杂很多，如果一样要生成这个Model类，估计会非常头痛！

2、刚接触Mysql感觉有一些坑，比如存储过程的使用和Sql Server不一样、在.Net的EF中Mysql事务的使用和Sql Server不一样。最大的不同是，Mysql可以设置语句是否立即执行，如果没设置为true，在程序中调用SQL语句操作数据，都不会成功保存到数据库，后面都要加一个 commit; 才行。

现在用过一段时间，还行，有些东西注意一下就和sql server差不多。