Using StringBuilder and a DataTable, How do I return multiple rows in three columns without breaking when there is only one row?

Question

I have a function that returns a list of email addresses from a SQL stored Proc based on an ID called. It is using StringBuilder and returns one column. For most IDs there are 4 or less email addresses and this format is fine. However we are now getting more IDs with 10+ email addresses and this is making the page too long.

The function is:

DataTable dt = DAL.ExecStoredProc(DAL.DatabaseName.DB, "storedProc", param);
StringBuilder sb = new StringBuilder();
sb.Append("<br/><br/>");
sb.Append("<table border='0' cellpadding='3'>");
for (int i = 0; i < dt.Rows.Count; i++)
{
    sb.Append("<tr><td>");
    sb.Append(dt.Rows[i]["EMail"].ToString());
    sb.Append("</td></tr>");
}
sb.Append("</table>");
return sb.ToString();

I have tried using the following but it breaks when there are too few addresses to return:

DataTable dt = DAL.ExecStoredProc(DAL.DatabaseName.DB, "storedProc", param);
StringBuilder sb = new StringBuilder();
sb.Append("<br/><br/>");
sb.Append("<table border='0' cellpadding='3'>");
for (int i = 0; i < dt.Rows.Count; i++)
{
    sb.Append("<tr><td>");
    sb.Append(dt.Rows[i]["EMail"].ToString());
    i++;
    sb.Append("</td>");
    sb.Append("<td>");
    sb.Append(dt.Rows[i]["EMail"].ToString());
    i++;
    sb.Append("</td>");
    sb.Append("<td>");
    sb.Append(dt.Rows[i]["EMail"].ToString());
    i++;
    sb.Append("</td></tr>");
}
sb.Append("</table>");
return sb.ToString();

Solution 1

Using Linq's Take function, you could replace the following block of code from your first example:

for (int i = 0; i < dt.Rows.Count; i++)
{
    sb.Append("<tr><td>");
    sb.Append(dt.Rows[i]["EMail"].ToString());
    sb.Append("</td></tr>");
}

with this:

foreach (var row in dt.Rows.OfType<DataRow>().Take(3))
{
    sb.Append("<tr><td>");
    sb.Append(row["EMail"].ToString());
    sb.Append("</td></tr>");
}

Since Take returns up to the specified number of elements from the beginning of the sequence, this block of code will be run anywhere from 0 to 3 times. You'll have 3 addresses displayed at most (even if more are present), and you won't get an IndexOutOfRangeException if you have less than 3.

---

UPDATE: ASP.NET 2.0 Compatible

Since you can't use Linq, this should have the same result:

for (int i = 0; i < (dt.Rows.Count > 3 ? 3 : dt.Rows.Count); i++)
{
    sb.Append("<tr><td>");
    sb.Append(dt.Rows[i]["EMail"].ToString());
    sb.Append("</td></tr>");
}

The expression dt.Rows.Count > 3 ? 3 : dt.Rows.Count uses the ? operator to cause the for loop to iterate over all of the email addresses unless there are more than 3, in which case it will iterate only 3 times.

Solution 2

Instead of building a html table with a string builder have you considered using an asp.net repeater control. You can bind the DataTable directly to the repeater and then control the html from the html design surface. It would prove to be much more flexible for what you are trying to do. This is assuming you're using asp.net.

Also see my post on creating a custom asp.net control as this will give you the most flexibility as well as encapsulate your custom html logic.

Enjoy!

Solution 3

I think Doug is on the right track, but if you insist on doing it with StringBuilder and a for-loop, try this:

DataTable dt = DAL.ExecStoredProc(DAL.DatabaseName.DB, "storedProc", param);

StringBuilder sb = new StringBuilder();
sb.Append("<br/><br/>");
sb.Append("<table border='0' cellpadding='3'>");

string rowFormat = "<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>";

for (int i = 0; i < dt.Rows.Count; i+=3)
{
    string[] rowEmails = { String.Empty, String.Empty, String.Empty };

    for (int j = 0; j < 3; j++)
    {
        if (i+j < dt.Rows.Count) rowEmails[j] = dt.Rows[i+j]["Email"].ToString();
    }

    sb.AppendFormat(rowFormat, rowEmails[0], rowEmails[1], rowEmails[2]);
}

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

return sb.ToString();

Comments

[InsertOldUserIDHere]

Sorry for not adding this at first. The site is ASP.NET 2.0 and updating the version is not something that can be done at this time.

[Donut]

No worries. I edited my answer to use the ternary operator, see if that helps.

[InsertOldUserIDHere]

I like this but as the site is .net 2.0 I don't think LINQ is an option. Sorry but I didn't state the version at first.

[InsertOldUserIDHere]

On compile the for returns "Operator '>' cannot be applied to operands of type 'bool' and 'int'"

[InsertOldUserIDHere]

The issue I am having with the repeater control is I can get the results all back in one column but not spread over three. The following gives three columns of the same results, and one <td> just gives one column <ItemTemplate><tr> <td> <%# Eval("Email") %> </td> <td> <%# Eval("Email") %> </td> <td> <%# Eval("Email") %> </td> </tr> </ItemTemplate>

[Donut]

Sorry about that, should've had parentheses. Use (dt.Rows.Count > 3 ? 3 : dt.Rows.Count), see my edit.

[Doug]

@zk - I see your issue - a better choice might be to make a quick custom control to give you exactly the output you need. See the link I added to my post.

[InsertOldUserIDHere]

This got me to what I was looking for and answered my question. However, the post from Doug below got me thinking about repeaters. That lead to the datalist, and I ended up switching this out for a datalist. Thanks again as this was very helpful to learn.

[InsertOldUserIDHere]

This got me thinking about the datalist control and after some reading up on that I found it worked great for me. So while it wasn't a direct answer, I up'd it for getting me to think :) Thanks much,

[Doug]

@zk - glad this discussion got you to a solution