iT邦幫忙

1

小優化Dapper POCO類別生成器 (自動以表格名稱當類別名稱)

原版本是作者Necroskillz寫的POCO生成器 Generate C# POCOs from SQL statement in LINQPad | NecroNET.org
接著修改版是kevin前輩做的,可以參考前輩的文章 : mrkt 的程式學習筆記: Dapper - 使用 LINQPad 快速產生相對映 SQL Command 查詢結果的類別


這次主要增加不用手工修改類別名稱,直接拿資料庫表格名稱來用,想指定名稱也保留原版方式
提供給同樣想偷懶的讀者 :)

邏輯:

  • ExecuteReader添加CommandBehavior.KeyInfo參數配合GetSchemaTable方法可以額外取得BaseTableName資料
  • 增加CommandBehavior.SingleRow只需要取得第一行資料就足夠分析產生class的資料

線上測試連結 : POCO Generator | .NET Fiddle

public static class LINQPadExtensions
{
	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)
	};

	public static string DumpClass(this IDbConnection connection, string sql,string className = null)
	{
		if (connection.State != ConnectionState.Open)
			connection.Open();

		var cmd = connection.CreateCommand();
		cmd.CommandText = sql;
		var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SingleRow );

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

			var schema = reader.GetSchemaTable();
			foreach (DataRow row in schema.Rows)
			{
				if (string.IsNullOrWhiteSpace(builder.ToString()))
				{
					var tableName = string.IsNullOrWhiteSpace(className)?row["BaseTableName"] as string:className;
					builder.AppendFormat("public class {0}{1}", tableName, Environment.NewLine);
					builder.AppendLine("{");
				}
				
				
				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"];

				builder.AppendLine(string.Format("\tpublic {0}{1} {2} {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName));
				builder.AppendLine();
			}

			builder.AppendLine("}");
			builder.AppendLine();
		} while (reader.NextResult());

		return builder.ToString();
	}
}

使用方式

void Main()
{
	using (IDbConnection connection = Connection)
	{
		Console.WriteLine(connection.DumpClass("select top 1 * from table"));
        /* Result:
            public class table
            {
              public int ID { get; set; }
              public string Name { get; set; }
            }        
        */
	}
}

尚未有邦友留言

立即登入留言