LINQ to DataTable
We have been adopting Linq slowly but surely and have run into some issues we never had to deal with in the past. Currently I use DataSets in VS2005 for everything. These are nice since they return DataTables easily and allow updates / inserts / etc without issue.
When we dove into Linq and had to return a DataTable for the first time we were stuck. Looking through examples & searching online did not return much. I did find an example by Kevin Goff in CoDe Magazine which outlined 13 tips for Linq. This worked great except I had some issues with null datatypes. Those issues should be fixed in the sample below.
The following code outlines a quick way using Linq to query a database and return the results to a DataTable.
Here is my newly created class:
static
public class ConvertDataTable{
public static DataTable ToADOTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn){
DataTable dtReturn = new DataTable(); // Could add a check to verify that there is an element 0T TopRec = varlist.ElementAt(0);
// Use reflection to get property names, to create table // column namesPropertyInfo[] oProps =((
Type)TopRec.GetType()).GetProperties();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)); }foreach (T rec in varlist){
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);}
public delegate object[] CreateRowDelegate<T>(T t);}
Here is the usage in code:
LData.MyDataContext db = new LData.MyDataContext(); var query = from p in db.Extensionswhere p.DepartmentNumber == "412"select p; DataTable dt = query.ToADOTable(rec => new object[] { query });
Hope this helps someone else out.
~ Steve