0

Hi I have an excel where each column belongs to different table in db. I need to insert rows of excel by mapping correct column of excel to correct table and then column, for comparing I am using dictionary . I have written small prog but its not feasible.

ex if i have 1000 rows in excel and 20 column then my total insert going to be 20000 which is not good for performance aspects.

Could you please help me to resolve this issue and get optimal solution.

    foreach (var v in res)
    {
        if (dic.ContainsKey(v.ColumnFromName))
        {
            dic[v.ColumnFromName].Add(new Tuple<string, string>(v.TableToName,
                v.ColumnToName));

        }
        else
        {
            dic.Add(v.ColumnFromName, new List<Tuple<string, string>>
                        { new Tuple<string, string>(v.TableToName, v.ColumnToName) });
        }
    }

    if (dt.Rows.Count > 0)
    {
        StringBuilder sb = new StringBuilder();
        List<string> ls = new List<string>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            foreach (DataColumn col in dt.Columns)
            {
                sb.Clear();
                var tbletoname = dic[col.ColumnName].FirstOrDefault().Item1;
                Console.WriteLine(col.ColumnName);
                var columntoname = dic[col.ColumnName].FirstOrDefault().Item2;

                sb.Append("Insert into ");
                sb.Append(tbletoname);
                sb.Append(" (");
                sb.Append(columntoname);
                sb.Append(" ) ");

                sb.Append("Values");
                sb.Append(" ('");
                sb.Append(dt.Rows[i][col.ColumnName]);
                sb.Append("')");
                ls.Add(sb.ToString());
                Console.WriteLine(dt.Rows[i][col.ColumnName]);
            }

        }
    }
mabiyan
  • 667
  • 7
  • 25
chandan kumar
  • 1
  • 1
  • If I understand your question correctly, you are trying here to pull records from different table and wants to map it to an excel, is that correct? – mabiyan Jun 24 '21 at 01:35
  • @ashmabi i am excel from customer and they have only provided mapping details where each column of excel belongs to which table and there respective column. i am thinking to get optimal solution with min db hits while doing insert – chandan kumar Jun 24 '21 at 01:59
  • probably, converting excel to JSON or XML with table-columns structure, create stored procedure to take this JSON/XML as input to insert at once. – mabiyan Jun 24 '21 at 02:22
  • SqlBulkCopy could be used to improve performance, here is an answer for a similar question which shows how to do it from a List, maybe you can adapt this to your needs: https://stackoverflow.com/a/3913486/700390 – user700390 Jun 24 '21 at 04:16
  • Do you need import it only once? Or you have multiple excel files need to be imported? – Epic Chen Jun 24 '21 at 05:14
  • i need to import only once but it will but insertion will be happen in many table depends on mapping for which i am using map – chandan kumar Jun 24 '21 at 05:17
  • SqlBulkCopy need target table means one table but in mine requirement i have to make insert into different tables – chandan kumar Jun 24 '21 at 05:19
  • if i want to reduce the insert how can i make table type class and there field as property. – chandan kumar Jun 24 '21 at 05:26
  • If possible could you please provide me step how to approach .Any help is appreciated – chandan kumar Jun 24 '21 at 13:36

0 Answers0