How do I create a model and view based on a SQL server stored procedure that returns a pivot table?

Question

0.00/5 (No votes)

See more:

, +

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 8-Apr-21 5:36am

Robert Woodard

Updated 8-Apr-21 22:20pm

v3

Add a Solution

Comments

[no name] 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?

[no name] 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2021-04-08T22:20:00

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);
    }
}