I am trying to generate a RDLC report in ASP.NET where the columns of my Dataset will be dynamic and determined only at run time.
I have a made a function that returns a DataTable, and by selecting this function in the RDLC report wizard, I can generate my report successfully.
public DataTable GetTable()
{
// Here we create a DataTable with four columns.
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("testColumn", typeof(DateTime));
// Here we add five DataRows.
table.Rows.Add(25, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
return table;
}
But, if I make a slight change to the function so that my datatable is truly dynamic, by populating columns from the database, my function then does not show up in the report wizard.
This is my changed function
public DataTable GetTable2()
{
// Here we create a DataTable with four columns.
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("testColumn", typeof(DateTime));
SqlConnection connection = new SqlConnection();
connection = Connection.getConnection();
connection.Open();
string tableName = "";
tableName += "Subject";
string Query = "select * from " + tableName + " where Status = 0;";
SqlDataAdapter da = new SqlDataAdapter(Query, connection);
DataSet ds = new DataSet();
da.Fill(ds);
DataRowCollection collection = ds.Tables[0].Rows;
foreach (DataRow row in collection)
{
// Here we add five DataRows.
table.Rows.Add(25, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
}
connection.Close();
return table;
}
You see, the only change I have made to the function is querying from the database and generating the report dataset columns within the loop that iterates through database data.
But due to this change, my function does not show up in the Report Wizard. If I omit the code, it shows up again.
I can use this function to generate a GridView nicely, but the problem is with RDLC reporting.
My objective is to generate the report datatable using database results. Please help me.
See Question&Answers more detail:
os