Is there a way to pass a string array instead of separate parameters to an Entity Framework class?

Question

Currently, I have a program that uses OleDb to take data from an Excel sheet and imports it into a SQL Server database using Entity Framework.

In bridging the gap between OleDb and EF, I push the data from the Excel sheet into a DataTable, go into the DataTable to grab all the data in a single row, put that into a StringBuilder separated by commas, and then turn that StringBuilder object into a string array separated by commas. With that, I then call the Add function to import the data into the database using EF.

In the code shown below, you can see that I have to call

Name = data[0], Message = data[1]

etc to push the data into the database. Is there a way that I can instead pass the string array into the class instead of each separate parameter and deal with the data there?

public static void Insert(string Sheet, OleDbConnection conn)
{
        OleDbCommand command = new OleDbCommand("SELECT Name, Message, Message type FROM [" + Sheet + "$]", conn); //Selects everything inside excel file
        DataTable Data = new DataTable();

        OleDbDataAdapter adapter = new OleDbDataAdapter(command);
        adapter.Fill(Data); //Puts all data inside a DataSet

        StringBuilder sb = new StringBuilder();
        var context = new DataWarehouseContext();

        // Prints out DataSet
        foreach (DataRow dataRow in Data.Rows)
        {
            foreach (var item in dataRow.ItemArray)
            {
                sb.Append(item);
                sb.Append(",");
            }

            string[] data = sb.ToString().Split(','); //Gets data for each item in vHealth Insert method
            context.RvtoolsVHealth.Add(new RvtoolsVHealth { Name = data[0], Message = data[1], MessageType = data[2] });
            context.SaveChanges();
        }
}

Any pointers would be great, thanks!

CSV seems entirely uncessary. You are already looping through the data rows, why not access the columns directly when creating your RVtoolsHealth object? Name = dataRow["Name"]. See https://stackoverflow.com/questions/7119993/datarow-select-cell-value-by-a-given-column-name . Linq works on Datatables, so you could select straight into a list of RvtoolsVHealth objects then do an AddMany to your context. — shox, May 27 '20 at 17:11

score 2 · Accepted Answer · answered May 27 '20 at 16:16

Not in a built-in way. You could create a constructor for RvtoolsVHealth that takes a string array and sets the properties, but it's a poor API in my opinion because there's no way to ensure that the properties are mapped to the proper array values. What if the array contains the message type, message, and name in that order?

What you're doing is the canonical method of instantiating objects.

I will say that you seem to waste some energy by concatenating a string only to split it back out. Why not just pull the values from the dataRow directly:

foreach (DataRow dataRow in Data.Rows)
{
    context.RvtoolsVHealth.Add(new RvtoolsVHealth { 
        Name        = dataRow[0].ToString(), 
        Message     = dataRow[1].ToString(), 
        MessageType = dataRow[2].ToString() 
        });
    context.SaveChanges();
}

You could even go one step further and use a OleDbDataReader to read the values rather than taking the time to fill a DataTable

This makes a lot of sense, I'm new to using DataRow, so I didn't know that was an option! Thank you for your help! — CKneeland, May 27 '20 at 16:25

Is there a way to pass a string array instead of separate parameters to an Entity Framework class?

1 Answers1