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