CRUD Operations In ASP.NET Core Web API Using ADO.NET

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.

  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. -- =============================================  
  6. -- Author:      <Author,,Name>  
  7. -- Create date: <Create Date,,>  
  8. -- Description: <Description,,>  
  9. -- EXEC GetUsers  
  10. -- =============================================  
  11. ALTER PROCEDURE [dbo].[GetUsers]  
  12. AS  
  13. BEGIN  
  14.     SET NOCOUNT ON;  
  15.     SELECT * FROM Users(NOLOCK) ORDER BY Id ASC  
  16. END  

SaveUser Stored Procedure (Add/Edit)

  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. -- =============================================  
  6. -- Author:      <Author,,Name>  
  7. -- Create date: <Create Date,,>  
  8. -- Description: <Description,,>  
  9. -- =============================================  
  10. ALTER PROCEDURE [dbo].[SaveUser]  
  11. (  
  12. @Id INT,  
  13. @Name NVARCHAR(MAX),  
  14. @EmailId NVARCHAR(MAX),  
  15. @Mobile NVARCHAR(20),  
  16. @Address NVARCHAR(MAX),  
  17. @ReturnCode NVARCHAR(20) OUTPUT  
  18. )  
  19. AS  
  20. BEGIN  
  21.     SET @ReturnCode = 'C200'  
  22.     IF(@Id <> 0)  
  23.     BEGIN  
  24.         IF EXISTS (SELECT 1 FROM Users WHERE EmailId = @EmailId AND Id <> @Id)  
  25.         BEGIN  
  26.             SET @ReturnCode = 'C201'  
  27.             RETURN  
  28.         END  
  29.         IF EXISTS (SELECT 1 FROM Users WHERE Mobile = @Mobile AND Id <> @Id)  
  30.         BEGIN  
  31.             SET @ReturnCode = 'C202'  
  32.             RETURN  
  33.         END  
  34.   
  35.         UPDATE Users SET  
  36.         Name = @Name,  
  37.         EmailId = @EmailId,  
  38.         Mobile = @Mobile,  
  39.         Address = @Address,  
  40.         IsActive = 1  
  41.         WHERE Id = @Id  
  42.   
  43.         SET @ReturnCode = 'C200'  
  44.     END  
  45.     ELSE  
  46.     BEGIN  
  47.         IF EXISTS (SELECT 1 FROM Users WHERE EmailId = @EmailId)  
  48.         BEGIN  
  49.             SET @ReturnCode = 'C201'  
  50.             RETURN  
  51.         END  
  52.         IF EXISTS (SELECT 1 FROM Users WHERE Mobile = @Mobile)  
  53.         BEGIN  
  54.             SET @ReturnCode = 'C202'  
  55.             RETURN  
  56.         END  
  57.   
  58.         INSERT INTO Users (Name,EmailId,Mobile,Address,IsActive)  
  59.         VALUES (@Name,@EmailId,@Mobile,@Address,1)  
  60.   
  61.         SET @ReturnCode = 'C200'  
  62.     END  
  63. END  
  64.   
  65.   
  66. Delete User Stored Procedure  
  67. SET ANSI_NULLS ON  
  68. GO  
  69. SET QUOTED_IDENTIFIER ON  
  70. GO  
  71. -- =============================================  
  72. -- Author:      <Author,,Name>  
  73. -- Create date: <Create Date,,>  
  74. -- Description: <Description,,>  
  75. -- =============================================  
  76. ALTER PROCEDURE [dbo].[DeleteUser]  
  77. (  
  78. @Id INT,  
  79. @ReturnCode NVARCHAR(20) OUTPUT  
  80. )  
  81. AS  
  82. BEGIN  
  83.     SET NOCOUNT ON;  
  84.     SET @ReturnCode = 'C200'  
  85.     IF NOT EXISTS (SELECT 1 FROM Users WHERE Id = @Id)  
  86.     BEGIN  
  87.         SET @ReturnCode ='C203'  
  88.         RETURN  
  89.     END  
  90.     ELSE  
  91.     BEGIN  
  92.         DELETE FROM Users WHERE Id = @Id  
  93.         SET @ReturnCode = 'C200'  
  94.         RETURN  
  95.     END  
  96. 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 which 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.
  1. [DataContract]  
  2.     public class UsersModel  
  3.     {  
  4.         [DataMember(Name = "Id")]  
  5.         public int Id { get; set; }  
  6.   
  7.         [DataMember(Name = "Name")]  
  8.         public string Name { get; set; }  
  9.   
  10.         [DataMember(Name = "EmailId")]  
  11.         public string EmailId { get; set; }  
  12.   
  13.         [DataMember(Name = "Mobile")]  
  14.         public string Mobile { get; set; }  
  15.   
  16.         [DataMember(Name = "Address")]  
  17.         public string Address { get; set; }  
  18.   
  19.         [DataMember(Name = "IsActive")]  
  20.         public bool IsActive { get; set; }  
  21.     }  

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.

  1. [DataContract]  
  2.     public class Message<T>  
  3.     {  
  4.         [DataMember(Name = "IsSuccess")]  
  5.         public bool IsSuccess { get; set; }  
  6.   
  7.         [DataMember(Name = "ReturnMessage")]  
  8.         public string ReturnMessage { get; set; }  
  9.   
  10.         [DataMember(Name = "Data")]  
  11.         public T Data { get; set; }  
  12.     }  

We will also create another model in order to fetch our connection string from appsettings.json.

 

 

ASP.NET Core

Class highlighted in yellow is the model for appsettings.json.

