Databases
StackOverflow2013
Postsdbo
POGeneric DbDataReader to List<T> mapping
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGeneric DbDataReader to List<T> mapping
    primarykey
    data
    text
    <p>I am having a slight issue (more like an annoyance) with my property binding data access classes. The problem is that the mapping fails when there exists no column in the reader for corresponding property in class.</p> <h1>Code</h1> <p>Here is the mapper class:</p> <pre><code>// Map our datareader object to a strongly typed list private static IList&lt;T&gt; Map&lt;T&gt;(DbDataReader dr) where T : new() { try { // initialize our returnable list List&lt;T&gt; list = new List&lt;T&gt;(); // fire up the lamda mapping var converter = new Converter&lt;T&gt;(); while (dr.Read()) { // read in each row, and properly map it to our T object var obj = converter.CreateItemFromRow(dr); // add it to our list list.Add(obj); } // reutrn it return list; } catch (Exception ex) { return default(List&lt;T&gt;); } } </code></pre> <p>Converter class:</p> <pre><code>/// &lt;summary&gt; /// Converter class to convert returned Sql Records to strongly typed classes /// &lt;/summary&gt; /// &lt;typeparam name="T"&gt;Type of the object we'll convert too&lt;/typeparam&gt; internal class Converter&lt;T&gt; where T : new() { // Concurrent Dictionay objects private static ConcurrentDictionary&lt;Type, object&gt; _convertActionMap = new ConcurrentDictionary&lt;Type, object&gt;(); // Delegate action declaration private Action&lt;IDataReader, T&gt; _convertAction; // Build our mapping based on the properties in the class/type we've passed in to the class private static Action&lt;IDataReader, T&gt; GetMapFunc() { var exps = new List&lt;Expression&gt;(); var paramExp = Expression.Parameter(typeof(IDataReader), "o7thDR"); var targetExp = Expression.Parameter(typeof(T), "o7thTarget"); var getPropInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(string) }); var _props = typeof(T).GetProperties(); foreach (var property in _props) { var getPropExp = Expression.MakeIndex(paramExp, getPropInfo, new[] { Expression.Constant(property.Name, typeof(string)) }); var castExp = Expression.TypeAs(getPropExp, property.PropertyType); var bindExp = Expression.Assign(Expression.Property(targetExp, property), castExp); exps.Add(bindExp); } // return our compiled mapping, this will ensure it is cached to use through our record looping return Expression.Lambda&lt;Action&lt;IDataReader, T&gt;&gt;(Expression.Block(exps), new[] { paramExp, targetExp }).Compile(); } internal Converter() { // Fire off our mapping functionality _convertAction = (Action&lt;IDataReader, T&gt;)_convertActionMap.GetOrAdd(typeof(T), (t) =&gt; GetMapFunc()); } internal T CreateItemFromRow(IDataReader dataReader) { T result = new T(); _convertAction(dataReader, result); return result; } } </code></pre> <h1>Exception</h1> <pre><code>System.IndexOutOfRangeException {"Mileage"} </code></pre> <h1>Stacktrace</h1> <pre><code>at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_Item(String name) at lambda_method(Closure , IDataReader , Typing ) at o7th.Class.Library.Data.Converter`1.CreateItemFromRow(IDataReader dataReader) in d:\Backup Folder\Development\o7th Web Design\o7th.Class.Library.C-Sharp\o7th.Class.Library\Data Access Object\Converter.cs:line 50 at o7th.Class.Library.Data.Wrapper.Map[T](DbDataReader dr) in d:\Backup Folder\Development\o7th Web Design\o7th.Class.Library.C-Sharp\o7th.Class.Library\Data Access Object\Wrapper.cs:line 33 </code></pre> <h1>Question</h1> <p>How can I fix it, so that it will not fail when I have an extra property that the reader may not have as column and vice versa? Of course the quick band-aid would be to simply add <code>NULL As Mileage</code> to this query in example, however, this is not a solution to the problem :)</p> <hr> <p>Here's <code>Map&lt;T&gt;</code> using reflection:</p> <pre><code>// Map our datareader object to a strongly typed list private static IList&lt;T&gt; Map&lt;T&gt;(DbDataReader dr) where T : new() { try { // initialize our returnable list List&lt;T&gt; list = new List&lt;T&gt;(); T item = new T(); PropertyInfo[] properties = (item.GetType()).GetProperties(); while (dr.Read()) { int fc = dr.FieldCount; for (int j = 0; j &lt; fc; ++j) { var pn = properties[j].Name; var gn = dr.GetName(j); if (gn == pn) { properties[j].SetValue(item, dr[j], null); } } list.Add(item); } // return it return list; } catch (Exception ex) { // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property _Msg += "Wrapper.Map Exception: " + ex.Message; ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.Map", _Msg); // make sure this method returns a default List return default(List&lt;T&gt;); } } </code></pre> <p><strong>Note:</strong> This method is 63% slower than using expression trees...</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USnawfal
    1. USKevin
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGeneric DbDataReader to List<T> mapping
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POGeneric DbDataReader to List<T> mapping
      1. USKevin
      1. VTFavorite
    3. VO
      singulars
      1. POGeneric DbDataReader to List<T> mapping
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou could at _least_ post the `Map` method.
      singulars
      1. POGeneric DbDataReader to List<T> mapping
      1. USD Stanley
    2. CO@o7thWebDesign I hope you realise the issue. It's with the fact that there exists no column in the reader for the specified property. An alternative is to loop by the column names of db first, and check to see if matching property exists. I will post a rough solution.
      singulars
      1. POGeneric DbDataReader to List<T> mapping
      1. USnawfal
    3. CO@o7thWebDesign I dont think expressions would make it *much* more efficient. The one area I see benefit is when you're `new`-ing a new object, but then there you're not using expression trees at all. Why do you think expressions would speed up things? Expressions should be used only to avoid boilerplate object creation code.
      singulars
      1. POGeneric DbDataReader to List<T> mapping
      1. USnawfal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload