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);
}
