List to datatable conversion takes more time

Question

What's the best way to pass list (i.e.. generic list) to a stored procedure using Entity Framework.

The current solution I am using converts the list to a DataTable and then it gets passed to the stored procedure.

The code shown below is used to convert generic list to data table but it took around 10 secs to process around 100000 records of object with 6 properties.

    public static DataTable ToDataTable<T>(List<T> iList)
    {
        DataTable dataTable = new DataTable();

        PropertyDescriptorCollection propertyDescriptorCollection =
            TypeDescriptor.GetProperties(typeof(T));

        for (int i = 0; i < propertyDescriptorCollection.Count; i++)
        {
            PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
            Type type = propertyDescriptor.PropertyType;

            if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                type = Nullable.GetUnderlyingType(type);

            dataTable.Columns.Add(propertyDescriptor.Name, type);
        }

        object[] values = new object[propertyDescriptorCollection.Count];

        foreach (T iListItem in iList)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = propertyDescriptorCollection[i].GetValue(iListItem);
            }

            dataTable.Rows.Add(values);
        }

        return dataTable;
    }

I need some best way to pass a list of values to stored procedure using less time.

See: https://stackoverflow.com/questions/12320594/passing-datatable-to-a-stored-procedure or https://www.c-sharpcorner.com/UploadFile/87b416/passing-datatable-to-storeprocedure-as-parameter-in-C-Sharp/ — NoChance, Aug 29 '18 at 03:22
Your `ToDataTable` code uses reflection. The downside of reflection is that it is quite slow. So what you should do is create a version of that code **specific to your type**. Now, run the original code and write down what is passed in to each `dataTable.Columns.Add(propertyDescriptor.Name, type);` call. Then write it out manually - so it might be something like `dataTable.Columns.Add("Column1", typeof(int)); dataTable.Columns.Add("Column2", typeof(string));` etc. Now do much the same for `dataTable.Rows.Add` - populate the arrays using `iListItem.Column1`, `iListItem.Column2` etc. — mjwills, Aug 29 '18 at 03:43
Can you check where is time of 10 secs getting used? foreach loop on iList or GetValue? It is all good to have generic solution but perhaps in this scenario one to one property mapping might be more helpful. I had faced similar scenario in past and had managed to reduce my mapping time from about 350ms to 32 ms using hardcoded mapping against initial generic solution using reflection. — touchofevil, Aug 29 '18 at 04:22
You are using PropertyDescriptor methods, which are heavy operations. Use one to one mapping for data rows creation as suggested by @mjwills. If still you want to use it, the use Parallel.ForEach — ManishM, Aug 29 '18 at 06:30

score 0 · Answer 1 · answered Aug 29 '18 at 05:33

0

I would do it as by creating a User Defined Table Type. I think it would be better and quicker.

Source : [https://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure][1]

answered Aug 29 '18 at 05:33

Praneet Nadkar

823
7
16

List to datatable conversion takes more time

1 Answers1