[Dapper][01]基本應用
Dapper套件可直接由NuGet取得
安裝後於程式碼中加入 using Dapper; 即可使用。
//SQL指令
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine(" UPDATE VipInfo SET IsStop=0 WHERE VipNo = @VipNo ");
//變數宣告
var dynamicParams = new DynamicParameters();
dynamicParams.Add("VipNo", Source.VipNo);
//執行SQL
var Results = conn.Execute(sbSql.ToString(), dynamicParams);
2.資料查詢 (單一資料表)
//SQL指令
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine(" SELECT VipNo, ShopNo, VipNa FROM VipInfo WHERE VipNo = @VipNo ");
var dynamicParams = new DynamicParameters();
//添加變數
dynamicParams.Add("VipNo", Source.VipNo);
//執行SQL
var Results = conn.Query<dynamic>(sbSql.ToString(), dynamicParams);
var Results = conn.Query<dynamic>(sbSql.ToString(), dynamicParams);
3.資料查詢 (多資料表)
//SQL指令
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine(" SELECT VipNo, ShopNo, VipNa FROM VipInfo WHERE VipNo = @VipNo ");
sbSql.AppendLine(" SELECT VipNo, VpTyNo FROM VipInfoType WHERE VipNo = @VipNo ");
var dynamicParams = new DynamicParameters();
//添加變數
dynamicParams.Add("VipNo", Source.VipNo);
//執行SQL
var multi = conn.QueryMultiple(sbSql.ToString(), dynamicParams, trans);
//IsConsumed當你讀完所有可用的結果集時，它會變為True
if (!multi.IsConsumed)
var mt1 = multi.Read<VipInfo>();
if (!multi.IsConsumed)
var mt2 = multi.Read<VipInfoType>();
4.資料新增
public class TestDapperSource
{
public string VipNo { get; set; }
public string VipNa { get; set; }
}
public int TestDapper(IEnumerable<TestDapperSource> Source)
{
int Result = 0;
using (var conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("INSERT INTO VipInfo (VipNo,VipNa) VALUES(@VipNo , @VipNa) ");
Result = conn.Execute(sbSql.ToString(), Source);
}
return Result;
}
5.資料更新
public class TestDapperSource
{
public string VipNo { get; set; }
public string VipNa { get; set; }
}
public int TestDapper(IEnumerable<TestDapperSource> Source)
{
int Result = 0;
using (var conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("UPDATE VipInfo SET VipNa=@VipNa WHERE VipNo=@VipNo ");
Result = conn.Execute(sbSql.ToString(), Source);
}
return Result;
}
6.資料刪除
public class TestDapperSource
{
public string VipNo { get; set; }
public string VipNa { get; set; }
}
public int TestDapper(IEnumerable<TestDapperSource> Source)
{
int Result = 0;
using (var conn = new SqlConnection(this.ConnectionString))
{
conn.Open();
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("DELETE VipInfo WHERE VipNo=@VipNo ");
Result = conn.Execute(sbSql.ToString(), Source);
}
return Result;
}
留言