如何使用Dapper删除ASP.NET Core MVC中的锅炉板代码

Question

我想使用Dapper而不是使用普通过程来映射数据。我已经浏览了文档并有了一些想法，但我很难掌握窍门。

这是我的控制器代码，不使用dapper从postgres表列中获取数据并将其映射到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);
}

我想像这样使用Dapper：

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));
}
}
}
}

有人能帮我把代码转换成Dapper吗？

Answer 1

您只需要使用Dapper将查询结果转换为List<Transaction>，然后就完成了—无需调用ExecuteReader或迭代行。。。。。。

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