gptAI
Home

Calling Stored Procedures In ASP.NET Core: A Step-by-Step Guide

9 min read 11-14- 2024
Calling Stored Procedures In ASP.NET Core: A Step-by-Step Guide

Table of Contents :

In modern web development, working with databases is a crucial aspect, and calling stored procedures is a common task when you want to perform complex operations or retrieve specific data efficiently. In this guide, we'll explore how to call stored procedures in ASP.NET Core applications, offering you a detailed step-by-step approach to ensure a smooth experience.

Understanding Stored Procedures

Before diving into the code, let’s first clarify what a stored procedure is. A stored procedure is a prepared SQL code that you can save and reuse. It consists of a set of SQL statements that can be executed in a database context. Stored procedures can perform operations like data retrieval, insertion, updating, and deletion, making them powerful tools for managing data operations.

Advantages of Using Stored Procedures

Using stored procedures comes with several benefits:

  • Performance: Stored procedures are compiled and stored in the database, which means they can run faster than executing individual SQL queries.
  • Security: They help in minimizing SQL injection vulnerabilities by allowing parameterized queries.
  • Code Reusability: You can call the same stored procedure from different parts of your application without rewriting the SQL code.
  • Maintenance: It’s easier to manage changes in SQL logic at the database level rather than altering the application code.

Setting Up Your ASP.NET Core Application

Prerequisites

Before we proceed, ensure you have the following:

  • .NET Core SDK: Download the latest version of the .NET Core SDK.
  • Visual Studio or another IDE of your choice installed.
  • A SQL Server instance where you can create and manage your stored procedures.

Creating a Sample ASP.NET Core Project

  1. Open Visual Studio and create a new project.
  2. Choose ASP.NET Core Web Application.
  3. Select Web API as the project template and click Create.
  4. Name your project and choose the framework version. Click Create.

Setting Up Your Database

Create a Database

You can use SQL Server Management Studio (SSMS) to create a new database. Here’s a simple script to create a database called SampleDB:

CREATE DATABASE SampleDB;
GO
USE SampleDB;
GO

Creating a Table and Stored Procedure

After setting up your database, create a sample table and a stored procedure. Below is a script that creates a Products table and a stored procedure to retrieve products:

CREATE TABLE Products (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Price DECIMAL(18, 2)
);

CREATE PROCEDURE GetProducts
AS
BEGIN
    SELECT * FROM Products;
END;

Configuring Your ASP.NET Core Application

Once your database is set up, you need to configure your ASP.NET Core application to connect to the database.

Adding Connection String

Open the appsettings.json file and add your connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=SampleDB;Trusted_Connection=True;"
  }
}

Adding Entity Framework Core

To call stored procedures using Entity Framework Core, you need to install the necessary NuGet packages. Run the following commands in the Package Manager Console:

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

Creating Your DbContext

Create a new folder named Data and add a class named ApplicationDbContext.cs:

using Microsoft.EntityFrameworkCore;

namespace YourNamespace.Data
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions options) : base(options) { }

        public DbSet Products { get; set; }
    }
}

Creating a Product Model

In the same folder, create a model class called Product.cs:

namespace YourNamespace.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}

Registering the DbContext

Now, you need to register your DbContext in the Startup.cs file:

using YourNamespace.Data;
using Microsoft.EntityFrameworkCore;

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
        
    services.AddControllers();
}

Calling the Stored Procedure

Now that everything is set up, you can call the stored procedure from your controller.

Creating a Products Controller

Create a new folder named Controllers and add a new controller named ProductsController.cs:

using Microsoft.AspNetCore.Mvc;
using YourNamespace.Data;
using YourNamespace.Models;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace YourNamespace.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private readonly ApplicationDbContext _context;

        public ProductsController(ApplicationDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task>> GetProducts()
        {
            var products = await _context.Products.FromSqlRaw("EXEC GetProducts").ToListAsync();
            return Ok(products);
        }
    }
}

Testing Your API

With your controller set up, it's time to test the API:

  1. Run your application.
  2. Use Postman or any API testing tool and make a GET request to https://localhost:5001/api/products.

If everything is working as expected, you should receive a list of products from your database.

Important Notes

  • Error Handling: Ensure you handle exceptions that may arise from database calls. Wrap your database calls in try-catch blocks to return appropriate error messages.
  • Asynchronous Programming: Notice the use of async and await. This is important for keeping your application responsive, especially when dealing with I/O-bound operations such as database access.
  • Entity Framework Migrations: You might want to use migrations to create and manage your database schema easily.
Add-Migration InitialCreate
Update-Database

Conclusion

Calling stored procedures in ASP.NET Core applications allows for efficient data operations and improves overall application performance. By following this guide, you can easily implement stored procedure calls in your applications while leveraging the power of Entity Framework Core.

Using stored procedures offers a structured way to manage your database interactions, and by integrating them into your ASP.NET Core applications, you are setting a solid foundation for your web projects. Happy coding! 🎉

Featured Posts