0

I currently have a system set up where the users can "build" their own SQL query using a GUI.

I need to show the result in a report like crystal reports, Report Viewer or Telerik Reporting etc.

Because I don't know what columns will be returned from the query (because the user has full control over what columns to select), I need to know if it is possible at all to show this data in a report.

So my question is: Is it possible to show the result of the query in a reporting control without knowing what columns are actually going to be returned/shown? And how?

Pieter888
  • 4,882
  • 13
  • 53
  • 74

1 Answers1

1

Web app or desktop?

I don't think this is exactly what you are looking for but in web apps I have used something like the following before:

SqlDataReader reader = cmd.ExecuteReader();
List<string> cols = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
    cols.Add(reader.GetName(i));
}

StringBuilder sb = new StringBuilder();
sb.Append("<table><tr>");
foreach (string s in cols)
{
    sb.Append("<th>"+s+"</th>");
}
sb.Append("</tr>");

while (reader.Read())
{
    sb.Append("<tr>");
    foreach (string s in cols)
    {
        sb.Append("<td>"+reader[s]+"</td>");
    }
sb.Append("</tr>");

}
sb.Append("</table>");

don't know if this is the best approach. its handy as excel will open an html table if you give it an excel extension - with formatting and everything - so you can easily offer an open in excel option.

For desktop then you could add columns to a datagrid and then rows or open the hmtl in a browser window.

For classic ASP there is actually another SO answer that has just what you need:

https://stackoverflow.com/a/1662143/359135

Community
  • 1
  • 1
  • Thanks for the answer, I'll see if this works for me. It's a web-app but it's ASP. So I'll have to handle the C# part using your desktop approach. – Pieter888 Apr 23 '12 at 08:39
  • I've done the same thing in classic ASP many times.. I look for a good reference and update the answer –  Apr 23 '12 at 08:52