Jquery – How to save Handsontable to database

handsontablejqueryjsonvb.netwcf

Is there a generally accepted pattern for saving the data in a handsontable back to a database?

I'm using ajax and a WCF service to pull the data from my database to populate the table. The service is returning a list of objects which represent a row of data from the database table.

WCF:

<ServiceContract(Namespace:="")>
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)>
Public Class TableService

    <OperationContract()>
    <WebGet(ResponseFormat:=WebMessageFormat.Json)>
    Public Function GetResource() As List(Of Resource)
        Dim conn = <some connection string>
        Dim sql = <some SQL>
        Dim dt = New DataTable("foo")

        Using da As New SqlDataAdapter(sql, conn)
            da.Fill(dt)
        End Using

        Return Objectify(dt)
    End Function

   Private Function Objectify(dt As DataTable) As List(Of Resource)
        Dim resourceTable = New List(Of Resource)

        For Each row As DataRow In dt.Rows
            resourceTable.Add(New Resource With {
                .ResourceAllocationID = row("ResourceAllocationID"),
                .ResourceName = row("ResourceName"),
                .AllocationPercent = row("AllocationPercent"),
                .Month = row("Month"),
                .Year = row("Year"),
                .Comments = row("Comments"),
                .ProjectID = row("ProjectID"),
                .ResourceUId = row("ResourceUId")})
        Next

        Return resourceTable
    End Function
End Class

Public Class Resource
    Public Property ResourceAllocationID As Integer
    Public Property ResourceName As String
    Public Property AllocationPercent As Integer
    Public Property Month As String
        Get
            Return _monthName
        End Get
        Set(value As String)
            Dim intMonth As Integer
            If Integer.TryParse(value, intMonth) Then
                If [Enum].IsDefined(GetType(MonthName), intMonth) Then
                    _monthName = CType(value, MonthName).ToString
                End If
            Else
                If [Enum].IsDefined(GetType(MonthName), value) Then
                    _monthName = value
                End If
            End If
        End Set
    End Property        
    Public Property Year As Integer
    Public Property Comments As String
    Public Property ProjectID As Integer
    Public Property ResourceUId As String

    Private _monthName As String

    Public Enum MonthName
        January = 1
        February = 2
        March = 3
        April = 4
        May = 5
        June = 6
        July = 7
        August = 8
        September = 9
        October = 10
        November = 11
        December = 12
    End Enum
End Class

Javacript:

$("#container").handsontable({
    contextMenu: true,
    startRows: 1,
    minRows: 1,
    colHeaders: ['Year', 'Month', 'Name', '% Allocation', 'Comments'],
    colWidths: [52, 100, 150, 100, 200],
    columns: [
        { data: 'Year', type: 'numeric' },
        { data: 'Month' },
        { data: 'ResourceName' },
        { data: 'AllocationPercent', type: 'numeric' },
        { data: 'Comments' }
    ]
});

$.ajax({
    url: "TableService.svc/GetResource",
    type: "GET",
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (data) {
        $("#container").handsontable(loadData, data.d)
    },
    error: function (error) {
        alert("Error: " + error);
    }
});

This works beautifully to fill the table. What I'm struggling with is how to save changes back to the database. The requirement is not to save any changes until all changes are complete and an update button is hit.

I know I can get an object containing all the cells in the table by calling handsontable.getData(). What I'm thinking is I need to serialize the object into Json, send it back to my service, deserialize it back into a list of objects, and then update the database for each object in the list. Am I on the right track? If so, how do I actually implement it?

Best Solution

So, I ended up piecing together a solution to meet my specific requirements.

I first needed to get a JSON formatted string representing all the cells of the Handsontable to pass back to my WCF service. The method handsontable.getData() returns an object representing all the data in the table. I then used JSON.stringify() to convert that object to a string. From there I was having trouble passing that string to my service. I eventually figured out I had to stringify my already stringified object to create the proper string parameter for my service while properly escaping the quotes within the object.

$("#btnUpdate").click(function () {
    var tableData = JSON.stringify(handsontable.getData());
    var input = JSON.stringify({ "input": tableData });

    $.ajax({
        type: 'POST',
        url: "TableService.svc/SaveResource",
        data: input,
        contentType: "application/json; charset=utf-8",
        dataType: 'json',
        success: function (res) {
            if (res.result === 'ok') {
                console.text('Data saved');
            }
        },
        error: function (xhr) {
            alert(xhr.responseText);
        }
    });
    $("btnUpdate").blur();
});

With my table data now back server-side, I needed to parse the JSON back into a list of objects. I ended up using JSON.NET to accomplish this. Once I had a list of objects, I added each list item as a row in a new DataTable on which I was able to run the SQL necessary to update the database.

<OperationContract()>
<WebInvoke(Method:="POST", BodyStyle:=WebMessageBodyStyle.WrappedRequest, ResponseFormat:=WebMessageFormat.Json)>
Public Function SaveResource(ByVal input As String) As String
    Dim resources As List(Of Resource) = Json.JsonConvert.DeserializeObject(Of List(Of Resource))(input)
    UpdateDB(resources)
    Return "ok"
End Function

Private Sub UpdateDB(resources As List(Of Resource))
    Dim dt As New DataTable
    Dim conn = <some connection string>
    Dim sql = <some SQL>
    Using da As New SqlDataAdapter(sql, conn)
        da.FillSchema(dt, SchemaType.Source)
        For Each resourceItem In resources
            Dim row As DataRow = dt.NewRow()
            Dim month As Resource.MonthName
            row("ResourceAllocationID") = resourceItem.ResourceAllocationID
            row("ResourceName") = resourceItem.ResourceName
            row("AllocationPercent") = resourceItem.AllocationPercent
            row("Month") = [Enum].TryParse(resourceItem.Month, month)
            row("Year") = resourceItem.Year
            row("Comments") = resourceItem.Comments
            row("ProjectID") = resourceItem.ProjectID
            row("ResourceUId") = resourceItem.ResourceUId
            dt.Rows.Add(row)
        Next
    End Using
    *<run the appropriate SQL on each row of dt to update the database>*
End Sub