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

Calling and executing stored procedures in ASP.Net MVC (C#)

In ASP.Net MVC, you can call and execute stored procedures using the ADO.Net framework. Here’s how you can do it:

1. Step 1: Create a connection string in your web.config file to connect to the database.

<connectionStrings>
  <add name="MyConnectionString" connectionString="your_connection_string_here" providerName="System.Data.SqlClient" />
</connectionStrings>

2. Step 2: Create a method in your MVC controller to call the stored procedure.

using System.Data;
using System.Data.SqlClient;

public class MyController : Controller
{
    private string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    
    public ActionResult ExecuteStoredProcedure()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand("YourStoredProcedureName", connection);
            command.CommandType = CommandType.StoredProcedure;
            
            // Add parameters if any
            command.Parameters.AddWithValue("@Param1", value1);
            command.Parameters.AddWithValue("@Param2", value2);
            
            // Open connection and execute the stored procedure
            connection.Open();
            command.ExecuteNonQuery();
            
            // Close the connection
            connection.Close();
        }
        
        return View();
    }
}

3. Step 3: Declare a button or a link in your view to trigger the execution of the stored procedure.

<a href="MyController/ExecuteStoredProcedure">Execute Stored Procedure</a>

4. Step 4: Run your MVC application and click on the link/button to execute the stored procedure.

Note: Make sure you replace “YourStoredProcedureName” with the actual name of your stored procedure, and “@Param1” and “@Param2” with the actual parameter names if your stored procedure has any.

Example:

Let’s assume you have a stored procedure called “AddProduct” that inserts a new product into a database table. The stored procedure takes two parameters – “ProductName” and “Price”. Here’s how you can call and execute this stored procedure in ASP.Net MVC:

1. Create the stored procedure in your database:

CREATE PROCEDURE AddProduct
    (
        @ProductName VARCHAR(50),
        @Price DECIMAL(10,2)
    )
    AS
    BEGIN
        -- Your insert logic here
        INSERT INTO Products (ProductName, Price) VALUES (@ProductName, @Price)
    END

2. Add the method to your MVC controller:

public class ProductController : Controller
{
    private string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    
    public ActionResult AddProduct()
    {
        // Get the product details from the user input
        string productName = "Sample Product";
        decimal price = 10.99m;
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand("AddProduct", connection);
            command.CommandType = CommandType.StoredProcedure;
            
            // Add the parameters
            command.Parameters.AddWithValue("@ProductName", productName);
            command.Parameters.AddWithValue("@Price", price);
            
            // Open connection and execute the stored procedure
            connection.Open();
            command.ExecuteNonQuery();
            
            // Close the connection
            connection.Close();
        }
        
        return View();
    }
}

3. Add the link/button to your product view:

<a href="Product/AddProduct">Add Product</a>

4. Run your MVC application, navigate to the product view, and click on the “Add Product” link/button to execute the stored procedure and insert a new product into the database.

That’s it! You have now learned how to call and execute stored procedures in ASP.Net MVC using C#. Feel free to modify the code according to your specific requirements.