<p>I have a very simple mapping function called "BuildEntity" that does the usual boring "left/right" coding required to dump my reader data into my domain object. (shown below) My question is this - If I don't bring back every column in this mapping as is, I get the "System.IndexOutOfRangeException" exception and wanted to know if ado.net had anything to correct this so I don't need to bring back every column with each call into SQL ... </p>

<p>What I'm really looking for is something like "IsValidColumn" so I can keep this 1 mapping function throughout my DataAccess class with all the left/right mappings defined - and have it work even when a sproc doesn't return every column listed ...</p>

<pre><code>Using reader As SqlDataReader = cmd.ExecuteReader()
  Dim product As Product
  While reader.Read()
    product = New Product()
    product.ID = Convert.ToInt32(reader("ProductID"))
    product.SupplierID = Convert.ToInt32(reader("SupplierID"))
    product.CategoryID = Convert.ToInt32(reader("CategoryID"))
    product.ProductName = Convert.ToString(reader("ProductName"))
    product.QuantityPerUnit = Convert.ToString(reader("QuantityPerUnit"))
    product.UnitPrice = Convert.ToDouble(reader("UnitPrice"))
    product.UnitsInStock = Convert.ToInt32(reader("UnitsInStock"))
    product.UnitsOnOrder = Convert.ToInt32(reader("UnitsOnOrder"))
    product.ReorderLevel = Convert.ToInt32(reader("ReorderLevel"))
    productList.Add(product)
  End While
</code></pre>

