使用 LinqPad 快速產生 Dapper 所需要的 SQL 語法 - Class、Insert、Update

前言

工作上目前已經很習慣用 Dapper 這種輕型的 ORM 來處理有關DAL層的應用，但相較於 Entity Freamwork，在處理新增/更新時就比較沒那麼方便，主要差異在Entity Freamwork 可以直接用物件操作的方式來做資料庫異動，而Dapper就必須寫SQL Commend，雖然有一些Open Source Extentions 已經封裝這些邏輯，譬如：SimpleCRUD﹉等，但實際使用常常得依規範設定一些屬性，有時發生錯誤時也不是那麼的直覺，故目前我還是會以SQL Commend為主。但產生Commend是一件麻煩事，如果搭配 Linqpad 寫一個Script 可以依需求快速產生所需要的commend，彈性是很大的，此篇文章介紹我在實務上我常用Scripts，來快速產生相關 SQL Commend

如何設定LinqPad

Step 1 : 這邊可以直接瀏覽資料庫的Schema
Step 2 : 這邊使用C# Program 來撰寫我們要產生Script的程式
Step 3 : 選擇資料庫的連線字串

接著以下是我常用的Helper，只要貼到內容裡面即可產生

 

void Main()

{

 this.Connection

 .DumpClass(@"

SELECT * FROM Table

 ","ViewModelName")

 .Dump();

}

// Define other methods and classes here

public static class LINQPadExtensions

{

 private static readonly Dictionary TypeAliases = new Dictionary {

 { 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 NullableTypes = new HashSet {

 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 Name)

 {

 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 " + Name);

 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.AppendLine(string.Format("\tpublic {0}{1} {2} {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName));

 }

 builder.AppendLine("}");

 builder.AppendLine(); 

 } while(reader.NextResult());

 return builder.ToString();

 }

}

產生 Insert 語法

void Main()
{
 // SQL Command
 var sqlCommand = @"SELECT top 1 * FROM dbo.Table WITH (NOLOCK);";
 
 this.Connection.GenerateInsertCommand(sqlCommand.ToString(), "Table").Dump();
 
}
 
public static class LINQPadExtensions
{
 public static string GenerateInsertCommand(this IDbConnection connection, string sql, string tableName = "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;
 }
 
 builder.AppendFormat("INSERT INTO [dbo].[{0}]{1}", tableName, Environment.NewLine);
 builder.AppendLine("(");
 
 var schema = reader.GetSchemaTable();
 var columnNames = new List();
 
 foreach (DataRow row in schema.Rows)
 {
 var columnName = (string)row["ColumnName"];
 columnNames.Add(columnName);
 }
 
 foreach (var columnName in columnNames)
 {
 builder.AppendFormat(" [{0}]{1}{2}",
 columnName,
 columnNames.IndexOf(columnName).Equals(columnNames.Count - 1) ? "" : ",",
 Environment.NewLine);
 }
 
 builder.AppendLine(")");
 builder.AppendLine("VALUES");
 builder.AppendLine("(");
 
 foreach (var columnName in columnNames)
 {
 builder.AppendFormat(" @{0}{1}{2}",
 columnName,
 columnNames.IndexOf(columnName).Equals(columnNames.Count - 1) ? "" : ",",
 Environment.NewLine);
 }
 
 builder.AppendLine(");");
 builder.AppendLine();
 }
 while (reader.NextResult());
 
 return builder.ToString();
 }
}

產生 Update 語法

void Main()
{
 // SQL Command
 var tableName = "TableName";
 var sqlCommand = @"SELECT top 1 * FROM [dbo].["+tableName+"]";
 
 this.Connection.GenerateInsertCommand(sqlCommand.ToString(), tableName).Dump();
 
}
 
public static class LINQPadExtensions
{
 public static string GenerateInsertCommand(this IDbConnection connection, string sql, string tableName = "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;
 }
 
 builder.AppendFormat("UPDATE [dbo].[{0}] SET {1}", tableName, Environment.NewLine);
 // builder.AppendLine("(");
 
 var schema = reader.GetSchemaTable();
 var columnNames = new List();
 
 foreach (DataRow row in schema.Rows)
 {
 var columnName = (string)row["ColumnName"];
 columnNames.Add(columnName);
 }
 
 foreach (var columnName in columnNames)
 {
 builder.AppendFormat(" [{0}] = @{0}{1}{2}",
 columnName,
 columnNames.IndexOf(columnName).Equals(columnNames.Count - 1) ? "" : ",",
 Environment.NewLine);
 }
 
 builder.AppendLine("WHERE ID = @ID");
 }
 while (reader.NextResult());
 
 return builder.ToString();
 }
}