)
We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Essential JS2

Syncfusion jQuery based widgets are no longer in active development. Switch to our pure JavaScript based next generation Essential JS 2 library.

Syncfusion Feedback

How to load and save a Sql table in Spreadsheet

Platform: jQuery |
Control: ejSpreadSheet |
Published Date: April 2, 2018 |
Last Revised Date: May 22, 2019

Description

This knowledge base explains how to load and save changes to a Sql table in Spreadsheet.

Solution

JavaScript

 

HTML

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
<div id="FlatSpreadsheet"></div>

 

JS

var dataSource;
$(function () {
    $.ajaxSetup({ async: false });            
    $.getJSON("api/Spreadsheet/GetDataFromDB", {}, function (data) {
        dataSource = data;
    });
    $.ajaxSetup({ async: true });
    $("#FlatSpreadsheet").ejSpreadsheet({
        columnWidth: 110,
        sheets: [
            {
                dataSource: dataSource,
                primaryKey: "OrderID"
            }
        ],
        loadComplete: "onLoadComplete"
    });
    $("#saveChangesToDB").on("click", function () {
        var ssObj = $("#FlatSpreadsheet").data("ejSpreadsheet"), key, rowIdx, sheetIdx = ssObj.getActiveSheetIndex(), editedChanges = ssObj.XLEdit.saveEditingValue().EditedData,
            dataSettings = ssObj.getDataSettings(sheetIdx)[0], isShowHeader = dataSettings.showHeader, dataSource = dataSettings.dataSource, changedDataSource = [], pushedRowColl = [];
        if (!editedChanges.length) {
            ssObj.alert("Please edit any cells and try again!");
            return;
        }
        ssObj.showWaitingPopUp();
        for (key in editedChanges) {
            rowIdx = editedChanges[key].CellIndex.RowIndex;
            if (isShowHeader)
                rowIdx--;
            if (pushedRowColl.indexOf(rowIdx) < 0) {
                changedDataSource.push(dataSource[rowIdx]);
                pushedRowColl.push(rowIdx);
            }
        }
        $.ajax({
            type: "POST",
            url: "api/Spreadsheet/SaveChangesToDatabase",
            data: { editedValues: JSON.stringify(changedDataSource) },
            success: function (response) {
                ssObj.alert(response);
                ssObj.hideWaitingPopUp();
            },
            error: function (err) {
                ssObj.alert(err.statusText);
                ssObj.hideWaitingPopUp();
            }
        });
    });
});
function onLoadComplete(args) {
    if (!this.isImport) {
        this.XLEdit.saveEditingValue(); //Get or reset the saved changes collection after data loaded
    }
}

 

WEBAPI

private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
[AcceptVerbs("Get")]
[OperationContract]
[WebGet(BodyStyle = WebMessageBodyStyle.Bare)]
public HttpResponseMessage GetDataFromDB()
{
    List<Order> orders = new List<Order>();
    SqlConnection sqlCon = new SqlConnection(connetionString);
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table]", sqlCon);
    sqlCon.Open();
    SqlDataReader sqlDR = sqlComm.ExecuteReader();
    while (sqlDR.Read())
    {
        Order order = new Order();
        order.OrderID = (int)sqlDR.GetValue(0);
        order.ItemName = sqlDR.GetValue(1).ToString();
        order.Quantity = sqlDR.GetValue(2).ToString();
        order.Price = sqlDR.GetValue(3).ToString();
        order.Amount = sqlDR.GetValue(4).ToString();
        orders.Add(order);
    }
    sqlCon.Close();
 
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    string str = serializer.Serialize(orders);
    return new HttpResponseMessage() { Content = new StringContent(str, Encoding.UTF8, "text/plain") };
}
 
