Create ASP.NET Core Web API Without Entity Framework

In this article, we will learn how to perform the crud operations in the Asp.net core web API without the entity framework.

In this article, I will explain to you a step-by-step tutorial for performing CRUD operations in ASP.NET Core Web API using ADO .NET database connection.

Recently I’m working on a project in which I need to build a Web API using ASP.NET Core without Entity Framework from the existing database. That database has some user define the function and stored procedures and my project manager do not want to use EF.

I searched this topic and but can’t find any relevant article which covers this topic. so that I decided to write an article on that. so let’s start step by step.

Points in the Article

Creating Database table for performing the operation
Adding Core Web API Project
Install Nuget Packages in our project for ADO .NET
Creating Model Classes for performing
Setup database connection and Creating an API Controller
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.
which is got, the Id, Teacher_Name, Teacher_Email, Teacher_ContactNo ,Teacher_Address and Teacher_Department columns.

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'John@gmail.com', 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'John@gmail.com', N'8287589645', N'Noida 18', N'IT')
GO

Adding Core Web API Project

Open Visual Studio(I’m using VS 2019)
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

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

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.

Request body

{  
  "teacher_Name": "John",
  "teacher_Email": "John@gmail.com",
  "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.

 // 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": "John@gmail.com",
    "teacher_ContactNo": "8287589645",
    "teacher_Department": "IT",
    "teacher_Address": "Noida 18"
  },
  {
    "id": 1002,
    "teacher_Name": "John",
    "teacher_Email": "John@gmail.com",
    "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": "John@gmail.com",
  "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.

 // 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": "John@gmail.com",
  "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.

The post Create ASP.NET Core Web API Without Entity Framework appeared first on Software Development | Programming Tutorials.

Ashok Patel

I'm a software engineer, having good experience in software programming web designing with great command on ASP.NET, React JS, Angular JS,.NET Core HTML5, JavaScript, T-SQL, JQuery. Also have great experience in Electronics and electrical engineers design.

Create ASP.NET Core Web API Without Entity Framework

Create SQL table for performing the Operation in the table

Adding Core Web API Project

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

Creating Model Classes for the Teacher table

Setup database connection and Creating an API Controller

API Testing

1. Create Customer Object in database

Request body

2. Get All Teacher

3. Get Teacher by Id

Request body

5. DELETE Teacher by Id

Read More Articles

Ashok Patel