public RoleItem GetByCode(string code)
{
using (SqlHelper sqlh = new SqlHelper(@"
select
r.role Id,
r.code Code,
r.mem Mem
from Access.role r
where r.code = @Code"))
{
sqlh.AddWithValue("@Code", code);
var dt = sqlh.ExecTable();
if (dt.Rows.Count == 0)
{
return null;
}
return new RoleItem()
{
Id = (int)dt.Rows[0]["Id"],
Code = (string)dt.Rows[0]["Code"],
Mem = (string)dt.Rows[0]["Mem"]
};
}
}
public List<RoleItem> GetList()
{
using (SqlHelper sqlh = new SqlHelper(@"
select
r.role Id,
r.code Code,
r.mem Mem
from Access.role r"))
{
var dt = sqlh.ExecTable();
var list = new List<RoleItem>();
foreach (DataRow row in dt.Rows)
{
list.Add(new RoleItem()
{
Id = (int)row["Id"],
Code = (string)row["Code"],
Mem = (string)row["Mem"]
});
}
return list;
}
}
public List<RoleItem> GetListByUser(UserItem user)
{
using (SqlHelper sqlh = new SqlHelper(@"
select
r.role Id,
r.code Code,
r.mem Mem
from Access.[user] u
join Access.user2role u2r on u2r.[user] = u.[user]
join Access.role r on r.role = u2r.role
where u.[user] = @Id"))
{
sqlh.AddWithValue("Id", user.Id);
var dt = sqlh.ExecTable();
var list = new List<RoleItem>();
foreach (DataRow row in dt.Rows)
{
list.Add(new RoleItem()
{
Id = (int)row["Id"],
Code = (string)row["Code"],
Mem = (string)row["Mem"]
});
}
return list;
}
}