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
![ASP.NET Core]()
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.
![ASP.NET Core]()
Then, we will select Web API as our template.
![ASP.NET Core]()
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.
![ASP.NET Core]()
Our Model class is,
![ASP.NET Core]()
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!
![ASP.NET Core]()
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,
![ASP.NET Core]()
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.
![ASP.NET Core]()
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.
![ASP.NET Core]()
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.
public static class SqlHelper
{
public static string ExecuteProcedureReturnString(string connString, string procName,
params SqlParameter[] paramters)
{
string result = "";
using (var sqlConnection = new SqlConnection(connString))
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = procName;
if (paramters != null)
{
command.Parameters.AddRange(paramters);
}
sqlConnection.Open();
var ret = command.ExecuteScalar();
if (ret != null)
result = Convert.ToString(ret);
}
}
return result;
}
public static TData ExtecuteProcedureReturnData<TData>(string connString,string procName,
Func<SqlDataReader,TData> translator,
params SqlParameter[] parameters)
{
using (var sqlConnection = new SqlConnection(connString))
{
using (var sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.CommandText = procName;
if(parameters !=null)
{
sqlCommand.Parameters.AddRange(parameters);
}
sqlConnection.Open();
using (var reader = sqlCommand.ExecuteReader())
{
TData elements;
try
{
elements = translator(reader);
}
finally
{
while(reader.NextResult())
{ }
}
return elements;
}
}
}
}
///Methods to get values of
///individual columns from sql data reader
#region Get Values from Sql Data Reader
public static string GetNullableString(SqlDataReader reader ,string colName)
{
return reader.IsDBNull(reader.GetOrdinal(colName)) ? null : Convert.ToString(reader[colName]);
}
public static int GetNullableInt32(SqlDataReader reader, string colName)
{
return reader.IsDBNull(reader.GetOrdinal(colName)) ? 0 : Convert.ToInt32(reader[colName]);
}
public static bool GetBoolean(SqlDataReader reader, string colName)
{
return reader.IsDBNull(reader.GetOrdinal(colName)) ? default(bool) : Convert.ToBoolean(reader[colName]);
}
//this method is to check wheater column exists or not in data reader
public static bool IsColumnExists(this System.Data.IDataRecord dr,string colName)
{
try
{
return (dr.GetOrdinal(colName) >= 0);
}
catch (Exception)
{
return false;
}
}
#endregion
}
Now, we will create a translator in order to get the records from data reader.
![ASP.NET Core]()
As you can see we have created a folder called translators just to organize our code a bit better.
Now if you can see in our class, we have a static method that returns UsersModel and takes the reader and isList Boolean as a parameter and you can see it uses our SqlHelper methods to get the values from the reader.
We will also create another method that will return the List<UsersModel>,
![ASP.NET Core]()
Here you can see that we have called the first method inside while and passed isList parameter as true.
Here is our complete translator class,
public static class UserTranslator
{
public static UsersModel TranslateAsUser(this SqlDataReader reader,bool isList = false)
{
if(!isList)
{
if (!reader.HasRows)
return null;
reader.Read();
}
var item = new UsersModel();
if (reader.IsColumnExists("Id"))
item.Id = SqlHelper.GetNullableInt32(reader, "Id");
if (reader.IsColumnExists("Name"))
item.Name = SqlHelper.GetNullableString(reader, "Name");
if (reader.IsColumnExists("EmailId"))
item.EmailId = SqlHelper.GetNullableString(reader, "EmailId");
if (reader.IsColumnExists("Address"))
item.Address = SqlHelper.GetNullableString(reader, "Address");
if (reader.IsColumnExists("Mobile"))
item.Mobile = SqlHelper.GetNullableString(reader, "Mobile");
if (reader.IsColumnExists("IsActive"))
item.IsActive = SqlHelper.GetBoolean(reader, "IsActive");
return item;
}
public static List<UsersModel> TranslateAsUsersList(this SqlDataReader reader)
{
var list = new List<UsersModel>();
while(reader.Read())
{
list.Add(TranslateAsUser(reader, true));
}
return list;
}
}
So now next step is to create a DbClient so that it can contact database and gives us the result. Now in a large application, it is recommended to use different clients for different modules so that Separation of Concern is followed.
So here we will create a UserDbClient,
![ASP.NET Core]()
As you can see, I have created a Repository folder and in that I have put my UserDbClient class just to organize my code.
Now the method GetAllUsers takes connectionString as an input parameter and calls SqlHelper’s method ExecuteProcedureReturnData and we have given it a return type which is List<UsersModel> and passed inputs which are connectionstring , stored procedure’s name which is GetUsers and the last parameter is translator.
We pass the translator as lambda expression and since we expect list of users model, so we have given the method of translator which returns the list. In order to use this, we need to add a using statement which is where our translator class resides.
using CoreApiAdoDemo.Translators;
Now we will call this method from our API controller. In order to call this, we will use a factory client with lazy loading (since we are focusing on performance by using ADO.NET over Entity framework hence lazy loading implementation is quite good to implement)
So in utility we add another class and that class will look something like this,
public class DbClientFactory<T>
{
private static Lazy<T> _factoryLazy = new Lazy<T>(
() => (T)Activator.CreateInstance(typeof(T)),
LazyThreadSafetyMode.ExecutionAndPublication);
public static T Instance
{
get
{
return _factoryLazy.Value;
}
}
}
This class will take the client type and return its instance.
So now let’s call our GetAllUsers method in db client from Api Controller using ClientFactory class.
![ASP.NET Core]()
The part in the yellow section is to get the connection string from appsetting.json.
And as you can see the part in red is how we call our UserDbClient through ClientFactory class.
Now let’s test this. You can see that url is localhost:portnumber/api/User which was defined at the top of controller and the Http protocol (HttpGet, HttpPost , HttpDelete etc)
![ASP.NET Core]()
I have used Postman client, you can use any other software that you like. Here you can see the results from database.
Now let’s add a record.
![ASP.NET Core]()
So this is our save user method. As you can see from the Db structure, Stored procedure has these inputs with an output parameter and this Stored procedure will give us value in output parameter on the basis of our input.
According to the stored procedure, both insert and update are handled by it so we are performing two actions at once.
Now let’s call it from our controller.
![ASP.NET Core]()
As you can see in our method, based on the value return by stored procedure, we have created some messages and returned to the user by using our Message class which we created earlier.
Now let’s test this.
![ASP.NET Core]()
Now based on this you can easily create a HttpDelete methods so I will show you something different.
Here you can see that if you have two HttpPost methods in your class with the same signature then you might face some issues because we are not giving our method’s name in the URL so I’m going to create a delete method but with same signature as post with HttpPost protocol.
To solve this issue, we give our methods an attribute called Route so that each method can be accessed by its route only.
Let’s see this in action
![ASP.NET Core]()
We have assigned these route attributes to the methods with a name (it can be accessed through these names only)
Let’s see this in action
![ASP.NET Core]()
As you can see now it shows not found 404.
So to fix this, we must append the key in URL which we have defined in Route attribute
![ASP.NET Core]()
Now it gives the result once we append /GetAllUsers to our url.
We will have to do it for post.
Now let’s create a delete method and compele our CRUD operations.
Let’s add our delete method in UserDbClient class
![ASP.NET Core]()
Now let’s call this method from our controller.
![ASP.NET Core]()
Let’s test it.
![ASP.NET Core]()
So we have completed CRUD operations.
If you wish to see/download the code please Click Here!
Summary
In this article, we have seen how to perform CRUD operation in ASP.NET Core Web API using ADO.NET.
We have seen how to give our custom routes to our methods.
We have implemented lazy loading through factory client to call db client.
Hope you all liked it.
Happy Coding!