In this article, we will learn how to perform CRUD operations in ASP.NET Core Web API without using Entity Framework.

I will provide a step-by-step tutorial for performing CRUD operations in ASP.NET Core Web API using ADO.NET database connection.

Recently, I have been working on a project where I needed to build a Web API using ASP.NET Core without Entity Framework, as my database already contained user-defined functions and stored procedures, and my project manager preferred not to use EF.

Despite searching extensively, I couldn't find any relevant articles covering this topic. Therefore, I decided to write an article on it. Let's begin step by step.

Points in the Article

  1. Creating Database table for performing the operation
  2. Adding Core Web API Project
  3. Install Nuget Packages in our project for ADO .NET
  4. Creating Model Classes for performing 
  5. Setup database connection and Creating an API Controller
  6. API Testing

Create SQL table for performing the Operation in the table

In this step, we are going to create the database table. I have created a table called "Teacher" for that purpose. This table includes columns such as Id, Teacher_Name, Teacher_Email, Teacher_ContactNo, Teacher_Address, and Teacher_Department.

Sql Script

CREATE TABLE [dbo].[Teacher](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Teacher_Name] [nvarchar](100) NULL,
	[Teacher_Email] [nvarchar](max) NOT NULL,
	[Teacher_ContactNo] [nvarchar](14) NULL,
	[Teacher_Department] [nvarchar](100) NOT NULL,
	[Teacher_Address] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK__Teacher__3214EC077B0B6A86] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Teacher] ON 
GO
INSERT [dbo].[Teacher] ([Id], [Teacher_Name], [Teacher_Email], [Teacher_ContactNo], [Teacher_Department], [Teacher_Address]) VALUES (1, N'John New', N'[email protected]', N'8287589645', N'IT', N'Noida 18')
GO
INSERT [dbo].[Teacher] ([Id], [Teacher_Name], [Teacher_Email], [Teacher_ContactNo], [Teacher_Department], [Teacher_Address]) VALUES (1002, N'John', N'[email protected]', N'8287589645', N'Noida 18', N'IT')
GO

Adding Core Web API Project

  1. Open Visual Studio(I’m using VS 2019)
  2. Click to >Create a new Project>Asp .Net Core Web Application

Creatiing Core Api Project

Creeating Core Project

Now Click on next button it will create the API Core Project for Us

6

Open NuGet package and Install Nuget Packages for ADO .NET Connection

Install System.Data.SqlClient

Data Provider for SQL Server Database has a list of classes used to access a SQL Server database. you can perform insert, update, delete, select, database changes etc operations in the database using those classes.

Creating Model Classes for the Teacher table

Right-click on the project and add a new folder “Model” in the project add TeacherModel.cs model class. This class represents the Teacher table in our database

  public class TeacherModel
    {
    public int Id { get; set; }
    public string Teacher_Name { get; set; }
    public string Teacher_Email { get; set; }
    public string Teacher_ContactNo { get; set; }
    public string Teacher_Department { get; set; }
    public string Teacher_Address { get; set; }
    }

Teacher

 

Setup database connection and Creating an API Controller

TeacherController.cs

TeacherController, is responsible for handling CRUD (Create, Read, Update, Delete) operations for a TeacherModel entity. Let's break down the code:

Constructor and Connection String:

The controller initializes a connection string constr for connecting to the database.

GET Methods:

  • GetAllTeacher(): Retrieves all teachers from the database table "Teacher" using a SELECT query. It maps the retrieved data to a list of TeacherModel objects and returns it.
  • GetTeacher(long id): Retrieves a specific teacher by their ID from the "Teacher" table using a SELECT query. It maps the retrieved data to a TeacherModel object and returns it.

PUT Method:

  • PutTeacher(long id, TeacherModel teacherModel): Updates an existing teacher record in the database. It first checks if the provided ID matches the ID of the teacher model. If valid, it executes an UPDATE query to modify the corresponding record in the "Teacher" table with the new values provided in the teacherModel. Returns a NoContent response upon successful update.

POST Method:

  • PostTeacher(TeacherModel teacherModel): Creates a new teacher record in the database. It first validates the model state. If valid, it executes an INSERT query to add a new record to the "Teacher" table with the values provided in the teacherModel. Returns an OK response upon successful insertion.

