How do I check to see if a column exists in a SqlDataReader object? In my data access layer, I have create a method that builds the same object for multiple stored procedures calls. One of the stored procedures has an additional column that is not used by the other stored procedures. I want to modified the method to accommodate for every scenario.
My application is written in C#.
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.
The correct code is:
public static bool HasColumn(DbDataReader Reader, string ColumnName) {
foreach (DataRow row in Reader.GetSchemaTable().Rows) {
if (row["ColumnName"].ToString() == ColumnName)
return true;
} //Still here? Column not found.
return false;
}
I think your best bet is to call GetOrdinal("columnName") on your DataReader up front, and catch an IndexOutOfRangeException in case the column isn't present.
In fact, let's make an extension method:
public static bool HasColumn(this IDataRecord r, string columnName)
{
try
{
return r.GetOrdinal(columnName) >= 0;
}
catch (IndexOutOfRangeException)
{
return false;
}
}
Edit
Ok, this post is starting to garner a few down-votes lately, and I can't delete it because it's the accepted answer, so I'm going to update it and (I hope) try to justify the use of exception handling as control flow.
The other way of achieving this, as posted by Chad Grant, is to loop through each field in the DataReader and do a case-insensitive comparison for the field name you're looking for. This will work really well, and truthfully will probably perform better than my method above. Certainly I would never use the method above inside a loop where performace was an issue.
I can think of one situation in which the try/GetOrdinal/catch method will work where the loop doesn't. It is, however, a completely hypothetical situation right now so it's a very flimsy justification. Regardless, bear with me and see what you think.
Imagine a database that allowed you to "alias" columns within a table. Imagine that I could define a table with a column called "EmployeeName" but also give it an alias of "EmpName", and doing a select for either name would return the data in that column. With me so far?
Now imagine that there's an ADO.NET provider for that database, and they've coded up an IDataReader implementation for it which takes column aliases into account.
Now, dr.GetName(i) (as used in Chad's answer) can only return a single string, so it has to return only one of the "aliases" on a column. However, GetOrdinal("EmpName") could use the internal implementation of this provider's fields to check each column's alias for the name you're looking for.
In this hypothetical "aliased columns" situation, the try/GetOrdinal/catch method would be the only way to be sure that you're checking for every variation of a column's name in the resultset.
Flimsy? Sure. But worth a thought. Honestly I'd much rather an "official" HasColumn method on IDataRecord.
Here is a working sample for Jasmin's idea:
var cols = r.GetSchemaTable().Rows.Cast<DataRow>().Select
(row => row["ColumnName"] as string).ToList();
if (cols.Contains("the column name"))
{
}
I wrote this for Visual Basic users:
Protected Function HasColumnAndValue(ByRef reader As IDataReader, ByVal columnName As String) As Boolean
For i As Integer = 0 To reader.FieldCount - 1
If reader.GetName(i).Equals(columnName) Then
Return Not IsDBNull(reader(columnName))
End If
Next
Return False
End Function
I think this is more powerful and the usage is:
If HasColumnAndValue(reader, "ID_USER") Then
Me.UserID = reader.GetDecimal(reader.GetOrdinal("ID_USER")).ToString()
End If
TLDR:
There are lots of answers with claims about performance and bad practice, so I clarify that here.
The exception route is faster for higher numbers of returned columns, the loop route is faster for lower number of columns, and the crossover point is around 11 columns. Scroll to the bottom to see a graph and test code.
Full answer:
The code for some of the top answers work, but there is an underlying debate here for the "better" answer based on the acceptance of exception handling in logic and its related performance.
To clear that away, I do not believe there is much guidance regarding catching exceptions. Microsoft does have some guidance regarding throwing exceptions. There they do state:
Do not use exceptions for the normal flow of control, if possible.
The first note is the leniency of "if possible". More importantly, the description gives this context:
framework designers should design APIs so users can write code that does not throw exceptions
That means, if you are writing an API, that might be consumed by somebody else, give them the ability to navigate an exception without a try/catch. For example, provide a TryParse with your exception-throwing Parse method. Nowhere does this say though that you shouldn't catch an exception.
Further, as another user points out, catches have always allowed filtering by type and somewhat recently allow further filtering via the when clause. This seems like a waste of language features if we're not supposed to be using them.
It can be said that there is some cost for a thrown exception, and that cost may impact performance in a heavy loop. However, it can also be said that the cost of an exception is going to be negligible in a "connected application". Actual cost was investigated over a decade ago: How expensive are exceptions in C#?
In other words, the cost of a connection and query of a database is likely to dwarf that of a thrown exception.
All that aside, I wanted to determine which method truly is faster. As expected there is no concrete answer.
Any code that loops over the columns becomes slower as the number of columns increase. It can also be said that any code that relies on exceptions will slow depending on the rate in which the query fails to be found.
Taking the answers of both Chad Grant and Matt Hamilton, I ran both methods with up to 20 columns and up to a 50% error rate (the OP indicated he was using this two test between different stored procedures, so I assumed as few as two).
Here are the results, plotted with LINQPad:
The zigzags here are fault rates (column not found) within each column count.
Over narrower result sets, looping is a good choice. However, the GetOrdinal/Exception method is not nearly as sensitive to number of columns and begins to outperform the looping method right around 11 columns.
That said, I don't really have a preference performance wise as 11 columns sounds reasonable as an average number of columns returned over an entire application. In either case we're talking about fractions of a millisecond here.
However, from a code simplicity aspect, and alias support, I'd probably go with the GetOrdinal route.
Here is the test in LINQPad form. Feel free to repost with your own method:
void Main()
{
var loopResults = new List<Results>();
var exceptionResults = new List<Results>();
var totalRuns = 10000;
for (var colCount = 1; colCount < 20; colCount++)
{
using (var conn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDb;Initial Catalog=master;Integrated Security=True;"))
{
conn.Open();
//create a dummy table where we can control the total columns
var columns = String.Join(",",
(new int[colCount]).Select((item, i) => $"'{i}' as col{i}")
);
var sql = $"select {columns} into #dummyTable";
var cmd = new SqlCommand(sql,conn);
cmd.ExecuteNonQuery();
var cmd2 = new SqlCommand("select * from #dummyTable", conn);
var reader = cmd2.ExecuteReader();
reader.Read();
Func<Func<IDataRecord, String, Boolean>, List<Results>> test = funcToTest =>
{
var results = new List<Results>();
Random r = new Random();
for (var faultRate = 0.1; faultRate <= 0.5; faultRate += 0.1)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var faultCount=0;
for (var testRun = 0; testRun < totalRuns; testRun++)
{
if (r.NextDouble() <= faultRate)
{
faultCount++;
if(funcToTest(reader, "colDNE"))
throw new ApplicationException("Should have thrown false");
}
else
{
for (var col = 0; col < colCount; col++)
{
if(!funcToTest(reader, $"col{col}"))
throw new ApplicationException("Should have thrown true");
}
}
}
stopwatch.Stop();
results.Add(new UserQuery.Results{
ColumnCount = colCount,
TargetNotFoundRate = faultRate,
NotFoundRate = faultCount * 1.0f / totalRuns,
TotalTime=stopwatch.Elapsed
});
}
return results;
};
loopResults.AddRange(test(HasColumnLoop));
exceptionResults.AddRange(test(HasColumnException));
}
}
"Loop".Dump();
loopResults.Dump();
"Exception".Dump();
exceptionResults.Dump();
var combinedResults = loopResults.Join(exceptionResults,l => l.ResultKey, e=> e.ResultKey, (l, e) => new{ResultKey = l.ResultKey, LoopResult=l.TotalTime, ExceptionResult=e.TotalTime});
combinedResults.Dump();
combinedResults
.Chart(r => r.ResultKey, r => r.LoopResult.Milliseconds * 1.0 / totalRuns, LINQPad.Util.SeriesType.Line)
.AddYSeries(r => r.ExceptionResult.Milliseconds * 1.0 / totalRuns, LINQPad.Util.SeriesType.Line)
.Dump();
}
public static bool HasColumnLoop(IDataRecord dr, string columnName)
{
for (int i = 0; i < dr.FieldCount; i++)
{
if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
return true;
}
return false;
}
public static bool HasColumnException(IDataRecord r, string columnName)
{
try
{
return r.GetOrdinal(columnName) >= 0;
}
catch (IndexOutOfRangeException)
{
return false;
}
}
public class Results
{
public double NotFoundRate { get; set; }
public double TargetNotFoundRate { get; set; }
public int ColumnCount { get; set; }
public double ResultKey {get => ColumnCount + TargetNotFoundRate;}
public TimeSpan TotalTime { get; set; }
}
The key to the whole problem is here:
if (-1 == index) {
throw ADP.IndexOutOfRange(fieldName);
}
If the referenced three lines (currently lines 72, 73, and 74) are taken out, then you can easily check for -1 in order to determine if the column doesn't exist.
The only way around this while ensuring native performance is to use a Reflection based implementation, like the following:
Usings:
using System;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Compilation; // I'm not sure what the .NET Core equivalent to BuildManager.cs
The Reflection based extension method:
/// Gets the column ordinal, given the name of the column.
/// </summary>
/// <param name="reader"></param>
/// <param name="name">The name of the column.</param>
/// <returns> The zero-based column ordinal. -1 if the column does not exist.</returns>
public static int GetOrdinalSoft(this SqlDataReader reader, string name)
{
try
{
// Note that "Statistics" will not be accounted for in this implemenation
// If you have SqlConnection.StatisticsEnabled set to true (the default is false), you probably don't want to use this method
// All of the following logic is inspired by the actual implementation of the framework:
// https://referencesource.microsoft.com/#System.Data/fx/src/data/System/Data/SqlClient/SqlDataReader.cs,d66096b6f57cac74
if (name == null)
throw new ArgumentNullException("fieldName");
Type sqlDataReaderType = typeof(SqlDataReader);
object fieldNameLookup = sqlDataReaderType.GetField("_fieldNameLookup", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(reader);
Type fieldNameLookupType;
if (fieldNameLookup == null)
{
MethodInfo checkMetaDataIsReady = sqlDataReaderType.GetRuntimeMethods().First(x => x.Name == "CheckMetaDataIsReady" && x.GetParameters().Length == 0);
checkMetaDataIsReady.Invoke(reader, null);
fieldNameLookupType = BuildManager.GetType("System.Data.ProviderBase.FieldNameLookup", true, false);
ConstructorInfo ctor = fieldNameLookupType.GetConstructor(new[] { typeof(SqlDataReader), typeof(int) });
fieldNameLookup = ctor.Invoke(new object[] { reader, sqlDataReaderType.GetField("_defaultLCID", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(reader) });
}
else
fieldNameLookupType = fieldNameLookup.GetType();
MethodInfo indexOf = fieldNameLookupType.GetMethod("IndexOf", BindingFlags.Public | BindingFlags.Instance, null, new Type[] { typeof(string) }, null);
return (int)indexOf.Invoke(fieldNameLookup, new object[] { name });
}
catch
{
// .NET Implemenation might have changed, revert back to the classic solution.
if (reader.FieldCount > 11) // Performance observation by b_levitt
{
try
{
return reader.GetOrdinal(name);
}
catch
{
return -1;
}
}
else
{
var exists = Enumerable.Range(0, reader.FieldCount).Any(i => string.Equals(reader.GetName(i), name, StringComparison.OrdinalIgnoreCase));
if (exists)
return reader.GetOrdinal(name);
else
return -1;
}
}
}
I would recommend using try{} catch{} for this simple issue. However, I would not recommend handling exception in catch.
try
{
if (string.IsNullOrEmpty(reader["Name"].ToString()))
{
name = reader["Name"].ToString();
}
}
catch
{
//Do nothing
}
You can also call GetSchemaTable() on your DataReader if you want the list of columns and you don't want to have to get an exception...
Use:
if (dr.GetSchemaTable().Columns.Contains("accounttype"))
do something
else
do something
It probably would not be as efficient in a loop.
Although there is no publicly exposed method, a method does exist in the internal class System.Data.ProviderBase.FieldNameLookup which SqlDataReader relies on.
In order to access it and get native performance, you must use the ILGenerator to create a method at runtime. The following code will give you direct access to int IndexOf(string fieldName) in the System.Data.ProviderBase.FieldNameLookup class as well as perform the book keeping that SqlDataReader.GetOrdinal()does so that there is no side effect. The generated code mirrors the existing SqlDataReader.GetOrdinal() except that it calls FieldNameLookup.IndexOf() instead of FieldNameLookup.GetOrdinal(). The GetOrdinal() method calls to the IndexOf() function and throws an exception if -1 is returned, so we bypass that behavior.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Reflection.Emit;
public static class SqlDataReaderExtensions {
private delegate int IndexOfDelegate(SqlDataReader reader, string name);
private static IndexOfDelegate IndexOf;
public static int GetColumnIndex(this SqlDataReader reader, string name) {
return name == null ? -1 : IndexOf(reader, name);
}
public static bool ContainsColumn(this SqlDataReader reader, string name) {
return name != null && IndexOf(reader, name) >= 0;
}
static SqlDataReaderExtensions() {
Type typeSqlDataReader = typeof(SqlDataReader);
Type typeSqlStatistics = typeSqlDataReader.Assembly.GetType("System.Data.SqlClient.SqlStatistics", true);
Type typeFieldNameLookup = typeSqlDataReader.Assembly.GetType("System.Data.ProviderBase.FieldNameLookup", true);
BindingFlags staticflags = BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Static;
BindingFlags instflags = BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
DynamicMethod dynmethod = new DynamicMethod("SqlDataReader_IndexOf", typeof(int), new Type[2]{ typeSqlDataReader, typeof(string) }, true);
ILGenerator gen = dynmethod.GetILGenerator();
gen.DeclareLocal(typeSqlStatistics);
gen.DeclareLocal(typeof(int));
// SqlStatistics statistics = (SqlStatistics) null;
gen.Emit(OpCodes.Ldnull);
gen.Emit(OpCodes.Stloc_0);
// try {
gen.BeginExceptionBlock();
// statistics = SqlStatistics.StartTimer(this.Statistics);
gen.Emit(OpCodes.Ldarg_0); //this
gen.Emit(OpCodes.Call, typeSqlDataReader.GetProperty("Statistics", instflags | BindingFlags.GetProperty, null, typeSqlStatistics, Type.EmptyTypes, null).GetMethod);
gen.Emit(OpCodes.Call, typeSqlStatistics.GetMethod("StartTimer", staticflags | BindingFlags.InvokeMethod, null, new Type[] { typeSqlStatistics }, null));
gen.Emit(OpCodes.Stloc_0); //statistics
// if(this._fieldNameLookup == null) {
Label branchTarget = gen.DefineLabel();
gen.Emit(OpCodes.Ldarg_0); //this
gen.Emit(OpCodes.Ldfld, typeSqlDataReader.GetField("_fieldNameLookup", instflags | BindingFlags.GetField));
gen.Emit(OpCodes.Brtrue_S, branchTarget);
// this.CheckMetaDataIsReady();
gen.Emit(OpCodes.Ldarg_0); //this
gen.Emit(OpCodes.Call, typeSqlDataReader.GetMethod("CheckMetaDataIsReady", instflags | BindingFlags.InvokeMethod, null, Type.EmptyTypes, null));
// this._fieldNameLookup = new FieldNameLookup((IDataRecord)this, this._defaultLCID);
gen.Emit(OpCodes.Ldarg_0); //this
gen.Emit(OpCodes.Ldarg_0); //this
gen.Emit(OpCodes.Ldarg_0); //this
gen.Emit(OpCodes.Ldfld, typeSqlDataReader.GetField("_defaultLCID", instflags | BindingFlags.GetField));
gen.Emit(OpCodes.Newobj, typeFieldNameLookup.GetConstructor(instflags, null, new Type[] { typeof(IDataReader), typeof(int) }, null));
gen.Emit(OpCodes.Stfld, typeSqlDataReader.GetField("_fieldNameLookup", instflags | BindingFlags.SetField));
// }
gen.MarkLabel(branchTarget);
gen.Emit(OpCodes.Ldarg_0); //this
gen.Emit(OpCodes.Ldfld, typeSqlDataReader.GetField("_fieldNameLookup", instflags | BindingFlags.GetField));
gen.Emit(OpCodes.Ldarg_1); //name
gen.Emit(OpCodes.Call, typeFieldNameLookup.GetMethod("IndexOf", instflags | BindingFlags.InvokeMethod, null, new Type[] { typeof(string) }, null));
gen.Emit(OpCodes.Stloc_1); //int output
Label leaveProtectedRegion = gen.DefineLabel();
gen.Emit(OpCodes.Leave_S, leaveProtectedRegion);
// } finally {
gen.BeginFaultBlock();
// SqlStatistics.StopTimer(statistics);
gen.Emit(OpCodes.Ldloc_0); //statistics
gen.Emit(OpCodes.Call, typeSqlStatistics.GetMethod("StopTimer", staticflags | BindingFlags.InvokeMethod, null, new Type[] { typeSqlStatistics }, null));
// }
gen.EndExceptionBlock();
gen.MarkLabel(leaveProtectedRegion);
gen.Emit(OpCodes.Ldloc_1);
gen.Emit(OpCodes.Ret);
IndexOf = (IndexOfDelegate)dynmethod.CreateDelegate(typeof(IndexOfDelegate));
}
}
This works to me:
public static class DataRecordExtensions
{
public static bool HasColumn(IDataReader dataReader, string columnName)
{
dataReader.GetSchemaTable().DefaultView.RowFilter = $"ColumnName= '{columnName}'";
return (dataReader.GetSchemaTable().DefaultView.Count > 0);
}
}
Use:
if(Enumerable.Range(0,reader.FieldCount).Select(reader.GetName).Contains("columName"))
{
employee.EmployeeId= Utility.ConvertReaderToLong(reader["EmployeeId"]);
}
You can get more details from Can you get the column names from a SqlDataReader?.