public int GetTotalRecords(SqlConnection connection, UserCriteria criteria)
{
if (criteria != null)
{
var result = new List <UserInfo>();
using (var command = new SqlCommand("Select count(*) as TotalRecords " +
" from tbl_User U " +
" where 1=1 and U.UserID <> 'admin' ", connection))
{
if (criteria.UserName != "" && criteria.UserName != null)
{
command.CommandText += " and U.UserName like '%" + criteria.UserName + "%' ";
}
if (criteria.UserID != "" && criteria.UserID != null)
{
command.CommandText += " and U.UserID = @UserID ";
AddSqlParameter(command, "@UserID", criteria.UserID, System.Data.SqlDbType.VarChar);
}
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
}
else
{
using (var command = new SqlCommand("Select count(*) as TotalRecords from tbl_User where 1 = 1 ", connection))
{
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
return(GetDbReaderValue <int>(reader["TotalRecords"]));
}
}
}
}
return(0);
}
/// <summary>
/// Hàm lấy tất cả user trừ user admin, mdx, config
/// </summary>
/// <returns>Return DataTable</returns>
///
public List <UserInfo> Getlist(SqlConnection connection, UserCriteria criteria)
{
var result = new List <UserInfo>();
using (var command = new SqlCommand("Select U.* " +
" from tbl_User U " +
" where 1=1 and U.UserID <> 'admin' ", connection))
{
if (criteria.UserName != "" && criteria.UserName != null)
{
command.CommandText += " and U.UserName like '%" + criteria.UserName + "%' ";
}
if (criteria.UserID != "" && criteria.UserID != null)
{
command.CommandText += " and U.UserID like '%" + criteria.UserID + "%' ";
}
if (criteria.pageSize == 0)
{
criteria.pageSize = 10;
}
var offSet = criteria.pageIndex * criteria.pageSize;
command.CommandText += " order by U.UserName ";
command.CommandText += " OFFSET @OFFSET ROWS FETCH NEXT @PAGESIZE ROWS ONLY ";
AddSqlParameter(command, "@OFFSET", offSet, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@PAGESIZE", criteria.pageSize, System.Data.SqlDbType.Int);
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var info = new UserInfo();
info.UserName = GetDbReaderValue <string>(reader["UserName"]);
info.UserID = GetDbReaderValue <string>(reader["UserID"]);
// info.Disable = GetDbReaderValue<byte>(reader["GroupID"]);
info.Email = GetDbReaderValue <string>(reader["Email"]);
info.Disable = GetDbReaderValue <Boolean>(reader["Disable"]);
result.Add(info);
}
}
return(result);
}
}