C# SQL Database to HTML Table

Question

I am trying to get the data from my SQL table to display onto my View page which I would like to display the contents from the table

I can currently read the items in the database using the code below

SqlConnection connection = new SqlConnection(VV);

using (connection)
{
  //LIMIT 5 DESC from ID which shows last 5 work outs 
  SqlCommand myCommand = new SqlCommand("SELECT * FROM Strength", connection);
  connection.Open();

  SqlDataReader read = myCommand.ExecuteReader();
  if (read.HasRows)
  {
    while (read.Read())
    {
      Id = read["Id"].ToString();
      System.Diagnostics.Debug.WriteLine(Id);

      Weight = read["Weight"].ToString();                    
      System.Diagnostics.Debug.WriteLine(Weight);

      Rep = read["Rep"].ToString();
      System.Diagnostics.Debug.WriteLine(Rep);
    }
  }
  else
  {
    Console.WriteLine("nothing");
  }
  read.Close();
}

And now I want to display this in a HTML table on the view. I have tried a few things such as

 ViewBag.HtmlStr = "<table class='table table-striped top-buffer'"
                 + "style='width:300px'>"
                 + "<tr><th>Weight(KG)</th><th>Reps</th></tr>"
                 + "<tr><td>" + TableWeight + "</td>"
                 + "</tr><tr><td>" + TableRep + "</td></tr></table>";

However it is only giving me one row.

Any advice? Many thanks

Code inside `while (read.Read())` will be executed for each row returned by the database. You need to create a list of objects and and a new item to the list for each row, and then send that list to your view. — Rui Jarimba, Nov 02 '18 at 10:49
Possible duplicate of [Pass Html String from Controller to View ASP.Net MVC](https://stackoverflow.com/questions/22781548/pass-html-string-from-controller-to-view-asp-net-mvc) — Muhammad Saqlain, Nov 02 '18 at 10:50
Oh and I strongly recommend you NOT to pass HTML from your controller to your view. You should pass your model (data), not HTML — Rui Jarimba, Nov 02 '18 at 10:51
create a model, then use that model in your view to create html table using Razor. — R.Sarkar, Nov 02 '18 at 10:52

score 1 · Accepted Answer · edited Nov 02 '18 at 11:25

1

using (connection)
{
  //LIMIT 5 DESC from ID which shows last 5 work outs 
  SqlCommand myCommand = new SqlCommand("SELECT * FROM Strength", connection);
  connection.Open();

  SqlDataReader read = myCommand.ExecuteReader();

  string result = "<table class='table table-striped top-buffer'" 
                + "style='width:300px'>" 
                + "<tr><th>Weight(KG)</th><th>Reps</th></tr>";               

  if (read.HasRows)
  {
    while (read.Read())
    {
      Id = read["Id"].ToString();
      System.Diagnostics.Debug.WriteLine(Id);

      Weight = read["Weight"].ToString();
      System.Diagnostics.Debug.WriteLine(Weight);

      Rep = read["Rep"].ToString();
      System.Diagnostics.Debug.WriteLine(Rep);

      result += "<tr><td>" + Weight + "</td>"
             +  "</tr><tr><td>" + Rep + "</td></tr>";
    }
  }
  else
  {
    Console.WriteLine("nothing");
  }
  read.Close();

  ViewBag.HtmlStr = result + "</table>";
}

You must consider that your data source contains multiple rows and you should add each row as a TR inside your table.

edited Nov 02 '18 at 11:25

Lewis86

511
6
15

answered Nov 02 '18 at 10:51

Amin Mozhgani

604
1
7
22

Not a good idea to mix HTML and data access code. You should create a list of objects from the SqlDataReader and then send that list to your view. – Rui Jarimba Nov 02 '18 at 10:53
@RuiJarimba I agree, but if he wants to use this approach, I showed him what's the problem with his code. – Amin Mozhgani Nov 02 '18 at 10:53
Thank you @Amin, It works now. Another quick question, why is it not a good idea to mix HTML and data access code? – Leon Nov 02 '18 at 11:09
1

You're welcome. HTML format is your representation format and it's not relevant to your data structure. So you should create a good data in your controller and you should represent it with your desired format in your view. – Amin Mozhgani Nov 02 '18 at 11:25
@Leon separation of concerns. Please read about the Model-View-Controller (MVC) or watch some videos such as [Introduction to ASP.NET MVC](https://mva.microsoft.com/en-US/training-courses/introduction-to-aspnet-mvc-8322?l=nKZwZ8Zy_3504984382) – Rui Jarimba Nov 02 '18 at 11:41

C# SQL Database to HTML Table

1 Answers1