how to establish a connection between a .NET Core Web API 3.1 application and a SQL Server database using the Dapper ORM?

1.     In your Startup.cs file, add the following code to the ConfigureServices method to register the connection string with the dependency injection container:

 

                Install-Package Dapper /* Run this command on package manager console */

2.     In your appsettings.json file, add a connection string for your SQL Server database. Here's an example:

 

"ConnectionStrings":

{

"MyDb": "Server=myserver;Database=mydatabase;User      Id=myusername;Password=mypassword;"

}

3.     In your Startup.cs file, add the following code to the ConfigureServices method to register the connection string with the dependency injection container:

 

services.AddScoped<IDbConnection>(c =>

    new SqlConnection(Configuration.GetConnectionString("MyDb")));

 

4.     In your controller or service class, inject the IDbConnection using constructor injection:

                private readonly IDbConnection _db;

                public MyController(IDbConnection db)

{

    _db = db;

}

 

5.     To execute a SQL query, you can use the Query or QueryAsync method of the IDbConnection object. Here's an example:

 

var customers = await _db.QueryAsync<Customer>("SELECT * FROM Customers");

 

In this example, we're selecting all the rows from the Customers table and mapping them to a list of Customer objects.