Convert SqlCommand Output to List<MyType>?

Question

I am using an ADO.NET SqlCommand with a single SqlDbType.Structured parameter to send a table-valued parameter to a sproc. The sproc returns many rows, which I need to get into a strongly-Typed List of <MyType>. What is the best way to convert the result set (whether DataTable from a DataAdapter or DataReader bits) into List<MyType>?

Elseelset

DataSets may be your best friend here but then again datasets are kinda nasty.... – Lor

It turns out this is a lot like #1465383 - thanks all! – Elseelset

Possible duplicate of How can I easily convert DataReader to List<T>? – Medallion

Answer 1

You can use LINQ with a DataReader:

var list = reader.Cast<IDataRecord>()
                 .Select(dr => new YourType { Name = dr.GetString(0), ... })
                 .ToList();

Orcinol

@Laurence: Uh, why not? If you really want to use a loop instead, you can. LINQ is not LINQ to SQL. – Orcinol

Answer 2

The most efficient way is using datareader:

var items = new LinkedList<MyClass>();
using(var connection = GetConnection()) {
    using(var cmd = connection.CreateCommand()){
        cmd.CommandText = "... your SQL statement ...";
        // ... add parameters

        cnn.Open();
        using(var reader = cmd.ExecuteReader()) {
            // accessing values via number index is most efficient

            //gets index of column with name "PrimaryKey"
            var ndxPrimaryKey = reader.GetOrdinal("PrimaryKey");
            var ndxColumn1 = reader.GetOrdinal("Column1");
            var ndxColumn2 = reader.GetOrdinal("Column2");

            while(reader.Read()) {
                var item = new MyClass();

                // returns value of column "PrimaryKey" typed to nullable Guid
                item.PrimaryKey = reader.GetValue(ndxPrimaryKey) as Guid?; 
                item.Column1 = reader.GetValue(ndxColumn1) as string;
                item.Column2 = reader.GetValue(ndxColumn2) as int?;

                items.AddLast(item);
            }
        }
        cnn.Close();
    }
}

return items;

Omphalos

Answer 3

i think you can use Dapper to convert a query to a class.

for more information see my answer in this link

Conflagration