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