前言
工作上目前已經很習慣用 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();
}
}
