public async Task <IEnumerable <UserLocal> > UserList(ArguUserList arguUserList)
{
int limitBefore = (arguUserList.Page - 1) * arguUserList.Limit;
string wheresql = $" where 1 = 1";
if (!string.IsNullOrWhiteSpace(arguUserList.UserName))
{
wheresql += $" and username like '%{arguUserList.UserName}%' ";
}
if (!string.IsNullOrWhiteSpace(arguUserList.Mobilephone))
{
wheresql += $" and mobnilephone = '{arguUserList.Mobilephone}' ";
}
if (!string.IsNullOrWhiteSpace(arguUserList.PickName))
{
wheresql += $" and pickname like '{arguUserList.PickName}'";
}
if (arguUserList.Status != null)
{
wheresql += $" and status = {arguUserList.Status}";
}
var commandText = $"select * from {EntityHelper.CallName<UserLocal>()} {wheresql} limit {limitBefore},{arguUserList.Limit}";
var userList = await QueryAsync <UserLocal>(commandText, null);
return(userList);
}
public async Task <ResultUserInfo> GetUserInfo(string userName)
{
var commandText = $"select * from {EntityHelper.CallName<UserLocal>()} A left join {EntityHelper.CallName<UserToken>()} B ON A.id = B.user_id where A.username = @username";
var resultUserInfo = await QueryOneAsync <ResultUserInfo>(commandText, new { userName });
return(resultUserInfo);
}
public async Task <IEnumerable <UserLocal> > GatchUserListByID(int id)
{
var commandText = $"select * from {EntityHelper.CallName<UserLocal>()} where id = @id";
var resultUserLocal = await QueryAsync <UserLocal>(commandText, new { id });
return(resultUserLocal);
}
public void MysqlExecute()
{
MyDapperPower.DbConnection("server=localhost;port=3306;user id=root;password=Aa82078542;database=testmysql;SslMode=none", "mysql", "mysql");
UserLocalDAL userLocalDAL = new UserLocalDAL();
var commandText = $"update {EntityHelper.CallName<UserLocal>()} set status = 7 where id > 30";
var result = userLocalDAL.Execute(commandText, null);
}
public async Task <ResultUserLocal> CheckUserLogin(string userName)
{
var commandText = $"select * from {EntityHelper.CallName<UserLocal>()} where username = @username";
var resultUserLocal = await QueryOneAsync <ResultUserLocal>(commandText, new { userName });
return(resultUserLocal);
}
public void MysqlExecuteScalar()
{
MyDapperPower.DbConnection("server=localhost;port=3306;user id=root;password=Aa82078542;database=testmysql;SslMode=none", "mysql", "mysql");
UserLocalDAL userLocalDAL = new UserLocalDAL();
var commandText = $"select * from {EntityHelper.CallName<UserLocal>()} where id = @id";
int id = 21;
var result = userLocalDAL.ExecuteScalar <int>(commandText, new { id });
Assert.Equal(id, result);
}
public async Task UpdateTenPayResult(string ordercode, int id)
{
string commandText = $"Update {EntityHelper.CallName<TenpayResultLog>()} set ordercode = @ordercode where id = @id";
var parameters = new DynamicParameters();
parameters.Add("@id", id);
parameters.Add("@ordercode", ordercode);
await ExecuteAsync(commandText, parameters);
//await ExecuteAsync(commandText, new { ordercode, id});
}
public async Task <bool> CheckMobilephoneExist(string mobilePhone)
{
var commandText = $"select count(1) from {EntityHelper.CallName<UserLocal>()} where mobilephone = @mobilephone";
var count = await ExecuteScalarAsync <int>(commandText, new { mobilePhone = mobilePhone });
if (count > 0)
{
return(false);
}
else
{
return(true);
}
}
public async Task <bool> CheckUsernameExist(string userName)
{
var commandText = $"select count(1) from {EntityHelper.CallName<UserLocal>()} where username = @username";
var count = await ExecuteScalarAsync <int>(commandText, new { username = userName });
if (count > 0)
{
return(false);
}
else
{
return(true);
}
}
public void MysqlExecuteTra()
{
MyDapperPower.DbConnection("server=localhost;port=3306;user id=root;password=Aa82078542;database=testmysql;SslMode=none", "mysql", "mysql");
UserLocalDAL userLocalDAL = new UserLocalDAL();
var commandText = $"update {EntityHelper.CallName<UserLocal>()} set status = 7 where id > 30";
using (var transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var connection = userLocalDAL.Connection)
{
var result = userLocalDAL.Execute(connection, commandText, null);
}
}
}
public void MysqlExecuteScalarTra()
{
MyDapperPower.DbConnection("server=localhost;port=3306;user id=root;password=Aa82078542;database=testmysql;SslMode=none", "mysql", "mysql");
UserLocalDAL userLocalDAL = new UserLocalDAL();
var commandText = $"select status from {EntityHelper.CallName<UserLocal>()} where id = @id";
var commandText2 = $"update {EntityHelper.CallName<UserLocal>()} set status = 5 where id = @id";
using (var transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var connection = userLocalDAL.Connection)
{
int id = 22;
var result1 = userLocalDAL.ExecuteScalar <int>(connection, commandText2, new { id });
var result2 = userLocalDAL.ExecuteScalar <int>(connection, commandText, new { id });
Assert.Equal(5, result2);
}
}
}
public async Task UpdateToken(string token, DateTime expiration_time, int id)
{
var commandText = $"Update {EntityHelper.CallName<UserToken>()} set token = @token, expiration_time = @expiration_time where id = @id";
await ExecuteAsync(commandText, new { token, expiration_time, id });
}
public async Task <UserToken> FindToken(string token)
{
var commandText = $"Select * from {EntityHelper.CallName<UserToken>()} where token = @token";
return(await QueryOneAsync <UserToken>(commandText, new { token }));
}
public ActionResult <string> GetTableName()
{
string TableName = EntityHelper.CallName <CustomerInfo>();
return(TableName);
}
/// <summary>
/// 主键属性对象
/// </summary>
//public PropertyInfo PrimaryKey { get; set; }
public BaseDAL()
{
this.TableName = EntityHelper.CallName <T>();
//this.PrimaryKey = EntityHelper.GetSingleKey<T>();
}