Introduction
In ASP.NET Core, interacting with databases often involves executing stored procedures to perform various operations. Whether retrieving data, updating records, or executing complex business logic, calling a stored procedure provides a powerful and efficient way to work with your database. This article will explore different methods available for calling a stored procedure in ASP.NET Core and provide examples for each approach.
1. Using ADO.NET
ADO.NET is a fundamental data access technology in .NET, and it offers a straightforward way to call stored procedures in ASP.NET Core. Here's an example.
using System.Data;
using System.Data.SqlClient;
public class UserRepository
{
private readonly string _connectionString;
public UserRepository(string connectionString)
{
_connectionString = connectionString;
}
public void ExecuteStoredProcedure()
{
using (var connection = new SqlConnection(_connectionString))
{
using (var command = new SqlCommand("StoredProcedureName", connection))
{
command.CommandType = CommandType.StoredProcedure;
// Add parameters if needed
connection.Open();
command.ExecuteNonQuery();
}
}
}
}
2. Entity Framework Core
Entity Framework Core (EF Core) is a popular Object-Relational Mapping (ORM) framework that simplifies database operations in ASP.NET Core. Here are two examples of calling stored procedures using EF Core.
If you want to learn more about Entity Framework Core, Please go through a detailed article here on c#Corner: Introduction To Entity Framework Core
a. Raw SQL queries
using Microsoft.EntityFrameworkCore;
public class UserRepository
{
private readonly DbContext _context;
public UserRepository(DbContext context)
{
_context = context;
}
public void ExecuteStoredProcedure()
{
_context.Database.ExecuteSqlRaw("EXEC StoredProcedureName");
}
}
b. Function mapping
using Microsoft.EntityFrameworkCore;
public class UserRepository
{
private readonly DbContext _context;
public UserRepository(DbContext context)
{
_context = context;
}
public void ExecuteStoredProcedure()
{
_context.Database.ExecuteSqlInterpolated($"EXEC StoredProcedureName {param1}, {param2}");
}
}
3. Dapper
Dapper is a lightweight micro-ORM that provides a fast and flexible way to query databases. Here's an example of calling a stored procedure using Dapper.
Read more here: Exploring Dapper In .NET Core
using Dapper;
using System.Data;
public class UserRepository
{
private readonly IDbConnection _connection;
public UserRepository(IDbConnection connection)
{
_connection = connection;
}
public void ExecuteStoredProcedure()
{
_connection.Execute("StoredProcedureName", commandType: CommandType.StoredProcedure);
}
}
4. Stored Procedure Mapping
Some ORMs, like FluentMigrator or Dapper-Extensions, provide additional features to map stored procedures to your data access layer. Here's an example using FluentMigrator.
using FluentMigrator;
using System.CodeDom.Compiler;
[Migration(20210519120000)]
public class ExecuteStoredProcedureMigration : Migration
{
public override void Up()
{
Execute.EmbeddedScript("StoredProcedureScript.sql");
}
public override void Down()
{
// Revert the changes if needed
}
}
Conclusion
Calling stored procedures in ASP.NET Core offers a powerful mechanism for working with databases. This article explored several methods for calling stored procedures, including ADO.NET, Entity Framework Core (using raw SQL queries and function mapping), Dapper, and stored procedure mapping frameworks like FluentMigrator. Each approach has advantages and uses cases, so choose the method that best fits your project requirements and preferences. Experiment with these examples to gain a solid understanding of how to call stored procedures effectively in your ASP.NET Core applications.