How to call and execute stored procedures in ASP.Net MVC(C#)

Question

Good day guys, I'm in a little limbo here. I have created my database, model, controller and view in visual studio using ASP.NET MVC and C#, but I can't figure out how to call a stored procedure that I created also.

I want for the stored procedure to be called on a button I placed in my view. This stored procedure should execute and display results when the button is click. Below are the Stored procedure, view, model and controller I created.

This is my 'Employee' Model:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace MVCSimpleApp.Models
{
    [Table("Employees")]
    public class Employee
    {
        [Display(Name ="Employee Id")]
        public int EmployeeId { get; set; }
        [Display(Name ="First Name")]
        public string FirstName { get; set; }
        [Display(Name ="Last Name")]
        public string LastName { get; set; }
    }
}

This is my Data Context:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace MVCSimpleApp.Models
{
    public class EmployeeContext : DbContext
    {
        public DbSet<Employee> Employee { get; set; }
    }
}

This is my Employee Controller:

using MVCSimpleApp.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;

namespace MVCSimpleApp.Controllers
{
    public class EmployeeController : Controller
    {
        private EmployeeContext db = new EmployeeContext();
        // GET: Employee
        public ActionResult Index()
        {

            var employees = from e in db.Employee select e;
            return View(employees);
        }
    }
 }

And now this is my Stored procedure. It is not much, just something for practice purpose.

Create Proc DisplayStudents
AS
BEGIN
     /*selecting all records from the table whose name is "Employee"*/
    Select * From Employee
END

This is my view:

@model IEnumerable<MVCSimpleApp.Models.Employee>

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
 }

 <h2>Student List</h2>

 <p>
    <a href="@Url.Action("Create")" title="Add new" class="btn btn-primary btn-lg">
        <span class="glyphicon glyphicon-plus "></span>
        Add Student
    </a>


</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.FirstName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.LastName)
        </th>
        <th></th>
    </tr>

 @foreach (var item in Model) {
 <tr>
    <td>
        @Html.DisplayFor(model => item.EmployeeId)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.FirstName)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.LastName)
    </td>
    <td>
        <span>
            <a href="@Url.Action("Edit", new { id = item.EmployeeId})" title="Edit Record">
                <span class="glyphicon glyphicon-pencil"></span>
            </a>
        </span>
        |
        <span>
            <a href="@Url.Action("Details", new { id = item.EmployeeId})" title="View Details">
                <span class="glyphicon glyphicon-th-list"></span>
            </a>
        </span>
        |
        <span>
            <a href="@Url.Action("Delete", new { id = item.EmployeeId})" title="Delete">
                <span class="glyphicon glyphicon-trash"></span>
            </a>
        </span>
    </td>
</tr>
}
  /*this is the button I want the stored procedure to be called on when I click it*/
  <button>Run</button>
</table>

Please guys I need your opinions and feedback on this matter. Will accept tips in passing parameters to a stored procedure. Please correct me if I am not doing things right here. Thanks for your concern.

Answer 1

If using EF is not a necessity you can do it in the following way:

string cnnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;

SqlConnection cnn = new SqlConnection(cnnString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "ProcedureName";
//add any parameters the stored procedure might require
cnn.Open();
object o = cmd.ExecuteScalar();
cnn.Close();

If you need to use Entity Framework check out this discussion. Also you want to use the Stored Procedures for Inserting, Updating and deleting check out this tutorial from Microsoft.

To execute the code from a button click you can create a form an place just one button inside the form like this:

@using(Html.BeginForm("TestAction", "TestController", FormMethod.Get))
{
    <input type="submit" value="Submit" />
}

And in your controller you would have a TestAction method like this

public ActionResult TestAction(){....}

if you need to pass any arguments to TestAction, just specify them as parameters in the method and then use the overloaded version of BeginForm that accepts actionName, controllerName, routeValues and formMethod as arguments.

To pass the results to a view you need to create a view model with properties according to the values you recieve from the stored procedure and then, return a view with the view model from the TestAction method.

Answer 2

Here is an example of how you can do this with entity framework. I personally am not a big fan of entity frameworks because it is slow and clunky, but people with limited DB EXP tend to like it.

Normally I like to give a full example with all the code but due to the way entity frameworks is configured I will pass on that part. Keep in mind this wont work without the entity framework context already being setup.

    private RAP_Entities db = new RAP_Entities();

    public string GetGUID(string DeviceID, string CCCShopID)
    {
        SqlParameter[] Parameters =
        {
            new SqlParameter("@DeviceID", DeviceID),
            new SqlParameter("@CCCShopID", CCCShopID)
        };

        string DistributionChannelGUID = db.Database.SqlQuery<string>("GetDistributionChannelGUID @DeviceID, @CCCShopID", Parameters).ToString();

        return DistributionChannelGUID;   
    }

Answer 3

You can make it through Normal ADO.Net Approach where you call a stored procedure using SqlCommand and pass few parameters to it.

1. Opening connection.
2. Creating instance of SqlCommand where we need to pass stored procedure name and connection string.
3. CommandType says the type of the command.
4. Pass procedure parameters.
5. SqlDataAdapter is used to make call for procedure.
6. da will return you the result. Use as per your requirement

Code:

try
{       
    conn.Open();
    SqlCommand dCmd = new SqlCommand("store_procedure_name",conn);
    dCmd.CommandType = CommandType.StoredProcedure;
    dCmd.Parameters.Add(new SqlParameter("@parameter2",parameter2));
    dCmd.Parameters.Add(new SqlParameter("@parameter1", parameter1));
    SqlDataAdapter da = new SqlDataAdapter(dCmd);
    DataTable table = new DataTable();
    ds.Clear();
    da.Fill(ds);
    conn.Close();

    var das = ds.Tables[0].AsEnumerable();
    return ConvertToDictionary(ds.Tables[0]);
}
catch
{
}