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: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to perform batch update in SQL table using dataManager in the Spreadsheet

Platform: JavaScript |
Control: ejSpreadSheet |
Published Date: July 31, 2018 |
Last Revised Date: May 22, 2019

Description

This knowledge base explains how to perform batch update in SQL table using Data Manager.

Solution

The edited data in Spreadsheet is saved in the SQL table using “saveBatchChanges()” method.

 

JavaScript

HTML

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

 

JS

$(function () {
    var dataManager = ej.DataManager({
        url: "Home/GetDataFromDB", adaptor: new ej.UrlAdaptor(), batchUrl: "Home/BatchUpdate"
    });
    $("#Spreadsheet").ejSpreadsheet({
        sheets: [
            {
                dataSource: dataManager,
                primaryKey: "OrderID"
            }
        ],
    });
});
 
$("#saveChangesToDB").on("click", function () {
    //To save the edited data.
    var ssObj = $("#Spreadsheet").data("ejSpreadsheet");
    ssObj.saveBatchChanges(ssObj.getActiveSheetIndex());
});

 

Controller

public ActionResult 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 Json(new { result = orders });
}
 
//Save the batch changes.
public void BatchUpdate(List<Order> changed, List<Order> added, List<Order> deleted, string action, string key)
{
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    if (changed != null)
    {
        int length = changed.Count();
        for (var i = 0; i < length; i++)
        {
            var ord = changed[i];
            SqlCommand sqlComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            sqlComm.CommandTimeout = 0;
            sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", ord.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", ord.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", ord.Price);
            sqlComm.Parameters.AddWithValue("@Amount", ord.Amount);
            sqlComm.ExecuteNonQuery();
        }
    }
    if (added != null)
    {
        int length = added.Count();
        for (var i = 0; i < length; i++)
        {
            var ord = added[i];
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon); // Add data to DB.
            sqlComm.CommandTimeout = 0;
            sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", ord.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", ord.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", ord.Price);
            sqlComm.Parameters.AddWithValue("@Amount", ord.Amount);
            sqlComm.ExecuteNonQuery();
        }
    }
    if (deleted != null)
    {
        int length = deleted.Count();
        for (var i = 0; i < length; i++)
        {
            var ord = deleted[i];
            SqlCommand sqlComm = new SqlCommand("Delete FROM [dbo].[Table] WHERE OrderID=@OrderID", sqlCon); // Delete data from DB.
            sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
            sqlComm.ExecuteNonQuery();
        }
    }
}

 

MVC

CSHTML

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
@(Html.EJ().DataManager("DataManager").URL("Home/GetDataFromDB").Adaptor(AdaptorType.UrlAdaptor).BatchURL("Home/BatchUpdate"))
@(Html.EJ().Spreadsheet<object>("Spreadsheet")
    .Sheets(sheet =>
    {
        sheet.DataManagerID("DataManager").PrimaryKey("OrderID").Add();
    })
)
 
<script>
    $("#saveChangesToDB").on("click", function () {
        //To save the edited data.
        var ssObj = $("#Spreadsheet").data("ejSpreadsheet");
        ssObj.saveBatchChanges(ssObj.getActiveSheetIndex());
    });
</script>

 

Controller

private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
 
public ActionResult 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 Json(new { result = orders });
}
 
//Save the batch changes
public void BatchUpdate(List<Order> changed, List<Order> added, List<Order> deleted, string action, string key)
{
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    if (changed != null)
    {
        int length = changed.Count();
        for (var i = 0; i < length; i++)
        {
            var ord = changed[i];
            SqlCommand sqlComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            sqlComm.CommandTimeout = 0;
            sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", ord.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", ord.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", ord.Price);
            sqlComm.Parameters.AddWithValue("@Amount", ord.Amount);
            sqlComm.ExecuteNonQuery();
        }
    }
    if (added != null)
    {
        int length = added.Count();
        for (var i = 0; i < length; i++)
        {
            var ord = added[i];
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon); // Add data to DB.
            sqlComm.CommandTimeout = 0;
            sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", ord.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", ord.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", ord.Price);
            sqlComm.Parameters.AddWithValue("@Amount", ord.Amount);
            sqlComm.ExecuteNonQuery();
        }
    }
    if (deleted != null)
    {
        int length = deleted.Count();
        for (var i = 0; i < length; i++)
        {
            var ord = deleted[i];
            SqlCommand sqlComm = new SqlCommand("Delete FROM [dbo].[Table] WHERE OrderID=@OrderID", sqlCon); // Delete data from DB.
            sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
            sqlComm.ExecuteNonQuery();
        }
    }
}

 

ASP

ASPX

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
<ej:DataManager ID="dataManager" URL="Default.aspx/GetDataFromDB" Adaptor="UrlAdaptor" BatchURL= "Default.aspx/BatchUpdate" runat="server"/>
<ej:Spreadsheet ID="Spreadsheet" runat="server">
    <Sheets>
        <ej:Sheet DataManagerID = "dataManager" PrimaryKey="OrderID"></ej:Sheet>
    </Sheets>
</ej:Spreadsheet>
 
<script>
    $("#saveChangesToDB").on("click", function () {
        //To save the edited data.
        var ssObj = $("#Spreadsheet").data("ejSpreadsheet");
        ssObj.saveBatchChanges(ssObj.getActiveSheetIndex());
    });
</script>

 

ASPX.CS

private static string connectionStrings = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
 
[WebMethod]        
    public static object GetDataFromDB()
    {
        List<Order> orders = new List<Order>();
        SqlConnection sqlCon = new SqlConnection(connectionStrings);
        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 new { result = orders.ToList() };
    }
 
    //Save the batch changes
    [WebMethod]
    public static void BatchUpdate(List<Order> changed, List<Order> added, List<Order> deleted, string action, string key)
    {
        SqlConnection sqlCon = new SqlConnection(connectionStrings);
        sqlCon.Open();
        if (changed != null)
        {
            int length = changed.Count();
            for (var i = 0; i < length; i++)
            {
                var ord = changed[i];
                SqlCommand sqlComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
                sqlComm.CommandTimeout = 0;
                sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
                sqlComm.Parameters.AddWithValue("@ItemName", ord.ItemName);
                sqlComm.Parameters.AddWithValue("@Quantity", ord.Quantity);
                sqlComm.Parameters.AddWithValue("@Price", ord.Price);
                sqlComm.Parameters.AddWithValue("@Amount", ord.Amount);
                sqlComm.ExecuteNonQuery();
            }
        }
        if (added != null)
        {
            int length = added.Count();
            for (var i = 0; i < length; i++)
            {
                var ord = added[i];
                SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon); // Add data to DB.
                sqlComm.CommandTimeout = 0;
                sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
                sqlComm.Parameters.AddWithValue("@ItemName", ord.ItemName);
                sqlComm.Parameters.AddWithValue("@Quantity", ord.Quantity);
                sqlComm.Parameters.AddWithValue("@Price", ord.Price);
                sqlComm.Parameters.AddWithValue("@Amount", ord.Amount);
                sqlComm.ExecuteNonQuery();
            }
        }
        if (deleted != null)
        {
            int length = deleted.Count();
            for (var i = 0; i < length; i++)
            {
                var ord = deleted[i];
                SqlCommand sqlComm = new SqlCommand("Delete FROM [dbo].[Table] WHERE OrderID=@OrderID", sqlCon); // Delete data from DB.
                sqlComm.Parameters.AddWithValue("@OrderID", ord.OrderID);
                sqlComm.ExecuteNonQuery();
            }
        }
    }
}

 

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.

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