Extract from DataRow or DataReader with one function

Question

I'm looking for a solution for how to be able to extract data from a database when using either a DataRow and a DataReader with only one function (or one base function).

My problem stems from the fact that sometimes I need a DataReader and sometimes I need a DataTable/DataRow but then in order to extract the data from those objects I need two seperate Data access methods because they do not share an interface.

Basically when my database structure changes, I don't want to have to go in and write the following data retrieval code in multiple functions:

someValue = dr["someValue"]

It's the same syntax and does the same thing so I want a function that shares that functionality regardless of whether I'm using a DataReader or DataTable/DataRow to extract the data from the database.

Answer 1

You can use CreateDataReader method in DataTable class to access data through DbDataReader base class. Hence you can change the implementation but keep the mapping.

public List<MyType> GetMyTypeCollection(DbDataReader reader)
{
//mapping code here
}

It would be better if you can move to an ORM where you do not have to map manually.

Take a look at this micro ORM Dapper

Answer 2

Use this article to convert the datareader to a datatable and then you can interface both as a datatable

So you would basically add this function that get's called from your dataLayer:

public DataTable ConvertDataReader(SqlDataReader dr)
{
  SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
  DataTable dtSchema = dr.GetSchemaTable();
  DataTable dt = new DataTable();

  // You can also use an ArrayList instead of List<>
  List<DataColumn> listCols = new List<DataColumn>();            
  if(dtSchema != null) 
  {
     foreach (DataRow drow in dtSchema.Rows)
     {
        string columnName = System.Convert.ToString(drow["ColumnName"]); 
        DataColumn column = new DataColumn(columnName, 
                               (Type)(drow["DataType"]));
        column.Unique = (bool)drow["IsUnique"];
        column.AllowDBNull = (bool)drow["AllowDBNull"];
        column.AutoIncrement = (bool)drow["IsAutoIncrement"];
        listCols.Add(column);
        dt.Columns.Add(column);
     }
  }

  // Read rows from DataReader and populate the DataTable 
  while (dr.Read())
  {
    DataRow dataRow = dt.NewRow();
    for(int i = 0; i < listCols.Count; i++)
    {
      dataRow[((DataColumn)listCols[i])] = dr[i];
    }
    dt.Rows.Add(dataRow);
  }
}

And then in your function where you get the datatable, you would then do an if it's a dataReader, pass the reader to the function to return a datatable:

DataTable dtFromReader = ConvertDataReader(dr);