Now, let me explain about 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.

  1. public static class SqlHelper  
  2.     {  
  3.         public static string ExecuteProcedureReturnString(string connString, string procName,  
  4.             params SqlParameter[] paramters)  
  5.         {  
  6.             string result = "";  
  7.             using (var sqlConnection = new SqlConnection(connString))  
  8.             {  
  9.                 using (var command = sqlConnection.CreateCommand())  
  10.                 {  
  11.                     command.CommandType = System.Data.CommandType.StoredProcedure;  
  12.                     command.CommandText = procName;  
  13.                     if (paramters != null)  
  14.                     {  
  15.                         command.Parameters.AddRange(paramters);  
  16.                     }  
  17.                     sqlConnection.Open();  
  18.                     var ret = command.ExecuteScalar();  
  19.                     if (ret != null)  
  20.                         result = Convert.ToString(ret);  
  21.                 }  
  22.             }  
  23.             return result;  
  24.         }  
  25.   
  26.         public static TData ExtecuteProcedureReturnData<TData>(string connString,string procName,  
  27.             Func<SqlDataReader,TData> translator,  
  28.             params SqlParameter[] parameters)  
  29.         {  
  30.             using (var sqlConnection = new SqlConnection(connString))  
  31.             {  
  32.                 using (var sqlCommand = sqlConnection.CreateCommand())  
  33.                 {  
  34.                     sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;  
  35.                     sqlCommand.CommandText = procName;  
  36.                     if(parameters !=null)  
  37.                     {  
  38.                         sqlCommand.Parameters.AddRange(parameters);  
  39.                     }  
  40.                     sqlConnection.Open();  
  41.                     using (var reader = sqlCommand.ExecuteReader())  
  42.                     {  
  43.                         TData elements;  
  44.                         try  
  45.                         {  
  46.                             elements = translator(reader);  
  47.                         }  
  48.                         finally  
  49.                         {  
  50.                             while(reader.NextResult())  
  51.                             { }  
  52.                         }  
  53.                         return elements;  
  54.                     }  
  55.                 }  
  56.             }  
  57.         }  
  58.   
  59.   
  60.         ///Methods to get values of   
  61.         ///individual columns from sql data reader  
  62.         #region Get Values from Sql Data Reader  
  63.         public static string GetNullableString(SqlDataReader reader ,string colName)  
  64.         {  
  65.             return reader.IsDBNull(reader.GetOrdinal(colName)) ? null : Convert.ToString(reader[colName]);  
  66.         }  
  67.   
  68.         public static int GetNullableInt32(SqlDataReader reader, string colName)  
  69.         {  
  70.             return reader.IsDBNull(reader.GetOrdinal(colName)) ? 0 : Convert.ToInt32(reader[colName]);  
  71.         }  
  72.   
  73.         public static bool GetBoolean(SqlDataReader reader, string colName)  
  74.         {  
  75.             return reader.IsDBNull(reader.GetOrdinal(colName)) ? default(bool) : Convert.ToBoolean(reader[colName]);  
  76.         }  
  77.   
  78.         //this method is to check wheater column exists or not in data reader  
  79.         public static bool IsColumnExists(this System.Data.IDataRecord dr,string colName)  
  80.         {  
  81.             try  
  82.             {  
  83.                 return (dr.GetOrdinal(colName) >= 0);  
  84.             }  
  85.             catch (Exception)  
  86.             {  
  87.                 return false;  
  88.             }  
  89.         }  
  90.         #endregion  
  91.     }   

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 which 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 which 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,

  1. public static class UserTranslator  
  2.     {  
  3.         public static UsersModel TranslateAsUser(this SqlDataReader reader,bool isList = false)  
  4.         {  
  5.             if(!isList)  
  6.             {  
  7.                 if (!reader.HasRows)  
  8.                     return null;  
  9.                 reader.Read();  
  10.             }  
  11.             var item = new UsersModel();  
  12.             if (reader.IsColumnExists("Id"))  
  13.                 item.Id = SqlHelper.GetNullableInt32(reader, "Id");  
  14.   
  15.             if (reader.IsColumnExists("Name"))  
  16.                 item.Name = SqlHelper.GetNullableString(reader, "Name");  
  17.   
  18.             if (reader.IsColumnExists("EmailId"))  
  19.                 item.EmailId = SqlHelper.GetNullableString(reader, "EmailId");  
  20.   
  21.             if (reader.IsColumnExists("Address"))  
  22.                 item.Address = SqlHelper.GetNullableString(reader, "Address");  
  23.   
  24.             if (reader.IsColumnExists("Mobile"))  
  25.                 item.Mobile = SqlHelper.GetNullableString(reader, "Mobile");  
  26.   
  27.             if (reader.IsColumnExists("IsActive"))  
  28.                 item.IsActive = SqlHelper.GetBoolean(reader, "IsActive");  
  29.   
  30.             return item;  
  31.         }  
  32.   
  33.         public static List<UsersModel> TranslateAsUsersList(this SqlDataReader reader)  
  34.         {  
  35.             var list = new List<UsersModel>();  
  36.             while(reader.Read())  
  37.             {  
  38.                 list.Add(TranslateAsUser(reader, true));  
  39.             }  
  40.             return list;  
  41.         }  
  42.     }  

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.

  1. 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,

  1. public class DbClientFactory<T>  
  2.     {  
  3.         private static Lazy<T> _factoryLazy = new Lazy<T>(  
  4.             () => (T)Activator.CreateInstance(typeof(T)),  
  5.             LazyThreadSafetyMode.ExecutionAndPublication);  
  6.   
  7.         public static T Instance  
  8.         {  
  9.             get  
  10.             {  
  11.                 return _factoryLazy.Value;  
  12.             }  
  13.         }  
  14.     }  

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!