StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<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> <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> <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> <p><strong>Updated Source Code</strong></p> <pre><code>Using reader As SqlDataReader = cmd.ExecuteReader() Dim table As DataTable = reader.GetSchemaTable() Dim colNames As New DataTable() For Each row As DataRow In table.Rows colNames.Columns.Add(row.ItemArray(0)) Next Dim product As Product While reader.Read() product = New Product() If Not colNames.Columns("ProductID") Is Nothing Then product.ID = Convert.ToInt32(reader("ProductID")) End If 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> <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> <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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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