Map enitity based on an array

Question

0.00/5 (No votes)

See more:

I have a EF Entity (based on a table) that hold column names like var1, var2, var3
How can I map the properties very simple to row[0], row[1], row[2]. Where row is a DataRow.
My biggest problem is that the Datatable object does not allways have the same number of columns.

My code:

private void AddExcelDataToEF()
        {
            string[] columnNames = _data.Columns.Cast<DataColumn>()
                                 .Select(x => x.ColumnName)
                                 .ToArray();
            using (var db = new Entities())
            {
                foreach (DataRow row in _data.Rows)
                {
                    var inputFile = new GL_InputTable();
                    inputFile.inputLineStatusID = 0;
                    inputFile.isDeleted = false;
                    inputFile.sourceFileID = _sourceFileID;
                    inputFile.lastModifiedBy = 0;
                    for (int lineNr = 0; lineNr < columnNames.Length; lineNr++)
                    {
                        switch (lineNr)
                        {
                            case 0: inputFile.lab1 = columnNames[lineNr]; inputFile.var1 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 1: inputFile.lab2 = columnNames[lineNr]; inputFile.var2 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 2: inputFile.lab3 = columnNames[lineNr]; inputFile.var3 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 3: inputFile.lab4 = columnNames[lineNr]; inputFile.var4 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 4: inputFile.lab5 = columnNames[lineNr]; inputFile.var5 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 5: inputFile.lab6 = columnNames[lineNr]; inputFile.var6 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 6: inputFile.lab7 = columnNames[lineNr]; inputFile.var7 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 7: inputFile.lab8 = columnNames[lineNr]; inputFile.var8 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 8: inputFile.lab9 = columnNames[lineNr]; inputFile.var9 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 9: inputFile.lab10 = columnNames[lineNr]; inputFile.var10 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 10: inputFile.lab11 = columnNames[lineNr]; inputFile.var11 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 11: inputFile.lab12 = columnNames[lineNr]; inputFile.var12 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 12: inputFile.lab13 = columnNames[lineNr]; inputFile.var13 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 13: inputFile.lab14 = columnNames[lineNr]; inputFile.var14 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 14: inputFile.lab15 = columnNames[lineNr]; inputFile.var15 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 15: inputFile.lab16 = columnNames[lineNr]; inputFile.var16 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 16: inputFile.lab17 = columnNames[lineNr]; inputFile.var17 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 17: inputFile.lab18 = columnNames[lineNr]; inputFile.var18 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 18: inputFile.lab19 = columnNames[lineNr]; inputFile.var19 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 19: inputFile.lab20 = columnNames[lineNr]; inputFile.var20 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 20: inputFile.lab21 = columnNames[lineNr]; inputFile.var21 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 21: inputFile.lab22 = columnNames[lineNr]; inputFile.var22 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 22: inputFile.lab23 = columnNames[lineNr]; inputFile.var23 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 23: inputFile.lab24 = columnNames[lineNr]; inputFile.var24 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 24: inputFile.lab25 = columnNames[lineNr]; inputFile.var25 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 25: inputFile.lab26 = columnNames[lineNr]; inputFile.var26 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 26: inputFile.lab27 = columnNames[lineNr]; inputFile.var27 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 27: inputFile.lab28 = columnNames[lineNr]; inputFile.var28 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 28: inputFile.lab29 = columnNames[lineNr]; inputFile.var29 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 29: inputFile.lab30 = columnNames[lineNr]; inputFile.var30 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 30: inputFile.lab31 = columnNames[lineNr]; inputFile.var31 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 31: inputFile.lab32 = columnNames[lineNr]; inputFile.var32 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 32: inputFile.lab33 = columnNames[lineNr]; inputFile.var33 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 33: inputFile.lab34 = columnNames[lineNr]; inputFile.var34 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 34: inputFile.lab35 = columnNames[lineNr]; inputFile.var35 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 35: inputFile.lab36 = columnNames[lineNr]; inputFile.var36 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 36: inputFile.lab37 = columnNames[lineNr]; inputFile.var37 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 37: inputFile.lab38 = columnNames[lineNr]; inputFile.var38 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 38: inputFile.lab39 = columnNames[lineNr]; inputFile.var39 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 39: inputFile.lab40 = columnNames[lineNr]; inputFile.var40 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 40: inputFile.lab41 = columnNames[lineNr]; inputFile.var41 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 41: inputFile.lab42 = columnNames[lineNr]; inputFile.var42 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 42: inputFile.lab43 = columnNames[lineNr]; inputFile.var43 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 43: inputFile.lab44 = columnNames[lineNr]; inputFile.var44 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 44: inputFile.lab45 = columnNames[lineNr]; inputFile.var45 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 45: inputFile.lab46 = columnNames[lineNr]; inputFile.var46 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 46: inputFile.lab47 = columnNames[lineNr]; inputFile.var47 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 47: inputFile.lab48 = columnNames[lineNr]; inputFile.var48 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 48: inputFile.lab49 = columnNames[lineNr]; inputFile.var49 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 49: inputFile.lab50 = columnNames[lineNr]; inputFile.var50 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 50: inputFile.lab51 = columnNames[lineNr]; inputFile.var51 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 51: inputFile.lab52 = columnNames[lineNr]; inputFile.var52 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 52: inputFile.lab53 = columnNames[lineNr]; inputFile.var53 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 53: inputFile.lab54 = columnNames[lineNr]; inputFile.var54 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 54: inputFile.lab55 = columnNames[lineNr]; inputFile.var55 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 55: inputFile.lab56 = columnNames[lineNr]; inputFile.var56 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 56: inputFile.lab57 = columnNames[lineNr]; inputFile.var57 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 57: inputFile.lab58 = columnNames[lineNr]; inputFile.var58 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 58: inputFile.lab59 = columnNames[lineNr]; inputFile.var59 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            case 59: inputFile.lab60 = columnNames[lineNr]; inputFile.var60 = row[lineNr] == DBNull.Value ? null : row[lineNr].ToString(); break;
                            default: break;
                        }
                    }
                    db.GL_InputTable.Add(inputFile);
                }
                db.SaveChanges();
            }
        }

