public static List <DBColInfo> GetColsBySql(string Server, string DB, string Sql, string ReferTables)
{
string strSql = string.Format(";with t as ({0}) select top 1 * from t", Sql);
var dt = new DbContext(Server, DB).Query(strSql);
if (dt == null && dt.Columns == null && dt.Columns.Count <= 0)
{
return(null);
}
var list = new List <DBColInfo>();
foreach (DataColumn col in dt.Columns)
{
var dateType = GetColType(col.DataType);
if (dateType == "byte[]")
{
continue;
}
var field = new DBColInfo()
{
ColumnType = dateType,
ColumnName = col.ColumnName,
DisplayName = col.ColumnName,
IsNullable = col.AllowDBNull,
};
list.Add(field);
}
///填充关联表的显示名称
List <DBColInfo> ReferCols = new List <DBColInfo>();
if (list.Count() > 0 && ReferTables.IsNullOrEmpty() == false)
{
var arrRef = ReferTables.Split(',');
foreach (var tb in arrRef)
{
var tempCols = GetColsByTable(Server, DB, tb);
if (tempCols != null)
{
ReferCols.AddRange(tempCols);
}
}
if (ReferCols != null && ReferCols.Count() > 0)
{
list.ForEach(u =>
{
var dpCol = ReferCols.Find(u1 => u1.ColumnName == u.ColumnName);
if (dpCol != null)
{
u.DisplayName = dpCol.DisplayName;
}
});
}
}
return(list);
}
private void FillCols(DBTableInfo TableInfo, string Server, string DB, string Table)
{
string sql = @"WITH t AS (
SELECT t.name AS TableName ,
c.name AS ColumnName ,
c.column_id AS ColumnId,
c.is_nullable AS IsNullable ,
ISNULL( e.value,'') AS DesText ,
s.name AS ColumnType ,
c2.length AS ColumnLength ,
CAST(c2.xprec AS INT) AS ColumnLengthL ,
CAST(c2.xscale AS INT) AS ColumnLengthR
FROM sys.tables t
inner JOIN sys.columns c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties e ON t.object_id = e.major_id
AND e.minor_id = c.column_id
JOIN syscolumns c2 ON c.column_id = c2.colid
AND c.object_id = c2.id
JOIN systypes s ON c2.xtype = s.xtype
AND ( ( s.status = 0 )
OR ( s.status = 1
AND s.name = 'timestamp'
)
)
WHERE ISNULL(e.class_desc, 'OBJECT_OR_COLUMN') = 'OBJECT_OR_COLUMN'
AND ISNULL(e.name, 'MS_Description') = 'MS_Description'
)
SELECT * FROM t WHERE t.TableName='{0}' ORDER BY t.ColumnId asc " .FormatWith(Table);
var dt = new DbContext(Server, DB).Query(sql);
foreach (DataRow row in dt.Rows)
{
var col = new DBColInfo();
col.ColumnName = row["ColumnName"].ToString();
col.ColumnType = row["ColumnType"].ToString();
col.ColumnLength = row["ColumnLength"].ToString();
if (col.ColumnType == "nvarchar" || col.ColumnType == "nchar")
{
int len = 0;
if (int.TryParse(col.ColumnLength, out len))
{
col.ColumnLength = (len / 2).ToString();
}
}
col.ColumnLengthL = row["ColumnLengthL"].ToString();
col.ColumnLengthR = row["ColumnLengthR"].ToString();
col.IsNullable = row["IsNullable"].TryBoolean().Value;
if (row["DesText"] != DBNull.Value)
{
col.DisplayName = row["DesText"].ToString();
}
TableInfo.Cols.Add(col);
}
}
