SQL query to HTML table for e-mail

Question

I've been using some code very similar to the following to populate a gridview control:

using(SqlDataAdapter sqlDataAdapter = 
    new SqlDataAdapter("SELECT * FROM Table1",
        "Server=.\\SQLEXPRESS; Integrated Security=SSPI; Database=SampleDb"))
{
    using (DataTable dataTable = new DataTable())
    {
        sqlDataAdapter.Fill(dataTable);
        this.dataGridView1.DataSource = dataTable;
    }
}

I now want to take that grid view and sent the results in an e-mail in a html table. I believe I could work through this by using the gridview as a middle man (add results to gridview then draw html table cell by cell) but I was wondering if there's a way to take the results of an SQL query and draw it into a html table ready for e-mailing?

Summary: How can I query a database and have the results returned in such a way that I could draw a HTML table with them?

styfle · Accepted Answer · 2012-11-12T17:19:49.917

3

No one in answering so I'll give it a shot.

It appears DataTable has a Rows property that is a collection of all the rows in the data table. All you need to do is iterate over the rows and print an html <tr> tag. Then an inner loop can be used for each column using the <td> tag. I have very little experience with C# but it should look something like this:

        emit("<table>\n");
        foreach(DataRow row in dataTable.Rows)
        {
            emit("<tr>");
            foreach(DataColumn column in dataTable.Columns)
            {
                emit("<td>" + row[column] + "</td>");
            }
            emit("</tr>\n"); // add a newline for readability
        }
        emit("</table>\n");

The emit() function can just concatenate a string if you wish and then you can send an email as html with that string as the body.

edited Nov 12 '12 at 17:19

answered Jan 23 '12 at 01:32

styfle

22,361
27
86
128

Do you know of a way I could get the desired result without going via a grid view control? – Michael A Jan 23 '12 at 01:38
Like I said, I have little experience using C# so I'm not sure I understand what you're saying. You're already using `sqlDataAdapter.Fill(dataTable);` to fill the dataTable with the results. This answer takes advantage of that. Do you mean how would you do it without using a DataTable at all? – styfle Jan 23 '12 at 01:54
Yes, sorry. Would be a lot more efficient I think. – Michael A Jan 23 '12 at 01:57
Looks like I can derive the answer from here: http://stackoverflow.com/questions/8640982/sqlcommand-or-sqldataadapter/8650592#8650592 Thank-you for your help! – Michael A Jan 23 '12 at 02:50
Your TD and TR tags are the wrong way around, but other than that this is useful and also works on dynamic pivot tables. Note that you can put column headers in (useful if you are running a dynamic query with varying columns returned) with emit(""); foreach (System.Data.DataColumn columnHeader in dataTable.Columns) { emit("" + columnHeader.Caption + ""); } emit(""); – Mad Halfling Nov 12 '12 at 16:03
@MadHalfling Good catch! How embarrassing. I fixed it. – styfle Nov 12 '12 at 19:07

score 1 · Answer 2 · answered Jan 23 '12 at 03:34

You can render the HTML code from the gridview directly on code-behind using the RenderControl method, and use the StringBuilder output. Example:

StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.RenderControl(hw);

SQL query to HTML table for e-mail

2 Answers2