Geek Answers Handbook

SqlDataReader GetOrdinal with the same name

needs to be used GetOrdinalfrom SqlDataReader, but my query is with joins and contains the same field name several times. Therefore i try

SELECT a.Id, b.Id FROM table1 AS a ...

but GetOrdinal seems don t understand the schema alias...GetOrdinal ('a.Id') `throws an exception ... is there this in the archive?

+4
source share
2 answers

Give an alias in your request

SELECT a.Id As EmployeeID, b.Id as ManagerId FROM table1 AS a ..

Now you can use aliases in your code to read the value

var employeeIdIndex = reader.GetOrdinal("EmployeeID")
+5
source

I had the same question, and I found two common answers:

  • Field alias in your SQL
  • Use integer index for column

I didn't like any of these options, so I created a third: GetNthOrdinal.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

public static class SqlDataReaderExtensions
{
    public static int GetNthOrdinal(this SqlDataReader reader, string columnName, int nthOccurrence = 1)
    {
        // Get the schema which represents the columns in the reader
        DataTable schema = reader.GetSchemaTable();

        // Find all columns in the schema which match the name we're looking for.
        // schema is a table and each row is a column from our reader.
        var occurrences = schema.Rows.Cast<DataRow>().Where(r => string.Equals((string)r["ColumnName"], columnName, StringComparison.Ordinal));

        // Get the nthOccurrence.  Will throw if occurrences is empty.
        // reader.GetOrdinal will also throw if a column is not present, but you may want to
        // have this throw a more meaningful exception
        var occurrence = occurrences.Skip(nthOccurrence - 1).First();

        // return the ordinal
        return (int)occurrence["ColumnOrdinal"];
    }
}

:

reader.GetNthOrdinal("Id", 2);

, n- 0; 1.

+3

More articles:


All Articles