- 概要
- 因許多人有介紹如何安裝與細節，在此就不在多介紹
- 下列的範例 Code 為各項操作，主要 Data Class 如下
public class User
{
///
/// Name
///
public string Name { get; set; }
///
/// Age
///
public int Age { get; set; }
}
- CommandType
- CommandType.Text for an SQL text command. (Default.)
- CommandType.StoredProcedure for the name of a stored procedure.
CommandType.TableDirect for the name of a table. (不支援)
- Dapper INSERT
int count = 0;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
User user = new User();
user.Name = "Helen";
user.Age = 25;
//1) 可執行SQL資料更新指令，支援參數
var Param = new DynamicParameters();
Param.Add("@Name", user.Name, dbType: DbType.String);
Param.Add("@Age", user.Age, dbType: DbType.Int32);
count = con.Execute(
"INSERT INTO UserInfo VALUES (@Name, @Age)",
Param,
commandTimeout: 0);
}
int count = 0;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
//2) 以陣列方式提供多組參數，可重複執行同一SQL指令
// 它可以一次傳進多組參數，用不同參數重複執行同一SQL操作，批次作業時格外有用
// 指定的型別(屬性與欄位名稱要一致)
List< User> lUser = new List< User>()
{
new User(){ Name = "Lyndon", Age = 30 },
new User(){ Name = "Daisy", Age = 27 },
};
count = con.Execute(
"INSERT INTO UserInfo VALUES (@Name, @Age)",
lUser,
commandTimeout: 0);
}
Dapper SELECT
List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
//1) 將SELECT結果轉成指定的型別(屬性與欄位名稱要一致)
lUser = con.Query< User>(
"SELECT * FROM UserInfo",
commandTimeout: 0).ToList();
}
List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
//2) 可執行SQL資料更新指令，支援參數
var Param = new DynamicParameters();
Param.Add("@Age", 26, dbType: DbType.Int32);
lUser = con.Query< User>(
"SELECT * FROM UserInfo WHERE Age > @Age",
Param,
commandTimeout: 0).ToList();
}
List< user> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
//3) 直接傳數字陣列作為WHERE IN比對參數
// =>自動轉成WHERE col in (@arg1,@arg2,@arg3)
lUser = con.Query< user>(
"SELECT * FROM UserInfo WHERE Age IN @Age",
new { Age = new int[] { 25, 30 } },
commandTimeout: 0).ToList();
}
Dapper StoredProcedure
- Code - StoredProcedure (one table)
USE [MVCVISTA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lyndonliu
-- =============================================
ALTER PROCEDURE [dbo].[SP_DemoGetUserInfo]
AS
BEGIN
SELECT * FROM UserInfo
END
List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
//1) 將SELECT結果轉成指定的型別(屬性與欄位名稱要一致)
lUser = con.Query< User>(
"SP_DemoGetUserInfo",
commandType: CommandType.StoredProcedure,
commandTimeout: 0).ToList();
}
- Code - StoredProcedure (one table where)
USE [MVCVISTA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lyndonliu
-- =============================================
ALTER PROCEDURE [dbo].[SP_DemoGetUserInfo](
@Age int
)
AS
BEGIN
SELECT * FROM UserInfo WHERE Age > @Age
END
List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
//2) 可執行SQL資料更新指令，支援參數
var Param = new DynamicParameters();
Param.Add("@Age", 27, dbType: DbType.Int32);
lUser = con.Query< User>(
"SP_DemoGetUserInfo",
Param,
commandType: CommandType.StoredProcedure,
commandTimeout: 0).ToList();
}
- Code - StoredProcedure (multiple table)
USE [MVCVISTA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Lyndonliu
-- =============================================
ALTER PROCEDURE [dbo].[SP_DemoGetUserInfo](
@Age int
)
AS
BEGIN
SELECT MAX(Age) AS MAXAge FROM UserInfo
SELECT MIN(Age) AS MINAge FROM UserInfo
SELECT * FROM UserInfo WHERE Age > @Age
END
int MAXAge = 0;
int MINAge = 0;
List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
//2) 可執行SQL資料更新指令，支援參數
var Param = new DynamicParameters();
Param.Add("@Age", 27, dbType: DbType.Int32);
//3) 可回傳多個 table
// 接收順序有先後問題，順序不正確將接收錯誤的資訊
using (var multi = con.QueryMultiple("SP_DemoGetUserInfo",
Param,
commandType: CommandType.StoredProcedure,
commandTimeout: 0))
{
MAXAge = multi.Read< int>().FirstOrDefault();
MINAge = multi.Read< int>().FirstOrDefault();
lUser = multi.Read< User>().ToList();
}
}
- Ref:
- http://blog.darkthread.net/post-2014-05-15-dapper.aspx
- http://stackoverflow.com/questions/16993021/how-to-insert-null-values-to-a-uniqueidentifier-column
- https://stackoverflow.com/questions/14191278/what-is-the-purpose-of-using-commandtype-tabledirect
- http://stackoverflow.com/questions/7836766/calling-a-stored-procedure-with-asp-net
- http://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper
- http://stackoverflow.com/questions/6387904/how-to-insert-an-ienumerablet-collection-with-dapper-dot-net
- http://stackoverflow.com/questions/17150542/how-to-insert-a-c-sharp-list-to-database-using-dapper-net
- https://www.jeremymorgan.com/blog/programming/how-to-dapper-web-api/
- https://stackoverflow.com/questions/6751052/how-to-map-multiple-records-from-a-single-sp-with-dapper-dot-net
