Database Operations in ASP.NET Core Web API Using ADO.NET

Introduction

 

In this post, we will perform database operations 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.

1. Create SQL Table
2. Create ASP.NET Core Web API Project
3. Create Folder and Add Class
4. Perform Database Operations
5. 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 anASP.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;


}

Perform Database Operations

 

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

 

 

Conclusion

 

In this post, we have seen how to perform database operations in ASP.NET Core Web API using ADO.NET. Hope you all liked it!

 

Thanks for reading!