Click here to Skip to main content
15,391,989 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Posted
Updated 8-Apr-21 22:20pm
v3
Comments
Gerry Schmitz 8-Apr-21 12:20pm
   
Instead of a DataTable, use a list / collection of the (dbContext) entity for the model.
Robert Woodard 8-Apr-21 16:09pm
   
What I am having the main issue with is the dbContext entity. How do you create a model/entity for something that is dynamic? The names of the columns change based on what the patient record is named in the database. Some of the auditors call them A,B,C,etc... and some of them call them Patient 1,Patient 2,etc... Can entity framework map to a model that the names of the column change? Am I just going about this all the wrong way?
Gerry Schmitz 9-Apr-21 5:49am
   
I see now ... you don't have any "entities". Nothing wrong with using a DataTable; you can still use EF for the other parts. For your "dynamic part", you need the "column definitions" of the data table; so, "no bad form". If you wanted to use EF, your (simplest) "reporting entity" would probably look like a csv record; with one type for headings and another for column data. After that, it would start looking like a data table.

1 solution

Even if you're using Entity Framework, you can still get access to the underlying connection to execute raw SQL commands.

For example:
C#
var dt = new DataTable();

using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "YourStoredProcedureName";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue(...);
    
    if (command.Connection.State != ConnectionState.Open)
    {
        command.Connection.Open();
    }
    
    using (var reader = command.ExecuteReader())
    {
        dt.Load(reader);
    }
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900