MVC execute stored procedure with parameters

MVC Execute Stored Procedure with Parameters

In a Model-View-Controller (MVC) architecture, executing a stored procedure with parameters can be achieved in several steps. Here’s an example of how to accomplish this using ASP.NET MVC:

Step 1: Create a Model

Create a model class to represent the data returned by the stored procedure. For example, let’s say we have a stored procedure called “GetCustomers” that returns a list of customer records:


public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

Step 2: Create a Database Context

Create a database context class that inherits from DbContext to establish a connection to the database. For example:


public class MyDbContext : DbContext
{
    public DbSet Customers { get; set; }
}

Step 3: Add Connection String to Web.config

Add a connection string to the Web.config file to specify the database connection details:


<connectionStrings>
    <add name="MyDbContext" connectionString="YourConnectionStringHere" providerName="System.Data.SqlClient" />
</connectionStrings>

Step 4: Create a Controller Action

Create a controller action to execute the stored procedure and pass the parameters. For example:


public class CustomerController : Controller
{
    public ActionResult Index()
    {
        using (var db = new MyDbContext())
        {
            var customerIdParam = new SqlParameter("@CustomerId", 1);
            var customers = db.Customers.SqlQuery("EXEC GetCustomers @CustomerId", customerIdParam).ToList();
            
            return View(customers);
        }
    }
}

Step 5: Create a View

Create a view to display the data returned by the stored procedure. For example:


@model List<Customer>

<table>
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var customer in Model)
        {
            <tr>
                <td>@customer.Id</td>
                <td>@customer.Name</td>
                <td>@customer.Email</td>
            </tr>
        }
    </tbody>
</table>

Step 6: Run the Application

Run the application and navigate to the corresponding controller action. The stored procedure will be executed with the specified parameters, and the resulting data will be displayed in the view.

MVC execute stored procedure with parameters