DEV Community

Cover image for SQL Query with Dynamic C# and Dapper
Kartik Chandra Biswas
Kartik Chandra Biswas

Posted on

SQL Query with Dynamic C# and Dapper

We use ADO.NET to connect to SQL Server and work with data. We simply use SqlConnection to establish the connection to SQL Server, and then utilize an SqlDataAdapter or SqlCommand to execute queries. After executing the query, we retrieve the required field values from the result set.

This task can be done using a NuGet package called Dapper, a micro-ORM (Object-Relational Mapper) that simplifies working with ADO.NET. Dapper makes it easy, as we don't even need to create models or classes to read data when using dynamic.

Image description

This simple code demonstrates how easily we can read data using Dapper with ADO.NET. Here I added the following two packages Microsoft.Data.SqlClient and Dapper

Image description

string connectionString = "Server=.;Database=MovieClub;User Id=kcbdbuser;Password=kcbdbuser;TrustServerCertificate=True;";

using var con = new SqlConnection(connectionString);

IEnumerable movies = con.Query("SELECT * FROM Movie");
foreach (dynamic item in movies)
{
Console.WriteLine($"Id: {item.Id}, Name: {item.Name}, Genre: {item.Genre}, Rating: {item.Rating}, Language: {item.Language}");
}
Enter fullscreen mode Exit fullscreen mode

con.Query executes the query and we can access f fields like an object's property.

Output:

Image description

Github link: https://github.com/onsaurav/SqlQueryWithDynamicAndDapper
Thank you.

Top comments (0)