[C#]NuGet - Dapper(Insert, Select, StoredProcedure, Multiple table)

• 概要
• 因許多人有介紹如何安裝與細節，在此就不在多介紹
• 下列的範例 Code 為各項操作，主要 Data Class 如下

public class User
{
    ///
    /// Name
    ///
    public string Name { get; set; }
    ///
    /// Age
    ///
    public int Age { get; set; }
}
C#

• Database 

• 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
• Code - one data

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);
}
C#

• Code - Multiple data

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);
}
C#

• Dapper SELECT
• Code - one data

List< User> lUser = null;
using (SqlConnection con = new SqlConnection(Info.LyndonliuConnectionString))
{
    //1) 將SELECT結果轉成指定的型別(屬性與欄位名稱要一致)
    lUser = con.Query< User>(
        "SELECT * FROM UserInfo",
        commandTimeout: 0).ToList();
}
C#

• Code - Where 

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();
}
C#

• Code - Where In

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();
}
C#

• 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
SQL

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();
}
C#

• 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
SQL

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();
}
C#

• 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
SQL

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();
    }
}
C#

• 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