Convert DataRow value to strongly typed value

Question

I want to convert a value from a DataRow to a strongly typed variable. I thought about something like:

int id = row.Field<int>("ID");

But the problem is, that our standard Db-Select returns a DataTable where every value is type of string. Means every value is object there and columns doesn't have specific type. By this Field<T> throws InvalidCastException because i try to cast object to int.

So i did something by my own:

    public static T ConvertTo<T>(this DataRow row, string columnName)
    {
        T returnValue = default(T);

        Type typ = Nullable.GetUnderlyingType(typeof(T));

        if ((row != null) &&
            (row[columnName] != null) &&
            (!Convert.IsDBNull(row[columnName])))
        {
            if (typ == null)
            {
                returnValue = (T)Convert.ChangeType(row[columnName], typeof(T));
            }
            else
            {
                returnValue = (T)Convert.ChangeType(row[columnName], typ);
            }
        }

        return returnValue;
    }

This method works as expected. I can convert nullable values, strings and value types. Ok i need to catch exception if someone input bullshit like own types. But else it's fine.

But if i load a DataTable with 30000 rows and 20 Columns and want to use this method to convert each value for object creation i got huge performance lack. If iam using int id = Convert.ToInt32(row["ID"]); and so on for each value it is 5-6 times faster then my general method. But i don't like to Convert them all manually especially if there are much DBNull.Value.

I think the problem is getting the underlying type by:

Type typ = Nullable.GetUnderlyingType(typeof(T));

This is reflaction call and slow down my method. Is this right? Is there someone who got the same problem and has a fast solution maybe?

UPDATE:

Our Standard-Select works like this:

DbDataAdapter dataAdapter = new DbDataAdapter();
dataAdapter.SelectCommand = cmd;

dataSet = new DataSet();

dataAdapter.Fill(dataSet);
dtReturn = dataSet.Tables[0].Copy();

DbDataAdapter internaly uses an OracleDataAdapter.

UPDATE:

For clarification iam doing the following (just small example):

i have a class which represents a select query. For example:

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

In Database (Oracle) i have a Table "Customer" which got 2 Columns Id number(5) and Name varchar2(100).

Now i want to read all Customer and Convert them to Customer-Objects. So i read the Data via our standard Routine SqlSelect. This returns me a DataTable. The intern of this method is posted before under first UPDATE.

Now i loop over every DataRow in this DataTable and Convert this Cell value for Object creation.

List<Customer> myList = new List<Customer>();
foreach (DataRow row in SqlSelect())
{
  Customer customer = new Customer();
  customer.Id = Convert.ToInt32(row["ID"]);
  customer.Name = Convert.ToString(row["NAME"]);
  myList.Add(customer);
}

This would be fine in this way. But i want to convert like:

customer.Id = row.ConvertTo<int>("ID");

If my DataTable at row["ID"] would be int the Field Method can handle this well. Especialy if there are other columns which can be null etc. But in my case the row["ID"] (and all other values of every select) is string. So Field can't cast this. My ConvertTo does it but has huge performance lack on large tables.

Answer 1

What you do here is very strange. You have to understand, that all datatypes in the C# inherit from object. So int, int?, char, double, class MyClass, struct MyStruct, they all inherit from object.

So row[columnName] contains data of type int, then even if it is returning object, it is possible to directly cast to int.

int i = (int)row[columnName]; //when int data,

The exception here is, when the data are DBNUll, has to be tested before casting. There is really no need for the Convert class, because it is playing heavily with reflection what is source of the performance loss!

EDIT[1]: @Scott Chamberlain is right, I improved the code to make it safer for value types:

public static class IsNullable<T>
{
    private static readonly Type type = typeof(T);
    private static readonly bool is_nullable = type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>);
    public static bool Result { get { return is_nullable; } }
}


 public static class Extension
 {
     public static T CastColumnData<T>(this DataRow row,
                                       string columnName)
    {
        object obj;
        if (row == null) throw new ArgumentNullException("row is null");
        if ((obj = row[columnName]) == null) throw new ArgumentNullException("row[" + columnName + "]  is null");
        bool is_dbnull = obj == DBNull.Value;
        if (is_dbnull && !IsNullable<T>.Result) throw new InvalidCastException("Columns data are DbNull, but the T[" + typeof(T).ToString() + "] is non nullable value type");
        return is_dbnull ? default(T) : (T)obj;
     }
 }

Class Convert is here for something else, for creating equivalent value between two datatypes, that share something in common, but here it is pretty heavy stuff using lots of reflection.

object and int are not equivalent, the object is inherited by the int, even when not directly...

EDIT[2]: New updates to original question finally clarified, that all columns types are actually strings, not different kind of data types, and they have to be transformed/parsed to the requsted data types.

I am afraid, that there is no faster solution, as value types do not implement Parse and TryParse from interface with generic type. They are static methods and that makes the attempt to solve this by generics pretty problematic.

Answer 2

Does this do what you want? If errors are frequent there'll be overheard catching them, but if not I don't see that being a problem. The Convert.ChangeType probably uses reflection under the scenes so that may be a bit slow also...

public static T ConvertTo<T>( this DataRow dataRow, string columnName )
{
    var defaultValue = default( T );

    var valueOfCell = GetCellValue(dataRow, columnName);

    if ( defaultValue == null && valueOfCell == null )
    {
        return default( T );
    }

    try
    {
        return ( T )Convert.ChangeType( valueOfCell, typeof( T ) );
    }
    catch ( InvalidCastException ex )
    {
        return default( T );
    }
}