Work with SQL Data with Fewer Lines of Code Using Dynamic C#

If you need to query a SQL database and work with the data quickly you can use dynamic C# to write less boilerplate code.

For example, if you wanted to query a Customer table (or a more complex joined query, etc.) you could start by writing a class to represent the fields in the Customer table and then use a library/ORM to connect to the SQL database, and perform the query, and get the results populated into Customer objects.

Sometimes you will want this more detailed approach if you dealing with more complex data, perhaps with joined tables/foreign keys, etc.

Sometimes however you just want to query some data and do something with it (display it, report it, etc.) – in this case you’ve got a lot of wasted effort creating classes to represent rows in tables. You can use dynamic C# in conjunction with a library such as Dapper to remove the need to create these “boilerplate” classes.

The following code shows how to do this in just a few lines of code:

using var cn = new SqlConnection(connectionString);

IEnumerable<dynamic> customers = cn.Query("SELECT TOP 10 * FROM CUSTOMER");

foreach (dynamic customer in customers)
    WriteLine($"{customer.FirstName} {customer.SecondName} {customer.Height} {customer.Age}");

The cn.Query method comes from Dapper and returns dynamic objects, each object is a row from the table/query.

To access a field from the database you simply reference it on the dynamic object such as customer.FirstName – here FirstName is a field in the CUSTOMER table.

Notice we didn’t need to go and spend the extra time to code a Customer class just to be able to query the database.

There’s a lot more to dynamic C# however and to learn more check out my Dynamic Programming in C# 10 Pluralsight course and even start watching with a free trial.


Add comment