Ways to convert List into Datatable using C#

In a previous article, I mentioned the Difference between Visual Studio Build, Re-build and Clean, solution but in this article, I will explain a few possible ways to convert List data into Datatable using C# easily.

Suppose we have the List data like below in C#

List<Student> Students = new List<Student>(){
                new Student() {Name="Ram", Email="ram@gmail.com" },
                new Student() { Name="Mohan", Email="Mohan@gmail.com" },          
                new Student() { Name="Karan", Email="Karan@gmail.com" }
            };

Where Student is a class as below

 public class Student
    {
        public string Name { get; set; }
        public string Email{ get; set; }
       
    }

So to Convert the above List into Datatable, we can implement these ways

1. Using For loop  which handles NULL data also

   public static DataTable ToDataTable<T>(this IList<T> 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);
       }
       return table;
   }?

   can call it as below for example

   var Dtatable= ToDataTable(Students);

2. Without using For Loop, creating Datatable using from query (Linq To Dataset)
   

   // Create a sequence. 
   Item[] items = new Item[] 
   { new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"}, 
     new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
     new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
     new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};
   
   // Query for items with price greater than 9.99.
   var query = from i in items
                where i.Price > 9.99
                orderby i.Price
                select i;
   
   // Load the query results into new DataTable.
   DataTable table = query.CopyToDataTable();?

   CopyToDataTable namespace is System.Data and it returns DataTable that contains copies of DataRow objects, given an input IEnumerable<T> object.Complete details here https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/creating-a-datatable-from-a-query-linq-to-dataset

3. Creating your own function to convert Linq query to datatable, so create a code first
   

   public static DataTable LinqQueryToDataTable(IEnumerable<dynamic> v)
   {
       //We really want to know if there is any data at all
       var firstRecord = v.FirstOrDefault();
       if (firstRecord == null)
           return null;
   
      
       // We have data and we can work on it
       //So what do you have?
       PropertyInfo[] infos = firstRecord.GetType().GetProperties();
   
       //Our table should have the columns to support the properties
       DataTable table = new DataTable();
       
       //Add, add, add the columns
       foreach (var info in infos)
       {
                  
           Type propType = info.PropertyType;
           
           if (propType.IsGenericType
               && propType.GetGenericTypeDefinition() == typeof(Nullable<>)) //Nullable types should be handled too
           {
               table.Columns.Add(info.Name, Nullable.GetUnderlyingType(propType));
           }
           else
           {
               table.Columns.Add(info.Name, info.PropertyType);
           }
       }
   
       // Let's begin with rows now.
       DataRow row;
   
       foreach (var record in v)
       {
           row = table.NewRow();
           for (int i = 0; i < table.Columns.Count; i++)
           {
               row[i] = infos[i].GetValue(record) != null ? infos[i].GetValue(record) : DBNull.Value;
           }
   
           table.Rows.Add(row);
       }
   
       //Table is ready to serve.
       table.AcceptChanges();
   
       return table;
   }?

   here in the above method which will accept the LINQ query, without any idea what it would be, as a parameter. Process its contents into a "DataTable" and return it. So, to receive the query, we will be using "dynamic" datatype which is very new to the context. Then, we will find out its "Property Information" and ready our DataTable for inserting data. After that, we will iterate through the query and populate our DataTable.

   Now suppose we have the following class

   public class Item
   {
       public int Id { get; set; }
       public double Price { get; set; }
       public string Genre { get; set; }
   }
   
   public class Book : Item
   {
       public string Author { get; set; }
   }
   
   public class Movie : Item
   {
       public string Director { get; set; }
   }

   Then we can convert the LINQ query into Datatable using the code below

   / Create a sequence. 
   Item[] items = new Item[] 
   { new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"}, 
     new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
     new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
     new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};
   
   // load sequence of scalars.
   var query = from i in items
                where i.Genre == "Comedy"
                orderby i.Price
                select i;
   
   DataTable table = LinqQueryToDataTable(query);

That's it, these procedures help to convert list into datatable using C#.

You may also like to read:

Ways to Convert Datatable to List in C# (with performance test example)

Create XML document using C# (Console Application example)