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
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; } }
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
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
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
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
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
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.
Read More Articles
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- Entity Framework / ASP net core 2.0 - add migration working for some context but not for others
- ASP NET Core Web API disable TLS 1.0 and TLS 1.1 support
- How do I implement data validation attributes on a list of derived types in Asp Net Core 3.1 Web API
- ASP.NET Core Web API with Entity Framework Core default scaffolded controller confuses put and post?
- How to create model without using Entity Framework in C#?
- ASP NET MVC Core 2 Roles without database
- Entity Framework Core 1.0 PostgreSQL API
- CORS not working with ASP NET 5 Web Api
- Minimal Web API and seeding an in-memory Entity Framework database
- ASP.NET Core - Add Controller - API Controller with actions, using Entity Framework - Throws error and Downgrades NuGet EntityFrameworkCore.SqlServer
- HttpClient SendAsync from ASP .NET Core blocking calls down stack in Web API 2/Delphi
- Error in command update-migration, Entity Framework 3.15, Net Core 3.1
- How to store token in Asp Net Core Web App (not WebAPI)
- Unable to reference app.config in .NET Framework library referenced by a .NET Core Web API
- Entity Framework Core use take on include without loading all data
- How can I map my entity to a table in .Net Core project without using Entity Framework and only using ADO.NET
- Entity Framework Core create code-first base from xsd file
- How to create and use docker volumes on Asp core Web app (MVC)
- How to setup angular 7 to communicate with net core web api in VS 2017
- Entity Framework - get related items without explicit reference using Fluent API configurations
- C# Ninject Web API async query in IAuthenticationFilter causes multiple operations error in entity framework
- Dynamic Entity Framework on Web API
- Model Class not showing when creating controller using Web API 2 Controller with actions using Entity Framework Database First
- Entity Framework Cascade Delete using Fluent API Configuration asp mvc 4
- Entity Framework Core Owned Type Value Object throws required Primary Key to be defined error when used with IdentityUser. Why?
- Facebook JWT authentication using ASP.NET Core Web API
- Error on try to post file from Angular 6 to ASP.NET Core Web Api
- Create Custom Return Error Validate in Asp Core 2.2 in WebApi
- How to parameterize concatenated string queries in Entity Framework Core
- .net core Web API - 415 Unsupported Media Type
- ASP.NET Core: Finding a View - works in IIS Express debug but not in Kestrel 'published'
- UTC time represented when the offset is applied must be between year 0 and 10,000 error
- Adding Witness functionality Calling DocuSign
- C# or VB in Blogengine.net page
- Error while creating an ASP.NET project
- How to count XML tag with specific value to produce a summary C#; ASP.NET
- Populate object from text
- How to bind Endpoint to RoutingKey while using ConfigureConsumer in MassTransit
- How to write unit test for Memory Stream in c#?
- Dangling event handlers and memory leaks: which way around?
- Error executing TFS command
- How can I run an exe within a form?
- Unable to Publish WCF Service with Unsafe code in Visual Studio 2013
- SSIS Variables Going Out of Scope
- Audio stuttering while scrolling (C# WebBrowser)
- Unable to show steps in Wizard control
- Make Chart AxisX Labels Display Days Starting from Monday
- Changing a ToolTip's cursor
- How to disable the Textbox's text encoding before saving it into the data base?
- Make a <li> invisible in asp.net