Create Get & Post Service Asp.Net Core Web Api Using Ado.Net

Please Subscribe Youtube| Like Facebook | Follow Twitter

Introduction

In this article, we will use Get and Post service in ASP.NET Core Web API using ADO.NET. If you are asking about performance, then ADO.NET will always be faster than Entity framework. This post will help you understand the implementation of ADO.NET with ASP.NET Core Web API. We have to perform the following steps to achieve the desired goal.

Create SQL Table
Create ASP.NET Core Web API Project
Create Folder and Add Class
Get and Post Service
Test the API using Postman

Create SQL Table

First of all, we will create a database named TestDB in SQL Server Management Studio and then we will create a table named Employee in it which will have four columns ID, FirstName, LastName, and Age. Here is a table query:

Use TestDB    
    
CREATE TABLE Employee (    
    ID int NOT NULL IDENTITY(1,1),    
     FirstName varchar(255),    
    LastName varchar(255) NOT NULL,    
    Age int,    
    PRIMARY KEY (ID)    
);

Create ASP.NET Core Web API Project

Now, we will create an ASP.NET Core Web API project.

Select ASP.NET Core Web Application.

Name your project ADODotnetwithASPDotnetCore.

Select API as the template.

Create Folder and Add Class

Now we will create a folder named DB. The DB folder will have one class named Database, which will have one variable sqlDataSource. This will have a connection string for connecting the database and one function named GetData, which will be used for fetching data from the database as DataTable. There will also be another function named ExecuteData, used for inserting data into the database. The database class will have the following code:

public static string sqlDataSource = "Data Source=DESKTOP-GV4424J;Initial Catalog=TestDB ;  
                                      Integrated Security=True;";  
  
public DataTable GetData(string str)  
{  
    DataTable objresutl = new DataTable();  
    try  
    {  
        SqlDataReader myReader;  
  
        using (SqlConnection myCon = new SqlConnection(sqlDataSource))  
        {  
            myCon.Open();  
            using (SqlCommand myCommand = new SqlCommand(str, myCon))  
            {  
                myReader = myCommand.ExecuteReader();  
                objresutl.Load(myReader);  
  
                myReader.Close();  
                myCon.Close();  
            }  
        }  
    }  
    catch (Exception ex)  
    {  
    }  
  
    return objresutl;  
  
}  
public int ExecuteData(string str, params IDataParameter[] sqlParams)  
{  
    int rows = -1;  
    try  
    {  
  
        using (SqlConnection conn = new SqlConnection(sqlDataSource))  
        {  
            conn.Open();  
            using (SqlCommand cmd = new SqlCommand(str, conn))  
            {  
                if (sqlParams != null)  
                {  
                    foreach (IDataParameter para in sqlParams)  
                    {  
                        cmd.Parameters.Add(para);  
                    }  
                    rows = cmd.ExecuteNonQuery();  
             }  
                   
            }  
        }  
    }  
    catch (Exception ex)  
    {  
  
    }  
  
  
    return rows;   
}

Get and Post Service

Once the class is added, we will use these class functions to perform some database operations. First, we will use Post service to insert data into the Employee table and then we will use the Get service to fetch data from the Employee table. We will go to Values controller and will remove the current code and add the following code:

Database db = new Database();  
  
[HttpPost]  
public ActionResult Post(object value)  
{  
    var serialize = JsonConvert.SerializeObject(value);  
    JObject jobject = JObject.Parse(serialize);           
    string query = "insert into Employee (FirstName,LastName,Age) values (@FirstName,@LastName,@Age);";  
    var parameters = new IDataParameter[]  
    {  
        new SqlParameter("@FirstName", jobject["FirstName"].ToString()),  
        new SqlParameter("@LastName", jobject["LastName"].ToString()),  
        new SqlParameter("@Age",jobject["Age"].ToString())  
   };  
    if (db.ExecuteData(query,parameters) > 0)  
    {  
  
        return Ok(new { Result = "Saved" });  
    }  
    else  
    {  
        return NotFound(new { Result = "something went wrong" });  
  
    }  
}  
  
[HttpGet]  
public ActionResult<ienumerable<string>> Get()  
{  
    string query = "select * from Employee";  
    DataTable dt = db.GetData(query);  
    var result = new ObjectResult(dt);  
    return result;  
}

In the Post service, we will receive data as an Object from client and will Serialize that object to write insert query from received data and will pass that query to the ExecuteData function. In the Get service, we will just write a Select query and pass it to the GetData function, then send returned data in response by converting it into an ObjectResult.

Test the API using Postman

Post Service

Get Service

Summary

In this article, we have seen how to use Get and Post service in ASP.NET Core Web API using ADO.NET.

Thanks for reading