I have my data inside a SqlDataReader and i passed it to the html View, how can i work with that info inside the view, and display it into a table?

Issue

I'm developing a scholl project(using asp.net core Web Application) and i select data from a sql server database using a stored procedure into a SqlDataReader, that returns that The return of the stored procedure.

I already passed that SQLDataReader to my html View, now i need to display that data into a html table like this:Table edited in paint to get the desired result, I'm not getting the result I want because i can't work correctly with the data.

That's how i execute the stored Procedure.

   SqlCommand cmd = new SqlCommand("tentativa1", conn);
   cmd.CommandType = System.Data.CommandType.StoredProcedure;
   cmd.Parameters.Add(new SqlParameter("@tempo", "day"));
   cmd.Parameters.Add(new SqlParameter("@ap_id", "18"));
   SqlDataReader rdr = cmd.ExecuteReader();

That's the code i use to pass the SqlDataReader to the view:

    public class MyViewModel
        {
            public List<teste> objteste { get; set; }
            public SqlDataReader rdr { get; set; }
            public List<L_AccessPoint> objAcessPoint { get; set; }
        }

And thats the code i use to "catch" the data that i pass to the view: @model MyViewModel

I dont know if use a SqlDataReader is the best way to work with data or if is better use other thing.

If you could help me displaying the desired result, I will be very grateful.

Solution

It seems you want use SqlDataReader in razor page directly. Like below

@model MyViewModel
...
// This way is wrong.
Model.rdr

As we know, we can get datas from SqlDataReader, and the datas should be filled in DataSet or DataTable.

---

So my suggestion is you can refer my sqlheler to get datatable data by query or StoredProcedure.

Then you will get a DataTable in your Method, I have mocked some data same as you provided.

Please check the sentence in the image.

Because the effect displayed on your front-end page still needs to be calculated, I recommend that you create a new DataTable, and then adjust the data according to the business, and then pass the DataTable to the front-end. This is a better way to implement it.

//MyViewModel model = new MyViewModel();
//model.DataObj= ToListByDataTable<DataModel>(dtSource);
DataTable dtYear = new DataTable();
DataView dvYear = dtSource.DefaultView;
dtYear = dvYear.ToTable(true,"year");

DataTable dtShow = new DataTable();
dtShow.Columns.Add("Ap_Name", typeof(String));
for (int i = 0; i < dtYear.Rows.Count; i++)
{
    dtShow.Columns.Add(dtYear.Rows[i]["year"].ToString(), typeof(String));
}
// Fill data into the DataTable of dtShow according to certain rules.

return View(dtShow);

Your cshtml should like below:

@using System.Data
@model List<DataTable> 
<style>
    table, th, td {
        border: 1px solid;
    }
</style>

<table cellpadding="0" cellspacing="0">
    <tr>
        <th>Ap_Name</th>
        @foreach (DataRow row in Model[0].Rows)
        {
            <th>@row["year"]</th>
        }
    </tr>

    @foreach (DataRow row in Model[1].Rows)
    {
        <tr>
            <td>@row["Ap_Name"]</td>
             @foreach (DataRow row1 in Model[0].Rows) { 
                 <td>@row1["year"]</td>
             }
        </tr>
    }
</table>

You need fill in data by yourself.

Answered By - Jason Pan