Post
Ask Question

Converting Generic List into DataTable

In one of my recent works I needed to save a generic list into a database. So I converted that generic List into a Datatable and passed the Datatable to the stored procedure as table variable for inserting. Let us see how to convert List to Data table.

We can convert list to Datataable using reflection. Let's see the code.

  1. using System.Collections.Generic;  
  2. using System.Data;  
  3. using System.Reflection;  
  4.   
  5. namespace ListtoDataTable  
  6. {  
  7.     class Program  
  8.     {  
  9.         static void Main(string[] args)  
  10.         {  
  11.   
  12.             List<Employee> Students = new List<Employee>(){  
  13.                 new Employee() { Name = "Pradeep", salary = 15000, EmpId = 100 },  
  14.                  new Employee() { Name = "Smith", salary = 25000, EmpId = 101},  
  15.                 new Employee() { Name = "John", salary = 21000, EmpId = 102 }  
  16.             };  
  17.   
  18.             ListtoDataTable lsttodt = new ListtoDataTable();  
  19.             DataTable dt = lsttodt.ToDataTable(Students);    
  20.         }  
  21.     }  
  22.   
  23.     public class Employee  
  24.     {  
  25.         public string Name { get; set; }  
  26.         public int EmpId { get; set; }  
  27.         public int salary { get; set; }  
  28.     }  
  29.   
  30.     public class ListtoDataTable  
  31.     {  
  32.         public DataTable ToDataTable<T>(List<T> items)  
  33.         {  
  34.             DataTable dataTable = new DataTable(typeof(T).Name);  
  35.             //Get all the properties by using reflection   
  36.             PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);  
  37.             foreach (PropertyInfo prop in Props)  
  38.             {  
  39.                 //Setting column names as Property names  
  40.                 dataTable.Columns.Add(prop.Name);  
  41.             }  
  42.             foreach (T item in items)  
  43.             {  
  44.                 var values = new object[Props.Length];  
  45.                 for (int i = 0; i < Props.Length; i++)  
  46.                 {  
  47.                      
  48.                     values[i] = Props[i].GetValue(item, null);  
  49.                 }  
  50.                 dataTable.Rows.Add(values);  
  51.             }  
  52.               
  53.             return dataTable;  
  54.         }  
  55.     }  
  56. }  
Output

 

Now after converting the list to datatable we can pass the datatable to the stored procedure which can take table variable as a parameter and insert these fields into Database.

Hope you understand the concept how to convert a generic list to datatable and how to save it in Database. Thanks for reading.