Execute SQL Queries in Web API using Dapper in .NET CORE

 

Every Dotnet developer knows what is Entity framework. Entity Framework is an open source ORM Framework for .NET applications supported by Microsoft. Like Entity Framework their or n number of ORM Frameworks in market. In that Dapper will be listed in top 5 . In this we will discuss how to execute SQL queries using Dapper in Web API.

What is ORM ?

Object relational Mapping. It is a programming technique to map your objects to the tables in database.

What is Dapper?

Dapper is a micro ORM or it is a simple object mapper framework which helps to map the  query output to a C# class. 

Advantages:

• fast in performance.
• object Mapper
• easy and less lines of code
• executing Queries
• executing Stored Procedures
• Dynamic object binding
• Multiple query support.

Lets dive into and see some of the features of using Dapper.

In this I will explain 

1. Create .NET Core Application for API

2. Add Dapper from NuGet package and install

3. Add Connection string.

4. Add new Controller and Create a method to execute Queries.

1. Create .NET Core Application for API

Open Visual Studio 2017 or 2019

go to File -> New -> Project

Select ASP.NET Core Web Application

Select API  and click OK

We have successfully created API.

2. Add Dapper from NuGet package and install

Go to  Tools-> NuGet Package Manager -> Manage NuGet Packages for Solution

Search with Name Dapper. and click install.

3. Add Connection string.

Open appsettings.json file and add connectionstring of the database.

"ConnectionStrings": {

    "DefaultDatabase": "Server=(localdb)\\MSSQLLocalDB;Database=TestDB;Trusted_Connection=True;"

  }

4. Add new Controller and Create a method to execute Queries.

Right click on Controllers folder and click Add and then Controller.

Select API Controller-Empty

Add Name like ServiceController

we have successfully created ServiceController.

After this Add below Namespaces:

using System.Data;

using System.Data.SqlClient;

using Microsoft.Extensions.Configuration;

using Dapper;

using Newtonsoft.Json;

Add below Code to the Service Controller:

       private readonly IConfiguration _configuration;

        public ServiceController(IConfiguration configuration)

        {

            _configuration = configuration;

        }

        [HttpGet]

        public ActionResult<IEnumerable<string>> Get(string myquery)

        {

            IDbConnection db = new SqlConnection(_configuration.GetConnectionString("DefaultDatabase"));

            // var myquery = "select * from Users";

            // db.Open();

            var result = db.Query(myquery);

            var json = JsonConvert.SerializeObject(result);

            return Ok(json);

        }

Dapper Query is used to run dynamic queries.

Download Post Man and install and then open post man and remove SSL:

We will create ,alter ,Update delete and drop table using this API endpoint.

I will explain how.

select GET and update URL as below.

Create Table Roles:

 by calling this endpoint this API will create a table.

https://localhost:44328/api/Service?myquery=

create table Roles(

Id int,

RoleKey int,

RoleName varchar(100)

)

Alter Roles Table:

https://localhost:44328/api/Service?myquery=alter table Roles add Name varchar(100)

Insert into Roles Table:

https://localhost:44328/api/Service?myquery=insert into Roles(ID,RoleKey,RoleName,Name) values(1,2,'Admin','Amar')

Select from Table:

https://localhost:44328/api/Service?myquery=Select * from Roles

Delete from Table:

https://localhost:44328/api/Service?myquery=delete from Roles

Drop table :

https://localhost:44328/api/Service?myquery=Drop table Roles

There are several options in Dapper for stored procedure we use Execute method and we have dynamic parameters. for a stored procedure output parameter. Dapper is in top of all micro ORM frameworks.

it is very easy to use than Entity Framework. In Entity Framework updating entities and deleting entity classes related to deleted tables or columns is hectic. So most of top companies prefer using ADO.NET ORM Framework as data layer to avoid production errors.

Thanks for Reading.

Github:

Source Code

Please find my more popular blogs:

Trello for Project Management

Web API Documentation Service Swagger