开发者

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:

  1. When a single IDataReader is used to retrieve data from two or more result sets, does IDataReader.GetSchemaTable return the metadata of all result sets, or just the metadata corresponding to the current result set?

  2. Are the column ordinals retrieved by IDataReader.GetSchemaTable equal to the ordinals used by the indexer IDataReader[int]? If not, is there any way to map the former into the latter?

Regarding the method's efficiency, I have the following question:

  1. What is DataRowCollection's underlying data structure? Even if that question cannot eb answered, at least, what is the asymptotic computational complexity of removing a DataRow from a DataRowCollection using DataRowCollection.Remove()?

And, regarding the method's evident ugliness, I have the following questions:

  1. 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?

  2. Is the cast to string in (string)field["ColumnName"] == name necessary? How does .NET compare an object variable that happens to contain a reference to a string to a string 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.)

  3. 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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