Dapper系列
Dapper是一個輕量級的ORM的工具，早期都是在N年前都是搭配SQL搭配DataSet、
DataTable方式來讀取資料，並自己搭配SqlHelper方式來做資料存取處理，因為自己在撰寫
API的時候，大部分都是用Dapper的方式來進行專案開發，已經用很久了，隨手整理一下
，這框架透過SQL語法撈出資料去對應相關的Class或是自己定義好的Entitie，如果想體驗
強型別，又不想接觸Entity Framework ORM相關的框架，又想使用LINQ方式，這個框架
是可以考慮使用、原因：輕巧、快速、方便。
來大概講一下常用的幾個方法。
- Execute
- Query
- QueryFirst
- QueryFirstOrDefault
- QuerySingle
- QuerySingleOrDefault
- QueryMultiple
- Insert/Update/Delete
- Transaction
安裝所需套件：
加入相關引用
using Dapper;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;先宣告好範例類別，準備MS-SQL連線字串，純展示範例Demo
class Category
{
public int Id { get; set; }
public string CategoryName { get; set; }
}
class Untity
{
public static string SqlConnection = "Server=localhost;Database=BlogapiDB;Integrated Security = True;";
}範例一：透過Query方式、Bind到自己的Class裡面
static void Query()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
List<Category> categorys = cn.Query<Category>("SELECT * FROM Category").ToList();
Console.WriteLine($"筆數{categorys.Count()}");
}
}補充：WHERE in方式
static void InQuery()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
List<Category> categorys = cn.Query<Category>("SELECT * FROM Category WHERE Id IN @ids",new { ids=new[] { 1,2} }).ToList();
Console.WriteLine($"筆數{categorys.Count()}");
}
}補充：LIKE
static void LikeQuery()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
List<Category> categorys = cn.Query<Category>("SELECT * FROM Category WHERE CategoryName LIKE @CategoryName", new { CategoryName = "%test%" }).ToList();
Console.WriteLine($"筆數{categorys.Count()}");
}
}
範例二：透過QueryFirst方法，回傳該資料的第一筆、沒有資料會拋出異常錯誤
static void QueryFirst()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
//
cn.Open();
// Category category = cn.QueryFirst<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id= 1});
//動態參數用法
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QueryFirst<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}範例三：過QueryFirstOrDefault方法，回傳該資料的第一筆、沒有資料會回傳NULL
static void QueryFirstOrDefault()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
//
//Category category = cn.QueryFirstOrDefault<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id = 1 });
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QueryFirstOrDefault<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}範例四：QuerySingle
static void QuerySingle()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
//
cn.Open();
// Category category = cn.QuerySingle<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id= 1});
//動態參數用法
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QuerySingle<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}範例五：QuerySingleOrDefault
static void QuerySingleOrDefault()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
//
//Category category = cn.QuerySingleOrDefault<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id = 1 });
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QuerySingleOrDefault<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}範例六：QueryMultiple
static void QueryMultiple()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
//
cn.Open();
string strSql = "SELECT * FROM Author WHERE Id = @Id;SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
using (var multi = cn.QueryMultiple(strSql, dynamicParams))
{
var categorys = multi.Read<Category>().ToList();
var authors = multi.Read<Author>().ToList();
Console.WriteLine(categorys.Count());
Console.WriteLine(authors.Count());
}
}
}
接者輪到常用的INSERT UDPATE DELETE常用用法
static void Insert()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "INSERT INTO Author (Name) VALUES (@Name) ";
Parameters.Add("Name","test");
//寫法二
//string strSql = string.Concat(
// "..." + Environment.NewLine +
// "..." + Environment.NewLine
// );
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
Console.WriteLine($"INSERT Count={ cn.Execute(strSql, Parameters)}");
}
}
static void Update()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "UPDATE Author SET Name = @Name WHERE Id = @Id ";
Parameters.Add("Name", "test");
Parameters.Add("Id", "1");
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
Console.WriteLine($"UPDATE Count={ cn.Execute(strSql, Parameters)}");
}
}
static void Delete()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "DELETE FROM Author WHERE Id = @Id ";
Parameters.Add("Id", "5");
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
Console.WriteLine($"DELETE Count={ cn.Execute(strSql, Parameters)}");
}
}
Transaction也當然不會缺席嚕
static void TransactionInsert()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "INSERT INTO Author (Name) VALUES (@Name) ";
Parameters.Add("Name", "testTransaction");
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
IDbTransaction transaction = cn.BeginTransaction();
try
{
transaction.Connection.Execute(strSql, Parameters, transaction);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
}
}
}
整個專案Demo
using System;
using Dapper;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace DapperDemo
{
class Untity
{
public static string SqlConnection = "Server=localhost;Database=BlogapiDB;Integrated Security = True;";
}
class Program
{
static void Main(string[] args)
{
Query();
InQuery();
LikeQuery();
QueryFirst();
QueryFirstOrDefault();
QuerySingle();
QuerySingleOrDefault();
QueryMultiple();
Insert();
Update();
Delete();
TransactionInsert();
}
static void Query()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
List<Category> categorys = cn.Query<Category>("SELECT * FROM Category").ToList();
Console.WriteLine($"筆數{categorys.Count()}");
}
}
static void InQuery()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
List<Category> categorys = cn.Query<Category>("SELECT * FROM Category WHERE Id IN @ids",new { ids=new[] { 1,2} }).ToList();
Console.WriteLine($"筆數{categorys.Count()}");
}
}
static void QueryFirst()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
//
cn.Open();
// Category category = cn.QueryFirst<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id= 1});
//動態參數用法
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QueryFirst<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}
static void QueryFirstOrDefault()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
//
//Category category = cn.QueryFirstOrDefault<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id = 1 });
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QueryFirstOrDefault<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}
static void QuerySingle()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
//
cn.Open();
// Category category = cn.QuerySingle<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id= 1});
//動態參數用法
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QuerySingle<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}
static void QuerySingleOrDefault()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
//
//Category category = cn.QuerySingleOrDefault<Category>("SELECT * FROM Category WHERE Id = @Id ",new { Id = 1 });
string strSql = "SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
Category category = cn.QuerySingleOrDefault<Category>(strSql, dynamicParams);
Console.WriteLine(category.CategoryName);
}
}
static void QueryMultiple()
{
using (var cn = new SqlConnection(Untity.SqlConnection))
{
//
cn.Open();
string strSql = "SELECT * FROM Author WHERE Id = @Id;SELECT * FROM Category WHERE Id = @Id ";
var dynamicParams = new DynamicParameters();//←動態參數
dynamicParams.Add("Id", 1);
using (var multi = cn.QueryMultiple(strSql, dynamicParams))
{
var categorys = multi.Read<Category>().ToList();
var authors = multi.Read<Author>().ToList();
Console.WriteLine(categorys.Count());
Console.WriteLine(authors.Count());
}
}
}
static void Insert()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "INSERT INTO Author (Name) VALUES (@Name) ";
Parameters.Add("Name","test");
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
Console.WriteLine($"INSERT Count={ cn.Execute(strSql, Parameters)}");
}
}
static void Update()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "UPDATE Author SET Name = @Name WHERE Id = @Id ";
Parameters.Add("Name", "test");
Parameters.Add("Id", "1");
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
Console.WriteLine($"UPDATE Count={ cn.Execute(strSql, Parameters)}");
}
}
static void Delete()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "DELETE FROM Author WHERE Id = @Id ";
Parameters.Add("Id", "5");
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
Console.WriteLine($"DELETE Count={ cn.Execute(strSql, Parameters)}");
}
}
static void TransactionInsert()
{
DynamicParameters Parameters = new DynamicParameters();
string strSql = "INSERT INTO Author (Name) VALUES (@Name) ";
Parameters.Add("Name", "testTransaction");
using (var cn = new SqlConnection(Untity.SqlConnection))
{
cn.Open();
IDbTransaction transaction = cn.BeginTransaction();
try
{
transaction.Connection.Execute(strSql, Parameters, transaction);
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
}
}
}
//整合測試用
//static void TestCreateCodeValueTable()
//{
// using (var cn = new SqlConnection(Untity.SqlConnection))
// {
// cn.Open();
// var sql = string.Concat(new object[] {
// "CREATE TABLE [dbo].[CodeValue]( " +
// " [Type] [varchar](20) NOT NULL," +
// " [Code] [varchar](50) NOT NULL," +
// " [Value] [nvarchar](max) NOT NULL," +
// " [Modifier] [nvarchar](15) NOT NULL," +
// " [ModifedDate] [datetime2](7) NOT NULL, " +
// " CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED " +
// "( " +
// " [Type] ASC, " +
// " [Code] ASC " +
// ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY " +
// "= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] " +
// ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] " +
// "ALTER TABLE [dbo].[CodeValue] ADD CONSTRAINT [DF__CodeValue__Value__292D09F3] " +
// "DEFAULT ('') FOR [Value] " +
// "ALTER TABLE [dbo].[CodeValue] ADD CONSTRAINT [DF__CodeValue__Updat__2A212E2C] " +
// "DEFAULT (sysdatetime()) FOR [ModifedDate] "
// });
// cn.Execute(sql);
// }
//}
//static void TestDroupCodeValueTable()
//{
// using (var cn = new SqlConnection(Untity.SqlConnection))
// {
// cn.Open();
// var sql = string.Concat(new object[] {
// "DROP TABLE [dbo].[CodeValue] "
// });
// cn.Execute(sql);
// }
//}
//static int TestInsert(List<CodeValue> codeValues)
//{
// using (var cn = new SqlConnection(Untity.SqlConnection))
// {
// cn.Open();
// var sql = string.Empty;
// var paras = new DynamicParameters();
// int i = 1;
// foreach (var codeValue in codeValues)
// {
// sql += string.Concat(new object[] {
// "INSERT INTO [dbo].[CodeValue] " + Environment.NewLine +
// " ([Type] " + Environment.NewLine +
// " ,[Code] " + Environment.NewLine +
// " ,[Value] " + Environment.NewLine +
// " ,[Modifier] " + Environment.NewLine +
// " ,[ModifedDate]) " + Environment.NewLine +
// " VALUES " + Environment.NewLine +
// " (@Type" + i + Environment.NewLine +
// " ,@Code" + i + "" + Environment.NewLine +
// " ,@Value" +i +" " + Environment.NewLine +
// " ,'EDDIE' " + Environment.NewLine +
// " ,GETDATE()) " + Environment.NewLine
// });
// paras.Add("Type" + i, codeValue.Type);
// paras.Add("Code" + i, codeValue.Code);
// paras.Add("Value" + i, codeValue.Value);
// i++;
// }
// return cn.Execute(sql, paras);
// }
//}
}
class Category
{
public int Id { get; set; }
public string CategoryName { get; set; }
}
class Author
{
public int Id { get; set; }
public string Name { get; set; }
}
}
Q&A：如果遇到類別上百個屬性、可用LINQPAD方式去產生Class吧。
Code
void Main()
{
// 資料表名稱
var nameOfTableAndClass = "Category";
// 這邊修改為您要執行的 SQL Command
var sqlCommand = "SELECT * FROM " + nameOfTableAndClass;
// 在 DumpClass 方法裡放 SQL Command 和 Class 名稱
this.Connection.DumpClass(sqlCommand.ToString(), nameOfTableAndClass).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, string className = "Info")
{
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("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"];
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()
{
// 這邊修改為你要執行的 SQL Command
var sqlCommand = @"SELECT top 1 * FROM dbo.Customers WITH (NOLOCK);";
this.Connection.GenerateInsertCommand(sqlCommand.ToString(), "Customer").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<string>();
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();
}
}後記：
通常不管到哪裡都會遭遇到幾十年老系統或是舊系統狀況，也會看到一些DataSet、DataTable狀
況、或是用ADO.net Reader這樣操作，過程都會有舊系統轉新系統，或是老系統與新系統並存狀況，適
當的改善系統和程式，逐步汰換升級，會減輕自身負擔，雖然"穩定"，但是只要碰到新需求且跟舊系統有
夾雜避免不了
參考來源：
官方文檔：https://dapper-tutorial.net/zh-TW/tutorial/1000167/dapper-plus
Mrkt linqPad文章：https://kevintsengtw.blogspot.com/2015/10/dapper-linqpad-sql-command.html
Mrkt linqPad文章：https://kevintsengtw.blogspot.com/2016/08/linqpad-table-insert-script.html
元哥的筆記