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
– Rui Jarimba, Commented Nov 2, 2018 at 10:49
Possible duplicate of Pass Html String from Controller to View ASP.Net MVC — Muhammad Saqlain
– Muhammad Saqlain, Commented Nov 2, 2018 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
– Rui Jarimba, Commented Nov 2, 2018 at 10:51
create a model, then use that model in your view to create html table using Razor. — R.Sarkar
– R.Sarkar, Commented Nov 2, 2018 at 10:52

Lewis86 · Accepted Answer · 2018-11-02 11:25:31Z

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 2, 2018 at 11:25

Lewis86

5116 silver badges15 bronze badges

answered Nov 2, 2018 at 10:51

Amin Mozhgani

6041 gold badge7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rui Jarimba Over a year ago

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.

Amin Mozhgani Over a year ago

@RuiJarimba I agree, but if he wants to use this approach, I showed him what's the problem with his code.

Leon Over a year ago

Thank you @Amin, It works now. Another quick question, why is it not a good idea to mix HTML and data access code?

Amin Mozhgani Over a year ago

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.

Rui Jarimba Over a year ago

@Leon separation of concerns. Please read about the Model-View-Controller (MVC) or watch some videos such as Introduction to ASP.NET MVC

Collectives™ on Stack Overflow

C# SQL Database to HTML Table

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related