[C#]使用Dapper工具測試執行帶有TVP型態參數的預存程序的資料寫入

  • 1701
  • 0
  • C#
  • 2019-11-11

本文延伸 [SQL]於預存程序中使用「使用者定義資料表類型」進行多筆資料新增,利用ORM套件Dapper來實作TVP(Table Value Parameters)參數的傳值並執行SQL命令。

範例說明:本範例的專案為主控台應用程式(Console Application),用來實作車輛資訊刪除、新增,並顯示查詢結果。

程式說明:

Program類別-程式執行的起點

CarBLL類別-業務邏輯層

CarDAL類別-資料存取層

需用Nuget安裝Dapper套件,如下圖:

CarDAL類別程式碼:(BaseDAL類別內為提供SqlConnection物件的初始化,自行撰寫)

using Dapper;
using DapperParameters;
    public class CarDAL : BaseDAL
    {
        private string sqlstring;

        /// <summary>
        /// 取得所有車輛資料
        /// </summary>
        /// <returns></returns>
        public List<CarType> GetCarDatas()
        {
            sqlstring = "select * from CarType";
            List<CarType> list = Conn.Query<CarType>(sqlstring, commandType: CommandType.Text ).ToList();
            return list;
        }

        /// <summary>
        /// 新增資料車輛資料
        /// </summary>
        /// <param name="list">資料</param>
        /// <returns></returns>
        public bool InsertCarDatas(List<CarType> list)
        {
            try
            {
                var parameters = new DynamicParameters();
                parameters.AddTable("@pi_info", "DataInfo", list);
                parameters.Add("@po_ret_code", dbType: DbType.String);
                parameters.Add("@po_ret_msg",  dbType: DbType.String);
                int result = Conn.Execute("SP_CAR_TYPE_INFO", 
                                parameters, 
                                commandType: CommandType.StoredProcedure
                             );

                return result > 0;
            }
            catch(Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 刪除所有車輛資料
        /// </summary>
        /// <returns></returns>
        public bool DeleteCarDatas()
        {
            sqlstring = "Delete CarType";
            int result = Conn.Execute(sqlstring, commandType:CommandType.Text);
            return result > 0;
        }
    }

補充:

DynamicParameters類別下的AddTable方法:

參數1.input變數名稱

參數2.「使用者定義資料表類型」名稱

參數3.輸入的資料集

CarBLL類別程式碼:

    public class CarBLL
    {
        private CarDAL dal;

        public CarBLL()
        {
            dal = new CarDAL();
        }

        public bool InsertCarDatas()
        {
            List<CarType> list = new List<CarType>();
            list.Add(new CarType() { CarID = "TTT-961", CarName = "CAR1" });
            list.Add(new CarType() { CarID = "UJN-513", CarName = "CAR2" });
            list.Add(new CarType() { CarID = "QRF-478", CarName = "CAR3" });
            bool isSuccess = dal.InsertCarDatas(list);
            return isSuccess;
        }

        public bool DeleteCarDatas()
        {
            return dal.DeleteCarDatas();
        }

        public List<CarType> GetCarDatas()
        {
            return dal.GetCarDatas();
        }
    }

Program類別程式碼:

    class Program
    {
        static void Main(string[] args)
        {
            CarBLL bll = new CarBLL();
            bool isSuccess;

            isSuccess = bll.DeleteCarDatas();
            if(isSuccess)
                Console.WriteLine("資料刪除成功!");
            else
                Console.WriteLine("資料刪除失敗!");

            isSuccess = bll.InsertCarDatas();
            if (isSuccess)
                Console.WriteLine("資料新增成功!");
            else
                Console.WriteLine("資料新增失敗!");

            List<CarType> list = bll.GetCarDatas();
            Console.WriteLine("-----------查詢結果-----------");
            foreach (var item in list)
            {
                Console.WriteLine(string.Format("車牌號碼:{0} 汽車名稱:{1}", item.CarID , item.CarName));
            }
            Console.ReadLine();
        }
    }

執行結果:

參考來源:https://stackoverflow.com/questions/18269886/call-stored-procedure-from-dapper-which-accept-list-of-user-defined-table-type