Iterate through DataTable and Update Database

Question

I have the following code which accepts a DataTable and iterates through the properties, then saves the data. I am looking for a more efficient way of processing this so that I do not have to manually hard-code the ItemArray counts as well as the row names. Can anyone provide details on how I can improve this?

IList<account_dim_temp> a1 = new List<account_dim_temp>();
                foreach (DataRow row in data.Rows)
                {
                    account_dim_temp a = new account_dim_temp();
                    a.account_id = (string) row.ItemArray[0].ToString().Replace("\\N", "");
                    a.canvas_id = (string) row.ItemArray[1].ToString().Replace("\\N", "");
                    a.name = (string) row.ItemArray[2].ToString().Replace("\\N", "");
                    a.depth = (string) row.ItemArray[3].ToString().Replace("\\N", "");
                    a.workflow_state = (string) row.ItemArray[4].ToString().Replace("\\N", "");
                    a.parent_account = (string) row.ItemArray[5].ToString().Replace("\\N", "");
                    a.parent_account_id = (string) row.ItemArray[6].ToString().Replace("\\N", "");
                    a.grandparent_account = (string) row.ItemArray[7].ToString().Replace("\\N", "");
                    a.grandparent_account_id = (string) row.ItemArray[8].ToString().Replace("\\N", "");
                    a.root_account = (string) row.ItemArray[9].ToString().Replace("\\N", "");
                    a.root_account_id = (string) row.ItemArray[10].ToString().Replace("\\N", "");
                    a.subaccount1 = (string) row.ItemArray[11].ToString().Replace("\\N", "");
                    a.subaccount1_id = (string) row.ItemArray[12].ToString().Replace("\\N", "");
                    a.subaccount2 = (string) row.ItemArray[13].ToString().Replace("\\N", "");
                    a.subaccount2_id = (string) row.ItemArray[14].ToString().Replace("\\N", "");
                    a.subaccount3 = (string) row.ItemArray[15].ToString().Replace("\\N", "");
                    a.subaccount3_id = (string) row.ItemArray[16].ToString().Replace("\\N", "");
                    a.subaccount4 = (string) row.ItemArray[17].ToString().Replace("\\N", "");
                    a.subaccount4_id = (string) row.ItemArray[18].ToString().Replace("\\N", "");
                    a.subaccount5 = (string) row.ItemArray[19].ToString().Replace("\\N", "");
                    a.subaccount5_id = (string) row.ItemArray[20].ToString().Replace("\\N", "");
                    a.subaccount6 = (string) row.ItemArray[21].ToString().Replace("\\N", "");
                    a.subaccount6_id = (string) row.ItemArray[22].ToString().Replace("\\N", "");
                    a.subaccount7 = (string) row.ItemArray[23].ToString().Replace("\\N", "");
                    a.subaccount7_id = (string) row.ItemArray[24].ToString().Replace("\\N", "");
                    a.subaccount8 = (string) row.ItemArray[25].ToString().Replace("\\N", "");
                    a.subaccount8_id = (string) row.ItemArray[26].ToString().Replace("\\N", "");
                    a.subaccount9 = (string) row.ItemArray[27].ToString().Replace("\\N", "");
                    a.subaccount9_id = (string) row.ItemArray[28].ToString().Replace("\\N", "");
                    a.subaccount10 = (string) row.ItemArray[29].ToString().Replace("\\N", "");
                    a.subaccount10_id = (string) row.ItemArray[30].ToString().Replace("\\N", "");
                    a.subaccount11 = (string) row.ItemArray[31].ToString().Replace("\\N", "");
                    a.subaccount11_id = (string) row.ItemArray[32].ToString().Replace("\\N", "");
                    a.subaccount12 = (string) row.ItemArray[33].ToString().Replace("\\N", "");
                    a.subaccount12_id = (string) row.ItemArray[34].ToString().Replace("\\N", "");
                    a.subaccount13 = (string) row.ItemArray[35].ToString().Replace("\\N", "");
                    a.subaccount13_id = (string) row.ItemArray[36].ToString().Replace("\\N", "");
                    a.subaccount14 = (string) row.ItemArray[37].ToString().Replace("\\N", "");
                    a.subaccount14_id = (string) row.ItemArray[38].ToString().Replace("\\N", "");
                    a.subaccount15 = (string) row.ItemArray[39].ToString().Replace("\\N", "");
                    a.subaccount15_id = (string) row.ItemArray[40].ToString().Replace("\\N", "");
                    a.sis_source_id = (string) row.ItemArray[41].ToString().Replace("\\N", "");
                    a1.Add(a);
                }
                db.account_dim_temp.AddRange(a1);
                db.SaveChanges();

Use a constructor for `account_dim_temp` that takes a `DataRow` and does all your required logic there. — Michael McGriff, Nov 02 '16 at 21:08

score 0 · Answer 1 · edited May 23 '17 at 12:00

0

Copied from [1] as suggestion:

// On all tables' rows
foreach (DataRow dtRow in dtTable.Rows)
{
// On all tables' columns
   foreach(DataColumn dc in dtTable.Columns)
   {
       var field1 = dtRow[dc].ToString();
   }
}

[1] How to 'foreach' a column in a DataTable using C#?

edited May 23 '17 at 12:00

Community

1
1

answered Nov 02 '16 at 19:01

cronoik

15,434
3
40
78

`foreach (DataRow row in data.Rows) { assignment_group_dim_temp a = new assignment_group_dim_temp(); foreach (DataColumn column in data.Columns) { string colName = column.ColumnName.ToString(); string colData = row[column].ToString(); **a.colName = colData;** agd.Add(a); } }` How do I assign the value in the *? – George Nov 02 '16 at 20:52
I might have to re-ask this question since I can't get the code to format correctly in comments. – George Nov 02 '16 at 21:01
You could use a dictionary instead of an item array or you could replace the inner foreach loop with a for loop. – cronoik Nov 02 '16 at 21:08
You could use a dictionary instead of an item array or you could replace the inner foreach loop with a for loop. With the for loop you can access the index in the row as well as in the item array. – cronoik Nov 02 '16 at 21:09
But, even with a for loop, how can I assign the variable to my model (assignment_group_dim_temp)? (That is the line with the asterisk -- a.colName = colData) – George Nov 02 '16 at 21:27
Something like this: a[i] = colData; but this requires that the order of your attributes is the same as column ordering. A dictionary or a other built-in type with a name indexer would be much better. You could also add an name indexer by your own [1]. – cronoik Nov 02 '16 at 21:34

George · Answer 2 · 2016-11-02T21:16:52.143

How Do I assign the values within the loop in the asterisk? Is this possible?

 foreach (DataRow row in data.Rows)
                {
                    assignment_group_dim_temp a = new assignment_group_dim_temp();
                    foreach (DataColumn column in data.Columns)
                    {
                        string colName = column.ColumnName.ToString();
                        string colData = row[column].ToString();

                        //How can I assign the following variable?
                        **a.colName = colData;**

                        agd.Add(a);
                    }
                }

Iterate through DataTable and Update Database

2 Answers2