private void GetFields(TableInfo table) { string _sqltext = $@"SELECT ORDINAL_POSITION ,COLUMN_NAME ,IS_NULLABLE ,COLUMN_TYPE ,(CASE WHEN COLUMN_TYPE IN ('tinyint(1)','char(36)') THEN COLUMN_TYPE ELSE DATA_TYPE END) AS DATA_TYPE ,COLUMN_COMMENT ,COALESCE( CASE WHEN DATA_TYPE IN ('tinyint','smallint','mediumint','int','bigint','bit','double','float','decimal') THEN NUMERIC_PRECISION WHEN DATA_TYPE IN ('date','time','year','timestamp','datetime') THEN DATETIME_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH END ,0) AS LENGTEH ,COALESCE(NUMERIC_SCALE,0) AS NUMERIC_SCALE ,(EXTRA='auto_increment') as auto_increment from information_schema.`COLUMNS` where TABLE_SCHEMA='{table.Schema}' and TABLE_NAME='{table.Name}';"; _sqltext = string.Format(_sqltext, table.Schema, table.Name); SQLContext.ExecuteDataReader(dr => { DbFieldInfo fi = new DbFieldInfo { Oid = Convert.ToInt32(dr["ORDINAL_POSITION"]), Name = dr["COLUMN_NAME"].ToString(), Length = Convert.ToInt64(dr["LENGTEH"].ToString()), NotNull = dr["IS_NULLABLE"].ToString() == "NO", Comment = dr["COLUMN_COMMENT"].ToString(), Numeric_scale = Convert.ToInt32(dr["NUMERIC_SCALE"].ToString()), DbType = dr["DATA_TYPE"].ToString(), AutoIncrement = Convert.ToBoolean(dr["auto_increment"]) }; fi.CsType = MysqlType.SwitchToCSharp(fi.DbType); if (!fi.NotNull && fi.CsType != "string" && fi.CsType != "byte[]" && fi.CsType != "JsonElement") { fi.RelType = $"{fi.CsType}?"; } else { fi.RelType = fi.CsType; } if ((fi.RelType == "string" && fi.Length != 0 && fi.Length != 255) || (fi.Numeric_scale > 0) || (MysqlType.ContrastType(fi.DbType) == null)) { fi.DbTypeFull = dr["COLUMN_TYPE"].ToString(); } table.Fields.Add(fi); }, CommandType.Text, _sqltext); if (table.Type == TableType.Table) { GetPrimarykey(table); } }
private void GetPrimarykey(TableInfo table) { string _sqltext = $@"SELECT COLUMN_NAME,CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA = '{table.Schema}' and TABLE_NAME = '{table.Name}' AND CONSTRAINT_NAME = 'PRIMARY'"; SQLContext.ExecuteDataReader(dr => { var constaint = new ConstraintInfo { Field = dr["COLUMN_NAME"].ToString(), Name = dr["CONSTRAINT_NAME"].ToString(), Type = ConstraintType.PK }; table.Constraints.Add(constaint); table.Fields.Where(f => f.Name == constaint.Field).First().PrimaryKey = true; }, CommandType.Text, _sqltext); }
public void Initialize(ProjectConfig config) { this.config = config; Tables = new List <TableInfo>(); string schema = new MySqlConnection(config.ConnectionString).Database; #region dir CheckNotNull.NotEmpty(config.ContextName, nameof(config.ContextName)); if (config.Mode == GeneralInfo.Db) { CheckNotNull.NotEmpty(config.OutputDir, nameof(config.OutputDir)); Config = new GeneralConfig { OutputDir = config.OutputDir, ProjectName = config.ContextName, ModelPath = config.OutputDir }; if (!Directory.Exists(Config.ModelPath)) { Directory.CreateDirectory(Config.ModelPath); } } #endregion #region Tables string _sqltext = $@"SELECT TABLE_SCHEMA,TABLE_NAME,(CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE'ELSE TABLE_TYPE END ) AS TABLE_TYPE FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '{schema}'"; SQLContext.ExecuteDataReader(dr => { var table = new TableInfo() { Schema = dr["TABLE_SCHEMA"].ToString(), Name = dr["TABLE_NAME"].ToString(), Type = Enum.Parse <TableType>(dr["TABLE_TYPE"].ToString(), true) }; GetFields(table); Tables.Add(table); }, CommandType.Text, _sqltext); #endregion }
public void Initialize(ProjectConfig config) { this.config = config; Tables = new List <TableInfo>(); #region dir CheckNotNull.NotEmpty(config.ContextName, nameof(config.ContextName)); if (config.Mode == GeneralInfo.Db) { CheckNotNull.NotEmpty(config.OutputDir, nameof(config.OutputDir)); Config = new GeneralConfig { OutputDir = config.OutputDir, ProjectName = config.ContextName, ModelPath = config.OutputDir }; if (!Directory.Exists(Config.ModelPath)) { Directory.CreateDirectory(Config.ModelPath); } } #endregion #region Schemas string[] filters = new string[this.Filters.Count]; for (int i = 0; i < Filters.Count; i++) { filters[i] = $"'{Filters[i]}'"; } string sql = $@"SELECT schema_name FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN({string.Join(",", filters)}) ORDER BY SCHEMA_NAME; "; List <string> schemas = new List <string>(); SQLContext.ExecuteDataReader(dr => { schemas.Add(dr[0].ToString()); }, CommandType.Text, sql); #endregion #region Tables foreach (var schema in schemas) { string _sqltext = $@"SELECT table_name,'table' as type FROM INFORMATION_SCHEMA.tables WHERE table_schema='{schema}' AND table_type='BASE TABLE' UNION ALL SELECT table_name,'view' as type FROM INFORMATION_SCHEMA.views WHERE table_schema = '{schema}'"; SQLContext.ExecuteDataReader(dr => { var table = new TableInfo() { Schema = schema, Name = dr["table_name"].ToString(), Type = dr["type"].ToString() == "table" ? TableType.Table : TableType.View }; GetFields(table); Tables.Add(table); }, CommandType.Text, _sqltext); } #endregion }