vb.net 如何将 Handsontable 保存到数据库

How to save Handsontable to database

提问by jtrohde

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.

我正在使用 ajax 和 WCF 服务从我的数据库中提取数据以填充表。该服务正在返回一个对象列表，这些对象代表数据库表中的一行数据。

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:

Java脚本：

$("#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?

我知道我可以通过调用handsontable.getData(). 我在想的是我需要将对象序列化为 Json，将其发送回我的服务，将其反序列化回对象列表，然后为列表中的每个对象更新数据库。我在正确的轨道上吗？如果是这样，我如何实际实施它？

回答by jtrohde

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.

我首先需要获取一个 JSON 格式的字符串，该字符串表示 Handsontable 的所有单元格，以传递回我的 WCF 服务。该方法handsontable.getData()返回一个代表表中所有数据的对象。然后我用来JSON.stringify()将该对象转换为字符串。从那里我无法将该字符串传递给我的服务。我最终发现我必须对我已经字符串化的对象进行字符串化，以便为我的服务创建正确的字符串参数，同时正确转义对象中的引号。

$("#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.NETto accomplish this. Once I had a list of objects, I added each list item as a row in a new DataTableon which I was able to run the SQL necessary to update the database.

我的表数据现在回到服务器端，我需要将 JSON 解析回对象列表。我最终使用JSON.NET来完成此任务。获得对象列表后，我将每个列表项作为一行添加到新的行中DataTable，我可以在该行上运行更新数据库所需的 SQL。

<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

回答by jcollum

There's two ways you can approach this:

有两种方法可以解决这个问题：

1. Collect data in small edits, save them locally in the browser, when user hits save you send the edited chunks of data back to the database.

2. Keep a local version of the data table, edit it, when user hits save you send the whole table back and save it to the database.

1. 在小的编辑中收集数据，将它们本地保存在浏览器中，当用户点击保存时，您将编辑后的数据块发送回数据库。

2. 保留数据表的本地版本，对其进行编辑，当用户点击保存时，您将整个表发回并将其保存到数据库中。

I'd use approach 1 because it'll be less churn and you won't have accidental overwrites. You'll want to use a the onChange callback. I think you'll need to have a hidden column that has the IDs of the rows you're editing.

我会使用方法 1，因为它会减少流失，并且不会意外覆盖。您将需要使用onChange 回调。我认为您需要有一个隐藏列，其中包含您正在编辑的行的 ID。

# coffeescript 
onChangeHandler = -> 
  rowChange = rowID: theDataArray[arguments[0]], rowCol: arguments[1], newVal: arguments[3], dateTime: new Date()
  window.MyNamespace.edits.push(rowChange)

tableDiv.handsontable
  ... 
  onChange: onChangeHandler 

$('#save').click( ->
 # save data back to the database
)