How to use Dapper to remove boiler plate code in ASP.NET Core MVC?

Question

I wanted to use Dapper instead of using the normal procedure to map the data. I have gone through the documentation and have some ideas, but I am struggling to get the hang of it.

This is my controller code without using dapper to get data from postgres tables columns and map it to transactionLists:

public IActionResult Index()
{
    using var conn = new NpgsqlConnection(connString);

    conn.Open();

    using var command = new NpgsqlCommand(null, conn);

    command.CommandText = "SELECT t.transaction_id, a.account_name, a.type,t.amount,t.date" +
                          " FROM account AS a " +  
                          " INNER JOIN transaction AS t ON a.account_id = t.account_id";
    command.Prepare();

    NpgsqlDataReader reader = command.ExecuteReader();

    List<Transaction> transactionLists = new List<Transaction>();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            transactionLists.Add(new Transaction
                                     {
                                         TransactionId = Convert.ToInt32(reader["transaction_id"]),
                                         AccountName = Convert.ToString(reader["account_name"]),
                                         Type = Convert.ToString(reader["type"]),
                                         Date = Convert.ToDateTime(reader["date"]),
                                         Amount = Convert.ToInt32(reader["amount"]),
                                     });
        }
    }

    ViewBag.accounts = GetAccountLists().OrderByDescending(x => x.AccountName).ToList();

    var model = new TransactionViewModel();
    model.Transactions = transactionLists;

    return View(model);
}

I want to use Dapper like this:

using (NpgsqlConnection connection = new NpgsqlConnection(connString))
{
    var transactions = connection.Query("SELECT t.transaction_id, 
                               a.account_name, a.type,t.amount,t.date" +
                            " FROM account AS a INNER JOIN transaction AS t " +
                            " ON a.account_id = t.account_id";).AsList();

    using (NpgsqlDataReader reader = command.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                allTransactionLists.Add(Transaction.FromReader(reader));
            }
        }
    }
}

Can anyone help me how to convert the code into Dapper?

I would also strongly suggest not having any data access code in your UI. Your UI should be data access agnostic, and simply call on a class library (via dependency injection and interfaces). It is the class library that then does your business logic and then uses Dapper to connect to your database. — Pierre Plourde, Jan 28 '22 at 17:17

score 1 · Answer 1 · answered Jan 28 '22 at 06:45

You just need to use Dapper to convert the results of your query into a List<Transaction> and then you're done - no need to call ExecuteReader or iterate over the rows......

using (NpgsqlConnection connection = new NpgsqlConnection(connString))
{
    string query = "SELECT t.transaction_id, a.account_name, a.type, t.amount, t.date " +
                   "FROM account AS a " + 
                   "INNER JOIN transaction AS t ON a.account_id = t.account_id;";
                    
    List<Transaction> transactions = connection.Query<Transaction>(query).ToList();
    
    /*
    The "transactions" list now contains all the data returned from the query.
    Do with these objects whatever it is you want to do - no need to use
     ExecuteReader() or iterating over rows - that's all handled by Dapper!
    */
}

Thanks for answering @marc_s, I did it as you told me....... but int `List transactions = connection.Query(query).ToList();` I am getting an error saying : [link](https://ibb.co/0JgQP2f) like this. can you help? — kiran, Jan 28 '22 at 07:12
@kiran: did you add `using Dapper;` at the top of your file?? — marc_s, Jan 28 '22 at 08:26

How to use Dapper to remove boiler plate code in ASP.NET Core MVC?

1 Answers1