ASP.NET MVC和SQL Server

Question

I am new to ASP.NET and the MVC framework as well, so I'd love it if someone could recommend and/or show me some examples of code on how to best read a table from a database and store it as json.

The goal after that is to receive some query filter options from a javascript, and then output the json-ified table back to the javascript. This will be done for multiple tables, with different column names and different amounts of columns that need to be returned.

When I was working in regular asp.net and c# I built JSON using a stringbuilder like this;

StringBuilder json = new StringBuilder();
while (reader.Read())
{

    json.AppendFormat("{{{{\"AvgDate\": \"{0}\"}},{{\"MarkerID\": \"{1}\"}}}},", reader["AvgDate"], reader["MarkerID"]);

}

Where "AvgDate" and "MarkerID" are columns. My concern with this way of doing it is how to implement a smart way of reusing the same code for when I need more than two columns from the table.

So I would really appreciate any good input I could get here, especially if MVC has any other good way of doing it.

Answer 1

Instead using a StringBuilder to create the JSON result, you could use the following approach:

public class MyClass
{
  public DateTime AverageDate { get; set; }
  public int MarkerId { get; set; }
}

// the action method

public JsonResult MyAction()
{
  var result = new List<MyClass>();
  //..
  while (reader.Read())
  {
    result.Add(new MyClass() {
      AverageDate = reader["AvgDate"], MarkerId = reader["MarkerId"] };
  }
  //..
  return Json(result);
}

Note: this code might not compile as-is. For a complete sample, see here for example.