Is Dapper the quickest method to convert a SqlDataReader result to an object?

ado.net c# dapper sql-server

Question

The results of my comparison of the materialize times of Dapper, ADO.NET, and Dapper demonstrate that Dapper is somewhat quicker than ADO.NET (almost all of result show that it comparable though)
Therefore, I believe that my method of mapping the SqlDataReader's output to an object is inefficient.
Here's 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());
                }

I usedas keyword to cast in nullable column, is that correct?
Dapper is indicated by this code.

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());
                }
            }
        }
1
10
4/9/2020 12:34:18 PM

Accepted Answer

Here's how to speed up your ADO.NET code.

Instead of utilizing choose * while doing your select, specify the fields that you are choosing. This will enable you to guarantee the order of the fields even if the database's order changes. Then, instead of receiving those fields from the Reader by name, retrieve them by index. Utilizing an index is quicker.

Additionally, unless there is a compelling business reason, I advise against making string database columns nullable. If there is no value, just store a blank text in the database. Finally, I suggest use theGet techniques on theDataReader to ensure that your fields are the type they are, so that your code doesn't need casting. thus as an example, rather than casting theDataReader[index++] usage of value as an intDataReader.GetInt(index++)

as in the following code:

 salesOrderHeader = new SalesOrderHeaderSQLserver();
 salesOrderHeader.SalesOrderId = (int)reader["SalesOrderId"];
 salesOrderHeader.SalesOrderNumber =       reader["SalesOrderNumber"] as string;
 salesOrderHeader.AccountNumber = reader["AccountNumber"] as string;

becomes

 int index = 0;
 salesOrderHeader = new SalesOrderHeaderSQLserver();
 salesOrderHeader.SalesOrderId = reader.GetInt(index++);
 salesOrderHeader.SalesOrderNumber = reader.GetString(index++);
 salesOrderHeader.AccountNumber = reader.GetString(index++);

Try it out and see how it works for you.

6
12/10/2019 1:41:38 PM

Popular Answer

I often question myself, "What would Margrave, Marc do? " when unsure about anything db or reflective.

He would utilize FastMember in this situation. Likewise, you need to. It serves as the basis for the data conversions in Dapper and makes it simple to map a DataReader to an object of your own (should you not want to use Dapper).

The extension technique for transforming aSqlDataReader into a certain itemT :

PLEASE NOTE: This code implies a dependency on FastMember and is written for .NET Core (though could easily be converted to .NET Framework/Standard compliant code).

public static T ConvertToObject<T>(this SqlDataReader rd) where T : class, new()
{
    Type type = typeof(T);
    var accessor = TypeAccessor.Create(type);
    var members = accessor.GetMembers();
    var t = new T();

    for (int i = 0; i < rd.FieldCount; i++)
    {
        if (!rd.IsDBNull(i))
        {
            string fieldName = rd.GetName(i);

            if (members.Any(m => string.Equals(m.Name, fieldName, StringComparison.OrdinalIgnoreCase)))
            {
                accessor[t, fieldName] = rd.GetValue(i);
            }
        }
    }

    return t;
}


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow