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

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?

CodePudding user response：

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!
    */
}

CodePudding user response：

@marc_s: this is kiran only,I have done it and it is working... but problem is that one of the data is not not showing, and query is working perfectly fine. I don't know why a.account_name data is not displaying.