Calling Stored Procedures in ASP.NET Core MVC
ASP.NET Core MVC provides a powerful framework for building web applications. One common task is interacting with databases, and stored procedures are a valuable tool for encapsulating complex database logic. This article will guide you through the process of calling stored procedures from your ASP.NET Core MVC applications.
Why Use Stored Procedures?
Stored procedures offer several advantages:
- Encapsulation: They bundle database operations, making your code more modular and reusable.
- Performance: Stored procedures are pre-compiled and stored on the database server, leading to faster execution compared to dynamic SQL queries.
- Security: Stored procedures can enforce data validation and security checks, reducing the risk of SQL injection vulnerabilities.
Setting Up Your Project
Before you begin, ensure you have the following:
- Visual Studio: Download and install the latest version of Visual Studio with ASP.NET Core support.
- SQL Server: You'll need a SQL Server database to store and execute your stored procedures.
Creating a Stored Procedure
Let's create a simple stored procedure to demonstrate the concept. In your SQL Server Management Studio, create a new stored procedure:
-- Stored procedure to retrieve a list of products
CREATE PROCEDURE GetProducts
AS
BEGIN
SELECT ProductID, ProductName, Price
FROM Products;
END;
GO
This stored procedure retrieves all product records from the Products table.
Implementing Stored Procedure Calls in ASP.NET Core MVC
Now, let's create an ASP.NET Core MVC project and implement code to call this stored procedure.
1. Create a Model:
Create a Product model class to represent the data retrieved from the stored procedure:
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public decimal Price { get; set; }
}
2. Configure Database Connection:
In your Startup.cs file, configure the database connection string:
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext(options =>
options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
// ... other services
}
Replace "DefaultConnection" with your actual connection string.
3. Create a Repository:
Create a repository class to handle data access operations:
public class ProductRepository
{
private readonly MyDbContext _context;
public ProductRepository(MyDbContext context)
{
_context = context;
}
public async Task> GetProductsAsync()
{
// Execute the stored procedure
var products = await _context.Database.ExecuteSqlRawAsync("EXEC GetProducts");
// Map the results to a list of Product objects
var productList = new List();
foreach (var row in products)
{
productList.Add(new Product
{
ProductID = Convert.ToInt32(row["ProductID"]),
ProductName = Convert.ToString(row["ProductName"]),
Price = Convert.ToDecimal(row["Price"])
});
}
return productList;
}
}
This repository uses ExecuteSqlRawAsync to execute the stored procedure. You can also use ExecuteSqlInterpolatedAsync for parameterized stored procedures.
4. Inject Dependencies:
Inject the ProductRepository into your controller:
public class ProductsController : Controller
{
private readonly ProductRepository _repository;
public ProductsController(ProductRepository repository)
{
_repository = repository;
}
// ... other controller actions
}
5. Create a Controller Action:
Create an action method in your controller to retrieve the product data:
public async Task Index()
{
var products = await _repository.GetProductsAsync();
return View(products);
}
6. Create a View:
Create a view to display the product data:
@model IEnumerable
Products
ID
Name
Price
@foreach (var product in Model)
{
@product.ProductID
@product.ProductName
@product.Price
}
7. Run Your Application:
Run your ASP.NET Core MVC application. The Index action should retrieve product data from the stored procedure and display it in the view.
Handling Parameters
Stored procedures often accept parameters. Here's how to pass parameters when calling a stored procedure:
// Example with a stored procedure taking a product ID as input
public async Task GetProductByIdAsync(int productId)
{
var product = await _context.Database.ExecuteSqlInterpolatedAsync($"EXEC GetProductById @productId = {productId}");
return product;
}
This example uses ExecuteSqlInterpolatedAsync to execute the stored procedure with a parameter.
Conclusion
Calling stored procedures in your ASP.NET Core MVC application is a powerful way to leverage the benefits of database logic encapsulation and improve performance. By following these steps, you can easily integrate stored procedures into your application, enhancing data access and application logic.