What I have tried:

Can't figure out how to solve this in a simple strategic manner.
I don't want a switch case statement with 60 cases !!

Posted 4-Oct-17 10:05am

Herman<T>.Instance

Updated 5-Oct-17 4:15am

v2

Add a Solution

Comments

GKP1992 5-Oct-17 1:30am

Hello digimanus,

Can you give an example to clarify the problem?

Thanks.

Herman<T>.Instance 5-Oct-17 2:56am

Added a code bit

GKP1992 5-Oct-17 5:28am

Thanks for the details. Can you also share the table structure, I'm not sure why you need the column names in the inputFile. If they're going to be based on the number of columns in the excel file, why not update the columns sequentially using a foreach loop and update the rest with nulls?

Herman<T>.Instance 5-Oct-17 6:37am

well.. the Entity InputFile IS the tablestructure.
Point is that my application receives an EXCEL file. A specific worksheet is then read and values are translated into a DATATABLE object. So that is why I only know that it is not possible to have more than 60 columns. But how many can be different.
So I hoped to find a way that based on the number of columns each datarowvalues should me mapped to the field in the table. So Columns I don't have stay NULL.

GKP1992 5-Oct-17 7:14am

Hello digimanus,

It is not as easy as it looks but, as far as I can understand your problem, you can take advantage of Take and Range methods of the Enumerable class. Use the columnNames.Count to take the collection of labs and vars and update them using a for each.

Hope it helps.

Herman<T>.Instance 5-Oct-17 8:07am

Thanks for the lead. I'll investigate that.

GKP1992 5-Oct-17 8:23am

You can use reflection to enumerate the properties of your entity. That way you can easily identify which properties to update.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Nielsen - getCore · Accepted Answer · 2017-10-05T04:15:00

This is a bit crude and could require som nullchecking but this should work :)

public class RowMapper
    {
        /*
            The way i read your sample is you get a datatable with columns and rows, 
            these are to be mapped to an entity which presumably doesn't require all columns filled.
            Those datatables however can have different set of columns defined.

            GL_InputTable entity apparently contains the columns 'var1' to 'var60' and 'lab1' to 'lab60'
         * */

        public void AddToDb(DataTable dataTable)
        {
            using (var db = new Entities())
            {
                for (int idx = 0; idx < dataTable.Rows.Count; idx++)
                {
                    GL_InputTableRow newrow;
                    GetMapAblesInRow(dataTable, idx, out newrow);
                    db.GL_InputTable.Add(newrow);
                }
                db.SaveChanges();
            }
        }

        private void GetMapAblesInRow(DataTable dataTable, int idxRow, out GL_InputTableRow outEntityTableRow)
        {
            var efTable = new GL_InputTableRow();
            Type t = typeof(GL_InputTableRow);
            PropertyInfo[] propInfos = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (DataColumn clmn in dataTable.Columns)
            {
                PropertyInfo pi = propInfos.FirstOrDefault(p => p.Name == clmn.ColumnName);
                if (pi != null && pi.CanWrite)
                {
                    pi.SetValue(efTable, dataTable.Rows[idxRow][clmn.ColumnName].ToString());
                }
            }
            outEntityTableRow = efTable;
        }


    }