How do you convert a List query result to view model?

Question

I have an MVC controller method List<DataRow> GetUserCriteria() which runs a standard SQL Query, no EF or automapper. After running the query it does:

DataTable dt = new DataTable();
SqlDataAdapter sdp = new SqlDataAdapter(objCommand)
conn.Open();
sdp.Fill(dt)
list = dt.AsEnumerable().ToList();

return list;

Question is in my ActionResultMethod which returns the relevant view, how can I convert that list to the right type for the view model to consume and use in the view?

public ActionResult ClientProfile() {

  var rawData = GetUserCriteria();
  //Convert to type for view model here and pass into the view below. 

 return View()
}

score 1 · Accepted Answer · answered May 08 '17 at 23:37

You can avoid converting, and make it simple:

public class aDataTableView 
{
    public DataTable aTable { get; set; }
}

public class HomeController : Controller
{
    //use any action or code that goes here
    public ActionResult Index63()
    {
        DataTable dt = new DataTable();
        SqlConnection conn = new SqlConnection(@"data source=.\sqlexpress;initial catalog=Breaz;integrated security=True");
        //conn.Open();
        SqlCommand objCommand = new SqlCommand("Select * from dbo.Example", conn);
        SqlDataAdapter sdp = new SqlDataAdapter(objCommand);
        sdp.Fill(dt);

        //you are not explicitely disposing of dt
        //dt.Dispose();

        aDataTableView dtw = new aDataTableView { aTable = dt };

        objCommand.Dispose();
        sdp.Dispose();
        conn.Close();
        conn.Dispose();

        return View(dtw);
    }

@model Testy20161006.Controllers.aDataTableView

@{
        Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index63</title>

</head>
<body>
    @using (Html.BeginForm())
        {
        <table border="1">
            <thead>
                <tr>
                    @foreach (System.Data.DataColumn col in Model.aTable.Columns) { 
                <th>@col.Caption</th>
            }
                </tr>
            </thead>
            <tbody>
                @foreach(System.Data.DataRow row in Model.aTable.Rows) { 
                <tr>
                    @foreach (var cell in row.ItemArray) {
                    <td>@cell.ToString() </td>
                    }
                </tr>
                } 
            </tbody>
        </table>
        <input type="submit" value="click" />
        }
</body>
</html>

credit to http://stackoverflow.com/questions/2243898/displaying-standard-datatables-in-mvc — kblau, May 08 '17 at 23:37
Is there an example of rendering this in a form? The data I am passing to the view is a user's profile data rendered with HtmlHelpers — LifeOf0sAnd1s, May 09 '17 at 14:27

score 0 · Answer 2 · answered May 09 '17 at 16:05

The above answer worked for the data part. I will just add that in the case of what I am doing, I was able to achieve it by doing the following in my controller:

public ActionResult Index63() {
 DataTable dt = new DataTable();
        SqlConnection conn = new SqlConnection(@"data source=.\sqlexpress;initial catalog=Breaz;integrated security=True");
        //conn.Open();
        SqlCommand objCommand = new SqlCommand("Select * from dbo.Example", conn);
        SqlDataAdapter sdp = new SqlDataAdapter(objCommand);
        sdp.Fill(dt);

        aDataTableView dtw = new aDataTableView { aTable = dt };

       //Cast Each Row Element to an object
       object FirstNameField = dt.Row[0][3]

      //Map user values to model backing view
      Index63ViewModel userViewModel = new Index63ViewModel(); 

      userViewModel.FirstName = FirstNameField.ToString();

return(userViewModel)
}

This approach works for passing a single user profile data to the view which is what I needed in this case. Credit is also given to the above link as well. Thank you!

How do you convert a List query result to view model?

2 Answers2