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?
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