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();
}
資料參考來源