DELETE Method:

  • DeleteTeacher(long id): Deletes a specific teacher record from the database by their ID. It executes a DELETE query to remove the corresponding record from the "Teacher" table. Returns a NoContent response upon successful deletion.

This controller provides endpoints to perform CRUD operations on the "Teacher" entity using ADO.NET database connection.


using CrudWithOutEntity.Model;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace CrudWithOutEntity.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TeacherController : ControllerBase
    {
    string constr = "Data Source=SQLEXPRESS01;Initial Catalog=DotNetPeTips;User ID=sa;Password=sa@1234";
    // GET: api/Teacher
        [HttpGet]
    public async Task<ActionResult<IEnumerable<TeacherModel>>> GetAllTeacher()
        {
            List<TeacherModel> teachers = new List<TeacherModel>();
    string query = "SELECT * FROM Teacher";
    using (SqlConnection con = new SqlConnection(constr))
            {
    using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
    while (sdr.Read())
                        {
                            teachers.Add(new TeacherModel
                            {
                                Id = Convert.ToInt32(sdr["Id"]),
                                Teacher_Name = Convert.ToString(sdr["Teacher_Name"]),
                                Teacher_Email = Convert.ToString(sdr["Teacher_Email"]),
                                Teacher_ContactNo = Convert.ToString(sdr["Teacher_ContactNo"]),
                                Teacher_Address = Convert.ToString(sdr["Teacher_Address"]),
                                Teacher_Department = Convert.ToString(sdr["Teacher_Department"])
                            });
                        }
                    }
                    con.Close();
                }
            }

    return teachers;
        }

    // GET: api/Teacher/5
        [HttpGet("{id}")]
    public async Task<ActionResult<TeacherModel>> GetTeacher(long id)
        {

            TeacherModel teacherObj = new TeacherModel();
    string query = "SELECT * FROM Teacher where Id=" + id;
    using (SqlConnection con = new SqlConnection(constr))
            {
    using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
    while (sdr.Read())
                        {
                            teacherObj = new TeacherModel
                            {
                                Id = Convert.ToInt32(sdr["Id"]),
                                Teacher_Name = Convert.ToString(sdr["Teacher_Name"]),
                                Teacher_Email = Convert.ToString(sdr["Teacher_Email"]),
                                Teacher_ContactNo = Convert.ToString(sdr["Teacher_ContactNo"]),
                                Teacher_Address = Convert.ToString(sdr["Teacher_Address"]),
                                Teacher_Department = Convert.ToString(sdr["Teacher_Department"])
                            };
                        }
                    }
                    con.Close();
                }
            }
    if (teacherObj == null)
            {
    return NotFound();
            }
    return teacherObj;
        }
    // PUT: api/Teacher/5
        [HttpPut("{id}")]
    public async Task<IActionResult> PutTeacher(long id, TeacherModel teacherModel)
        {
    if (id != teacherModel.Id)
            {
    return BadRequest();
            }
            TeacherModel teacher = new TeacherModel();
    if (ModelState.IsValid)
            {
    string query = "UPDATE Teacher SET Teacher_Name = @Teacher_Name, Teacher_Email = @Teacher_Email," +
    "Teacher_ContactNo=@Teacher_ContactNo," +
    "Teacher_Address=@Teacher_Address,Teacher_Department=@Teacher_Department Where Id =@Id";
    using (SqlConnection con = new SqlConnection(constr))
                {
    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Teacher_Name", teacherModel.Teacher_Name);
                        cmd.Parameters.AddWithValue("@Teacher_Email", teacherModel.Teacher_Email);
                        cmd.Parameters.AddWithValue("@Teacher_ContactNo", teacherModel.Teacher_ContactNo);
                        cmd.Parameters.AddWithValue("@Teacher_Address", teacherModel.Teacher_Address);
                        cmd.Parameters.AddWithValue("@Teacher_Department", teacherModel.Teacher_Department);
                        cmd.Parameters.AddWithValue("@Id", teacherModel.Id);
                        con.Open();
    int i = cmd.ExecuteNonQuery();
    if(i>0)
                        {
    return NoContent();
                        }
                        con.Close();
                    }
                }
               
            }
    return BadRequest(ModelState);
        }

    // POST: api/Teacher
        [HttpPost]
    public async Task<ActionResult<TeacherModel>> PostTeacher(TeacherModel teacherModel)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }
    using (SqlConnection con = new SqlConnection(constr))
            {
    //inserting Patient data into database
    string query = "insert into Teacher values (@Teacher_Name, @Teacher_Email, @Teacher_ContactNo,@Teacher_Address,@Teacher_Department)";
    using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Teacher_Name", teacherModel.Teacher_Name);
                    cmd.Parameters.AddWithValue("@Teacher_Email", teacherModel.Teacher_Email);
                    cmd.Parameters.AddWithValue("@Teacher_ContactNo", teacherModel.Teacher_ContactNo);
                    cmd.Parameters.AddWithValue("@Teacher_Address", teacherModel.Teacher_Address);
                    cmd.Parameters.AddWithValue("@Teacher_Department", teacherModel.Teacher_Department);
                    con.Open();
    int i = cmd.ExecuteNonQuery();
    if (i > 0)
                    {
    return Ok();
                    }
                    con.Close();
                }
            }
    return BadRequest();

        }

    // DELETE: api/Teacher/5
        [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteTeacher(long id)
        {

    using (SqlConnection con = new SqlConnection(constr))
            {
    string query = "Delete FROM Teacher where Id='" + id + "'";
    using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    con.Open();
    int i = cmd.ExecuteNonQuery();
    if (i > 0)
                    {
    return NoContent();
                    }
                    con.Close();
                }
            }
    return BadRequest();
        }

    }
}

