Category / Section
How to perform batch update in SQL table using dataManager in JavaScript Spreadsheet?
4 mins read
Description
This knowledge base explains how to perform batch update in SQL table using Data Manager in JavaScript Spreadsheet.
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();
}
}
}
}
Conclusion
I hope you enjoyed learning about how to perform batch update in SQL table using dataManager in JavaScript Spreadsheet.
You
can refer to our JavaScript Spreadsheet feature tour page to know about its
other groundbreaking feature representations and documentation, and
how to quickly get started for configuration specifications. You can also
explore our JavaScript Spreadsheet example to understand how to
create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
