/// <summary> /// Loads a collection of Role objects for the given user /// </summary> /// <param name="userId">Id of the user to load the roles for</param> /// <returns>A collection of Role objects for the given user</returns> public static RoleCollection LoadForUser(Int32 userId) { //CREATE THE DYNAMIC SQL TO LOAD OBJECT StringBuilder selectQuery = new StringBuilder(); selectQuery.Append("SELECT " + Role.GetColumnNames("R")); selectQuery.Append(" FROM (ac_Roles R INNER JOIN ac_GroupRoles GR ON R.RoleId = GR.RoleId)"); selectQuery.Append(" INNER JOIN ac_UserGroups UG ON GR.GroupId = UG.GroupId"); selectQuery.Append(" WHERE UG.UserId = @userId"); Database database = Token.Instance.Database; DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString()); database.AddInParameter(selectCommand, "@userId", System.Data.DbType.Int32, userId); //EXECUTE THE COMMAND RoleCollection results = new RoleCollection(); using (IDataReader dr = database.ExecuteReader(selectCommand)) { while (dr.Read()) { Role role = new Role(); Role.LoadDataReader(role, dr); results.Add(role); } dr.Close(); } return(results); }
public static RoleCollection LoadForGroup(Int32 groupId, int maximumRows, int startRowIndex, string sortExpression) { //CREATE THE DYNAMIC SQL TO LOAD OBJECT StringBuilder selectQuery = new StringBuilder(); selectQuery.Append("SELECT"); if (maximumRows > 0) { selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString()); } selectQuery.Append(" " + Role.GetColumnNames("ac_Roles")); selectQuery.Append(" FROM ac_Roles, ac_GroupRoles"); selectQuery.Append(" WHERE ac_Roles.RoleId = ac_GroupRoles.RoleId"); selectQuery.Append(" AND ac_GroupRoles.GroupId = @groupId"); if (!string.IsNullOrEmpty(sortExpression)) { selectQuery.Append(" ORDER BY " + sortExpression); } Database database = Token.Instance.Database; DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString()); database.AddInParameter(selectCommand, "@groupId", System.Data.DbType.Int32, groupId); //EXECUTE THE COMMAND RoleCollection results = new RoleCollection(); int thisIndex = 0; int rowCount = 0; using (IDataReader dr = database.ExecuteReader(selectCommand)) { while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows))) { if (thisIndex >= startRowIndex) { Role role = new Role(); Role.LoadDataReader(role, dr); results.Add(role); rowCount++; } thisIndex++; } dr.Close(); } return(results); }
public static RoleCollection LoadForCriteria(string sqlCriteria, int maximumRows, int startRowIndex, string sortExpression) { //CREATE THE DYNAMIC SQL TO LOAD OBJECT StringBuilder selectQuery = new StringBuilder(); selectQuery.Append("SELECT"); if (maximumRows > 0) { selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString()); } selectQuery.Append(" " + Role.GetColumnNames(string.Empty)); selectQuery.Append(" FROM ac_Roles"); string whereClause = string.IsNullOrEmpty(sqlCriteria) ? string.Empty : " WHERE " + sqlCriteria; selectQuery.Append(whereClause); if (!string.IsNullOrEmpty(sortExpression)) { selectQuery.Append(" ORDER BY " + sortExpression); } Database database = Token.Instance.Database; DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString()); //EXECUTE THE COMMAND RoleCollection results = new RoleCollection(); int thisIndex = 0; int rowCount = 0; using (IDataReader dr = database.ExecuteReader(selectCommand)) { while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows))) { if (thisIndex >= startRowIndex) { Role role = new Role(); Role.LoadDataReader(role, dr); results.Add(role); rowCount++; } thisIndex++; } dr.Close(); } return(results); }