To enable real-time data persistence between a JSpreadsheet (formerly jexcel) interface and an ASP.NET backend, you can leverage AJAX events to trigger database updates whenever a cell value is modified. The following implementation demosntrates how to capture row-level changes and propagate them to a SQL Server database.

Frontend Implementation

Confgiure the JSpreadsheet instance to listen for the onchange event. This handler extracts the unique identifier (DBID) from the row and the updated content, then sends an asynchronous request to an HTTP Handler.

var updateCellData = function (instance, cell, colIndex, rowIndex, newValue) {
    // Retrieve the unique row identifier from the first column
    var rowId = instance.getValue(instance.getColumnNameFromId([0, rowIndex]));
    
    // Define field mapping logic
    var fieldMapping = { 2: "Shipto", 3: "Soldto" }; 
    var targetField = fieldMapping[colIndex];

    if (targetField) {
        $.ajax({
            url: "DataHandler.ashx",
            type: "POST",
            data: { 
                "action": "update_cell", 
                "id": rowId, 
                "field": targetField, 
                "value": newValue 
            },
            success: function (response) {
                console.log("Update successful");
            },
            error: function (xhr) {
                alert("Error updating record: " + xhr.responseText);
            }
        });
    }
};

// Initialize spreadsheet with the change listener
var mySpreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
    url: 'DataHandler.ashx?action=fetch',
    onchange: updateCellData,
    columns: [ /* Column definitions */ ]
});

Backend Implementation (DataHandler.ashx)

The HTTP handler processes the incoming request, constructs a parameterized SQL query to prevent injection, and executes the update command against the data base.

<%@ WebHandler Language="C#" Class="DataHandler" %>

using System;
using System.Web;
using System.Data.SqlClient;

public class DataHandler : IHttpHandler {
    public void ProcessRequest(HttpContext context) {
        string action = context.Request["action"];

        if (action == "update_cell") {
            int recordId = Convert.ToInt32(context.Request["id"]);
            string column = context.Request["field"];
            string newValue = context.Request["value"];

            string sql = string.Format("UPDATE outstanding SET {0} = @val WHERE id = @id", column);

            using (SqlConnection conn = new SqlConnection("your_connection_string")) {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn)) {
                    cmd.Parameters.AddWithValue("@val", newValue);
                    cmd.Parameters.AddWithValue("@id", recordId);
                    cmd.ExecuteNonQuery();
                }
            }
            context.Response.Write("Success");
        }
    }

    public bool IsReusable { get { return false; } }
}

Key Considerations

• Security: Always use parameterized queries (as shown above) to prevent SQL injection attacks.
• Synchronization: Ensure the DBID mapped in your JSpreadsheet columns corresponds exactly to your database primary key to avoid accidental record overwrites.
• Error Handling: Implement server-side validation to ensure that newValue meets the data constraints of the database schema before executing the update.