public int GetTotalRecords(SqlConnection connection, UserGroupCriteria criteria)
{
if (criteria != null)
{
var result = new List <UserGroupInfo>();
using (var command = new SqlCommand("Select count(*) as TotalRecords " +
" from tbl_User_Group UG " +
" where 1=1 ", connection))
{
if (criteria.GroupName != "" && criteria.GroupName != null)
{
command.CommandText += " and UG.Name like '%" + criteria.GroupName + "%' ";
}
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_Group 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 <UserGroupInfo> Getlist(SqlConnection connection, UserGroupCriteria criteria)
{
var result = new List <UserGroupInfo>();
using (var command = new SqlCommand("Select UG.* " +
" from tbl_User_Group UG " +
" where 1=1 ", connection))
{
if (criteria.GroupName != "" && criteria.GroupName != null)
{
command.CommandText += " and UG.Name like '%" + criteria.GroupName + "%' ";
}
if (criteria.pageSize == 0)
{
criteria.pageSize = 10;
}
var offSet = criteria.pageIndex * criteria.pageSize;
command.CommandText += " order by UG.Name ";
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 UserGroupInfo();
info.GroupID = GetDbReaderValue <int>(reader["ID"]);
info.GroupName = GetDbReaderValue <string>(reader["Name"]);
result.Add(info);
}
}
return(result);
}
}
