public List <UserGroupInfo> GetListGroupOfUser(SqlConnection connection, String userId)
{
var result = new List <UserGroupInfo>();
using (var command = new SqlCommand("Select UG.ID , UG.Name " +
" from tbl_User_Group_User UGU " +
" inner join tbl_User_Group UG " +
" on UGU.GroupID = UG.ID" +
" where UGU.UserID=@UserID ", connection))
{
AddSqlParameter(command, "@UserID", userId, System.Data.SqlDbType.VarChar);
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);
}
}
public void UpdateUserGroup(SqlConnection connection, string id, UserGroupInfo userGroup)
{
using (var command = new SqlCommand("update tbl_User_Group " +
" SET Name=@GroupName, " +
"where ID=@GroupID "
, connection))
{
AddSqlParameter(command, "@GroupID", userGroup.GroupID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@GroupName", userGroup.GroupName, System.Data.SqlDbType.NVarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
public void CreateUserGroup(SqlConnection connection, UserGroupInfo userGroup)
{
using (var command = new SqlCommand("Insert into tbl_User_Group " +
" (Name)" +
"VALUES " +
"(@GroupName) ", connection))
{
AddSqlParameter(command, "@GroupName", userGroup.GroupName, System.Data.SqlDbType.NVarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
/// <summary>
/// Hàm lấy tất cả user trừ user admin, mdx, config
/// </summary>
/// <returns>Return DataTable</returns>
///
public UserGroupInfo GetUserGroup(SqlConnection connection, int userGroupID)
{
var info = new UserGroupInfo();
using (var command = new SqlCommand("Select * " +
" from tbl_User_Group where ID=@GroupID ", connection))
{
AddSqlParameter(command, "@GroupID", userGroupID, System.Data.SqlDbType.Int);
var reader = command.ExecuteReader();
reader.Read();
info.GroupID = GetDbReaderValue <int>(reader["ID"]);
info.GroupName = GetDbReaderValue <string>(reader["Name"]);
return(info);
}
}
/// <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);
}
}
