//
public int Count(User whereModel, string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from [User] ");
strSql.AppendFormat("where 1=1 and {0} ", where);
var result = _dbHelper.Count(strSql.ToString(), whereModel);
return result;
}
//
public int Add(User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into [User](");
strSql.Append("Username,PasswordHash,Email,PhoneNumber,IsFirstTimeLogin,AccessFailedCount,CreationDate,IsActive)");
strSql.Append(" values (");
strSql.Append("@Username,@PasswordHash,@Email,@PhoneNumber,@IsFirstTimeLogin,@AccessFailedCount,@CreationDate,@IsActive)");
//
var id= _dbHelper.InsertReturnId(strSql.ToString(), model);
return id;
}
//
public IList<User>FindList(User whereModel, string where, int top)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("select top {0} ",top);
strSql.Append("userId,username,passwordHash,email,phoneNumber,isFirstTimeLogin,accessFailedCount,creationDate,isActive,lastTimestamp ");
strSql.Append("from [User] ");
strSql.AppendFormat("where 1=1 and {0} ", where);
var result = _dbHelper.FindList<User>
(strSql.ToString(), whereModel);
return result;
}
private static void Test_User()
{
var model = new User
{
Username = "******",
IsFirstTimeLogin = true,
AccessFailedCount = 1,
CreationDate = DateTime.Now,
IsActive = true
};
var userDao = new UserDao();
var isOkDelete = userDao.DeleteById(2);
var userId = userDao.Add(model);
var isOkUpdate = userDao.UpdateById(new User() {Username = "******"}, userId);
var model01 = userDao.FindById(userId);
var model02 = userDao.FindList(new User() {UserId = userId}, " userId=@userid", 1);
var model03 = userDao.FindListByPage(new User() {UserId = userId}, " userId=@userid", " userId", 0, 10);
var count = userDao.Count(new User() {Username = "******"}, " Username=@Username");
}
//
public IList<User> FindListByPage(User whereModel, string where, string orderBy, int pageIndex, int pageSize)
{
var startIndex = pageIndex * pageSize;
var size = pageSize;
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append("userId,username,passwordHash,email,phoneNumber,isFirstTimeLogin,accessFailedCount,creationDate,isActive,lastTimestamp ");
strSql.Append("from [User] ");
strSql.AppendFormat("where 1=1 and {0} ", where);
strSql.AppendFormat("ORDER BY {0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", orderBy, startIndex, size);
var result = _dbHelper.FindList<User>(strSql.ToString(), whereModel);
return result;
}
//
public bool UpdateById(User model, Int32 userId )
{
model.UserId = userId;
StringBuilder strSql = new StringBuilder();
strSql.Append("update [User] set ");
if(model.@Username!=null)
strSql.Append("Username=@Username,");
if(model.@PasswordHash!=null)
strSql.Append("PasswordHash=@PasswordHash,");
if(model.@Email!=null)
strSql.Append("Email=@Email,");
if(model.@PhoneNumber!=null)
strSql.Append("PhoneNumber=@PhoneNumber,");
if(model.@IsFirstTimeLogin!=null)
strSql.Append("IsFirstTimeLogin=@IsFirstTimeLogin,");
if(model.@AccessFailedCount!=null)
strSql.Append("AccessFailedCount=@AccessFailedCount,");
if(model.@CreationDate!=null)
strSql.Append("CreationDate=@CreationDate,");
if(model.@IsActive!=null)
strSql.Append("IsActive=@IsActive,");
int n = strSql.ToString().LastIndexOf(",", StringComparison.Ordinal);
if(n>0)strSql.Remove(n, 1);
strSql.Append(" where UserId=@userId ");
var flag = _dbHelper.Update(strSql.ToString(), model);
return flag;
}
