So I have a very complex stored procedure that returns a pivot table of data. This data is dynamic. Column names will change and the amount of columns can change. Based of the number of chart audits that were audited for a particular physician. This is how SQL Server returns the data from the stored procedure for a particular physician.
Question A B C D Average
11_ChartWellOrg 5.0 6.0 6.0 6.0 5.8
12_HistInfoAdequate 5.0 6.0 5.0 5.0 5.3
14_PhysExamAdequate 6.0 6.0 6.0 6.0 6.0
16_AssessFormulation 6.0 6.0 5.0 6.0 5.8
18_PlanTreatAdequate 6.0 6.0 6.0 6.0 6.0
20_GlobalOverallAssess 6.0 6.0 6.0 6.0 6.0
In the past, I simply put this in a DataGrid that I could handle the dynamic portion with code behind the page.
Public Sub BindGridView()
Dim tc As New TestCode
Dim dt As New DataTable
If intType <> 3 Then
dt = tc.GetDT(intParticipant, intActivity, intAuditor, dBatch)
Else
dt = tc.GetDT(intParticipant, intActivity, intAuditor, intMonth, intYrChartEntry, intDept)
End If
intRows = dt.Rows.Count
intColumns = dt.Columns.Count
gvPivot.DataSource = dt
gvPivot.DataBind()
gvPivot.HeaderRow.Visible = True
gvPivot.HeaderRow.BackColor = Drawing.Color.FromArgb(79, 113, 185)
GridFormat()
End Sub
The requirement now is that I move this to a web portal we have built using DOTNET Core 3.1 MVC. I figured I could load this into a DataTable and send it to this view:
@using System.Data
@model DataTable
@{
}
<div>
<div>
<h4>Matrix</h4>
<table id="tblData" class="table">
<thead>
<tr>
@foreach(DataColumn col in Model.Columns)
{
<th>@col.ColumnName</th>
}
</tr>
</thead>
<tbody>
@foreach (DataRow row in Model.Rows)
{
<tr>
@foreach (DataColumn col in Model.Columns)
{
<td>@row[col.ColumnName]</td>
}
</tr>
}
</tbody>
</table>
</div>
</div>
I figured that would handle the dynamic part of the DataTable that was supplied.
What I cannot figure out is how to get the data using my DBContext. I do not think this can handle dynamic tables or models. Can I go around the DBContext and pull this data outside of the DBContext? Is that bad form? I am novice to MVC and Entity Framework. I have done well so far with the portal, this one is just beating me about the head and neck. Suggestions? Help? Drugs?
If I need to supply more information, let me know. I usually do not post on forums because the answers already seem to be there and I do not have to ask the question.
What I have tried:
Lots and lots of research and have not found an answer yet.