API Testing

1. Create Customer Object in database

POST–api/Teacher
The PostTeacher action method will handle HTTP POST requests and make an entry in the database Teacher table.

// POST: api/Teacher
        [HttpPost]
    public async Task<ActionResult<TeacherModel>> PostTeacher(TeacherModel teacherModel)
        {
    if (!ModelState.IsValid)
            {
    return BadRequest(ModelState);
            }
    using (SqlConnection con = new SqlConnection(constr))
            {
    //inserting Patient data into database
    string query = "insert into Teacher values (@Teacher_Name, @Teacher_Email, @Teacher_ContactNo,@Teacher_Address,@Teacher_Department)";
    using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Teacher_Name", teacherModel.Teacher_Name);
                    cmd.Parameters.AddWithValue("@Teacher_Email", teacherModel.Teacher_Email);
                    cmd.Parameters.AddWithValue("@Teacher_ContactNo", teacherModel.Teacher_ContactNo);
                    cmd.Parameters.AddWithValue("@Teacher_Address", teacherModel.Teacher_Address);
                    cmd.Parameters.AddWithValue("@Teacher_Department", teacherModel.Teacher_Department);
                    con.Open();
    int i = cmd.ExecuteNonQuery();
    if (i > 0)
                    {
    return Ok();
                    }
                    con.Close();
                }
            }
    return BadRequest();

        }

In the PostTeacher method, we first validating the model using ModelState.IsValid and make sure that the TeacherModel object includes all the required information.

If this is not true then Api Return BadRequest response. If the TeacherModel model i.e data is valid then insert data in the table and return the Ok status response.

  • It establishes a connection to the database using a SqlConnection object initialized with the connection string constr.
  • It constructs an SQL query to insert a new record into the "Teacher" table, providing parameterized values for the columns Teacher_Name, Teacher_Email, Teacher_ContactNo, Teacher_Address, and Teacher_Department.
  • It creates a SqlCommand object with the SQL query and adds parameters to it using the Parameters.AddWithValue method.
  • It opens the database connection and executes the SQL command using ExecuteNonQuery(), which returns the number of rows affected by the query.
  • If the number of rows affected is greater than zero (indicating successful insertion), it returns an OK response.Otherwise, it returns a BadRequest response.

This endpoint inserts a new teacher record into the database table "Teacher" and returns an appropriate HTTP response based on the success of the operation.

Request body

{  
    "teacher_Name": "John",
    "teacher_Email": "[email protected]",
    "teacher_ContactNo": "8287589645",
    "teacher_Department": "IT",
    "teacher_Address": "Noida 18"
}
  • Let’s do test on swagger, Click Post /api/Teacher tab,then click on Try it Out Button
  • Input Body parameter for adding teacher and click on the excute button
  • It will insert a record in the database

Post Request

2. Get All Teacher

GET: api/Teacher