[AcceptVerbs("Post")]
[OperationContract]
[WebGet(BodyStyle = WebMessageBodyStyle.Bare)]
public HttpResponseMessage SaveChangesToDatabase()
{
    string editedValues = HttpContext.Current.Request.Params["editedValues"];
    JavaScriptSerializer serialize = new JavaScriptSerializer();
    List<Order> editedData = serialize.Deserialize<List<Order>>(editedValues);
 
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    for (int i = 0; i < editedData.Count; i++)
    {
        var order = editedData[i];
        SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [OrderID] = '" + order.OrderID + "'", sqlCon);
        var hasFileInDB = selectComm.ExecuteScalar();
        if (hasFileInDB == null)
        {
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon);
            sqlComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", order.Price);
            sqlComm.Parameters.AddWithValue("@Amount", order.Amount);
            sqlComm.ExecuteNonQuery();
        }
        else
        {
            SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            updateComm.CommandTimeout = 0;
            updateComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            updateComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            updateComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            updateComm.Parameters.AddWithValue("@Price", order.Price);
            updateComm.Parameters.AddWithValue("@Amount", order.Amount);
            updateComm.ExecuteNonQuery();
        }
    }
    sqlCon.Close();
    return new HttpResponseMessage() { Content = new StringContent("Successfully updated", Encoding.UTF8, "text/plain") };
}

 

MVC

 

CSHTML

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
@(Html.EJ().Spreadsheet<object>("FlatSpreadsheet")
    .ColumnWidth(110)
    .Sheets(sheet =>
    {
        sheet.Datasource(@ViewBag.Datasource).PrimaryKey("OrderID").Add();
    })
    .ClientSideEvents(eve => eve.LoadComplete("onLoadComplete"))
)
<script type="text/javascript">    
    $(function () {
        $("#saveChangesToDB").on("click", function () {
            var ssObj = $("#FlatSpreadsheet").data("ejSpreadsheet"), key, rowIdx, sheetIdx = ssObj.getActiveSheetIndex(), editedChanges = ssObj.XLEdit.saveEditingValue().EditedData,
                dataSettings = ssObj.getDataSettings(sheetIdx)[0], isShowHeader = dataSettings.showHeader, dataSource = dataSettings.dataSource, changedDataSource = [], pushedRowColl = [];
            if (!editedChanges.length) {
                ssObj.alert("Please edit any cells and try again!");
                return;
            }
            ssObj.showWaitingPopUp();
            for (key in editedChanges) {
                rowIdx = editedChanges[key].CellIndex.RowIndex;
                if (isShowHeader)
                    rowIdx--;
                if (pushedRowColl.indexOf(rowIdx) < 0) {
                    changedDataSource.push(dataSource[rowIdx]);
                    pushedRowColl.push(rowIdx);
                }
            }
            $.ajax({
                type: "POST",
                url: "Home/SaveChangesToDatabase",
                data: { editedValues: JSON.stringify(changedDataSource) },
                success: function (response) {
                    ssObj.alert(response);
                    ssObj.hideWaitingPopUp();
                },
                error: function (err) {
                    ssObj.alert(err.statusText);
                    ssObj.hideWaitingPopUp();
                }
            });
        });
    });
    function onLoadComplete(args) {
        if (!this.isImport) {
            this.XLEdit.saveEditingValue(); //Get or reset the saved changes collection after data loaded
        }
    }
</script>

 

CONTROLLER

private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
public ActionResult Index()
{
    @ViewBag.Datasource = GetDataFromDB();
    return View();
}
 
private List<Order> GetDataFromDB()
{
    List<Order> orders = new List<Order>();
    SqlConnection sqlCon = new SqlConnection(connetionString);
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table]", sqlCon);
    sqlCon.Open();
    SqlDataReader sqlDR = sqlComm.ExecuteReader();
    while (sqlDR.Read())
    {
        Order order = new Order();
        order.OrderID = (int)sqlDR.GetValue(0);
        order.ItemName = sqlDR.GetValue(1).ToString();
        order.Quantity = sqlDR.GetValue(2).ToString();
        order.Price = sqlDR.GetValue(3).ToString();
        order.Amount = sqlDR.GetValue(4).ToString();
        orders.Add(order);
    }
    sqlCon.Close();
    return orders;
}
 
//Save changes only
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult SaveChangesToDatabase(string editedValues)
{
    JavaScriptSerializer serialize = new JavaScriptSerializer();
    List<Order> editedData = serialize.Deserialize<List<Order>>(editedValues);
 
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    for (int i = 0; i < editedData.Count; i++)
    {
        var order = editedData[i];
        SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [OrderID] = '" + order.OrderID + "'", sqlCon);
        var hasFileInDB = selectComm.ExecuteScalar();
        if (hasFileInDB == null)
        {
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon);
            sqlComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", order.Price);
            sqlComm.Parameters.AddWithValue("@Amount", order.Amount);
            sqlComm.ExecuteNonQuery();
        }
        else
        {
            SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            updateComm.CommandTimeout = 0;
            updateComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            updateComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            updateComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            updateComm.Parameters.AddWithValue("@Price", order.Price);
            updateComm.Parameters.AddWithValue("@Amount", order.Amount);
            updateComm.ExecuteNonQuery();
        }
    }
    sqlCon.Close();
    return Content("Successfully updated");
}

 

