Column CreateColumn(Table table, DataRow dataRow) { Column column = new Column(table); column.AllowNull = Convert.ToBoolean(dataRow["AllowNull"]); column.ColumnName = dataRow["ColumnName"].ToString(); column.ColumnComment = dataRow["ColumnComment"].ToString(); column.IsPrimaryKey = Convert.ToBoolean(dataRow["ColumnKey"]); column.IsIdentity = Convert.ToBoolean(dataRow["IsIdentity"]); if (DbSetting.CheckIgnoreField(column.TableName + "." + column.ColumnName) || DbSetting.CheckIgnoreField("*." + column.ColumnName)) { return(null); } if (DbSetting.GetAliasField(column.TableName + "." + column.ColumnName, out string fieldName) || DbSetting.GetAliasField("*." + column.ColumnName, out fieldName)) { column.FieldName = fieldName; } else { column.FieldName = column.ColumnName; } string columnType = dataRow["ColumnType"].ToString(); string dataType = dataRow["DataType"].ToString(); column.RawType = dataType; if (dataType.Equals("bit", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = "bool"; } else if (dataType.Equals("datetime", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("timestamp", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("date", StringComparison.InvariantCultureIgnoreCase) ) { column.DataType = "DateTime"; } else if (dataType.Equals("decimal", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("money", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = "decimal"; } else if (dataType.Equals("double", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = "double"; } else if (dataType.Equals("float", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = "float"; } else if (dataType.Equals("char", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("varchar", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("text", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("tinytext", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("mediumtext", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("longtext", StringComparison.InvariantCultureIgnoreCase) ) { column.DataType = "string"; } else if (dataType.Equals("smallint", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = "short"; } else if (dataType.Equals("tinyint", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = "sbyte"; } else if (dataType.Equals("mediumint", StringComparison.InvariantCultureIgnoreCase) || dataType.Equals("int", StringComparison.InvariantCultureIgnoreCase) ) { column.DataType = "int"; } else if (dataType.Equals("bigint", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = "long"; } else { column.DataType = "byte[]"; } string tmpType = column.DataType; if (string.IsNullOrEmpty(column.ColumnComment)) { column.ColumnComment = column.ColumnName; } if (DbSetting.GetSpecifiedType(column.TableName + "." + column.ColumnName, out string specifiedName) || DbSetting.GetSpecifiedType("*." + column.ColumnName, out specifiedName)) { column.DataType = specifiedName; } else if (columnType.Contains("unsigned")) { if (column.DataType == "int" || column.DataType == "short" || column.DataType == "long") { column.DataType = string.Format("u{0}", column.DataType); } else if (column.DataType == "sbyte") { column.DataType = "byte"; } } if (column.AllowNull && !string.Equals(tmpType, "string", StringComparison.InvariantCultureIgnoreCase) && !string.Equals(tmpType, "byte[]", StringComparison.InvariantCultureIgnoreCase)) { column.DataType = string.Format("{0}?", column.DataType); } if (int.TryParse(dataRow["MaxLength"].ToString(), out int maxLength)) { column.MaxLength = maxLength; } if (column.AllowNull) { if (DbSetting.CheckNotNullField(column.TableName + "." + column.ColumnName) || DbSetting.CheckNotNullField(column.TableName + ".*") || DbSetting.CheckNotNullField("*." + column.ColumnName)) { column.AllowNull = false; } } if (DbSetting.GetDefaultValue(column.TableName + "." + column.ColumnName, out string defaultValue) || DbSetting.GetDefaultValue("*." + column.ColumnName, out defaultValue)) { if (DbSetting.GetDefaultValueStringMode()) { column.DefaultValue = "\"" + defaultValue + "\""; } else if (column.DataType == "string") { column.DefaultValue = "\"" + defaultValue + "\""; } else if (column.DataType == "DateTime" || column.DataType == "DateTime?") { if (defaultValue.StartsWith("DefaultTime.")) { column.DefaultValue = defaultValue; } else { column.DefaultValue = "\"" + defaultValue + "\""; } } else { column.DefaultValue = defaultValue; } } if (DbSetting.GetDbType(column.TableName + "." + column.ColumnName, out string dbType) || DbSetting.GetDbType("*." + column.ColumnName, out dbType)) { column.DBType = dbType; } if (DbSetting.GetControl(column.TableName + "." + column.ColumnName, out string control) || DbSetting.GetControl("*." + column.ColumnName, out control)) { column.Control = "FunctionControl." + control; if (control == "Create" || control == "Read") { column.NoUpdate = true; } } return(column); }
public List <Table> GetTables() { const string tableCommandText = @"SELECT A.name as TableName,A.object_id as TableCode, C.value as CommentText FROM sys.tables A left JOIN sys.extended_properties C ON C.major_id = A.object_id and minor_id=0 WHERE A.name = N'{0}'"; const string columnCommandText = @" with indexcte as( select ic.column_id, ic.index_column_id, ic.object_id from {0}.sys.indexes idx inner join {0}.sys.index_columns ic on idx.index_id = ic.index_id and idx.object_id = ic.object_id where idx.object_id = object_id('{0}.dbo.{1}') and idx.is_primary_key = 1 ) select colm.column_id ColumnId, cast( case when indexcte.column_id is null then 0 else 1 end as bit ) ColumnKey, cast(colm.max_length as int) bytelength, ( case when systype.name = 'nvarchar' and colm.max_length > 0 then colm.max_length / 2 when systype.name = 'nchar' and colm.max_length > 0 then colm.max_length / 2 when systype.name = 'ntext' and colm.max_length > 0 then colm.max_length / 2 else colm.max_length end ) MaxLength, colm.name ColumnName, systype.name DataType, colm.is_identity IsIdentity, colm.is_nullable AllowNull, cast(colm.precision as int) Precision, cast(colm.scale as int) Scale, prop.value ColumnComment from {0}.sys.columns colm inner join {0}.sys.types systype on colm.system_type_id = systype.system_type_id and colm.user_type_id = systype.user_type_id left join {0}.sys.extended_properties prop on colm.object_id = prop.major_id and colm.column_id = prop.minor_id left join indexcte on colm.column_id = indexcte.column_id and colm.object_id = indexcte.object_id where colm.object_id = object_id('{0}.dbo.{1}') order by colm.column_id" ; List <Table> tables = new List <Table>(); foreach (TableNameSet tableNameSet in DbSetting.GetTables()) { string tableCommandStr = String.Format(tableCommandText, tableNameSet.TableName); SqlConnection tableConn = new SqlConnection(_connectionString); tableConn.Open(); SqlCommand tableCommand = new SqlCommand(tableCommandStr, tableConn); SqlDataAdapter tableAd = new SqlDataAdapter(tableCommand); DataSet tableDs = new DataSet(); tableAd.Fill(tableDs); DataTable tableColumns = tableDs.Tables[0]; tableConn.Close(); if (tableColumns.Rows.Count == 0) { continue; } string tableComment = Convert.ToString(tableColumns.Rows[0]["CommentText"]); string tableCode = Convert.ToString(tableColumns.Rows[0]["TableCode"]); string columnCommandStr = String.Format(columnCommandText, this._dataBaseName, tableNameSet.TableName); SqlConnection columnConn = new SqlConnection(_connectionString); columnConn.Open(); SqlCommand columnCommand = new SqlCommand(columnCommandStr, tableConn); SqlDataAdapter columnAd = new SqlDataAdapter(columnCommand); DataSet columnDs = new DataSet(); columnAd.Fill(columnDs); DataTable columnColumns = columnDs.Tables[0]; tableConn.Close(); Table table = new Table(tableNameSet.AliasName, tableNameSet.TableName); if (String.IsNullOrEmpty(tableComment)) { tableComment = tableNameSet.TableName; } table.CommentText = tableComment; foreach (DataRow item in columnColumns.Rows) { Column column = CreateColumn(table, item); if (column != null) { table.SetColumn(column); } } tables.Add(table); } return(tables); }
public List <Table> GetTables() { const string tableCommandText = @"select TABLE_NAME as TableName,TABLE_COMMENT as CommentText from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}'"; const string columnCommandText = @" select ORDINAL_POSITION as 'ColumnId', case when COLUMN_KEY='PRI' then 1 else 0 end as 'ColumnKey', CHARACTER_MAXIMUM_LENGTH AS 'MaxLength', COLUMN_NAME AS 'ColumnName', DATA_TYPE AS 'DataType', case when EXTRA='auto_increment' then 1 else 0 end as 'IsIdentity', case when IS_NULLABLE='YES' then 1 else 0 end as 'AllowNull', NUMERIC_PRECISION as 'Precision', NUMERIC_SCALE as 'Scale', COLUMN_COMMENT as 'ColumnComment', COLUMN_TYPE AS 'ColumnType' from information_schema.COLUMNS where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}' order by ORDINAL_POSITION"; List <Table> tables = new List <Table>(); foreach (TableNameSet tableNameSet in DbSetting.GetTables()) { string tableCommandStr = String.Format(tableCommandText, this._dataBaseName, tableNameSet.TableName); MySqlConnection tableConn = new MySqlConnection(_connectionString); tableConn.Open(); MySqlCommand tableCommand = new MySqlCommand(tableCommandStr, tableConn); MySqlDataAdapter tableAd = new MySqlDataAdapter(tableCommand); DataSet tableDs = new DataSet(); tableAd.Fill(tableDs); DataTable tableColumns = tableDs.Tables[0]; tableConn.Close(); if (tableColumns.Rows.Count == 0) { continue; } string tableComment = Convert.ToString(tableColumns.Rows[0]["CommentText"]); string columnCommandStr = String.Format(columnCommandText, this._dataBaseName, tableNameSet.TableName); MySqlConnection columnConn = new MySqlConnection(_connectionString); columnConn.Open(); MySqlCommand columnCommand = new MySqlCommand(columnCommandStr, tableConn); MySqlDataAdapter columnAd = new MySqlDataAdapter(columnCommand); DataSet columnDs = new DataSet(); columnAd.Fill(columnDs); DataTable columnColumns = columnDs.Tables[0]; tableConn.Close(); Table table = new Table(tableNameSet.AliasName, tableNameSet.TableName); if (String.IsNullOrEmpty(tableComment)) { tableComment = tableNameSet.TableName; } table.CommentText = tableComment; foreach (DataRow item in columnColumns.Rows) { Column column = CreateColumn(table, item); if (column != null) { table.SetColumn(column); } } tables.Add(table); } return(tables); }
public List <Table> GetTables() { const string tableCommandText = @"select relname as ""TableName"",relfilenode as ""TableCode"",col_description(relfilenode,0) as ""CommentText"" from pg_class where relname = '{0}'"; const string columnCommandText = @"select a.ordinal_position as ""ColumnId"", (case when b.constraint_type='PRIMARY KEY' then 1 else 0 end)::BOOLEAN as ""ColumnKey"", a.character_maximum_length as ""MaxLength"", a.column_name as ""ColumnName"", a.udt_name as ""DataType"", (case when a.column_default like 'nextval%' then 1 else 0 end)::BOOLEAN as ""IsIdentity"", (case when a.is_nullable='YES' then 1 else 0 end)::BOOLEAN as ""AllowNull"", a.numeric_precision as ""Precision"", a.numeric_scale as ""Scale"", a.column_comment as ""ColumnComment"" from ( select table_name, ordinal_position, column_name, column_default, is_nullable, udt_name, character_maximum_length, numeric_precision, numeric_scale, col_description({2},ordinal_position) as column_comment from information_schema.columns where table_name='{1}' and table_catalog='{0}' ) as a left join ( SELECT kcu.table_name,kcu.column_name,tc.constraint_type FROM information_schema.key_column_usage kcu JOIN information_schema.table_constraints tc ON kcu.constraint_name=tc.constraint_name where kcu.table_name='{1}' and kcu.table_catalog='{0}' and tc.table_name='{1}' and tc.table_catalog='{0}' ) as b on a.table_name=b.table_name and a.column_name=b.column_name "; List <Table> tables = new List <Table>(); foreach (TableNameSet tableNameSet in DbSetting.GetTables()) { string tableCommandStr = String.Format(tableCommandText, tableNameSet.TableName); NpgsqlConnection tableConn = new NpgsqlConnection(_connectionString); tableConn.Open(); NpgsqlCommand tableCommand = new NpgsqlCommand(tableCommandStr, tableConn); NpgsqlDataAdapter tableAd = new NpgsqlDataAdapter(tableCommand); DataSet tableDs = new DataSet(); tableAd.Fill(tableDs); DataTable tableColumns = tableDs.Tables[0]; tableConn.Close(); if (tableColumns.Rows.Count == 0) { continue; } string tableComment = Convert.ToString(tableColumns.Rows[0]["CommentText"]); string tableCode = Convert.ToString(tableColumns.Rows[0]["TableCode"]); string columnCommandStr = String.Format(columnCommandText, this._dataBaseName, tableNameSet.TableName, tableCode); NpgsqlConnection columnConn = new NpgsqlConnection(_connectionString); columnConn.Open(); NpgsqlCommand columnCommand = new NpgsqlCommand(columnCommandStr, tableConn); NpgsqlDataAdapter columnAd = new NpgsqlDataAdapter(columnCommand); DataSet columnDs = new DataSet(); columnAd.Fill(columnDs); DataTable columnColumns = columnDs.Tables[0]; tableConn.Close(); Table table = new Table(tableNameSet.AliasName, tableNameSet.TableName); if (String.IsNullOrEmpty(tableComment)) { tableComment = tableNameSet.TableName; } table.CommentText = tableComment; foreach (DataRow item in columnColumns.Rows) { Column column = CreateColumn(table, item); if (column != null) { table.SetColumn(column); } } tables.Add(table); } return(tables); }