Converting Generic List into DataTable

In this blog we will see how we can convert generic list into datatable and insert those into database.

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.

using System.Collections.Generic;
using System.Data;
using System.Reflection;

namespace ListtoDataTable
{
    class Program
    {
        static void Main(string[] args)
        {

            List<Employee> Students = new List<Employee>(){
                new Employee() { Name = "Pradeep", salary = 15000, EmpId = 100 },
                 new Employee() { Name = "Smith", salary = 25000, EmpId = 101},
                new Employee() { Name = "John", salary = 21000, EmpId = 102 }
            };

            ListtoDataTable lsttodt = new ListtoDataTable();
            DataTable dt = lsttodt.ToDataTable(Students);
        }
    }

    public class Employee
    {
        public string Name { get; set; }
        public int EmpId { get; set; }
        public int salary { get; set; }
    }

    public class ListtoDataTable
    {
        public DataTable ToDataTable<T>(List<T> items)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            //Get all the properties by using reflection
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                //Setting column names as Property names
                dataTable.Columns.Add(prop.Name);
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {

                    values[i] = Props[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }

            return dataTable;
        }
    }
}

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.