ASP

 

ASPX

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
<ej:Spreadsheet ID="FlatSpreadsheet" ColumnWidth="110" runat="server">
    <Sheets>
        <ej:Sheet PrimaryKey="OrderID"></ej:Sheet>
    </Sheets>
    <ClientSideEvents LoadComplete="onLoadComplete" />
</ej:Spreadsheet>
<script type="text/javascript">
 
    $(function () {
        $("#saveChangesToDB").on("click", function () {
            var ssObj = $("#" + '<% =FlatSpreadsheet.ClientID %>').data("ejSpreadsheet"), key, rowIdx, sheetIdx = ssObj.getActiveSheetIndex(), editedChanges = ssObj.XLEdit.saveEditingValue().EditedData,
                dataSettings = ssObj.getDataSettings(sheetIdx)[0], isShowHeader = dataSettings.showHeader, dataSource = dataSettings.dataSource, changedDataSource = [], pushedRowColl = [];
            if (!editedChanges.length) {
                ssObj.alert("Please edit any cells and try again!");
                return;
            }
            ssObj.showWaitingPopUp();
            for (key in editedChanges) {
                rowIdx = editedChanges[key].CellIndex.RowIndex;
                if (isShowHeader)
                    rowIdx--;
                if (pushedRowColl.indexOf(rowIdx) < 0) {
                    changedDataSource.push(dataSource[rowIdx]);
                    pushedRowColl.push(rowIdx);
                }
            }
            $.ajax({
                type: "POST",
                url: "Default.aspx/SaveChangesToDatabase",
                data: JSON.stringify({ editedValues: JSON.stringify(changedDataSource) }),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    ssObj.alert(response.d);
                    ssObj.hideWaitingPopUp();
                },
                error: function (err) {
                    ssObj.alert(err.statusText);
                    ssObj.hideWaitingPopUp();
                }
            });
        });
    });
    function onLoadComplete(args) {
        if (!this.isImport) {
            this.XLEdit.saveEditingValue(); //Get or reset the saved changes collection after data loaded
        }
    }
</script>

 

ASHX

private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.FlatSpreadsheet.Sheets[0].Datasource = GetDataFromDB();
    }
}
 
private List<Order> GetDataFromDB()
{
    List<Order> orders = new List<Order>();
    SqlConnection sqlCon = new SqlConnection(connetionString);
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table]", sqlCon);
    sqlCon.Open();
    SqlDataReader sqlDR = sqlComm.ExecuteReader();
    while (sqlDR.Read())
    {
        Order order = new Order();
        order.OrderID = (int)sqlDR.GetValue(0);
        order.ItemName = sqlDR.GetValue(1).ToString();
        order.Quantity = sqlDR.GetValue(2).ToString();
        order.Price = sqlDR.GetValue(3).ToString();
        order.Amount = sqlDR.GetValue(4).ToString();
        orders.Add(order);
    }
    sqlCon.Close();
    return orders;
}
 
//Save changes only
[WebMethod]
public static string SaveChangesToDatabase(string editedValues)
{
    JavaScriptSerializer serialize = new JavaScriptSerializer();
    List<Order> editedData = serialize.Deserialize<List<Order>>(editedValues);
 
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    for (int i = 0; i < editedData.Count; i++)
    {
        var order = editedData[i];
        SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [OrderID] = '" + order.OrderID + "'", sqlCon);
        var hasFileInDB = selectComm.ExecuteScalar();
        if (hasFileInDB == null)
        {
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon);
            sqlComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", order.Price);
            sqlComm.Parameters.AddWithValue("@Amount", order.Amount);
            sqlComm.ExecuteNonQuery();
        }
        else
        {
            SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            updateComm.CommandTimeout = 0;
            updateComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            updateComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            updateComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            updateComm.Parameters.AddWithValue("@Price", order.Price);
            updateComm.Parameters.AddWithValue("@Amount", order.Amount);
            updateComm.ExecuteNonQuery();
        }
    }
    sqlCon.Close();
    return "Successfully updated";
}

 

ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon