public async Task DapperUpdateAsync()
{
LoginUser bu = await DapperDataAsync.GetAsync <LoginUser>(2, null, null);
bu.UserName = "******";
bu.Del = false;
LoginUser newbu = await DapperDataAsync.UpdateAsync <LoginUser>(bu, null, null);
Assert.AreEqual("tester", newbu.UserName);
}
public async Task DapperDel()
{
LoginUser bu = new LoginUser()
{
Id = 6
};
bool b = await DapperDataAsync.DeleteAsync <LoginUser>(bu);
Assert.AreEqual(true, b);
}
public async Task DapperInsert()
{
LoginUser bu = await DapperDataAsync.GetAsync <LoginUser>(2, null, null);
bu.UserCode = "testNo2";
bu.UserName = "******";
int id = await DapperDataAsync.InsertAsync <LoginUser>(bu, null, null);
Assert.AreEqual(6, id);
}
public void test()
{
LoginUser bu = new LoginUser()
{
Id = 6
};
bool b = true;
Task.Run(async() => {
b = await DapperDataAsync.DeleteAsync <LoginUser>(bu);
});
}
/// <summary>
/// 分页查询(为什么不用out，请参考：http://www.cnblogs.com/dunitian/p/5556909.html)
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="p">动态参数</param>
/// <param name="sqlTotal">total语句</param>
/// <param name="p2">Total动态参数</param>
/// <returns></returns>
public async Task <PageData <T> > PageLoadAsync <T>(string sql, object p = null, string sqlTotal = "", object p2 = null)
{
var pageData = new PageData <T>()
{
rows = await DapperDataAsync.QueryAsync <T>(sql.ToString(), p)
};
if (!sqlTotal.IsNullOrWhiteSpace())
{
pageData.total = await DapperDataAsync.ExecuteScalarAsync <int>(sqlTotal, p2);
}
return(pageData);
}
/// <summary>
/// 获取Model-Key为long类型
/// </summary>
/// <param name="id"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public async Task <T> GetAsync(long id, IDbTransaction transaction = null, int?commandTimeout = null)
{
return(await DapperDataAsync.GetAsync <T>(id, transaction, commandTimeout));
}
/// <summary>
/// 增删改系
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public async Task <int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
return(await DapperDataAsync.ExecuteAsync(sql, param, transaction, commandTimeout, commandType));
}
/// <summary>
/// 动态类型查询 | 多映射动态查询
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <param name="buffered"></param>
/// <returns></returns>
public async Task <IEnumerable <dynamic> > QueryAsyncDynamic(string sql, object param = null, IDbTransaction transaction = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
return(await DapperDataAsync.QueryAsync(sql, param, transaction, commandTimeout, commandType));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="p">动态参数</param>
/// <param name="sqlTotal">total语句</param>
/// <param name="p2">Total动态参数</param>
/// <returns></returns>
public async Task <PageOutput> GetPageAsync(string sql, object p = null, string sqlTotal = "", object p2 = null)
{
return(await DapperDataAsync.GetPageAsync <T>(sql, p, sqlTotal, p2));
}
/// <summary>
/// 更新一个Model
/// </summary>
/// <param name="connection"></param>
/// <param name="entityToUpdate"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public async Task <T> UpdateAsync(T model, IDbTransaction transaction = null, int?commandTimeout = null)
{
return(await DapperDataAsync.UpdateAsync <T>(model, transaction, commandTimeout));
}
/// <summary>
/// 获取Model集合（没有Where条件）
/// </summary>
/// <returns></returns>
public async Task <IEnumerable <T> > GetAllAsync()
{
return(await DapperDataAsync.GetAllAsync <T>());
}
/// <summary>
/// 分页查询(为什么不用out，请参考：http://www.cnblogs.com/dunitian/p/5556909.html)
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="p">动态参数</param>
/// <param name="sqlTotal">total语句</param>
/// <param name="p2">Total动态参数</param>
/// <returns></returns>
public async Task <string> PageLoadAsync(string sql, object p = null, string sqlTotal = "", object p2 = null)
{
return(await DapperDataAsync.PageLoadAsync <T>(sql, p, sqlTotal, p2));
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="model">查询模型</param>
/// <returns></returns>
public async Task <PageData <NoteInfo> > PageLoadAsync(QueryModel model)
{
StringBuilder sqlWhere = new StringBuilder();
var p = DapperDataAsync.GetDynamicParameters();
var p2 = DapperDataAsync.GetDynamicParameters();
StringBuilder sqlStr = new StringBuilder("select NId,NTitle,NAuthor,NHitCount,NPush,NDataStatus,NCreateTime,NUpdateTime from NoteInfo");
StringBuilder sqlCount = new StringBuilder("select count(1) from NoteInfo");
#region 条件系列
//文章状态
if (model.DataStatus == StatusEnum.Init)
{
model.DataStatus = StatusEnum.Normal;
}
p.Add("NDataStatus", (int)model.DataStatus);
sqlWhere.Append(" where NDataStatus=@NDataStatus");
//文章标题
if (!model.Title.IsNullOrWhiteSpace())
{
p.Add("NTitle", string.Format("%{0}%", model.Title));
sqlWhere.Append(" and NTitle like @NTitle");
}
//创建时间
if (DateTime.Compare(model.StartTime, DateTime.MinValue) > 0)
{
p.Add("StartTime", model.StartTime);
sqlWhere.Append(string.Format(" and NCreateTime>=@StartTime", model.StartTime));
}
//更新时间
if (DateTime.Compare(model.EndTime, DateTime.MinValue) > 0 && DateTime.Compare(model.EndTime, DateTime.Now) <= 0)
{
p.Add("EndTime", model.EndTime);
sqlWhere.Append(string.Format(" and NCreateTime<=@EndTime", model.EndTime));
}
#endregion
sqlStr.Append(sqlWhere.ToString());
sqlCount.Append(sqlWhere.ToString());
p2.AddDynamicParams(p);
#region 分页系列
if (model.Offset == 0 && model.PageSize == 0)//不分页
{
return(await PageLoadAsync <NoteInfo>(sqlStr.ToString(), p, sqlCount.ToString(), p2));
}
if (model.Offset < 0)
{
model.Offset = 0;
}
if (model.PageSize < 1)
{
model.PageSize = 10;
}
model.PageIndex = model.Offset / model.PageSize + 1;
//Order by
if (model.OrderStr.IsNullOrWhiteSpace())
{
p.Add("OrderStr", "NCreateTime");
}
else
{
p.Add("OrderStr", model.OrderStr);
}
p.Add("PageIndex", model.PageIndex);
p.Add("PageSize", model.PageSize);
sqlStr.Insert(0, "select * from(select row_number() over(order by @OrderStr) Id,* from (");
sqlStr.Append(") TempA) as TempInfo where Id<= @PageIndex * @PageSize and Id>(@PageIndex-1)*@PageSize");
return(await PageLoadAsync <NoteInfo>(sqlStr.ToString(), p, sqlCount.ToString(), p2));
#endregion
}
public async Task DapperGetAsync()
{
LoginUser bu = await DapperDataAsync.GetAsync <LoginUser>(1, null, null);
Assert.AreEqual("sadmin", bu.UserName);
}
