Datareader Ordinal-Based Lookups VS Named Lookups

DataReader ordinal-based lookups vs named lookups

Microsoft recommends not calling GetOrdinal within a loop.

That would include indirect calls with the string indexer.

You can use GetOrdinal at the top of your loop put the ordinals in an array and have the indexes in the array be const or have an enum for them (no GetOrdinal at all) or use GetOrdinal into individual variables with descriptive names.

Only if your sets are small would I really consider this to be premature optimization.

It's apparently a 3% penalty.

.NET SqlDataReader Item[] vs. GetString(GetOrdinal())?

Casting issues aside, for the singular call, there are none. The indexer will make a call to DbDataReader.GetOrdinal and then call the appropriate Get method to get the value (note that it's faster to call the Get methods using an ordinal than it is to use the indexer with the field name).

However, this will incur a lookup of the ordinal every time. If you are iterating through a number of records in a forward-only, read-only way (which is exactly what DbDataReader instances are meant to do), then you can reduce the overhead of this lookup by doing it just once.

You could do so like this:

// Move to the first record.  If no records, get out.
if (!dataReader.Read()) return;

// Before the loop.  Can do this for any other fields being
// accessed in the loop as well.
int myFieldNameOrdinal = dataReader.GetOrdinal("MyFieldName");

// Process the records.  Remember, already on the first record, so
// use do/while here.
do
{
    // Do something with your field.
    Console.WriteLine(dataReader.GetString(myFieldNameOrdinal));
} while (dataReader.Read());

Why use the GetOrdinal() Method of the SqlDataReader

I think that the reason to use GetOrdinal() is so that you can cache the result and re-use it multiple times for performance.

E.g.

Dim reader As SqlClient.SqlDataReader
int valueOrdinal = reader.GetOrdinal("value");
while ( ... )
{
    var value = reader.GetString(valueOrdinal);
}

Reading data from reader in C# with Sql Server

reader.GetString(index);

This will get the row value at that column index as string, The second solution is more ideal because it allows you to get the value at that index in your own prefered type.

Example:-

String name = reader["Name"].ToString();
int age = (int) reader["Age"]

C# SQLDataReader accessing by column name

I have code which defines a SqlDataReader, opens the connection, and executes the ExecuteReader()

And isn't it the most incredibly tedious code to have to write? Many people have thought this over the time and many things have been invented to relieve you of the tedium of it. MarkPflug's answer directly addresses your question, but just in case you aren't aware that there are significant productivity boosts available I'd like to introduce you to one of these technologies

Is there a way in c# to specify the column name that I would like to retrieve similar to the way it works in Visual Basic?

Here's a way to do it, in that when you do this you don't have to type it. It avoids typing the same thing again that you've already typed (twice - once for the variable name, once in the SQL)

Use the nuget package manager built into visual studio, to install Dapper

Then lets say you have a class that holds your data:

//C#
record DbMenu(string DbMenuPEO, string DbMenuTransfer, string DbMenuLoan);

'or VB, if you like that sort of thing
Class DbMenu
  Public Property DbMenuPEO as String
  Public Property DbMenuTransfer As String
  Public Property DbMenuLoan As String
End Class

You can get Dapper to make your query, add any parameters, open your connection, download your data, fill up a list full of your classes, close the connection and return it.. all in one line of code:

//C#
using var conn = ... //code here that gets a connection; doesn't need to be open
var myListOfDbMenus = conn.Query<DbMenu>("SELECT * FROM ... ");

'VB
Using conn = ...
  Dim myListOfDbMenus = conn.Query(Of DbMenu)("SELECT * FROM ... ");
End Using

The short short version is: your c# class properties should be named the same as your columns. If they aren't, it's easiest to use AS xyz in the SQL to equalize the names. If you want to write a parameterized query, you provide @parameterNames that are the same as the property names of an anonymous object you pass at the same time as your query:

var q = conn.Query<Type>("SELECT ... WHERE col = @val1", new {val1 = "hello" } );

If you like writing SQL and having that low level control/don't want to use an ORM like EF, then Dapper lets you carry on doing the SQL directly as you're doing, but takes away all the repetitive surrounding boilerplate

DataReader - hardcode ordinals?

It is possible to argue both sides in this situation. As already pointed out by others, using the name is more readable and will not break if someone changes the order of columns in the underlying database. But one might also argue the case that using an ordinal has the advantage of not breaking if someone changes the column name in the underlying database. I prefer the former argument, though, and think the readability argument for column names trumps the second argument in general. And an additional argument for names is that it is that it can “self-detect” errors. If someone does change a field name, then the code has a better chance of breaking rather than having the subtle bug of appearing to work while it reads the wrong field.

It seems obvious but maybe it is worth mentioning a usage case that has both the self-detecting error and the performance of ordinals. If you specify the SELECT list explicitly in the SQL, then using ordinals won’t be a problem because the statement in the code guarantees the order:

SELECT name, address, phone from mytable

In this case, it would be fairly safe to use ordinals to access the data. It doesn’t matter if someone moves fields around in the table. And if someone changes a name, then the SQL statement produce an error when it runs.

And one final point. I just ran a test on a provider I helped write. The test read 1 million rows and accessed the “lastname” field on each record (compared against a value). The usage of rdr[“lastname”] took 3301 milliseconds to process while rdr.GetString(1) took 2640 milliseconds (approximately a 25% speedup). In this particular provider, the lookup of the name uses a sorted lookup to translate the name to ordinal.

Check for column name in a SqlDataReader object

public static class DataRecordExtensions
{
    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i=0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;
        }
        return false;
    }
}

Using Exceptions for control logic like in some other answers is considered bad practice and has performance costs. It also sends false positives to the profiler of # exceptions thrown and god help anyone setting their debugger to break on exceptions thrown.

GetSchemaTable() is also another suggestion in many answers. This would not be a preffered way of checking for a field's existance as it is not implemented in all versions (it's abstract and throws NotSupportedException in some versions of dotnetcore). GetSchemaTable is also overkill performance wise as it's a pretty heavy duty function if you check out the source.

Looping through the fields can have a small performance hit if you use it a lot and you may want to consider caching the results.

What is the most efficient way of retrieving data from a DataReader?

I doubt there will be a very appreciable performance difference, but you can avoid the name lookup on every row simply by lifting it out of the loop. This is probably the best you'll be able to achieve:

int idIdx = rdr.GetOrdinal("Id");
int createdIdx = rdr.GetOrdinal("Created");

while(rdr.Read())
{
    var myObj = new myObj();

    myObj.Id = rdr.GetFieldValue<int>(idIdx);

    //more populating of myObj from rdr

    myObj.Created = rdr.GetFieldValue<DateTime>(createdIdx);
}

---

Related Topics