How the display the MySQL Table to HTML table using asp.net c#

Question

I am having trouble with working on how to display the rows of the table in an HTML data table. I want to display the information stored on the Mysql Table in HTML data table using c# in asp.net. The problem is, the code is working but, only the last row is shown in the table of HTML. please help. the code follows,

C#

protected void ListOfEmployee()
{
    List<EmployeeAccounts> empList = new List<EmployeeAccounts>();
    using (MySqlConnection conn = new MySqlConnection(connectDB()))
    {
        conn.Open();
        MySqlCommand cmd = new MySqlCommand("SELECT * FROM user", conn);
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = 0;
        MySqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            empList.Add(new EmployeeAccounts
            {
                idNumber = dr["user_id"].ToString(),
                uName = dr["username"].ToString(),
                fName = dr["fullname"].ToString(),
                uType = dr["usertype"].ToString(),
                email = dr["email"].ToString(),
                status = dr["status"].ToString()
            });
        }
        dr.Close();
        conn.Close();
    }
}

public class EmployeeAccounts {
    public string idNumber;
    public string uName;
    public string fName;
    public string uType;
    public string email;
    public string status;
}

ASPX

<asp:Table ID="tblUserList" CssClass="table table-hover table-vcenter" runat="server">
<asp:TableHeaderRow TableSection="TableHeader"> 
<asp:TableHeaderCell >#</asp:TableHeaderCell>
<asp:TableHeaderCell >Full Name</asp:TableHeaderCell>
<asp:TableHeaderCell >Email</asp:TableHeaderCell>
<asp:TableHeaderCell >Status</asp:TableHeaderCell>
<asp:TableHeaderCell >User Type</asp:TableHeaderCell>
</asp:TableHeaderRow>
<asp:TableRow>
<asp:TableCell ID="cellID"></asp:TableCell>
<asp:TableCell ID="cellName"></asp:TableCell>
<asp:TableCell ID="cellEmail"></asp:TableCell>
<asp:TableCell CssClass="label label-table label-info" ID="cellStatus"></asp:TableCell>
<asp:TableCell ID="celluType"></asp:TableCell>
</asp:TableRow>
</asp:Table>

Can you show the code that you have in the code-behind (.aspx.vb) file? — A-A-ron
– A-A-ron, Commented Apr 22, 2019 at 14:53
@A-A-ron I have already attached the code sir.. it was the C# code. — Boooo Booooo
– Boooo Booooo, Commented Apr 23, 2019 at 2:12
I have provide 2nd version of doing this, you may re-check my answer below. — mjb
– mjb, Commented Oct 30, 2019 at 12:18

mjb · Accepted Answer · 2019-10-25 02:05:39Z

List<EmployeeAccounts> lst = GetEmployeeAccounts();

var sb = new System.Text.StringBuilder();

sb.Append(@"
<table class='table table-hover table-vcenter'>
<tr>
<th>ID</th>
<th>Full Name</th>
<th>Type</th>
<th>Email</th>
<th>Status</th>
</tr>
");

if(lst.Count==0)
{
    sb.Append("<tr><td colspan='5'>No record</td></tr>");
}
else
{
    foreach(var acc in lst)
    {
        sb.Append("<tr>");
        sb.AppendFormat("<td>{0}</td>", acc.idNumber);
        sb.AppendFormat("<td>{0}</td>", acc.fName);
        sb.AppendFormat("<td>{0}</td>", acc.uType);
        sb.AppendFormat("<td>{0}</td>", acc.email);
        sb.AppendFormat("<td class='label label-table label-info'>{0}</td>", acc.status);
        sb.Append("</tr>");
    }
}

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

string htmlTable = sb.ToString();

add a placeholder at the ASP.NET page:

<asp:PlaceHolder ID="ph1" runat="server"></asp:PlaceHolder>

then at the code behind, continue the coding:

ph1.Controls.Add(new LiteralControl(htmlTable));

==================================

Update: Version 2 (Oct 25, 2019)

List<EmployeeAccounts> lst = GetEmployeeAccounts();

var sb = new System.Text.StringBuilder();

sb.Append(@"
<table class='table table-hover table-vcenter'>
<tr>
<th>ID</th>
<th>Full Name</th>
<th>Type</th>
<th>Email</th>
<th>Status</th>
</tr>
");

if (lst.Count == 0)
{
    sb.Append("<tr><td colspan='5'>No record</td></tr>");
}
else
{
    foreach (var acc in lst)
    {
        sb.Append($@"
<tr>
<td>{acc.idNumber}</td>
<td>{acc.fName}</td>
<td>{acc.uType}</td>
<td>{acc.email}</td>
<td class='label label-table label-info'>{acc.status}</td>
</tr>
");
    }
}

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

string htmlTable = sb.ToString();

Collectives™ on Stack Overflow

How the display the MySQL Table to HTML table using asp.net c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related