The following GetAllTeacher() action method in TeacherController class returns all the Teacher from the database using ADO .Net.

  • It initializes an empty list called teachers to store instances of TeacherModel.
  • Constructs an SQL query to select all records from the "Teacher" table.
  • Establishes a connection to the database using a SqlConnection object initialized with the connection string constr.
  • Creates a SqlCommand object with the SQL query and sets its connection to the previously established connection.
  • Opens the database connection.Executes the SQL command using ExecuteReader() and obtains the result set as a SqlDataReader object.
  • Iterates through the result set using a while loop and adds each record's data to the teachers list by creating a new TeacherModel instance for each record.
  • Closes the database connection.Finally, returns the list of TeacherModel instances containing the data retrieved from the database.

 // GET: api/Teacher
        [HttpGet]
    public async Task<ActionResult<IEnumerable<TeacherModel>>> GetAllTeacher()
        {
            List<TeacherModel> teachers = new List<TeacherModel>();
    string query = "SELECT * FROM Teacher";
    using (SqlConnection con = new SqlConnection(constr))
            {
    using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
    while (sdr.Read())
                        {
                            teachers.Add(new TeacherModel
                            {
                                Id = Convert.ToInt32(sdr["Id"]),
                                Teacher_Name = Convert.ToString(sdr["Teacher_Name"]),
                                Teacher_Email = Convert.ToString(sdr["Teacher_Email"]),
                                Teacher_ContactNo = Convert.ToString(sdr["Teacher_ContactNo"]),
                                Teacher_Address = Convert.ToString(sdr["Teacher_Address"]),
                                Teacher_Department = Convert.ToString(sdr["Teacher_Department"])
                            });
                        }
                    }
                    con.Close();
                }
            }

    return teachers;
        }
  • Let’s do test on swagger, Click Get /api/Teacher tab,then click on Try it Out Button
  • Click on the excute button in the swagger

GetAllTeacher

It will return all teacher records in the Teacher table.

Response body

[
  {
    "id": 1,
    "teacher_Name": "John New",
    "teacher_Email": "[email protected]",
    "teacher_ContactNo": "8287589645",
    "teacher_Department": "IT",
    "teacher_Address": "Noida 18"
  },
  {
    "id": 1002,
    "teacher_Name": "John",
    "teacher_Email": "[email protected]",
    "teacher_ContactNo": "8287589645",
    "teacher_Department": "Noida 18",
    "teacher_Address": "IT"
  }
]

3. Get Teacher by Id

GET: api/Teacher/1
It will return all Teacher with id=1 in the database

As you can see in the below code, GetTeacher() method returns Teacher by Id using EF. If no Teacher exists in the database table then it will return 404 NotFound responses otherwise it will return 200 OK responses with Teacher data.

// GET: api/Teacher/5
        [HttpGet("{id}")]
    public async Task<ActionResult<TeacherModel>> GetTeacher(long id)
        {

            TeacherModel teacherObj = new TeacherModel();
    string query = "SELECT * FROM Teacher where Id=" + id;
    using (SqlConnection con = new SqlConnection(constr))
            {
    using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
    while (sdr.Read())
                        {
                            teacherObj = new TeacherModel
                            {
                                Id = Convert.ToInt32(sdr["Id"]),
                                Teacher_Name = Convert.ToString(sdr["Teacher_Name"]),
                                Teacher_Email = Convert.ToString(sdr["Teacher_Email"]),
                                Teacher_ContactNo = Convert.ToString(sdr["Teacher_ContactNo"]),
                                Teacher_Address = Convert.ToString(sdr["Teacher_Address"]),
                                Teacher_Department = Convert.ToString(sdr["Teacher_Department"])
                            };
                        }
                    }
                    con.Close();
                }
            }
    if (teacherObj == null)
            {
    return NotFound();
            }
    return teacherObj;
        }
  • Let’s do test on swagger, Click Get /api/Teacher/{id} tab,then click on Try it Out Button
  • Enter Teacher Id for find record,Click on the excute button

GetTeacherById

Return the database record with Id=1

Response body

{
    "id": 1,
    "teacher_Name": "John New",
    "teacher_Email": "[email protected]",
    "teacher_ContactNo": "8287589645",
    "teacher_Department": "IT",
    "teacher_Address": "Noida 18"
}

4. PUT–/api/Teacher/1