[{'answer_id': 22186, 'author': 'Slavo', 'author_id': 1801, 'author_profile': 'https://Stackoverflow.com/users/1801', 'pm_score': 0, 'selected': False, 'text': '<p>Why don\'t you use LinqToSql - everything you need is done automatically. For the sake of being general you can use any other <a href="http://en.wikipedia.org/wiki/Object-relational_mapping" rel="nofollow noreferrer">ORM tool</a> for .NET</p>\n'}, {'answer_id': 22189, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 0, 'selected': False, 'text': "<p>I would call <code>reader.GetOrdinal</code> for each field name before starting the while loop. Unfortunately <code>GetOrdinal</code> throws an <code>IndexOutOfRangeException</code> if the field doesn't exist, so it won't be very performant.</p>\n\n<p>You could probably store the results in a <code>Dictionary&lt;string, int&gt;</code> and use its <code>ContainsKey</code> method to determine if the field was supplied.</p>\n"}, {'answer_id': 22195, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 3, 'selected': False, 'text': '<p>Also check out this <a href="http://www.madprops.org/blog/another-idbcommand-extension-fill/" rel="noreferrer">extension method I wrote</a> for use on data commands:</p>\n\n<pre><code>public static void Fill&lt;T&gt;(this IDbCommand cmd,\n    IList&lt;T&gt; list, Func&lt;IDataReader, T&gt; rowConverter)\n{\n    using (var rdr = cmd.ExecuteReader())\n    {\n        while (rdr.Read())\n        {\n            list.Add(rowConverter(rdr));\n        }\n    }\n}\n</code></pre>\n\n<p>You can use it like this:</p>\n\n<pre><code>cmd.Fill(products, r =&gt; r.GetProduct());\n</code></pre>\n\n<p>Where "products" is the IList&lt;Product&gt; you want to populate, and "GetProduct" contains the logic to create a Product instance from a data reader. It won\'t help with this specific problem of not having all the fields present, but if you\'re doing a lot of old-fashioned ADO.NET like this it can be quite handy.</p>\n'}, {'answer_id': 22199, 'author': 'FantaMango77', 'author_id': 2374, 'author_profile': 'https://Stackoverflow.com/users/2374', 'pm_score': 1, 'selected': False, 'text': '<p>Use the <code>GetSchemaTable()</code> method to retrieve the metadata of the <code>DataReader</code>. The <code>DataTable</code> that is returned can be used to check if a specific column is present or not.</p>\n'}, {'answer_id': 22201, 'author': 'Tundey', 'author_id': 1453, 'author_profile': 'https://Stackoverflow.com/users/1453', 'pm_score': 1, 'selected': False, 'text': "<p>Why not just have each sproc return complete column set, using null, -1, or acceptable values where you don't have the data. Avoids having to catch IndexOutOfRangeException or re-writing everything in LinqToSql.</p>\n"}, {'answer_id': 22273, 'author': 'AlexCuse', 'author_id': 794, 'author_profile': 'https://Stackoverflow.com/users/794', 'pm_score': 0, 'selected': False, 'text': '<p>If you don\'t want to use an ORM you can also use reflection for things like this (though in this case because ProductID is not named the same on both sides, you couldn\'t do it in the simplistic fashion demonstrated here):\n<a href="http://wiki.lessthandot.com/index.php/Generic_List_Provider_in_CSharp" rel="nofollow noreferrer">List Provider in C#</a></p>\n'}, {'answer_id': 26103, 'author': 'Toran Billups', 'author_id': 2701, 'author_profile': 'https://Stackoverflow.com/users/2701', 'pm_score': 2, 'selected': True, 'text': '<p>Although connection.GetSchema("Tables") does return meta data about the tables in your database, it won\'t return everything in your sproc if you define any custom columns.  </p>\n\n<p>For example, if you throw in some random ad-hoc column like *SELECT ProductName,\'Testing\' As ProductTestName FROM dbo.Products" you won\'t see \'ProductTestName\' as a column because it\'s not in the Schema of the Products table.  To solve this, and ask for every column available in the returned data, leverage a method on the SqlDataReader object "GetSchemaTable()"</p>\n\n<p>If I add this to the existing code sample you listed in your original question, you will notice just after the reader is declared I add a data table to capture the meta data from the reader itself.  Next I loop through this meta data and add each column to another table that I use in the left-right code to check if each column exists.</p>\n\n<p><strong>Updated Source Code</strong></p>\n\n<pre><code>Using reader As SqlDataReader = cmd.ExecuteReader() \nDim table As DataTable = reader.GetSchemaTable()\nDim colNames As New DataTable()\nFor Each row As DataRow In table.Rows\n colNames.Columns.Add(row.ItemArray(0))\nNext\nDim product As Product  While reader.Read()    \nproduct = New Product()  \nIf Not colNames.Columns("ProductID") Is Nothing Then\n  product.ID = Convert.ToInt32(reader("ProductID"))\nEnd If    \nproduct.SupplierID = Convert.ToInt32(reader("SupplierID"))    \nproduct.CategoryID = Convert.ToInt32(reader("CategoryID"))    \nproduct.ProductName = Convert.ToString(reader("ProductName"))    \nproduct.QuantityPerUnit = Convert.ToString(reader("QuantityPerUnit"))    \nproduct.UnitPrice = Convert.ToDouble(reader("UnitPrice"))    \nproduct.UnitsInStock = Convert.ToInt32(reader("UnitsInStock"))    \nproduct.UnitsOnOrder = Convert.ToInt32(reader("UnitsOnOrder"))    \nproduct.ReorderLevel = Convert.ToInt32(reader("ReorderLevel"))    \nproductList.Add(product)  \nEnd While\n</code></pre>\n\n<p>This is a hack to be honest, as you <em>should</em> return every column to hydrate your object correctly.  But I thought to include this reader method as it would actually grab all the columns, even if they are not defined in your table schema.</p>\n\n<p>This approach to mapping your relational data into your domain model might cause some issues when you get into a lazy loading scenario.  </p>\n'}, {'answer_id': 20476167, 'author': 'Nat Wallbank', 'author_id': 2841771, 'author_profile': 'https://Stackoverflow.com/users/2841771', 'pm_score': 0, 'selected': False, 'text': '<p>I ended up writing my own, but this mapper is pretty good (and simple): <a href="https://code.google.com/p/dapper-dot-net/" rel="nofollow">https://code.google.com/p/dapper-dot-net/</a></p>\n'}]
['https://Stackoverflow.com/questions/22181', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']