将SqlDataReader结果映射到对象的最快方法 [英] Fastest way to map result of SqlDataReader to object
问题描述
我正在比较Dapper与ADO.NET和Dapper之间的实现时间。 最终，尽管第一次执行给定的获取查询要比ADO.NET慢，但Dapper往往比ADO.NET快。一些结果表明，Dapper比ADO.NET快一点（
所以我认为我使用效率低下的方法将SqlDataReader的结果映射到对象。
这是我的代码
I'm comparing materialize time between Dapper and ADO.NET and Dapper. Ultimately, Dapper tend to faster than ADO.NET, though the first time a given fetch query was executed is slower than ADO.NET. a few result show that Dapper a little bit faster than ADO.NET(almost all of result show that it comparable though)
So I think I'm using inefficient approach to map result of SqlDataReader to object.
This is my code
var sql = "SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = @Id";
var conn = new SqlConnection(ConnectionString);
var stopWatch = new Stopwatch();
try
{
conn.Open();
var sqlCmd = new SqlCommand(sql, conn);
for (var i = 0; i < keys.GetLength(0); i++)
{
for (var r = 0; r < keys.GetLength(1); r++)
{
stopWatch.Restart();
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.AddWithValue("@Id", keys[i, r]);
var reader = await sqlCmd.ExecuteReaderAsync();
SalesOrderHeaderSQLserver salesOrderHeader = null;
while (await reader.ReadAsync())
{
salesOrderHeader = new SalesOrderHeaderSQLserver();
salesOrderHeader.SalesOrderId = (int)reader["SalesOrderId"];
salesOrderHeader.SalesOrderNumber = reader["SalesOrderNumber"] as string;
salesOrderHeader.AccountNumber = reader["AccountNumber"] as string;
salesOrderHeader.BillToAddressID = (int)reader["BillToAddressID"];
salesOrderHeader.TotalDue = (decimal)reader["TotalDue"];
salesOrderHeader.Comment = reader["Comment"] as string;
salesOrderHeader.DueDate = (DateTime)reader["DueDate"];
salesOrderHeader.CurrencyRateID = reader["CurrencyRateID"] as int?;
salesOrderHeader.CustomerID = (int)reader["CustomerID"];
salesOrderHeader.SalesPersonID = reader["SalesPersonID"] as int?;
salesOrderHeader.CreditCardApprovalCode = reader["CreditCardApprovalCode"] as string;
salesOrderHeader.ShipDate = reader["ShipDate"] as DateTime?;
salesOrderHeader.Freight = (decimal)reader["Freight"];
salesOrderHeader.ModifiedDate = (DateTime)reader["ModifiedDate"];
salesOrderHeader.OrderDate = (DateTime)reader["OrderDate"];
salesOrderHeader.TerritoryID = reader["TerritoryID"] as int?;
salesOrderHeader.CreditCardID = reader["CreditCardID"] as int?;
salesOrderHeader.OnlineOrderFlag = (bool)reader["OnlineOrderFlag"];
salesOrderHeader.PurchaseOrderNumber = reader["PurchaseOrderNumber"] as string;
salesOrderHeader.RevisionNumber = (byte)reader["RevisionNumber"];
salesOrderHeader.Rowguid = (Guid)reader["Rowguid"];
salesOrderHeader.ShipMethodID = (int)reader["ShipMethodID"];
salesOrderHeader.ShipToAddressID = (int)reader["ShipToAddressID"];
salesOrderHeader.Status = (byte)reader["Status"];
salesOrderHeader.SubTotal = (decimal)reader["SubTotal"];
salesOrderHeader.TaxAmt = (decimal)reader["TaxAmt"];
}
stopWatch.Stop();
reader.Close();
await PrintTestFindByPKReport(stopWatch.ElapsedMilliseconds, salesOrderHeader.SalesOrderId.ToString());
}
我用 as 关键字强制转换为可为空的列，对吗？
，这是Dapper的代码。
I used as keyword to cast in nullable column, is that correct?
and this is code for Dapper.
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
var stopWatch = new Stopwatch();
for (var i = 0; i < keys.GetLength(0); i++)
{
for (var r = 0; r < keys.GetLength(1); r++)
{
stopWatch.Restart();
var result = (await conn.QueryAsync<SalesOrderHeader>("SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = @Id", new { Id = keys[i, r] })).FirstOrDefault();
stopWatch.Stop();
await PrintTestFindByPKReport(stopWatch.ElapsedMilliseconds, result.ToString());
}
}
}
推荐答案
这是一种使ADO.NET代码更快的方法。
Here's a way to make your ADO.NET code faster.
进行选择时，请列出您要选择的字段，而不要使用select *。 。这样一来，即使数据库中的顺序发生更改，也可以确保字段返回的顺序。然后从Reader获取这些字段时，请按索引而不是按名称获取它们。使用和索引速度更快。
When you do your select, list out the fields that you are selecting rather than using select *. This will let you ensure the order that the fields are coming back even if that order changes in the database.Then when getting those fields from the Reader, get them by index rather than by name. Using and index is faster.
此外，除非有充分的商业理由，否则我建议不要将字符串数据库字段设置为可空。然后，如果没有值，只需将空白字符串存储在数据库中。最后，我建议在 DataReader 上使用 Get 方法来获取字段类型，以便进行转换您的代码中不需要。因此，例如，不要将 DataReader [index ++] 值转换为int，而使用 DataReader.GetInt（index ++）
Also, I'd recommend not making string database fields nullable unless there is a strong business reason. Then just store a blank string in the database if there is no value. Finally I'd recommend using the Get methods on the DataReader to get your fields in the type they are so that casting isn't needed in your code. So for example instead of casting the DataReader[index++] value as an int use DataReader.GetInt(index++)
例如，这段代码：
salesOrderHeader = new SalesOrderHeaderSQLserver();
salesOrderHeader.SalesOrderId = (int)reader["SalesOrderId"];
salesOrderHeader.SalesOrderNumber = reader["SalesOrderNumber"] as string;
salesOrderHeader.AccountNumber = reader["AccountNumber"] as string;
成为
int index = 0;
salesOrderHeader = new SalesOrderHeaderSQLserver();
salesOrderHeader.SalesOrderId = reader.GetInt(index++);
salesOrderHeader.SalesOrderNumber = reader.GetString(index++);
salesOrderHeader.AccountNumber = reader.GetString(index++);
旋转一下，看看效果如何。
Give that a whirl and see how it does for you.
这篇关于将SqlDataReader结果映射到对象的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
