Convert Rows from a Data Reader into Typed Results

Convert rows from a data reader into typed results

Do you really need a list, or would IEnumerable be good enough?

I know you want it to be generic, but a much more common pattern is to have a static Factory method on the target object type that accepts a datarow (or IDataRecord). That would look something like this:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }

    public static Employee Create(IDataRecord record)
    {
        return new Employee
        {
           Id = record["id"],
           Name = record["name"]
        };
    }
}

.

public IEnumerable<Employee> GetEmployees()
{
    using (var reader = YourLibraryFunction())
    {
       while (reader.Read())
       {
           yield return Employee.Create(reader);
       }
    }
}

Then if you really need a list rather than an IEnumerable you can call .ToList() on the results. I suppose you could also use generics + a delegate to make the code for this pattern more re-usable as well.

Update: I saw this again today and felt like writing the generic code:

public IEnumerable<T> GetData<T>(IDataReader reader, Func<IDataRecord, T> BuildObject)
{
    try
    {
        while (reader.Read())
        {
            yield return BuildObject(reader);
        }
    }
    finally
    {
         reader.Dispose();
    }
}

//call it like this:
var result = GetData(YourLibraryFunction(), Employee.Create);

How can I easily convert DataReader to ListT?

I have seen systems that use Reflection and attributes on Properties or fields to maps DataReaders to objects. (A bit like what LinqToSql does.) They save a bit of typing and may reduce the number of errors when coding for DBNull etc. Once you cache the generated code they can be faster then most hand written code as well, so do consider the “high road” if you are doing this a lot.

See "A Defense of Reflection in .NET" for one example of this.

You can then write code like

class CustomerDTO  
{
    [Field("id")]
    public int? CustomerId;

    [Field("name")]
    public string CustomerName;
}

...

using (DataReader reader = ...)
{    
   List<CustomerDTO> customers = reader.AutoMap<CustomerDTO>()
                                    .ToList();
}

(AutoMap(), is an extension method)

---

@Stilgar, thanks for a great comment

If are able to you are likely to be better of using NHibernate, EF or Linq to Sql, etc However on old project (or for other (sometimes valid) reasons, e.g. “not invented here”, “love of stored procs” etc) It is not always possible to use a ORM, so a lighter weight system can be useful to have “up your sleeves”

If you every needed too write lots of IDataReader loops, you will see the benefit of reducing the coding (and errors) without having to change the architecture of the system you are working on. That is not to say it’s a good architecture to start with..

I am assuming that CustomerDTO will not get out of the data access layer and composite objects etc will be built up by the data access layer using the DTO objects.

---

A few years after I wrote this answer Dapper entered the world of .NET, it is likely to be a very good starting point for writing your onw AutoMapper, maybe it will completely remove the need for you to do so.

How to convert a data reader to dynamic query results

You're missing basic C# syntax.

Data = reader;
// You cant do this. You have to loop the reader to get the values from it.
// If you simply assign reader object itself as the data you wont be 
// able to get data once the reader or connection is closed. 
// The reader is typically closed in the method.

Data = reader.Cast<dynamic>;
// You should call the Cast method. And preferably execute the resulting query. 
// As of now you're merely assigning method reference to a variable
// which is not what you want. 
// Also bear in mind that, as I said before there's no real benefit in casting to dynamic

Data = reader.Cast<IEnumerable<dynamic>>;
// Cast method itself returns an IEnumerable. 
// You dont have to cast individual rows to IEnumerable

Data = reader.Cast<IEnumerable<string>>;
// Meaningless I believe. 
// The data you get from database is not always strings

The major mistake you make is not calling the method. This is what you want:

Data = reader.Cast<IDataRecord>().ToList();
                               ^^ // notice the opening and closing parentheses

---

You could go about this a number of ways depending on what is easier to process (say, to display in front-end).

1. Return data records.

   public IEnumerable<IDataRecord> SelectDataRecord()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
           foreach (IDataRecord record in reader as IEnumerable)
               yield return record; //yield return to keep the reader open
   }
   

2. Return ExpandoObjects. Perhaps this is what you wanted?

   public IEnumerable<dynamic> SelectDynamic()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
           foreach (IDataRecord record in reader as IEnumerable)
           {
               var expando = new ExpandoObject() as IDictionary<string, object>;
               foreach (var name in names)
                   expando[name] = record[name];
   
               yield return expando;
           }
       }
   }
   

3. Return sequence of property bag

   public IEnumerable<Dictionary<string, object>> SelectDictionary()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
           foreach (IDataRecord record in reader as IEnumerable)
               yield return names.ToDictionary(n => n, n => record[n]);
       }
   }
   

4. Return sequence of plain object array

   public IEnumerable<List<object>> SelectObjectArray()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var indices = Enumerable.Range(0, reader.FieldCount).ToList();
           foreach (IDataRecord record in reader as IEnumerable)
               yield return indices.Select(i => record[i]).ToList();
       }
   }
   

