In my previous post SqlBulkCopy I wrote about how to load data from .NET code into SQL Server database using efficient BULK INSERT command. This class needs a DataTable or IDataReader instance as a source. I’ve collected a couple of examples how to convert List to DataTable.
1. Variation of Marc Gravell solution.
Source: https://stackoverflow.com/a/14548027
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
public static DataTable ListToDataTable<T>(IList<T> data) { DataTable table = new DataTable(); //special handling for value types and string if (typeof(T).IsValueType || typeof(T).Equals(typeof(string))) { DataColumn dc = new DataColumn("Value"); table.Columns.Add(dc); foreach (T item in data) { DataRow dr = table.NewRow(); dr[0] = item; table.Rows.Add(dr); } } else { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); foreach (PropertyDescriptor prop in properties) { table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); } foreach (T item in data) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) { try { row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; } catch (Exception ex) { row[prop.Name] = DBNull.Value; } } table.Rows.Add(row); } } return table; } |
2. Solution created by Jennifer Hubbard, Bill Wagner, etc.
Source: How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 |
public static class CustomLINQtoDataSetMethods { public static DataTable CopyToDataTable<T>(this IEnumerable<T> source) { return new ObjectShredder<T>().Shred(source, null, null); } public static DataTable CopyToDataTable<T>(this IEnumerable<T> source, DataTable table, LoadOption? options) { return new ObjectShredder<T>().Shred(source, table, options); } } public class ObjectShredder<T> { private System.Reflection.FieldInfo[] _fi; private System.Reflection.PropertyInfo[] _pi; private System.Collections.Generic.Dictionary<string, int> _ordinalMap; private System.Type _type; // ObjectShredder constructor. public ObjectShredder() { _type = typeof(T); _fi = _type.GetFields(); _pi = _type.GetProperties(); _ordinalMap = new Dictionary<string, int>(); } /// <summary> /// Loads a DataTable from a sequence of objects. /// </summary> /// <param name="source">The sequence of objects to load into the DataTable.</param> /// <param name="table">The input table. The schema of the table must match that /// the type T. If the table is null, a new table is created with a schema /// created from the public properties and fields of the type T.</param> /// <param name="options">Specifies how values from the source sequence will be applied to /// existing rows in the table.</param> /// <returns>A DataTable created from the source sequence.</returns> public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options) { // Load the table from the scalar sequence if T is a primitive type. if (typeof(T).IsPrimitive) { return ShredPrimitive(source, table, options); } // Create a new table if the input table is null. if (table == null) { table = new DataTable(typeof(T).Name); } // Initialize the ordinal map and extend the table schema based on type T. table = ExtendTable(table, typeof(T)); // Enumerate the source sequence and load the object values into rows. table.BeginLoadData(); using (IEnumerator<T> e = source.GetEnumerator()) { while (e.MoveNext()) { if (options != null) { table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options); } else { table.LoadDataRow(ShredObject(table, e.Current), true); } } } table.EndLoadData(); // Return the table. return table; } public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options) { // Create a new table if the input table is null. if (table == null) { table = new DataTable(typeof(T).Name); } if (!table.Columns.Contains("Value")) { table.Columns.Add("Value", typeof(T)); } // Enumerate the source sequence and load the scalar values into rows. table.BeginLoadData(); using (IEnumerator<T> e = source.GetEnumerator()) { Object[] values = new object[table.Columns.Count]; while (e.MoveNext()) { values[table.Columns["Value"].Ordinal] = e.Current; if (options != null) { table.LoadDataRow(values, (LoadOption)options); } else { table.LoadDataRow(values, true); } } } table.EndLoadData(); // Return the table. return table; } public object[] ShredObject(DataTable table, T instance) { FieldInfo[] fi = _fi; PropertyInfo[] pi = _pi; if (instance.GetType() != typeof(T)) { // If the instance is derived from T, extend the table schema // and get the properties and fields. ExtendTable(table, instance.GetType()); fi = instance.GetType().GetFields(); pi = instance.GetType().GetProperties(); } // Add the property and field values of the instance to an array. Object[] values = new object[table.Columns.Count]; foreach (FieldInfo f in fi) { values[_ordinalMap[f.Name]] = f.GetValue(instance); } foreach (PropertyInfo p in pi) { values[_ordinalMap[p.Name]] = p.GetValue(instance, null); } // Return the property and field values of the instance. return values; } public DataTable ExtendTable(DataTable table, Type type) { // Extend the table schema if the input table was null or if the value // in the sequence is derived from type T. foreach (FieldInfo f in type.GetFields()) { if (!_ordinalMap.ContainsKey(f.Name)) { // Add the field as a column in the table if it doesn't exist // already. DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name] : table.Columns.Add(f.Name, f.FieldType); // Add the field to the ordinal map. _ordinalMap.Add(f.Name, dc.Ordinal); } } foreach (PropertyInfo p in type.GetProperties()) { if (!_ordinalMap.ContainsKey(p.Name)) { // Add the property as a column in the table if it doesn't exist // already. DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name] : table.Columns.Add(p.Name, p.PropertyType); // Add the property to the ordinal map. _ordinalMap.Add(p.Name, dc.Ordinal); } } // Return the table. return table; } } |