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);
}
}
