Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>CopyToDataTable() only works when your query returns an IEnumerable&lt;'DataRow>. In your query, you are returning an anonymous type. Anonymous types don't carry the extension method for CopyToDataTable(). You could just select the entire row like this, assuming Result is a DataTable. Then create your anonymous type.</p> <pre><code> public static void Start() { DataTable Result = new DataTable(); var ShowResult = from r in Result.AsEnumerable() where Convert.ToInt32(r.Field&lt;double&gt;("ASLVAM") / r.Field&lt;double&gt;("GEST")) &gt; 60 orderby Convert.ToInt32(r.Field&lt;double&gt;("ASLVAM") / r.Field&lt;double&gt;("GEST")) descending select r; DataTable newDataTbl = ShowResult.CopyToDataTable(); var anonType = newDataTbl.AsEnumerable() .Select(r =&gt; new { pascode = r.Field&lt;string&gt;("PAS_CODE"), melli = r.Field&lt;string&gt;("CODEMELI"), name = r.Field&lt;string&gt;("NAM"), family = r.Field&lt;string&gt;("FAMILY"), bycode = r.Field&lt;string&gt;("BAYGANI"), jancode = r.Field&lt;string&gt;("CODEJANBAZ"), darsad = r.Field&lt;int&gt;("DARSAD"), ostan = r.Field&lt;string&gt;("OSTAN_N"), vacode = r.Field&lt;string&gt;("VA_CODE"), moin = r.Field&lt;string&gt;("VA_MOIN"), onvan = r.Field&lt;string&gt;("TAFZILI"), aslvam = r.Field&lt;double&gt;("ASLVAM"), gest = r.Field&lt;double&gt;("GEST"), //tededGestKol = Convert.ToInt32(r.Field&lt;double&gt;("ASLVAM") / r.Field&lt;double&gt;("GEST")), mandeVam = r.Field&lt;double&gt;("MANDE_VAM"), dPardakht = r.Field&lt;string&gt;("DATE_P") } ); } </code></pre> <p>In lieu of the former method, you could use the following extension methods to create a Datatable from a List&lt;'T>.</p> <pre><code> using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.ComponentModel; using System.Reflection; namespace Common { public static class DataTableExtensions { public static DataTable ConvertToDataTable&lt;T&gt;(this IList&lt;T&gt; data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); 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) row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; table.Rows.Add(row); } table.AcceptChanges(); return table; } public static DataRow ConvertToDataRow&lt;T&gt;(this T item, DataTable table) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; return row; } public static T ConvertToEntity&lt;T&gt;(this DataRow tableRow) where T : new() { // Create a new type of the entity I want Type t = typeof(T); T returnObject = new T(); foreach (DataColumn col in tableRow.Table.Columns) { string colName = col.ColumnName; // Look for the object's property with the columns name, ignore case PropertyInfo pInfo = t.GetProperty(colName.ToLower(), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance); // did we find the property ? if (pInfo != null) { object val = tableRow[colName]; // is this a Nullable&lt;&gt; type bool IsNullable = (Nullable.GetUnderlyingType(pInfo.PropertyType) != null); if (IsNullable) { if (val is System.DBNull) { val = null; } else { // Convert the db type into the T we have in our Nullable&lt;T&gt; type val = Convert.ChangeType(val, Nullable.GetUnderlyingType(pInfo.PropertyType)); } } else { // Convert the db type into the type of the property in our entity SetDefaultValue(ref val, pInfo.PropertyType); if (pInfo.PropertyType.IsEnum &amp;&amp; !pInfo.PropertyType.IsGenericType) { val = Enum.ToObject(pInfo.PropertyType, val); } else val = Convert.ChangeType(val, pInfo.PropertyType); } // Set the value of the property with the value from the db if (pInfo.CanWrite) pInfo.SetValue(returnObject, val, null); } } // return the entity object with values return returnObject; } private static void SetDefaultValue(ref object val, Type propertyType) { if (val is DBNull) { val = GetDefault(propertyType); } } public static object GetDefault(Type type) { if (type.IsValueType) { return Activator.CreateInstance(type); } return null; } public static List&lt;T&gt; ConvertToList&lt;T&gt;(this DataTable table) where T : new() { Type t = typeof(T); // Create a list of the entities we want to return List&lt;T&gt; returnObject = new List&lt;T&gt;(); // Iterate through the DataTable's rows foreach (DataRow dr in table.Rows) { // Convert each row into an entity object and add to the list T newRow = dr.ConvertToEntity&lt;T&gt;(); returnObject.Add(newRow); } // Return the finished list return returnObject; } } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload