Issues when using Reflection and IDataReader.GetSchemaTable to create a generic method that reads and processes an IDataReader's current result set
I am writing a class that encapsulates the complexity of retrieving data from a database using ADO.NET. Its core method is
private void Read<T>(Action<T> action) where T : class, new() {
var matches = new LinkedList<KeyValuePair<int, PropertyInfo>>();
// Read the current result set's metadata.
using (DataTable schema = this.reader.GetSchemaTable()) {
DataRowCollection fields = schema.Rows;
// Retrieve the target type's properties.
// This is functionally equivalent to typeof(T).GetProperties(), but
// previously retrieved PropertyInfo[]s are memoized for efficiency.
var properties = ReflectionHelper.GetProperties(typeof(T));
// Attempt to match the target type's columns...
foreach (PropertyInfo property in properties)开发者_如何学JAVA {
string name = property.Name;
Type type = property.PropertyType;
// ... with the current result set's fields...
foreach (DataRow field in fields) {
// ... according to their names and types.
if ((string)field["ColumnName"] == name && field["DataType"] == type) {
// Store all successful matches in memory.
matches.AddLast(new KeyValuePair<int, PropertyInfo>((int)field["ColumnOrdinal"], property));
fields.Remove(field);
break;
}
}
}
}
// For each row, create an instance of the target type and set its
// properties to the row's values for their matched fields.
while (this.reader.Read()) {
T result = new T();
foreach (var match in matches)
match.Value.SetValue(result, this.reader[match.Key], null);
action(result);
}
// Go to the next result set.
this.reader.NextResult();
}
Regarding the method's correctness, which unfortunately I cannot test right now, I have the following questions:
When a single
IDataReader
is used to retrieve data from two or more result sets, doesIDataReader.GetSchemaTable
return the metadata of all result sets, or just the metadata corresponding to the current result set?Are the column ordinals retrieved by
IDataReader.GetSchemaTable
equal to the ordinals used by the indexerIDataReader[int]
? If not, is there any way to map the former into the latter?
Regarding the method's efficiency, I have the following question:
- What is
DataRowCollection
's underlying data structure? Even if that question cannot eb answered, at least, what is the asymptotic computational complexity of removing aDataRow
from aDataRowCollection
usingDataRowCollection.Remove()
?
And, regarding the method's evident ugliness, I have the following questions:
Is there any way to retrieve specific metadata (e.g., just the columns' ordinals, names and types), not the full blown schema table, from an
IDataReader
?Is the cast to
string
in(string)field["ColumnName"] == name
necessary? How does .NET compare anobject
variable that happens to contain a reference to astring
to astring
variable: by reference value or by internal data value? (When in doubt, I prefer to err on the side of correctness, thus the cast; but, when able to remove all doubt, I prefer to do so.)Even though I am using
KeyValuePair<int, PropertyInfo>
s to represent pairs of matched fields and properties, those pairs are not actual key-value pairs. They are just plain-old ordinary 2-tuples. However, version 2.0 of the .NET Framework does not provide a tuple data type, and, if I were to create my own special purpose tuple, I still would not know where to declare it. In C++, the most natural place would be inside the method. But this is C# and in-method type definitions are illegal. What should I do? Cope with the inelegance of using a type that, by definition, is not the most appropriate (KeyValuePair<int, PropertyInfo>
) or cope with the inability to declare a type where it fits best?
As far as A1, I believe that until IDataReader.NextResult()
is envoked, the GetSchemaTable
will only return the information for the current resultset.
Then when NextResult()
is envoked, you would have to do a GetSchemaTable
again to get the information about the current resultset.
HTH.
I can answer a couple of these:
A2) Yes, the column ordinals that come out of GetSchemaTable
are the same column ordinals that are used for the indexer.
B1) I'm not sure, but it won't matter, because you'll throw if you remove from the DataRowCollection
while you're enumerating it in the foreach
. If I were you, I'd make a hash table of the fields or the properties to help match them up instead of worrying about this linear-search-with-removal.
EDIT: I was wrong, this is a lie -- as Eduardo points out below, it won't throw. But it's still sort of slow if you think you might ever have a type with more than a few dozen properties.
C2) Yes, it's necessary, or else it would compare by reference.
C3) I would be inclined to use KeyValuePair
anyway.
精彩评论