[Byrdsong]

For all you ole-school ADO.NET developers, like myself, which find yourself needing to perform some LINQ-to-Object functionality against your DataTables, but just hate all the extra baggage of "Field<int>("SomeIntField")", etc. conversions in your LINQ queries, here's a cool extension you just might like.

I extended the base "AsEnumerable()" method on the DataTable to produce an Enumerable set of some "T" object. Check out the code below...

//make sure the extensions class is static
public static class MyExtensions
{
    public static IEnumerable<T> AsEnumerable<T>(this DataTable table) where T : new()
        {
            //check for table availability
            if (table == null)
                throw new NullReferenceException("DataTable");
            
            //grab property length
            int propertiesLength = typeof(T).GetProperties().Length;

            //if no properties stop
            if (propertiesLength == 0)
                throw new NullReferenceException("Properties");

            //create list to hold object T values
            var objList = new List<T>();

            //iterate thru rows of the datatable
            foreach (DataRow row in table.Rows)
            {
                //create a new instance of our object T
                var obj = new T();

                //grab properties of object T
                PropertyInfo[] objProperties = obj.GetType().GetProperties();

                //iterate thru and populate property values
                for (int i = 0; i < propertiesLength; i++)
                {
                    //grab current property
                    PropertyInfo property = objProperties[i];

                    //check datatable to see if datacolumn exists
                    if (table.Columns.Contains(property.Name))
                    {
                        //get row cell value
                        object objValue = row[property.Name];

                        //check for nullable property type and handle
                        var propertyType = property.PropertyType;
                        if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                            propertyType = propertyType.GetGenericArguments()[0];

                        //set property value
                        objProperties[i].SetValue(obj, Convert.ChangeType(objValue, propertyType, System.Globalization.CultureInfo.CurrentCulture), null);
                    }
                }

                //add to obj list
                objList.Add(obj);
            }

            return objList;
       }
}

So you populate a DataSet using your SqlAdapter and from that, grab the DataTable out...

//So we have this Car class
public class Car
{
  public string Make{ get; set; }
  public string Model{ get; set; }
  public int Year{ get; set; }
  public string Color{ get; set; }
}

//...In some class or form or service that utilizes this process...
//this method has all the plumbing inside to fill and return a dataset
DataSet dataSet = _dataBase.ExecuteDataSet("sproc_GetMeSomeCarData");
DataTable dataTable = dataSet.Tables[0];

//wallah! now call the new extension passing the Car object for less cumbersome, normalized LINQ-to-Object syntax
var nissanCars = from c in dataTable.AsEnumerable<Car>()
                 where c.Make == "Nissan"
                 select c;

//have fun with this guys!!

This post has been edited by tlhIn`toq: 22 February 2013 - 08:49 AM