public IList <DataTableColumnDefCollection> GetTableStruct(List <string> tables)
{
var strSql = @" select cols.table_schema tableschema
,quote_ident(cols.table_name) tablename
,cols.ordinal_position ordinal
,cols.column_name colname
,col_description((cols.table_schema || '.' ||cols.table_name)::regclass::oid,cols.ordinal_position ) as description
,case when position('_' in cols.udt_name) > 0 then regexp_replace(cols.udt_name,'(_)(.*)','\2[]') else cols.udt_name end datatype
,case cols.data_type when 'character varying' then cols.character_maximum_length when 'numeric' then cols.numeric_precision else null end length
,cols.numeric_scale as precision
, CASE WHEN position( 'extval(' in cols.column_default) > 1 THEN '√' ELSE '' END as identity
, case when EXISTS ( select a.table_schema,a.table_name,b.constraint_name,a.ordinal_position as position,a.column_name as key_column
from information_schema.table_constraints b inner join information_schema.key_column_usage a on a.constraint_name = b.constraint_name
and a.constraint_schema = b.constraint_schema and a.constraint_name = b.constraint_name
where b.constraint_type = 'PRIMARY KEY' and a.table_schema = cols.table_schema and a.table_name = cols.table_name and a.column_name = cols.column_name) then '√' ELSE '' END primaykey
,case when cols.is_nullable = 'YES' THEN '√' ELSE '' END as isnull
,cols.column_default coldefault
,'' as memo
from
information_schema.columns cols inner join information_schema.tables tbs on cols.TABLE_NAME = tbs.TABLE_NAME
where tbs.table_type = 'BASE TABLE'
ORDER BY 1, 2 ";
var result = help.Query <DataTableColumnDef>(strSql).ToList();
IList <DataTableColumnDefCollection> result11 = new List <DataTableColumnDefCollection>();
List <DataTable> lst = new List <DataTable>();
var result11grp = result.GroupBy(p => new { p.tableschema, p.tablename });
foreach (var table in tables)
{
var dtdefs = result11grp.Where(p => table == $"{p.Key.tableschema}.{p.Key.tablename}").FirstOrDefault();
if (dtdefs != null)
{
DataTableColumnDefCollection col = new DataTableColumnDefCollection();
col.TableName = table;
col.AddRange(dtdefs.AsEnumerable());
result11.Add(col);
}
}
return(result11);
}
/// <summary>
/// 导出表数据为html格式 居中表格样式
/// </summary>
/// <param name="dt">DataTable，需要给TableName赋值</param>
/// <param name="KeepNull">保持Null为Null值，否则为空</param>
/// <param name="Path">保存路径</param>
/// <param name="hasReturn">携带返回目录链接</param>
/// <param name="tableDesc">携带返回目录链接</param>
/// <param name="alternateColor">是否隔行变色</param>
public static void CreateHtml(DataTableColumnDefCollection dt, bool KeepNull, string Path, bool hasReturn = true, string tableDesc = "", bool alternateColor = true)
{
var code = new StringBuilder();
code.AppendLine("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">");
code.AppendLine("<html xmlns=\"http://www.w3.org/1999/xhtml\">");
code.AppendLine("<head>");
code.AppendLine(" <META http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\"> ");
code.AppendLine($" <title>{dt.TableName}</title>");
code.AppendLine(" <style type=\"text/css\">");
code.AppendLine(" body");
code.AppendLine(" {");
code.AppendLine(" font-size: 9pt;");
code.AppendLine(" }");
code.AppendLine(" .styledb");
code.AppendLine(" {");
code.AppendLine(" font-size: 14px;");
code.AppendLine(" }");
code.AppendLine(" .styletab");
code.AppendLine(" {");
code.AppendLine(" font-size: 14px;");
code.AppendLine(" padding-top: 15px;");
code.AppendLine(" }");
code.AppendLine(" a");
code.AppendLine(" {");
code.AppendLine(" color: #015FB6;");
code.AppendLine(" }");
code.AppendLine(" a:link, a:visited, a:active");
code.AppendLine(" {");
code.AppendLine(" color: #015FB6;");
code.AppendLine(" text-decoration: none;");
code.AppendLine(" }");
code.AppendLine(" a:hover");
code.AppendLine(" {");
code.AppendLine(" color: #E33E06;");
code.AppendLine(" }");
if (alternateColor)
{
code.AppendLine(" tr:nth-child(even) {bgcolor: #CCC}");
code.AppendLine(" tr:nth-child(odd) {bgcolor: #FFF}");
}
code.AppendLine(" </style>");
code.AppendLine("</head>");
code.AppendLine("<body>");
code.AppendLine(" <div style=\"text-align: center\">");
code.AppendLine(" <div>");
code.AppendLine(" <table border=\"0\" cellpadding=\"5\" cellspacing=\"0\" width=\"95%\" style=\"text-align: left\">");
code.AppendLine(" <tr>");
code.AppendLine(" <td bgcolor=\"#FBFBFB\">");
code.AppendLine(" <table cellspacing=\"0\" cellpadding=\"5\" border=\"1\" width=\"100%\" bordercolorlight=\"#D7D7E5\" bordercolordark=\"#D3D8E0\">");
code.AppendLine(" <caption>");
code.AppendLine($" <div class=\"styletab\">{dt.TableName}{(string.IsNullOrEmpty(tableDesc) ? string.Empty : " （" + tableDesc + "） ")}{(hasReturn ? "<a href ='../../数据库表目录.html' style = 'float: left; margin-top: 6px;'>返回目录</a>" : string.Empty)}</div>");
//.FormatString(dt.TableName,
//tableDesc.Length == 0 ? string.Empty : " （" + tableDesc + "） ",
//(hasReturn ? "<a href='../数据库表目录.html' style='float: right; margin-top: 6px;'>返回目录</a>" : string.Empty));
code.AppendLine(" </caption>");
code.AppendLine(" <tr bgcolor=\"#DEEBF7\">"); //bgcolor="#DEEBF7"
//构建表头
Type itemtype = typeof(DataTableColumnDef);
var dtStructProps = itemtype.GetProperties().Where(p => p.CustomAttributes.Count() > 0).ToArray();
for (int i = 0; i < dtStructProps.Length; i++)
{
var dc = dtStructProps[i];
var prop = dc.GetCustomAttribute <DescriptionAttribute>();
code.AppendLine($" <td>{prop.Description}</td>");//属性描述 作为列名
}
//foreach (DataColumn dc in dt.Columns)
//{
// code.AppendLine($" <td>{dc.ColumnName}</td>");//.FormatString(dc.ColumnName));
//}
code.AppendLine(" </tr>");
//构建数据行
var dtsort = dt.OrderBy(p => p.ordinal);
foreach (var dr in dtsort)
{
code.AppendLine(" <tr>");
for (int i = 0; i < dtStructProps.Length; i++)
{
var dc = dtStructProps[i];
if (KeepNull && dc.GetValue(dr) == DBNull.Value)
{
code.AppendLine(" <td> </td>");
}
else
{
code.AppendLine($" <td>{(!string.IsNullOrWhiteSpace(dc.GetValue(dr)?.ToString()) ? dc.GetValue(dr)?.ToString() : " ")}</td>"); //.FormatString(
//dr[dc.ColumnName].ToString().Trim().Length > 0 ? dr[dc.ColumnName].ToString() : " "));
}
}
code.AppendLine(" </tr>");
}
code.AppendLine(" </table>");
code.AppendLine(" </td>");
code.AppendLine(" </tr>");
code.AppendLine(" </table>");
code.AppendLine(" </div>");
code.AppendLine(" </div>");
code.AppendLine("</body>");
code.AppendLine("</html>");
File.WriteAllText(Path, code.ToString(), Encoding.GetEncoding("gb2312"));
//File.WriteAllText(Path, code.ToString(), Encoding.UTF8);
}
