Aug 15, 2016

Generalize a method to read any master table with C# and Dapper

On a web page or application form, it is common to have different master data like Country, State...etc. available in dropdownlist, listbox or any other control. It is needed to read all master data from database and bind to proper controls. This post explains different ways to read master tables with C# and Dapper.

The following environment is used for this post:

Dapper 1.50.2

Postgresql 9.5.4

Npgsql 3.1.9

Visual Studio 2015 update 3

.NET Core 1.0.1

Approach 1:

The default way is to create separate method for each master table like below



  public async Task<IEnumerable<Country>> GetCountries()
        {
            using (var conn = _connectionProvider.Create())
            {
                await conn.OpenAsync();

                string query = "Select * from dbo.\"Country\"";

                return await conn.QueryAsync<Country>(query);
            }

        }

Approach 2:

Let's optimize the above method to resuse for other masters. Before this, create enum of master types.


 public enum EnumMaster
    {
        Country,
       	State,
	City
    }

Now we will use it as argument. It is assumed the database table name is same as enum string name.


 public async Task<IEnumerable<TKey>> Get<TKey>(EnumMaster enumMaster)
        {
            using (var conn = _connectionProvider.Create())
            {
                await conn.OpenAsync();

                string query = String.Format("Select * from dbo.\"{0}\"", enumMaster.ToString());
                return await conn.QueryAsync<TKey>(query);
            }

        }

For simplicity, I don't post the entire class. The above method is used in repository layer and used in service layer. The service method can be called as following:


var listCountry =  await _masterService.Get<Country>(EnumMaster.Country);

Approach 3:

In this approach, Instead of creating enum, create a base class say "MasterEntity" and inherit all master classes i.e.


 public class Country : MasterEntity
    {

 public class State: MasterEntity
    {

 public class City : MasterEntity
    {

Use it in the method:


public async Task<IEnumerable<TKey>> Get<TKey>() where TKey : MasterEntity
        {
            using (var conn = _connectionProvider.Create())
            {
                await conn.OpenAsync();

                string query = String.Format("Select * from dbo.\"{0}\"",typeof(TKey).Name);
                return await conn.QueryAsync<TKey>(query);
            }

        }

It is very easy to call, no need to pass any enum as in approach 2.


var listCountry =  await _masterService.Get<Country>();

If you hate * in select query, you can get column names from model properties using reflection. It is assumed model properties are following column names.


 public async Task<IEnumerable<TKey>> Get<TKey>() where TKey : MasterEntity
        {
            using (var conn = _connectionProvider.Create())
            {
                await conn.OpenAsync();
                var cols = typeof(TKey).GetProperties(BindingFlags.Public | BindingFlags.Instance).AsEnumerable().Select(x => string.Concat("\"", x.Name, "\""));
                string query = String.Format("Select {1} from dbo.\"{0}\"",typeof(TKey).Name, String.Join(",",cols));
                return await conn.QueryAsync<TKey>(query);
            }

        }

I am using PostgreSQL database so taking column names with quotes in query. You can customize according to your database.

Conclusion:

In this post, we have seen different ways to read master data, created a generic method to select any master table and to return list of strongly typed object. Also use power of C# reflection to remove * in SQL query.

Hope It helps. Enjoy C#.