How to populate DataTable using stored procedure in EF database-first

Question

I have a stored procedure GetAllUsers which returns all the columns from a the table Users.

Here is a sample of a the DataTable I want to achieve:

I'm using Entity Framework (database-first approach).

Stored procedure:

ALTER PROCEDURE [dbo].[GetAllUsers]
AS
BEGIN
    SELECT * 
    FROM Users
END

Controller:

    public ActionResult Index()
    {
        // Create ViewModel/ResourcViewModel instance
        var vm = new ResourceViewModel();

        // Code to fetch data from stored procedure and display to DataTable
    }

View model:

public class ResourceViewModel
{
    public int UserID { set; get; }
    public string FirstName { set; get; }
    public string LastName { set; get; }
}

View:

<table class="table" id="dataTable">
    <thead>
        <tr>
           <th class="text-center">First Name</th>
           <th class="text-center">Last Name</th>
           <th class="text-center">Actions</th>

       </tr>
   </thead>
   <tbody>
    <tr>
        <td>John</td>
        <td>Wick</td>
        <td><button class="btn btn-xs btn-primary" data-toggle="modal" data-target="#exampleModal">iew Details</button>
        </td>
    </tr>

    <tr>
        <td>Black</td>
        <td>Panther</td>
        <td><button class="btn btn-xs btn-primary" data-toggle="modal" data-target="#exampleModal">View Details</button>
        </td>
    </tr>
</tbody>
</table>

Once I have displayed the list of users into the DataTable. I also want to know how to bind the UserID to my View Details button.

select * from users doesn't need a stored procedure don't you think ? — niceman
– niceman, Commented May 9, 2018 at 10:57
I'm planning to add a variable soon to filter out a specific UserID. — Odie
– Odie, Commented May 9, 2018 at 10:58
Yes. I just want to know how to fetch data from SP to a DataTable first. If you could provide a suggestion that would be great. — Odie
– Odie, Commented May 9, 2018 at 11:02

Philiop · Accepted Answer · 2018-05-09 13:27:53Z

1

Your approach seems to be a little off. With MVC the ideal approach is to return a model to the page, which contains what your after so

public class IndexViewModel
{
      public List<ResourceViewModel> Resources { get; set; } 
}

Then your view should be more like.

@model IndexViewModel
<table class="table" id="dataTable">
    <thead>
        <tr>
           <th class="text-center">First Name</th>
           <th class="text-center">Last Name</th>
           <th class="text-center">Actions</th>

       </tr>
   </thead>
   <tbody>
@foreach(var resource in Model.Resources)
{

    <tr>
        <td>@resource.FirstName</td>
        <td>@resource.LastName</td>
        <td><button class="btn btn-xs btn-primary" data-toggle="modal" data-target="#exampleModal">View Details</button>
        </td>
    </tr>

    <tr>
        <td>Black</td>
        <td>Panther</td>
        <td><button class="btn btn-xs btn-primary" data-toggle="modal" data-target="#exampleModal">View Details</button>
        </td>
    </tr>

}
</tbody>
</table>

So your controller action should be more like

public ActionResult Index()
{
    // Create ViewModel/ResourcViewModel instance
    var vm = new IndexViewModel();

    // Code to fetch data from stored procedure and display to DataTable
    vm.Resources = new List<ResourceViewModel>();

    foreach(var user in GetAllUsers())
    {

        Resources.Add(new ResourceViewModel(){
            FirstName = user .FirstName,
            LastName = user .LastName,
            UserId = user .UserId
        });
    }

    return View(vm);
}

Obviously this is just so pseudo code, and you will need to correctly call your stored procedure. Alternative to the stored procedure though would be using a Repository pattern, with a Query syntax that will allow you to pass through a lamda where clause through.

edited May 9, 2018 at 13:27

answered May 9, 2018 at 11:33

Philiop

4733 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Odie Over a year ago

Huge help. I can't seem to make the .each appear after the calling the stored procedure. I have the sp_GetAllUsers_Result and I can also call the sp_GetAllUsers. Do you have any idea why?

Philiop Over a year ago

Apologies, .each is a extension I have in my framework. Just use a standard foreach loop, i'll ammend the answer

Niranjan Singh · Accepted Answer · 2018-05-09 11:36:48Z

Refer this - In table form controls to implement custom action button in each row or conditionally.

Define Datatables column layout and customize it according to you need.

Example:

$('#example').DataTable( {
        ajax: "../php/staff.php",
        columns: [
            { data: null, render: function ( data, type, row ) {
                // Combine the first and last names into a single table field
                return data.first_name+' '+data.last_name;
            } },
            { data: "position" },
            { data: "office" },
            { data: "extn" },
            { data: "start_date" },
            { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
            {
                data: null,
                className: "center",
                defaultContent: '<a href="" class="editor_edit">Edit</a> / <a href="" class="editor_remove">Delete</a>'
            }
        ]
    } );

References:
jquery dataTables - how to add an edit and delete option

Collectives™ on Stack Overflow

How to populate DataTable using stored procedure in EF database-first

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related