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.

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



using var con = new SqlConnection(connectionString);



IEnumerable<dynamic> 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:

Thank you!