How to convert Handsontable data back into C# class

Question

I am using an ajax post to send my column headers and data from a handsontable back to an ashx handler.

   $.ajax({
       type: 'POST',
       url: "Scripts/SaveExcelData.ashx",
       contentType: "application/json; charset=utf-8",
       data: JSON.stringify({"columns": hot.getColHeader(), "rows": hot.getData()}),
       success: function (data) {}
   });

Currently I am using to following to deserialize the request, but have not been able to successfully come up with anything that converts the rows into an array of DataBaseRow class objects.

var jsonString = String.Empty;
context.Request.InputStream.Position = 0;
using (var inputStream = new StreamReader(context.Request.InputStream))
{
    jsonString = inputStream.ReadToEnd();
    var results = JsonConvert.DeserializeObject<dynamic>(jsonString);
    var columns = results.columns;
    var rows = results.rows;

    //use columns here to convert rows into DataBaseRow class
}

columns looks like: {["Col1","Col2","Col3"]}
rows looks like: {[["Val1","Val2","Val3"],["Val1","Val2","Val3"],["Val1","Val2","Val3"]]}

How can I do this?

UPDATE
Instead of trying to convert the dynamic class into the DataBaseRow class, I found I could actually just manually loop through the array values and write them into new instances of the DataBaseRow class.

using (DBEntities edmx = new DBEntities())
{
    foreach (var row in rows)
    {
        DataBaseRow dbr = new DataBaseRow();
        edmx.DataBaseRow.Add(dbr);
        dbr.LoadedTime = DateTime.Now;

        for (int i = 0; i < row.Count; i++)
        {
            string colval = row[i].ToString();
            string colname = columns[i].ToString();
            switch (colname)
            {
                case "Col1":
                    dbr.DBCol1 = colval;
                    break;
                case "Col2":
                    dbr.DBCol2 = colval;
                    break;
                case "Col3":
                    dbr.DBCol3 = colval;
                    break;
            }
        }
    }
    edmx.SaveChanges();
}

This works, but is very slow (see comment for timings). Is there a faster/better way to process this data? (if it matters - I actually have 14 columns that I'm mapping in the switch)

Have you considered writing a loop to add the column definitions, and another loop to add each row? — 15ee8f99-57ff-4f92-890c-b56153, Dec 07 '15 at 17:23
I was sort of able to get it to work by manually looping through the JArray values. See updated question above. — wham12, Dec 07 '15 at 22:07
A 5000 record dataset took just over 4 minutes total (sending time, about 1.5 minutes for FOREACH loop, and about 2 minutes for the SaveChanges call). A 50000 record dataset ran for just about 2 hours. It seemed to be doing okay slowly processing all the data, but then gave me an out of memory error when it tried to do the SaveChanges call. — wham12, Dec 07 '15 at 22:09
1. There's no need to look up the column names in the innermost loop. If you need to read the first row to get the column names, do that on the first iteration of the foreach loop. Otherwise do it outside the foreach loop. You're doing it 4,999 extra times. That's costing you. 2. A table is a set of rows with a given column structure. Create one table outside the foreach, and add each row to it. Logically this is 5,000 rows in one table. My guess is that creating 4,999 extra tables and adding each one to the edmx might be what's killing you. I bet that's the memory issue on 50k rows. — 15ee8f99-57ff-4f92-890c-b56153, Dec 08 '15 at 14:05
I found this answer, which has helped with the speed some http://stackoverflow.com/a/5942176/266592 (save to the database in smaller chunks to avoid the OOM error). — wham12, Dec 08 '15 at 14:40
Right, saving changes is another thing you shouldn't be doing 5,000 times. — 15ee8f99-57ff-4f92-890c-b56153, Dec 08 '15 at 14:45
@EdPlunkett - I agree that I am adding a lot of extra processing by having to map every column for each row, over and over again....but I'm just not seeing how I can "save the mapping" to reuse it. I would love an example if you can give me one. — wham12, Dec 08 '15 at 14:45
@EdPlunkett - I knew I wouldn't want to save every record individually, that's why I saved outside of the foreach loop. However it appears that the entity framework isn't great at saving large datasets, so I actually needed to do multiple saves instead of trying to do one huge one at the end. — wham12, Dec 08 '15 at 14:48
Actually hang on, is `columns` the same `columns` in your second code sample? My bad -- that's probably fine the way it is, if it's just an array or List or something. But you need to stop creating a new table for each row. If you need to save more than once, increment an index variable in the foreach and call SaveChanges every 500 or 1,000 iterations or whatever EF can tolerate; maybe save more often if the rows are large. — 15ee8f99-57ff-4f92-890c-b56153, Dec 08 '15 at 14:51
i ran into the speed issue with entity framework when moving large data around since the round-trips add up. I switched to using [`SqlBulkCopy`](https://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx) for the bulk imports, it is way faster. — weeksdev, Dec 09 '15 at 02:30

score 1 · Accepted Answer · edited May 23 '17 at 11:45

So the technical answer to my question can be found in the Update I added (just reference the dynamic objects as arrays, don't try to convert them).

However, seems like Entity Framework is very poor at handling saving large datasets. This can be sped up by grouping the saves into chunks and recreating the context for every chunck. https://stackoverflow.com/a/5942176/266592

I ended up rewriting this to insert the values into a DataTable and then using SqlBulkCopy to save the records to the database.

var jsonString = String.Empty;
context.Request.InputStream.Position = 0;
using (var inputStream = new StreamReader(context.Request.InputStream))
{
    jsonString = inputStream.ReadToEnd();
    var results = JsonConvert.DeserializeObject<dynamic>(jsonString);
    var columns = results.columns;
    var rows = results.rows;

    var dt = new DataTable();
    for (int i = 0; i < columns.Count; i++)
    {        
        dt.Columns.Add(columns[i].ToString());
    }

    foreach (var row in rows)
    {
        var datarow = dt.NewRow();
        for (int i = 0; i < row.Count; i++)
        {
            datarow[i] = row[i];
        }
        dt.Rows.Add(datarow);
    }

    using (var connection = new SqlConnection(ConnectionString))
    {
        SqlTransaction transaction = null;
        connection.Open();
        try
        {
            transaction = connection.BeginTransaction();
            using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
            {
                sqlBulkCopy.DestinationTableName = "TABLENAME";
                sqlBulkCopy.BatchSize = 100000;
                sqlBulkCopy.BulkCopyTimeout = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    sqlBulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                }
                sqlBulkCopy.WriteToServer(dt);
            }
            transaction.Commit();
        }
        catch (Exception)
        {
            transaction.Rollback();
        }
    }
}

How to convert Handsontable data back into C# class

1 Answers1