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.

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

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}");
}

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

Output: