Extract from DataRow or DataReader with one function

I'm looking for a solution for how to be able to extract dat开发者_Python百科a 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.

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

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

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;
}

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.

Create an adapter to hide implementation of DataReader to work with DataTable/DataRow codes