- 概要
- 因許多人有介紹如何安裝與細節，在此就不在多介紹
- 下列的範例 Code 為各項操作，主要 Data Class 如下
public class User
{
public string Name { get; set; }
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;
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))
{
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))
{
lUser = con.Query< User>(
"SELECT * FROM UserInfo",
commandTimeout: 0).ToList();
}
List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
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))
{
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
ALTER PROCEDURE [dbo].[SP_DemoGetUserInfo]
AS
BEGIN
SELECT * FROM UserInfo
END
List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
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
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))
{
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
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))
{
var Param = new DynamicParameters();
Param.Add("@Age", 27, dbType: DbType.Int32);
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
