使用小工具 LINQPad 快速產生相對映 SQL Command 查詢結果的類別

資料來源：http://kevintsengtw.blogspot.tw/2015/10/dapper-linqpad-sql-command.html


以下以 Northwind 為例

(1) LinqPad 執行環境上方選項

Language：C# Program
Connection：資料庫連線 (例) 127.0.0.1.sa.Northwind


(2) LinqPad 產生資料表欄位對應的 class

執行以下程式碼，得到資料表欄位對應類別的程式碼

void Main() { this.Connection.DumpClass("select top 1 * from Categories").Dump(); } 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) { 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; builder.AppendLine("public class Info"); 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"]; builder.Append(string.Format("\tpublic {0}{1} {2}", name, isNullable ? "?" : string.Empty, collumnName)).AppendLine(" { get; set; } "); } builder.AppendLine("}"); builder.AppendLine(); } while (reader.NextResult()); return builder.ToString(); } }

(3) LinqPad 使用Dapper查詢

驗證(2)產生的類別程式碼，將查詢結果放入 類別 可正常顯示

void Main() { using (var connection = this.Connection) { var sqlCommand = "select * from Categories"; var Result = connection.Query<Info>(sqlCommand); Result.Dump(); } } //資料表欄位對應的類別 public class Info { public int CategoryID { get; set; } public string CategoryName { get; set; } public string Description { get; set; } public byte[] Picture { get; set; } }

{End}