Checking to see if a column exists in a data reade

问题:

This question already has an answer here:

• Check for column name in a SqlDataReader object 22 answers

Is there a way to see if a field exists in an IDataReader-based object w/o just checking for an IndexOutOfRangeException?

In essence, I have a method that takes an IDataReader-based object and creates a strongly-typed list of the records. In 1 instance, one data reader has a field that others do not. I don't really want to rewrite all of the queries that feed this method to include some form of this field if I don't have to. The only way I have been able to figure out how to do it so far is to throw the 1 unique field into a try/catch block as shown below.

try
{
    tmp.OptionalField = reader["optionalfield"].ToString();
}
catch (IndexOutOfRangeException ex)
{
    //do nothing
}

Is there a cleaner way short of adding the "optional field" to the other queries or copying the loading method so 1 version uses the optional field and the other doesn't?

I'm in the 2.0 framework also.

回答1:

I ended up finding a solution using the reader.GetName(int) method. I created the below method to encompass the logic.

public bool ColumnExists(IDataReader reader, string columnName)
{
    for (int i = 0; i < reader.FieldCount; i++)
    {
         if (reader.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
        {
            return true;
        }
    }

    return false;
}

回答2:

The following will give you a list of the column name strings given a data reader. (Remember the results are based on the last read so it may not be the same depending on what the reader read).

var cols = reader.GetSchemaTable()
                 .Rows
                 .OfType<DataRow>()
                 .Select(row => row["ColumnName"]);

Or to check if a column exists:

public bool ColumnExists(IDataReader reader, string columnName)
{

  return reader.GetSchemaTable()
               .Rows
               .OfType<DataRow>()
               .Any(row => row["ColumnName"].ToString() == columnName);
}

回答3:

Appears I stand corrected. I know your actual column names are in there, but I was going down the wrong path. This reference helped clear things up a bit, but I'm still not sure if there's an elegant way of doing it. Adapted from the above link, you could get a list of all of your columns with the following:

List<string> myCols = new List<string>();
DataTable schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
    myCols.Add(row[schema.Columns["ColumnName"]]);
}

Unfortunately it appears you can only access schema.Rows by index, so I'm not sure you can get around looping through the rows first before checking by name. In that case, your original solution seems far more elegant!

Note: my original answer suggested checking for presence of a column simply by: reader.GetSchemaTable().Columns["optionalfield"].

回答4:

Enumerable.Range(0, reader.FieldCount).Any(i => reader.GetName(i) == "ColumnName")

回答5:

This should work, try this:

private static bool ColumnExists(SqlDataReader reader, string columnName)
        {
            using (var schemaTable = reader.GetSchemaTable())
            {
                if (schemaTable != null)
                    schemaTable.DefaultView.RowFilter = String.Format("ColumnName= '{0}'", columnName);

                return schemaTable != null && (schemaTable.DefaultView.Count > 0);
            }
        }

回答6:

Load it into a DataTable and then you can check for column:

DataTable dataTable = new DataTable();
dataTable.Load(reader);
foreach (var item in dataTable.Rows) 
{
    bool columnExists = item.Table.Columns.Contains("ColumnName");
}

回答7:

Don't need so much complication, just this:

bool bFieldExists = datareader.GetSchemaTable().Columns.Contains(strFieldName);