This Put is used to update the Teacher table in the database, You just need to pass the TeacherModel object in the request body, and in response, you will able to get an updated Teacher record.
PutTeacher() action method in our TeacherController is used to update an existing Teacher record in the database using ADO .NET.

This PutTeacher method handles HTTP PUT requests to update an existing teacher record in the database.

  • It first checks if the provided id parameter matches the Id property of the teacherModel object. If not, it returns a BadRequest response.
  • It initializes a new instance of TeacherModel named teacher.If the model state is valid, it constructs an SQL query to update the corresponding record in the "Teacher" table with the new values provided in the teacherModel.
  • It establishes a connection to the database using a SqlConnection object initialized with the connection string constr.
  • It creates a SqlCommand object with the SQL query and adds parameters to it using the Parameters.AddWithValue method.It opens the database connection and executes the SQL command using ExecuteNonQuery(), which returns the number of rows affected by the query.
  • If the number of rows affected is greater than zero (indicating successful update), it returns a NoContent response.Otherwise, it returns a BadRequest response along with the ModelState errors.
  • In summary, this method updates an existing teacher record in the database based on the provided ID and the data in the teacherModel object, and returns an appropriate HTTP response based on the success of the operation.
 // PUT: api/Teacher/5
        [HttpPut("{id}")]
    public async Task<IActionResult> PutTeacher(long id, TeacherModel teacherModel)
        {
    if (id != teacherModel.Id)
            {
    return BadRequest();
            }
            TeacherModel teacher = new TeacherModel();
    if (ModelState.IsValid)
            {
    string query = "UPDATE Teacher SET Teacher_Name = @Teacher_Name, Teacher_Email = @Teacher_Email," +
    "Teacher_ContactNo=@Teacher_ContactNo," +
    "Teacher_Address=@Teacher_Address,Teacher_Department=@Teacher_Department Where Id =@Id";
    using (SqlConnection con = new SqlConnection(constr))
                {
    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@Teacher_Name", teacherModel.Teacher_Name);
                        cmd.Parameters.AddWithValue("@Teacher_Email", teacherModel.Teacher_Email);
                        cmd.Parameters.AddWithValue("@Teacher_ContactNo", teacherModel.Teacher_ContactNo);
                        cmd.Parameters.AddWithValue("@Teacher_Address", teacherModel.Teacher_Address);
                        cmd.Parameters.AddWithValue("@Teacher_Department", teacherModel.Teacher_Department);
                        cmd.Parameters.AddWithValue("@Id", teacherModel.Id);
                        con.Open();
    int i = cmd.ExecuteNonQuery();
    if(i>0)
                        {
    return NoContent();
                        }
                        con.Close();
                    }
                }
               
            }
    return BadRequest(ModelState);
        }
  • Let’s do test on swagger, Click PUT ​/api​/Teacher​/{id} tab,then click on Try it Out Button
  • Enter Teacher Id for which you want to update the record,and let’s try to update the database record with Id=1
  • Input updated Teacher value,Click on the excute button,
  • It will update Teacher record with Id=1

Put Api

Request body

{
    "id": 1,
    "teacher_Name": "John New",
    "teacher_Email": "[email protected]",
    "teacher_ContactNo": "8287589645",
    "teacher_Department": "IT",
    "teacher_Address": "Noida 18"
}

5. DELETE Teacher by Id

DELETE:/api/Teacher/1
It will delete the Teacher with id=1 in the database

DeleteTeacher() action method in our TeacherController use to delete an existing Teacher  record in the database without Entity Framework.

// DELETE: api/Teacher/5
        [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteTeacher(long id)
        {

    using (SqlConnection con = new SqlConnection(constr))
            {
    string query = "Delete FROM Teacher where Id='" + id + "'";
    using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    con.Open();
    int i = cmd.ExecuteNonQuery();
    if (i > 0)
                    {
    return NoContent();
                    }
                    con.Close();
                }
            }
    return BadRequest();
        }
  • Let’s do test on swagger, Click DELETE /api/Teacher/{id} tab,then click on Try it Out Button
  • Enter Teacher Id for deleting record,Click on the excute button

Delete Record

So, in this post, we have seen how to perform creating ASP.NET Core Web API without using Entity Framework , if you have any queries or doubts, please comment, will reply.

Read Similar Articles