Dapper 使用資料庫查詢和新增

Dapper 使用資料庫查詢和新增

Dapper 是一個.Net套件,在做資料庫查詢獲取資料時可以把資料轉換為強行別,不同於ADO.NET的弱型別來說更加安全,其撰寫方式與 ADO.NET 寫法很像似。

安裝

請使用Nutget command,並執行下面語法

Install-Package Dapper

新增多筆語法範例

var lists = new List<BasicInformationDTO>
{
	new BasicInformationDTO
	{
		ID = Guid.NewGuid(),
		Name = "Johnes",
		Address = "新北土城",
		Birthday = DateTime.Now,
		Email = "johnes0714@gmail.com",
		Mobile ="0916-116-255"
	},
	new BasicInformationDTO
	{
		ID = Guid.NewGuid(),
		Name = "Johnes",
		Address = "新北新店",
		Birthday = DateTime.Now,
		Email = "johnes.lee@gmail.com",
		Mobile ="0916-116-255"
	},
};


using (var conn = new SqlConnection(connectString))
{
	var sql = "INSERT INTO Basic_Information(ID,Name,Email,Address,Birthday,Mobile) VALUES (@ID,@Name,@Email,@Address,@Birthday,@Mobile);";
	conn.Execute(sql, lists);
}

使用transaction當新增或更新失敗

var connectString = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;

var lists = new List<BasicInformationDTO>
{
    new BasicInformationDTO
    {
        ID = Guid.Parse("0AFE6D9B-3235-492D-B4B7-5F0E982F0DAF"),
        Name = "TripleH",
        Address = "新北土城",
        Birthday = DateTime.Now,
        Email = "johnes0714@gmail.com",
        Mobile ="0916-116-158"
    },
    new BasicInformationDTO
    {
        ID = Guid.Parse("A9ACF40E-18EC-4F97-97FE-F7E7C0092A2D"),
        Name = "StoneCone",
        Address = "新北新店",
        Birthday = DateTime.Now,
        Email = "johnes.lee@gmail.com",
        Mobile ="0916-116-175"
    },
};

using (var connection = new SqlConnection(connectString))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            var sql = "update Basic_Information set Name=@Name,Mobile=Mobile where ID = @ID";
            connection.Execute(sql, lists, transaction);
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}

}

新增查詢語法範例

using (var conn = new SqlConnection(connectString))
{
	var sql = "select * from Basic_Information where ID = @ID";
	//指定型別查詢,效能比較快
	var parameters = new DynamicParameters();
	parameters.Add("@ID", Guid.Parse("0AFE6D9B-3235-492D-B4B7-5F0E982F0DAF"), System.Data.DbType.Guid);
	var results = conn.Query<BasicInformationDTO>(
		sql, parameters
	).ToList();
}
using (var conn = new SqlConnection(connectString))
{
    var sql = "select * from Basic_Information where ID = @ID";
    //匿名化查詢
    var results = conn.Query<BasicInformationDTO>(
        sql, new { ID = Guid.Parse("0AFE6D9B-3235-492D-B4B7-5F0E982F0DAF") }
    ).ToList();
}

資料參考來源

https://github.com/DapperLib/Dapper

https://blog.poychang.net/note-dapper/