public static List <CheckBoxListItem> GetAllCompanyForUser(int userid) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT distinct tblCompany.CompanyID, tblCompany.CompanyName FROM cUser INNER JOIN tblCompanyUser on tblCompanyUser.UserID = cUser.UserID"); sb.Append(" INNER JOIN tblCompany on tblCompany.CompanyID = tblCompanyUser.CompanyID"); sb.Append(" where tblCompanyUser.UserID =" + userid); List <CheckBoxListItem> allReports = new List <CheckBoxListItem>(); using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString())) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(sb.ToString(), connection); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CheckBoxListItem role = new CheckBoxListItem(); role.ID = Int32.Parse(reader[0].ToString()); role.Display = reader[1].ToString(); allReports.Add(role); } reader.Close(); } catch (Exception ex) { string exms = ex.Message; } return(allReports); } }
public static List <CheckBoxListItem> GetAllRoles(int roleID) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT distinct RoleID, RoleName FROM cRole WHERE ROLEID>=" + roleID + " order by RoleName"); List <CheckBoxListItem> allRoles = new List <CheckBoxListItem>(); using (SqlConnection connection = new SqlConnection(Helpers.Helpers.GetAppConnectionString())) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(sb.ToString(), connection); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CheckBoxListItem role = new CheckBoxListItem(); role.ID = Int32.Parse(reader[0].ToString()); role.Display = reader[1].ToString(); allRoles.Add(role); } reader.Close(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { connection.Close(); } return(allRoles); } }