ASP.NET Core | How to Call Stored Procedure in Web API without Entity Framework

In the .NET Framework, there are various methods to connect the database without using Entity Framework. In this tutorial, we will learn How to Call Stored Procedures in WEB API without Entity Framework using ASP.NET Core Web API.

Creating ASP.NET Core Web API without using Entity Framework, is an alternative data access to optimize API for speed and scalability.

Call Stored Procedure in WEB API without Entity Framework

In this tutorial, we will explore two different methods, ADO.Net and Dapper for calling stored procedures.

Create Asp.Net Core Web API

• Open Visual Studio or VS Code
• Click on “Create a new Project”
• Select “ASP.NET Core Web  API”
• Click Next”
• Enter a project name and choose a location.
• Select target framework
• Click Create

Install the NuGet Package

• Dapper
• Microsoft.Data.SqlClient

Database Connection String  

Configure the Database Connection String in the appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "connectionstr": "Server=localhost;Database=Northwind;Encrypt=False;Trusted_Connection=True;"
  },
  "AllowedHosts": "*"
}

Create Models

Create a model class named Employees.cs

namespace WEB_API_without_EF.Models
{
    public class Employees
    {
        public int ID { get; set; }
        public string Company { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string JobTitle { get; set; }
        public string Address { get; set;}
 
    }
}

Create the Services

Create the class named AppDbcontext.cs.

using System.Data;
using Microsoft.Data.SqlClient;
using Dapper;

namespace WEB_API_without_EF.Data
{
    public class AppDbContext
    {
        private readonly IConfiguration _configuration;
        public AppDbContext(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public IDbConnection Createconnection()
        {

            return new SqlConnection(_configuration.GetConnectionString("connectionstr"));

        }

        public SqlConnection sqlconnection()
        {
            SqlConnection connection = new SqlConnection(_configuration.GetConnectionString("connectionstr"));
            return connection;
        }
 
        public async Task Init()
        {
            var conn = Createconnection();

            await _initcustomer();

            async Task _initcustomer()
            {
                var sqlstr = "CREATE TABLE IF NOT EXISTS Employees(ID INTEGER NOT NULL PRIMARY KEY,FirstName TEXT,LastName TEXT,Address TEXT,Email TEXT,Company TEXT);";

                await conn.ExecuteAsync(sqlstr);

            }
        }

    }
}

Create Controller

In the controller, you can define API endpoints.

using Dapper;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using WEB_API_without_EF.Data;
using WEB_API_without_EF.Models;

namespace WEB_API_without_EF.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {
        private readonly AppDbContext _context;

        public EmployeeController(AppDbContext context)
        {
            _context = context;
        }

        // Using Dapper

        [HttpGet("GetAllusingDapper")]
        public async Task<IEnumerable<Employees>> GetAlldapper()
        {
            using var connection = _context.Createconnection();
            var sqlstr = "Exec GetEmployee";

            return await connection.QueryAsync<Employees>(sqlstr);

        }

        // Using ADO.Net
        [HttpGet("GetAllusingAdo")]
        public  List<Employees> GetAllAdo()
        {
            using var connection = _context.sqlconnection();
            var sqlQ = "Exec GetEmployee";

            List<Employees> employees = new List<Employees>();            

           using (SqlCommand cmd = new SqlCommand(sqlQ, connection))
            {
                connection.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while(sdr.Read())
                    {
                        employees.Add(new Employees
                        {
                            ID = int.Parse(sdr["Id"].ToString()),
                            FirstName = sdr["FirstName"].ToString(),
                            LastName = sdr["LastName"].ToString(),
                            Email = sdr["Email"].ToString(),
                            Company = sdr["Company"].ToString(),
                            Address = sdr["Address"].ToString(),
                            JobTitle = sdr["JobTitle"].ToString(),
                        });
                    }
                }
                connection.Close();
                    
                }


            return employees;
        }


    }
}

Configure Program.cs Class

using WEB_API_without_EF.Data;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddSingleton<AppDbContext>();
builder.Services.AddControllers();



var app = builder.Build();

// Configure the HTTP request pipeline.

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Create Stored Procedure in SQL Server

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[GetEmployee]    Script Date: 9/26/2023 1:09:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetEmployee]
	 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT  * from Employees;
END

Test Web API

Using the tool Postman for testing API Endpoints.

ADO.Net

Dapper

See More: How to Implement JWT Authentication in Asp.Net Core Web API