/// <summary>
/// 取得 DB Table 的結構資訊
/// </summary>
public static DbTableSchema GetDbTableSchema(string connectionString, string tableName)
{
SqlHelper sqlHelper = new SqlHelper(connectionString);
DbTableSchema schema = new DbTableSchema();
schema.Fields = sqlHelper.ExecuteDataTable(@"
SELECT
c.name as Name,
t.name as DbType,
c.isnullable as Nullable,
c.prec as [Length],
c.xprec as Prec,
c.xscale as Scale,
p.value as Description
FROM syscolumns c
INNER JOIN sysobjects o ON o.id = c.id
LEFT JOIN systypes t ON t.xusertype = c.xusertype
LEFT JOIN sys.extended_properties p ON p.major_id = c.id
AND p.minor_id = c.colid
AND p.name = 'MS_Description'
WHERE o.type = 'U' AND o.name = @TableName
ORDER BY c.colorder",
new System.Data.SqlClient.SqlParameter("@TableName", tableName))
.Rows.ToModels <DbTableSchema.Field>();
foreach (DbTableSchema.Field field in schema.Fields)
{
field.DbFullType = GetDbFullTypeName(field.DbType, field.Length, field.Prec, field.Scale);
field.CsType = GetCsTypeName(field.DbType, field.Nullable == 1);
}
return(schema);
}
public static DbTableSchema GetDbTableSchemaByContext(string context)
{
DbTableSchema schema = new DbTableSchema();
IEnumerable <string> rows = context.Replace("\r\n", "\n").Split('\n').Where(x => string.IsNullOrWhiteSpace(x) == false);
foreach (string row in rows)
{
// TODO:
}
return(schema);
}
/// <summary>
/// 從 DB 取得 Table 的欄位資訊並生成 cs 的 Class
/// </summary>
public static string GenerateModel(string connectionString, string tableName, bool useSummary = true, bool useColumnAttr = false)
{
List <string> props = new List <string>();
DbTableSchema schema = GetDbTableSchema(connectionString, tableName);
foreach (var field in schema.Fields)
{
string prop = "";
if (useSummary && !string.IsNullOrWhiteSpace(field.Description))
{
prop = $@"
{prop.ReplaceEndline(@"
")}
/// <summary>
/// {field.Description.ReplaceEndline(@"
/// ")}
/// </summary>".CutEmptyHead().DecreaseIndentAllLines();
}
if (useColumnAttr)
{
if (field.DbType.In(
"char", "nchar", "ntext", "nvarchar", "text", "varchar", "xml"))
{
if (field.Nullable == 0)
{
prop = $@"
{prop.ReplaceEndline(@"
")}
[Required]".CutEmptyHead().DecreaseIndentAllLines();
}
prop = $@"
{prop.ReplaceEndline(@"
")}
[Column(""{field.Name}"")]".CutEmptyHead().DecreaseIndentAllLines();
if (field.Length > 0)
{
prop = $@"
{prop.ReplaceEndline(@"
")}
[StringLength({field.Length})]".CutEmptyHead().DecreaseIndentAllLines();
}
}
else
{
prop = $@"
{prop.ReplaceEndline(@"
")}
[Column(""{field.Name}"", TypeName = ""{field.DbFullType}"")]".CutEmptyHead().DecreaseIndentAllLines();
}
}
prop = $@"
{prop.ReplaceEndline(@"
")}
public {field.CsType} {field.Name} {{ get; set; }}".CutEmptyHead().DecreaseIndentAllLines();
props.Add(prop);
}
return($@"
[Table(""{tableName}"")]
public class {tableName}
{{
{string.Join(@"
", props.Select(x => x.ReplaceEndline(@"
"))).CutEmptyHead().TrimStart()}
}}".CutEmptyHead().DecreaseIndentAllLines());
}
