private void BindReport()
{
string query = "SELECT ";
bool isSelected = chkColumns.Items.Cast <ListItem>().Count(i => i.Selected == true) > 0;
if (!isSelected)
{
chkColumns.Items[0].Selected = true;
}
foreach (ListItem item in chkColumns.Items)
{
if (item.Selected)
{
query += item.Value + ",";
isSelected = true;
}
}
query = query.Substring(0, query.Length - 1);
query += " FROM tblDesignation";
ReportDocument crystalReport = new ReportDocument();
CrystalReportViewer1.DisplayGroupTree = false;
crystalReport.Load(Server.MapPath("crptDesignation.rpt"));
dsDesignation dsCustomers = GetData(query, crystalReport);
crystalReport.SetDataSource(dsCustomers);
CrystalReportViewer1.ReportSource = crystalReport;
}
private dsDesignation GetData(string query, ReportDocument crystalReport)
{
string conString = ConfigurationManager.ConnectionStrings["MyCon"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
dsDesignation ds1 = new dsDesignation();
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
//Get the List of all TextObjects in Section2.
List <TextObject> textObjects = crystalReport.ReportDefinition.Sections["Section2"].ReportObjects.OfType <TextObject>().ToList();
for (int i = 0; i < textObjects.Count; i++)
{
//Set the name of Column in TextObject.
textObjects[i].Text = string.Empty;
if (sdr.FieldCount > i)
{
if (sdr.GetName(i).ToUpper() == "DESIGNATIONID")
{
textObjects[i].Text = "Desgination ID";
}
else if (sdr.GetName(i).ToUpper() == "DESIGNATIONNAME")
{
textObjects[i].Text = "Desgination Name";
}
else if (sdr.GetName(i).ToUpper() == "DESCRIPTION")
{
textObjects[i].Text = "Description";
}
else if (sdr.GetName(i).ToUpper() == "ISACTIVE")
{
textObjects[i].Text = "Is Active";
}
}
}
//Load all the data rows in the Dataset.
while (sdr.Read())
{
DataRow dr = ds1.Tables[0].Rows.Add();
for (int i = 0; i < sdr.FieldCount; i++)
{
dr[i] = sdr[i];
}
}
}
con.Close();
return(ds1);
}
}