public List <User2Role> LoadUser2Roles()
{
string sql = @"SELECT [UserID]
, [RoleID]
FROM [BE_User2Role]" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
List <User2Role> ret = new List <User2Role>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
User2Role iret = new User2Role();
if (!Convert.IsDBNull(dr["UserID"]))
{
iret.UserID = (Guid)dr["UserID"];
}
if (!Convert.IsDBNull(dr["RoleID"]))
{
iret.RoleID = (Guid)dr["RoleID"];
}
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int LoadUser2RoleByUserID_RoleID(User2Role obj)
{
string sql = @"SELECT [UserID]
, [RoleID]
FROM [BE_User2Role] WITH(NOLOCK) WHERE [UserID]=@UserID AND [RoleID]=@RoleID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pUserID = new SqlParameter("UserID", Convert2DBnull(obj.UserID));
pUserID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pUserID);
SqlParameter pRoleID = new SqlParameter("RoleID", Convert2DBnull(obj.RoleID));
pRoleID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pRoleID);
int ret = 0;
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["UserID"]))
{
obj.UserID = (Guid)dr["UserID"];
}
if (!Convert.IsDBNull(dr["RoleID"]))
{
obj.RoleID = (Guid)dr["RoleID"];
}
ret += 1;
}
}
finally
{
dr.Close();
}
return(ret);
}
public int InsertUser2Role(User2Role obj)
{
string sql = @"INSERT INTO[BE_User2Role]([UserID]
, [RoleID]
) VALUES(@UserID
, @RoleID
)" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pUserID = new SqlParameter("UserID", Convert2DBnull(obj.UserID));
pUserID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pUserID);
SqlParameter pRoleID = new SqlParameter("RoleID", Convert2DBnull(obj.RoleID));
pRoleID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pRoleID);
return(cmd.ExecuteNonQuery());
}
public List <User2Role> LoadUser2RolesByRoleID(Guid roleID)
{
string sql = @"SELECT [UserID]
, [RoleID]
FROM [BE_User2Role] WHERE [RoleID]=@RoleID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pRoleID = new SqlParameter("RoleID", roleID);
pRoleID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pRoleID);
List <User2Role> ret = new List <User2Role>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
User2Role iret = new User2Role();
if (!Convert.IsDBNull(dr["UserID"]))
{
iret.UserID = (Guid)dr["UserID"];
}
if (!Convert.IsDBNull(dr["RoleID"]))
{
iret.RoleID = (Guid)dr["RoleID"];
}
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}