使用LINQPad快速產生MSSQL中Table的POCO

前言

最近需要產生大量的資料表模型(DBModle)，但時常常又會面臨表格修改，環境有些限制所以不是使用EF，所以找了一些產生POCO的方式，搭配LINQPad就可以快速產生對應資料模型。

需求

首先其實網路上可以找到不少作法，可以產生出基礎的資料模型，但是不太符合我當前的需求，我還想要取得資料表中的描述資訊當作註解，方便識別資料模型每一個屬性用途。

作法

擴充 MyExtensions

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
public static class MyExtensions
{
  // Write custom extension methods here. They will be available to all queries.
   
  public static string DumpClass(this IDbConnection connection, string sql, string >className = "Info",string tableName = "Info")
  {
  	var descDict = connection.DumpClassDesc(tableName);
  	if (connection.State != ConnectionState.Open)
  	{
  		connection.Open();
  	}

  	var cmd = connection.CreateCommand();
  	cmd.CommandText = sql;
  	var reader = cmd.ExecuteReader();

  	var builder = new StringBuilder();
  	do
  	{
  		if (reader.FieldCount <= 1) continue;

  		if (descDict.TryGetValue(tableName, out _))
  		{
  			builder.AppendLine("/// <summary>");
  			builder.AppendLine($"/// {descDict[tableName]}");
  			builder.AppendLine("/// </summary>");
  		}

  		builder.AppendFormat("public class {0}{1}", className, Environment.NewLine);
  		builder.AppendLine("{");
  		var schema = reader.GetSchemaTable();

  		foreach (DataRow row in schema.Rows)
  		{
  			var type = (Type)row["DataType"];
  			var name = TypeAliases.ContainsKey(type) ? TypeAliases[type] : type.Name;
  			var isNullable = (bool)row["AllowDBNull"] && NullableTypes.Contains(type);
  			var collumnName = (string)row["ColumnName"];

  			if (descDict.TryGetValue(collumnName, out _))
  			{
  				builder.AppendLine("\t/// <summary>");
  				builder.AppendLine($"\t/// {descDict[collumnName]}");
  				builder.AppendLine("\t/// </summary>");
  			}
  			builder.AppendLine(string.Format("\tpublic {0}{1} {2} {{ get; set; }}", >name, isNullable ? "?" : string.Empty, collumnName));
  			//builder.AppendLine();
  		}

  		builder.AppendLine("}");
  		builder.AppendLine();
  	} while (reader.NextResult());
  	//關閉連線
  	connection.Close();
  	return builder.ToString();
  }

  private static Dictionary<string, string> DumpClassDesc(this IDbConnection connection, >string tableName = "Info")
  {
  	if (connection.State != ConnectionState.Open)
  	{
  		connection.Open();
  	}
  	var sign = ".";
  	var defaultDbo = "dbo";
  	tableName = tableName.Replace("[","").Replace("]","");
  	if (tableName.Contains(sign)) 
  	{
  		var temp = tableName.Split(sign);
  		defaultDbo = temp.First();
           tableName = temp.Last();
  	}

  	var sql = $@"SELECT objname AS [ColunmName], value AS [Desc]
                    FROM fn_listextendedproperty (NULL, 'schema', '{defaultDbo}', 'table', >N'{tableName}', NULL, default)
                    WHERE name = 'MS_Description'
                    AND objtype = 'TABLE'
                    UNION
                    SELECT objname AS [ColunmName], value AS [Desc]
                    FROM fn_listextendedproperty (NULL, 'schema', '{defaultDbo}', 'table', >N'{tableName}', 'column', default)
                    WHERE name = 'MS_Description' AND objtype = 'COLUMN'";
  	var dict = new Dictionary<string, string>();
  	var cmd = connection.CreateCommand();
  	cmd.CommandText = sql;
  	try
  	{
  		var reader = cmd.ExecuteReader();

  		while (reader.Read())
  		{
  			var val = reader[1].ToString().Replace("\r\n", " ");
  			dict.Add($"{reader[0]}", $"{val}");
  		}
  		reader.Close();
  	}
  	catch (Exception ex)
  	{
  		Console.WriteLine(ex.Message);
  	}
  	connection.Close();
  	return dict;
  }

  private static readonly Dictionary<Type, string> TypeAliases = new Dictionary<Type, >string> {
  	{ typeof(int), "int" },
  	{ typeof(short), "short" },
  	{ typeof(byte), "byte" },
  	{ typeof(byte[]), "byte[]" },
  	{ typeof(long), "long" },
  	{ typeof(double), "double" },
  	{ typeof(decimal), "decimal" },
  	{ typeof(float), "float" },
  	{ typeof(bool), "bool" },
  	{ typeof(string), "string" }
  };

  private static readonly HashSet<Type> NullableTypes = new HashSet<Type> {
  	typeof(int),
  	typeof(short),
  	typeof(long),
  	typeof(double),
  	typeof(decimal),
  	typeof(float),
  	typeof(bool),
  	typeof(DateTime)
  };

}

Demo

資料庫使用AdventureWorks2022做展示

成果展示

小結

程式碼並沒有寫很漂亮，只是需要產出，有興趣的朋友可以在改寫可以再跟我分享XD，完整程式碼分享放在下方連結中有需要請自取。

使用LINQPad快速產生MSSQL中Table的POCO

如何快速產生對應MSSQL的資料表的Dotnet資料模型

前言

需求

作法

擴充 MyExtensions

Demo

小結

參考連結