[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; }
    }

• 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);
                }

• 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);
                }

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

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

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

• 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