ADO.NET Mapping from SQLDataReader to a Domain Object?

Question

I have a very simple mapping function called "BuildEntity" that does the usual boring left / right encoding needed to dump my reader data into my domain object. (shown below) My question is this: if I do not return each column in this mapping as it is, I get a "System.IndexOutOfRangeException" exception and would like to know if ado.net has something to fix this, t need to return every column with every call in SQL ...

What I'm really looking for is something like "IsValidColumn", so I can save this 1 mapping function in my DataAccess class with all the defined left / right mappings and work even if sproc does not return each in the column ...

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

Answer 1

Although connection.GetSchema ("Tables") returns metadata about tables in your database, it will not return everything in your sproc if you define any custom columns.

, - ad-hoc-, * SELECT ProductName, 'Testing' As ProductTestName FROM dbo.Products, 'ProductTestName' , . , , SqlDataReader "GetSchemaTable()"

, , . . , , , .

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

, , , . , , .

, .

Answer 2

, , :

public static void Fill<T>(this IDbCommand cmd,
    IList<T> list, Func<IDataReader, T> rowConverter)
{
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            list.Add(rowConverter(rdr));
        }
    }
}

:

cmd.Fill(products, r => r.GetProduct());

"" - IList <Product> , "GetProduct" Product . , ADO.NET, .

Answer 3

GetSchemaTable() DataReader. DataTable .

Answer 4

sproc, null, -1 , . IndexOutOfRangeException LinqToSql.

Answer 5

LinqToSql - , , . , , ORM .NET.

Answer 6

reader.GetOrdinal while. , GetOrdinal IndexOutOfRangeException, , .

, Dictionary<string, int> ContainsKey, , .

Answer 7

ORM, ( , ProductID , , ): #

Answer 8

I ended up writing my own, but this cartographer is pretty good (and simple): https://code.google.com/p/dapper-dot-net/