Methods to convert a Linq output query to a DataTable

The following extension method is a sample of how to convert a Linq output query to a DataTable. This example creates and returns a new DataTable object.

public static class ExtensionMethods
{
   public static DataTable ToADOTable<T>(this IEnumerable<T> varlist)
   {    
      // create a new instance of a DataTable object
      DataTable dtReturn = new DataTable();     
                     
      // column names    
      PropertyInfo[] oProps = null;    
                     
      if (varlist == null) 
         return dtReturn;
         
      // iterate through each of the items (rows) in the object passed in.
      foreach (T rec in varlist)
      {
         // Use reflection to get property names, to create table columns, 
         // Only first time, others will follow
         if (oProps == null)
         {
            // get all the properties of the current record.
            oProps = ((Type)rec.GetType()).GetProperties();

            // iterate through each of the row properties.   
            foreach (PropertyInfo pi in oProps)
            {
               Type colType = pi.PropertyType;
               if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
               {
                  colType = colType.GetGenericArguments()[0];
               }
               
               dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
            }
         }
         
         DataRow dr = dtReturn.NewRow();
            
         foreach (PropertyInfo pi in oProps)
         {
            dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
         }
            
         dtReturn.Rows.Add(dr);
      }     
                     
      return dtReturn;
   }    
}

The following sample takes the output from a Linq query and outputs it to an existing DataTable. Only columns that match are added to the destination table. Columns in the destination table that don't exist in the source are left as null.

public static class ExtensionMethods
{
   public static void ToADOTable<T>(this IEnumerable<T> varlist, DataTable tblOutput)
   {
      do
      {
         if (varlist == null) break;
         if (tblOutput == null) break;
         
         if (varlist == tblOutput)
         {
            throw new System.Exception("In call to ToADOTable(DataTable), source and destination are the same!");
         }
      
         // column names of the source
         Type oTypeInput = null;
         
         // iterate through each of the rows in the input table.
         foreach (T recInput in varlist)
         {
             // create a new row.
             DataRow drOutput = tblOutput.NewRow();
             
             // initialize all the column values
             csClasses.Classes.Utility.ClearRow(drOutput);
             
             if (oTypeInput == null)
             {
                oTypeInput = (Type)recInput.GetType();
             }
             
             System.Diagnostics.Debug.Indent();
             
             // set the column values.
             // iterate through each of the columns in the destination table.
             foreach (DataColumn colOutput in drOutput.Table.Columns)
             {
                //if (col.ReadOnly) continue;
                
                // get the column name.
                string strColumnName = colOutput.ColumnName;
                Type oTypeOutput = colOutput.DataType;
                if (oTypeOutput == typeof(byte[])) continue;       // put this here to ignore timestamp column. May need to revisit later.
                
                //System.Diagnostics.Debug.WriteLine(string.Format("column name: '{0}'", strColumnName));
                
                // look for the column in the source
                PropertyInfo piInputColumn = oTypeInput.GetProperty(strColumnName);
                // if not found, skip to next column
                if (piInputColumn == null) continue;
                
                MethodInfo miNullTest = oTypeInput.GetMethod(string.Format("Is{0}Null", strColumnName));
                if (miNullTest != null)
                {
                   object oNullTestResult = miNullTest.Invoke(recInput, new object[] { });
                   
                   if (oNullTestResult.ToBoolean())
                   {
                      drOutput[strColumnName] = DBNull.Value;
                      
                      continue;
                   }
                }
                
                // get the object value.
                object objVal = piInputColumn.GetValue(recInput, null);
                
                // set the value in the column
                drOutput[strColumnName] = objVal ?? DBNull.Value;
             }
             
             System.Diagnostics.Debug.Unindent();
             
             // add the row to the table.
             tblOutput.Rows.Add(drOutput);
         }
         
         // accept the changes to the output table.
         tblOutput.AcceptChanges();
         
         } while (false);
      }                     
   }
}