/// <summary>
/// 获取查询表结构
/// </summary>
/// <param name="type"></param>
/// <param name="tableName"></param>
/// <param name="dbName"></param>
/// <returns></returns>
private async Task <DataTable> GetTableStruct(string tableName, DbParam db)
{
DataTable dt = null;
string sql;
switch (db.DbType)
{
case "MySql":
sql = @"SELECT
COLUMN_NAME AS Name,
DATA_TYPE AS Type,
IFNULL(CHARACTER_MAXIMUM_LENGTH , 0) AS TypeLength,
COLUMN_COMMENT AS Comment,
IS_NULLABLE AS nullable,
COLUMN_KEY AS pk
FROM INFORMATION_SCHEMA.Columns WHERE table_name = @tableName AND table_schema = @dbName;";
dt = await dbClient.Ado.GetDataTableAsync(sql, new { tableName, dbName = db.DbName });
break;
case "SqlServer":
sql = @"SELECT col.name AS Name ,
ISNULL(ep.[value], '') AS Comment,
t.name AS Type,
col.length AS TypeLength,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN 'PRI'
ELSE ''
END AS pk ,
CASE WHEN col.isnullable = 1 THEN 'true'
ELSE 'false'
END AS nullable
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHERE obj.name = @tableName
ORDER BY col.colorder;";
dt = await dbClient.Ado.GetDataTableAsync(sql, new { tableName });
break;
case "Sqlite":
sql = "";
break;
case "Oracle":
sql = "";
break;
case "PostgreSQL":
sql = "";
break;
case "DbType.Dm":
sql = "";
break;
case "Kdbndp":
sql = "";
break;
default:
break;
}
return(dt);
}
