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

Question

Good afternoon guys, I'm a little suspended here. I created my database, model, controller, and view in visual studio using ASP.NET MVC and C #, but I cannot figure out how to call the stored procedure I created.

I want the stored procedure to be called on the button that I placed on my view. This stored procedure should execute and display the results when the button is clicked. Below are the stored procedure, view, model, and controller that 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 staff 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. This is not much, just something for a practical purpose.

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

This is my opinion:

 @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. Will accept hints when passing parameters to the stored procedure. Please correct me if I do not do something right here. Thank you for your concern.

Answer 1

If using EF is not necessary, you can do it as follows:

 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 the Entity Framework, check out this discussion . You also want to use stored procedures to insert, update, and delete this manual from Microsoft.

To execute the code with the click of a button, you can create a form as a single button inside the form as follows:

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

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

 public ActionResult TestAction(){....} 

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



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

Answer 2

 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 { }