5. Return data rows

   public IEnumerable<DataRow> SelectDataRow()
   {
       ....
   
       using (var reader = cmd.ExecuteReader())
       {
           var table = new DataTable();
           table.BeginLoadData();
           table.Load(reader);
           table.EndLoadData();
           return table.AsEnumerable(); // in assembly: System.Data.DataSetExtensions
       }
   }
   

6. Last but not least, if it helps, you can return a strongly-typed sequence without any manual plumbing. You can use expression trees to compile code at run-time. See this for e.g.

How to get a DataRow out the current row of a DataReader?

Is there any way to extract a DataRow out the current row of a
DataReader ?

No, at least no simple way. Every DataRow belongs to one Table. You cannot leave that property empty, you cannot even change the table(without using ImportRow).

But if you need DataRows, why don't you fill a DataTable in the first place?

DataTable table = new DataTable();
using(var con = new SqlConnection("...."))
using(var da = new SqlDataAdapter("SELECT ... WHERE...", con))
    da.Fill(table);
// now you have the row(s)

If you really need to get the row(s) from the DataReader you can use reader.GetSchemaTable to get all informations about the columns:

if (reader.HasRows)
{
    DataTable schemaTable = reader.GetSchemaTable();
    DataTable data = new DataTable();
    foreach (DataRow row in schemaTable.Rows)
    {
        string colName = row.Field<string>("ColumnName");
        Type t = row.Field<Type>("DataType");
        data.Columns.Add(colName, t);
    }
    while (reader.Read())
    {
        var newRow = data.Rows.Add();
        foreach (DataColumn col in data.Columns)
        {
            newRow[col.ColumnName] = reader[col.ColumnName];
        }
    }
}

But that is not really efficient.

How to convert SqlDataReader result to generic list ListT

SqlDataReader isn't a container, it's a cursor used to load data. It can't be converted to any container type. The application code must use it to load the results and then construct the objects and place them in a list. This is described in the ADO.NET docs, eg in Retrieving data using a DataReader:

    var list=new List<Student>();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            var student=new Student();
            student.Id=reader.GetInt32(0);
            student.Name = reader.GetString(1));
            ...
        }
    }
    else
    {
        Console.WriteLine("No rows found.");
    }

That's a lot of boilerplate, which is why ORMs like Entity Framework or micro-ORMs like Dapper are used to execute queries and map the results to objects.

Using Dapper, all this code can be replaced with :

var sql="Select * from Students where Major=@major";
var students=connection.Query<Student>(sql,new {major="Computer Science"});

Dapper will create a parameterized query with the @major parameter, execute it, construct Student objects from the results and return them as an IEnumerable<Student>. It even takes care of opening and disposing the connection.

Dapper works by using Reflection to identify a type's properties, use their names to load the correct fields and assign them to the objects it creates.

Returning Data Rows to Liststring with SqlDataReader

The best option for you to do this task is DataTable, But you don't want to use it. So, the net option will be, Create a class based on the query-output then use a List<objectOftheClass>. But in your case, the Input query will be changed all times so a common class will not be meaningful Since you are trying to make it generic. So the option you can follow is List<List<string>> or List<List<object>>. As per this the method signature will be like the following:

public static List<object[]> loadSQL(string query, string connectString)
{
    List<object[]> dataList = new List<object[]>();

    using (SqlConnection connection = new SqlConnection(connectString))
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    object[] tempRow = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        tempRow[i] = reader[i];
                    }
                    dataList.Add(tempRow);
                }
            }
        }
    }
    return dataList;
}

