// 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; }