/// <summary>
/// 获取表信息
/// </summary>
/// <param name="connectionString"></param>
/// <param name="tables"></param>
/// <returns></returns>
public static List <DbTable> GetDbTables(string connectionString, string tables = "")
{
//查询条件，如果包含,就是要in,否则就是要like查询
if (!string.IsNullOrEmpty(tables))
{
tables = ((!tables.Contains(",")) ? $" AND obj.name like '{tables}%' " : string.Format(" and obj.name in ('{0}')", tables.Replace(",", "','")));
}
string sql = $@"
SELECT obj.name Tablename,
schem.name Schemname,
idx.rows,
CAST(CASE
WHEN
(
SELECT COUNT(1)
FROM sys.indexes
WHERE object_id = obj.object_id
AND is_primary_key = 1
) >= 1 THEN
1
ELSE
0
END AS BIT) HasPrimaryKey,
b.value TableDesc,
t.*
FROM sys.objects obj
INNER JOIN sysindexes idx --行数
ON obj.object_id = idx.id
AND idx.indid <= 1
INNER JOIN sys.schemas schem --架构
ON obj.schema_id = schem.schema_id
LEFT JOIN sys.extended_properties b --描述
ON obj.object_id = b.major_id
AND b.minor_id = 0 AND b.name='MS_Description'
OUTER APPLY --主键名称和类型
(
SELECT TOP 1
colm.name AS TablePrimarkeyName,
systype.name AS TablePrimarkeyType
FROM sys.columns colm
INNER JOIN sys.types systype
ON colm.system_type_id = systype.system_type_id
WHERE colm.object_id = obj.object_id
AND colm.column_id IN (
SELECT ic.column_id
FROM sys.indexes idx
INNER JOIN sys.index_columns ic
ON idx.index_id = ic.index_id
AND idx.object_id = ic.object_id
WHERE idx.object_id = obj.object_id
AND idx.is_primary_key = 1
)
) t
WHERE obj.type = 'U' {tables}
ORDER BY obj.name";
DataTable dataTable = GetDataTable(connectionString, sql);
//List<DbTable> list = dataTable.Rows.Cast<DataRow>().Select(row => new DbTable
//{
// TableName = row.Field<string>("Tablename"),
// Schemname = row.Field<string>("Schemname"),
// TableDesc = row.Field<string>("TableDesc") ?? "",
// Rows = row.Field<int>("rows"),
// HasPrimaryKey = row.Field<bool>("HasPrimaryKey"),
// TablePrimarkeyName = row.Field<string>("TablePrimarkeyName") ?? "",
// TablePrimarkeyType = row.Field<string>("TablePrimarkeyType") ?? "",
// DbColumns = GetDbColumns(connectionString, row.Field<string>("Tablename"),)
//}).ToList();
List <DbTable> list = new List <DbTable>();
foreach (DataRow row in dataTable.Rows)
{
DbTable table = new DbTable
{
TableName = row.Field <string>("Tablename"),
Schemname = row.Field <string>("Schemname"),
TableDesc = row.Field <string>("TableDesc") ?? "",
Rows = row.Field <int>("rows"),
HasPrimaryKey = row.Field <bool>("HasPrimaryKey"),
TablePrimarkeyName = row.Field <string>("TablePrimarkeyName") ?? "",
TablePrimarkeyType = row.Field <string>("TablePrimarkeyType") ?? ""
};
table.DbColumns = GetDbColumns(connectionString, row.Field <string>("Tablename"), table);
list.Add(table);
}
return(list);
}
/// <summary>
/// 获取枚举字符串
/// </summary>
/// <param name="columnDesc"></param>
/// <param name="colName"></param>
/// <param name="table"></param>
/// <returns></returns>
private static string GetEnumStr(string columnDesc, string colName, DbTable table)
{
string regex = @"\(([^)]*)\)";
if (Regex.IsMatch(columnDesc, regex))
{
//枚举定义规则: xxxm枚举(value.key[Des];)
// 枚举结尾,()中是枚举定义,[]中是描述值可有可无, .分割值和key, ;分割每个定义
string enumString = columnDesc;
// "测试枚举(7.Sunday[星期天];1.Monday[星期一];2.Tuesday[星期二];3.Wednesday[];5.Friday)";
string math = Regex.Match(enumString, regex).Value; //得到枚举字符串，带()
string text = math.Replace("(", "").Replace(")", ""); //去除()
string one = " "; //一级空格
string two = " "; //二级空格
string enumName = colName + "Enum";
StringBuilder enumSb = new StringBuilder();
//拼接头部
enumSb.AppendLine($"{one}/// <summary>");
enumSb.AppendLine($"{one}/// {columnDesc}");
enumSb.AppendLine($"{one}/// </summary>");
enumSb.AppendLine($"{one}public enum {enumName}");
enumSb.AppendLine($"{one}{{");
int i = 1;
string[] arry = text.Split(';');
foreach (string item in arry)
{
if (item.Contains("[") && item.Contains("]")) //是否包含描述
{
string[] arry3 = item.Split('[');
string[] arry2 = arry3[0].Split('.');
//拼接枚举值
enumSb.AppendLine($"{two}/// <summary>");
enumSb.AppendLine($"{two}/// {arry2[1]}");
enumSb.AppendLine($"{two}/// </summary>");
enumSb.AppendLine($"{two}[Description(\"{arry3[1].Replace("]", "")}\")]");
if (i == arry.Length) //最后一个不带,
{
enumSb.AppendLine($"{two}{arry2[1]} = {arry2[0]}");
}
else //带,
{
enumSb.AppendLine($"{two}{arry2[1]} = {arry2[0]},");
}
enumSb.AppendLine("");
}
else
{
string[] arry2 = item.Split('.');
enumSb.AppendLine($"{two}/// <summary>");
enumSb.AppendLine($"{two}/// {arry2[1]}");
enumSb.AppendLine($"{two}/// </summary>");
if (i == arry.Length)
{
enumSb.AppendLine($"{two}{arry2[1]} = {arry2[0]}");
}
else
{
enumSb.AppendLine($"{two}{arry2[1]} = {arry2[0]},");
}
enumSb.AppendLine("");
}
i++;
}
enumSb.AppendLine($"{one}}}");
table.ListEnumStr.Add(enumSb.ToString());
return(enumName);
}
return(string.Empty);
}
/// <summary>
/// 获取列信息
/// </summary>
/// <param name="connectionString"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <DbColumn> GetDbColumns(string connectionString, string tableName, DbTable table)
{
string sql = $@"WITH indexCTE
AS (SELECT ic.column_id,
ic.index_column_id,
ic.object_id
FROM sys.indexes idx
INNER JOIN sys.index_columns ic
ON idx.index_id = ic.index_id
AND idx.object_id = ic.object_id
WHERE idx.object_id = OBJECT_ID('{tableName}') --找到该表的主键信息
AND idx.is_primary_key = 1)
SELECT colm.column_id ColumnID, --列id
CAST(CASE
WHEN indexCTE.column_id IS NULL THEN
0
ELSE
1
END AS BIT) IsPrimaryKey,
colm.name ColumnName, --列名称
systype.name ColumnType, --列类型
colm.is_identity IsIdentity, --是否自增长
colm.is_nullable IsNullable, --是否为空
CAST(colm.max_length AS INT) ByteLength, -- sys.columns中的max_length是字节
(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
) CharLength, --得到字符类型长度
CAST(colm.precision AS INT) Precision,
CAST(colm.scale AS INT) Scale,
sep.value ColumnDesc --列描述
FROM sys.columns colm
INNER JOIN sys.types systype
ON colm.system_type_id = systype.system_type_id
AND systype.user_type_id = colm.user_type_id --通过两个关联进行过滤得到用户创建的类型
LEFT JOIN sys.extended_properties sep
ON sep.major_id = colm.object_id --得到是这个表的
AND colm.column_id = sep.minor_id AND sep.name='MS_Description' --这列的
LEFT JOIN indexCTE
ON indexCTE.column_id = colm.column_id
AND indexCTE.object_id = colm.object_id
WHERE colm.object_id = OBJECT_ID('{tableName}')";
DataTable dataTable = GetDataTable(connectionString, sql);
List <DbColumn> list = new List <DbColumn>();
foreach (DataRow row in dataTable.Rows)
{
DbColumn column = new DbColumn
{
ColumnID = row.Field <int>("ColumnID"),
IsPrimaryKey = row.Field <bool>("IsPrimaryKey"),
ColumnName = row.Field <string>("ColumnName"),
ColumnType = row.Field <string>("ColumnType"),
ByteLength = row.Field <int>("ByteLength"),
CharLength = row.Field <int>("CharLength"),
IsIdentity = row.Field <bool>("IsIdentity"),
IsNullable = row.Field <bool>("IsNullable"),
Precision = row.Field <int>("Precision"),
Scale = row.Field <int>("Scale"),
ColumnDesc = row.Field <string>("ColumnDesc") ?? "",
TableName = tableName
};
string enumStr = GetEnumStr(column.ColumnDesc, column.ColumnName, table);
if (!string.IsNullOrEmpty(enumStr))
{
column.ColumnType = enumStr;
}
list.Add(column);
}
return(list);
}