Why List<object>? why not `List?:

The reader will give you the column data as the same type of column in the table. If it is object then you need not convert it every time.

** Note:-** Change String to string for the arguments in the method signature. You can find a reason here

I am getting data from SqlDataReader and it returns all results into 1 row

You're returning a list of strings, which basically means that no sets of values in that list are meaningfully associated with one another. For example, say you have two records:

ID    Name
----------
1     David
2     Michael

Then your resulting data would look like this:

1
David
2
Michael

Which loses all meaning of what a "record" is in the original data. There's no difference between an ID or a Name in your List<string>.

Instead, create a class to represent your data. In my example it could be as simple as:

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
}

Then return a List<Person> instead of a List<string>, thus retaining the conceptual meaning of individual records in the data. You'd build that list with something like:

while (dataReader.Read())
{
    var person = new Person();

    var id = 0;
    var idValue = dataReader.GetValue(0);
    if (int.TryParse(idValue, out id))
        person.ID = id;
    else
    {
        // ID was invalid, handle the error here
    }

    person.Name = dataReader.GetValue(1).ToString();

    people.Add(person);
}

Populate data table from data reader

You can load a DataTable directly from a data reader using the Load() method that accepts an IDataReader.

var dataReader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dataReader);

Replace DataReader data

Ok, there are two approaches common used here.

First, lets take a sample GV, and work though the two common apporaches here.

Our simple markup:

    <asp:GridView ID="GridView1" runat="server" 
        AutoGenerateColumns="False" DataKeyNames="ID" CssClass="table">
        <Columns>
            <asp:BoundField DataField="FirstName" HeaderText="FirstName"     />
            <asp:BoundField DataField="LastName" HeaderText="LastName"       />
            <asp:BoundField DataField="HotelName" HeaderText="HotelName"     />
            <asp:BoundField DataField="Description" HeaderText="Description" />
            <asp:BoundField DataField="Nights" HeaderText="Nights"  ItemStyle-HorizontalAlign="Center"          />
            <asp:BoundField DataField="Price" HeaderText="Price"  DataFormatString="{0:c2}" 
                ItemStyle-HorizontalAlign="Right" />
        </Columns>
    </asp:GridView>

Ok, and our code to load:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid()
    {
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = "SELECT * FROM tblHotels ORDER BY HotelName";
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn)) {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }

        GridView1.DataSource = rstData;
        GridView1.DataBind();
    }

Ok, and we now have this:

so, with above, we say want the the total price for each row booking to show.

So, lets use the "common" approach here. We first add our extra control to the grid view. Lets use a plane jane text box:

eg: this:

            <asp:BoundField DataField="Price" HeaderText="Price"  DataFormatString="{0:c2}" 
                ItemStyle-HorizontalAlign="Right" />

            <asp:TemplateField HeaderText="Total">
                <ItemTemplate>
                    <asp:Label ID="lblTotal" runat="server"></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>

so, we just added a label to the gv.

So, the event we use is the row data bound event This even is great, since it gives us the one data row, and the one grid view row to work with. This event also great for changing the color of a row, or text box, and of course also doing some calculation or setting up the value of a un-bound control - such as our label called

So, now in our row data bound event, we can do this:

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataRowView gData = (DataRowView)e.Row.DataItem;
            Label lblTotal = e.Row.FindControl("lblTotal") as Label;
            decimal MyPrice = (int)gData["Nights"] * (decimal)gData["Price"];
            lblTotal.Text =  string.Format("{0:c2}", MyPrice);
        }
    }

And now our gird is this:

Ok, so above is the common approach.

However, a interesting approach?

Once you fill a data table, then you are free to process that table, and that EVEN includes adding new columns to the table!!!

So, lets dump (remove) our data bound row event.

lets CHANGE the label to use bound data from the table. So, our markup is now:

            <asp:TemplateField HeaderText="Total">
                <ItemTemplate>
                    <asp:Label ID="lblTotal" runat="server"
                        Text = '<%# string.Format("{0:c2}", Eval("MyTotal")) %>'
                        ></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

Now, we don't' have a column in data table called MyTotal, do we?

but, we can AFTER getting the table from the query or stored procedure ADD the table.

so, our grid load code now becomes this:

        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = "SELECT * FROM tblHotels ORDER BY HotelName";
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn)) {
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }

        rstData.Columns.Add("MyTotal", typeof(decimal));

        foreach (DataRow MyRow in rstData.Rows)
        {
            MyRow["MyTotal"] = (int)MyRow["Nights"] * (decimal)MyRow["Price"];
        }

        GridView1.DataSource = rstData;
        GridView1.DataBind();
    }

Note how we just added a new column, and then did a table process loop to setup that value. The results of the GV are thus the same.

Last but not least?

FEW PEOPLE realize that a data table supports expressions!!!
And when you modify values etc. the update automatic. So, in place of that row processing loop above? We could in fact do this:

        DataColumn MyCol = new DataColumn("MyTotal", typeof(decimal));
        MyCol.Expression = "[Nights] * [Price]";
        rstData.Columns.Add(MyCol);

        GridView1.DataSource = rstData;
        GridView1.DataBind();

So, in most cases, I often just use the row data bound. And this event is nice since you don't write a loop, and for conditional format such as a row color change, or a column/cell format of color etc., or the new setting of the new text box? row data bound is nice.

But, you can also as noted, add columns, and loop + process the data table, and you can even add columns that are based on expressions of other columns. You then send that updated and pre-processed table to the gridview as normal.

Also note that while I used a query, a store procedure would work the same way:

eg:

        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand("MyStoreProcedure", conn)) {
                cmdSQL.CommandType = CommandType.StoredProcedure;
                cmdSQL.Parameters.Add("@Active", SqlDbType.Bit).Value = 1;
                conn.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }

        DataColumn MyCol = new DataColumn("MyTotal", typeof(decimal));
        MyCol.Expression = "[Nights] * [Price]";
        rstData.Columns.Add(MyCol);

        GridView1.DataSource = rstData;
        GridView1.DataBind();

How to convert from DbDataReader to TaskIEnumerableTEntity in Entity Framework Core 2.0?

Apart from obvious async code issues, you can't materialize DbDataReader to class by simply calling Cast. If it was possible, there wouldn't be a need of micro ORMs like Dapper and similar.

EF Core currently does not expose a public way to do that. But if TEntity is a model entity class, you can simply use the FromSql method:

private async Task<IEnumerable<TEntity>> InvokeStoredProcedureAsync(string entityName)
{
    var storedProcedureName = string.Format(CultureInfo.InvariantCulture, "sp_{0}BulkSelect", entityName);
    using (var db = new MyDbContext(_options))
    {
        var result = await db.Set<TEntity>().FromSql(storedProcedureName).ToListAsync();
        return result;
    }
}

Make sure the SP returns all expected columns by the TEntity mapping.

---

Related Topics