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.

Solution 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

Solution 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);

Solution 3

Do you mean auto map sql query result's row to an entity? Like this?

public static List<T> ToList<T>(this IDataReader idr, int count) where T : new()
{
    if (idr == null)
        throw new ArgumentNullException("idr");

    if (idr.IsClosed)
        throw new ArgumentException("IDataReader is closed.");

    Type businessEntityType = typeof(T);
    List<T> entitys = new List<T>();
    Hashtable hashtable = new Hashtable();
    PropertyInfo[] properties = businessEntityType.GetProperties();

    int idx = 0;

    foreach (PropertyInfo info in properties)
    {
        hashtable[info.Name.ToUpper()] = info;
    }

    while (idr.Read())
    {
        if (count > 0)
            idx++;

        T newObject = new T();
        for (int index = 0; index < idr.FieldCount; index++)
        {
            PropertyInfo info = (PropertyInfo)hashtable[idr.GetName(index).ToUpper()];
            if (info != null && info.CanWrite)
            {
                try
                {
                    info.SetValue(newObject, idr.GetValue(index), null);
                }
                catch
                {

                }
            }
        }

        entitys.Add(newObject);

        if (idx > count)
            break;
    }
    return entitys;
}

Solution 4

As an alternate solution I used a private function that took a dynamic argument. Then added two public functions that took "DataRow" and "DataReader" arguments. You can use this to limit the calls to known types or interfaces.

public MyResults DoStuff(DataRow dr)
{
    return ActualDoStuff(dr);
}

public MyResults DoStuff(DataReader dr) //IDataRecord is better if just reading
{
    return ActualDoStuff(dr);
}

private MyResults ActualDoStuff(dynamic dr)
{
    var rez = new MyResults();
    rez.someValue = dr["someValue"];
    return rez;
}

A word of warning though. You may need to re-write your load code slightly as the behavior of dr content accessed via string on a dynamic is slightly different. I.e.

if(dr["someValue"] == DBNull.Value)

might need to be changed to

if(dr["someValue"] is DBNull)

But this approach still avoids the duplicated load code issue.

View more solutions

Comments

[Nate32]

Thanks this is exactly what I needed! I had no idea that method existed.

[nawfal]

Db actions has a cost. Add reflection to it, it's going to be noticeably slower. You should rely on expression trees instead of reflection. See this answer stackoverflow.com/a/19845980/661933 for e.g. And make use of generics (use dictionary instead of hashtable)