我需要使用SqlDataReader
中的GetOrdinal
,但我的查询包含多个相同字段名,并且有多个联接。因此,我尝试使用以下代码:
SELECT a.Id, b.Id FROM table1 AS a ...
但是GetOrdinal似乎不理解模式别名... GetOrdinal('a.Id')会抛出异常...有什么方法可以解决这个问题吗?
我需要使用SqlDataReader
中的GetOrdinal
,但我的查询包含多个相同字段名,并且有多个联接。因此,我尝试使用以下代码:
SELECT a.Id, b.Id FROM table1 AS a ...
在您的查询中给一个别名
SELECT a.Id As EmployeeID, b.Id as ManagerId FROM table1 AS a ..
var employeeIdIndex = reader.GetOrdinal("EmployeeID")
我自己也曾有过这个问题,而我发现两种常见的答案是:
我不喜欢这两个选项,所以我创造了第三种方法: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);
reader.GetInt32(0)
和reader.GetInt32(1)
。 - Tim Schmelter