In today’s article, we will see how to perform simple CRUD operations in ASP.NET Core Web API using ADO.NET.
Why do we need this?
This article is for those who want to combine ADO.NET with Web APIs in ASP.NET Core. If you want to implement your logic with Entity Framework, that is totally fine.
So now, the answer to why we need this. As we all know ADO.NET approach is better than EF in terms of performance, in a lot of real-world scenarios, we might need to design our application in such a way that we can maximize the performance. Hence instead of EF, we would prefer ADO.NET.
Let’s get started.
Here is the DB Structure and Stored procedures (Microsoft SQL Server).
Table structure of Users

GetUsers Stored Procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- EXEC GetUsers
-- =============================================
ALTER PROCEDURE [dbo].[GetUsers]
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Users(NOLOCK) ORDER BY Id ASC
END
SaveUser Stored Procedure (Add/Edit)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SaveUser]
(
@Id INT,
@Name NVARCHAR(MAX),
@EmailId NVARCHAR(MAX),
@Mobile NVARCHAR(20),
@Address NVARCHAR(MAX),
@ReturnCode NVARCHAR(20) OUTPUT
)
AS
BEGIN
SET @ReturnCode = 'C200'
IF(@Id <> 0)
BEGIN
IF EXISTS (SELECT 1 FROM Users WHERE EmailId = @EmailId AND Id <> @Id)
BEGIN
SET @ReturnCode = 'C201'
RETURN
END
IF EXISTS (SELECT 1 FROM Users WHERE Mobile = @Mobile AND Id <> @Id)
BEGIN
SET @ReturnCode = 'C202'
RETURN
END
UPDATE Users SET
Name = @Name,
EmailId = @EmailId,
Mobile = @Mobile,
Address = @Address,
IsActive = 1
WHERE Id = @Id
SET @ReturnCode = 'C200'
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM Users WHERE EmailId = @EmailId)
BEGIN
SET @ReturnCode = 'C201'
RETURN
END
IF EXISTS (SELECT 1 FROM Users WHERE Mobile = @Mobile)
BEGIN
SET @ReturnCode = 'C202'
RETURN
END
INSERT INTO Users (Name,EmailId,Mobile,Address,IsActive)
VALUES (@Name,@EmailId,@Mobile,@Address,1)
SET @ReturnCode = 'C200'
END
END
Delete User Stored Procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[DeleteUser]
(
@Id INT,
@ReturnCode NVARCHAR(20) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
SET @ReturnCode = 'C200'
IF NOT EXISTS (SELECT 1 FROM Users WHERE Id = @Id)
BEGIN
SET @ReturnCode ='C203'
RETURN
END
ELSE
BEGIN
DELETE FROM Users WHERE Id = @Id
SET @ReturnCode = 'C200'
RETURN
END
END
Now, we will create a project.
We will select ASP.NET Core Web Application which will be under Web section.

Then, we will select Web API as our template.

Now, once the project is created, we will create a User model class that will be similar to our user table in our database. Our table structure is as below.

Our Model class is,

In the model class, we have to serialize this class in order for it to be usable for the consumers of this model. Hence, we will add DataContract attribute on the class and DataMember attribute on each property. Both of these attributes are available in System.Runtime.Serialization namespace.
I have added Name property to the DataMember attribute. This is because this name will be the key in the JSON returned wherever this model is used.
This practice helps in hiding our actual property names to the outside exposure. The major point is if in future you have decided to rename your properties, then you can do that but you don’t have to change the name property value and hence consumer of this model won’t notice any difference but if you rename your property without the name attribute then the consumer of this model will have to modify his/her code also.
Our UserModel will look something like this.
[DataContract]
public class UsersModel
{
[DataMember(Name = "Id")]
public int Id { get; set; }
[DataMember(Name = "Name")]
public string Name { get; set; }
[DataMember(Name = "EmailId")]
public string EmailId { get; set; }
[DataMember(Name = "Mobile")]
public string Mobile { get; set; }
[DataMember(Name = "Address")]
public string Address { get; set; }
[DataMember(Name = "IsActive")]
public bool IsActive { get; set; }
}
While we are creating models we will create a message model, we will use this model to return a standard form return type for our application. Our message model will be a generic class so that we can pass any type of data we want, it will have a property of isSuccess to tell the consumer that the response is success or not and it will contain a string message property.
Our Message class will look like this.
[DataContract]
public class Message<T>
{
[DataMember(Name = "IsSuccess")]
public bool IsSuccess { get; set; }
[DataMember(Name = "ReturnMessage")]
public string ReturnMessage { get; set; }
[DataMember(Name = "Data")]
public T Data { get; set; }
}
We will also create another model in order to fetch our connection string from appsettings.json.
- In order to know how to ready values from appsettings.json click here!

Class highlighted in yellow is the model for appsettings.json.
Now, let me explain the architecture that we will follow from now on in order to contact database.
First, we will create SqlHelper class.
In this class, there will be several methods that we will need throughout the application such as a method to execute procedures and return a string value, a method that will return the data(object/list of an object) according to our need and some methods to get the column values from the SqlDataReader.
Let’s see this class in action,

As you can see I have created a folder called utility and in that, I have created my helper class.
Now, if you see the method, it takes connection string, procedure name and the array of SqlPrameters and returns the scalar string that will be given by execution of the stored procedure.
Now, we will see the method that will return the actual data to us.

As you can see this method is generic and takes a type and returns the same type. Here all the inputs are same but there is one more parameter and that is Func<SqlDataReader,TData> translator.
What this input will do is it will take the reader as input and returns the class type which we are expecting. I will show you how to create a translator for your particular class.
But now, we will see some more methods in SqlHelper class.

Here, you can see 4 methods with pretty much self-explanatory code but I will explain it further.
First three methods are taking reader and column name as input, getting value from data reader and returning its null/default value or the original value depending upon the condition. In this application, I only needed string, int, bool values. You can create more methods like these according to your needs such as for DateTime, decimal, long etc.
And the last method is IsColumnExists. So this method will be used to check whether the column exists in a dataset or not.
So, we are done with SqlHelper class. I hope all is clear to you and if it’s not, just be patient; it will be clear once we use this in our application.
So here is our entire class.















Atif AhmedPosted Aug 24, 2023, 5:42 AM
What if we wont follow lazy loading and use method directly like this UserDbClient obj = new UserDbClient(); var data2 = obj.GetAllUsers(appSettings.Value.DbConn);
Juan CabreraPosted Dec 7, 2021, 8:05 PM
Hello, how do you deal with situations where there are multiple result sets inside a stored procedure. I noticed that the following block still needs implementation to support multiple result sets, but any help would be appreciated: while(reader.NextResult()) { }
Sagar JaybhayPosted Aug 11, 2021, 3:17 PM
Excellent very brief explanation
Share KnowledgePosted Sep 3, 2020, 6:25 AM
Excellent way of briefing...
Rameez JavedPosted Jun 29, 2020, 6:29 AM
Awesome, I was looking about this. It would be lovable if anyone can implement this is EF too.
IMAD AYOUBPosted Jun 13, 2020, 3:50 AM
Excellent Tutorial. Thanks a lot.
Pham van DongPosted May 11, 2020, 3:14 AM
Great, Thanks so much
Yogesh KhurpePosted Apr 27, 2020, 3:27 AM
Nice Explanation Ravi..!!
Ankur ShahPosted Oct 29, 2019, 5:17 AM
Great Work Dude
santhosh samPosted Sep 17, 2019, 12:39 AM
Can i have architecture pattern for this api
Chittaranjan SwainPosted Sep 12, 2019, 6:43 AM
Nice article, thanks for sharing.
dipesh guptaPosted Feb 16, 2019, 8:04 AM
Very Nice Article Ravi , what is performance impact if we uses dataset & poulate the list from userlist from dataset ?
Muhammad HashirPosted Dec 19, 2018, 7:40 AM
Very good article. I found something here in the controller code: if (data == "C200") { msg.IsSuccess = true; if (model.id == 0) msg.ReturnMessage = "user saved successfully"; else msg.ReturnMessage = "user updated successfully"; } It will always show "user updated successfully" message unless id = 0. is there something you could do, if the Id is different for a new insert and it should a message "user saved successfully" instead of "user updated successfully" message.
Bhavesh JadavPosted Nov 29, 2018, 6:19 AM
Super article for crud operation in asp.net core web API, It helps to me. I made a demo from it, thanks to sharing it, Ravi. Great work.
Fabio DimasiPosted Sep 12, 2018, 8:27 AM
Great Article!
Farhan AhmedPosted Jul 24, 2018, 12:50 AM
Nai ine .........
Catcher WongPosted Jul 2, 2018, 7:28 PM
Nice article! And you can use Dapper to simplify.
Anshya GuptaPosted Jun 30, 2018, 4:34 AM
Nice article. Thank you so much.
Suraj KumarPosted Jun 29, 2018, 11:46 PM
This is a good article for freshers and want to learn ASP.Net Core.