Category / Section
How to load and save a Sql table in Spreadsheet
5 mins read
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";
}
Did not find the solution
Contact Support
ES
esayas
SP
Sangeetha Priya Murugan
Hi esayas,
Thank you for your update.
We have checked your reported requirement and we would suggest you to refer the below help documentation link to getting started with our EJ1 controls in ASP.NET MVC.
https://help.syncfusion.com/aspnetmvc/getting-started-vs-2019 https://help.syncfusion.com/aspnetmvc/spreadsheet/getting-started
For spreadsheet server side dependencies, please refer the below link.
https://help.syncfusion.com/aspnetmvc/spreadsheet/open-and-save#server-dependencies
Regards, Sangeetha M
ES
esayas
thank you for the update. How I can insert spreadsheet each cell data to the mysql database table in aspnetcore 3 mvc & visual studio 2019 using latest sync fusion spreadsheet or latest of above code that compatible with aspnetcore 3 MVC & visual studio 2019 editer. not excel file import.
SP
Sangeetha Priya Murugan
Hi esayas,
Thank you for your update.
We have checked your reported requirement and we would like to let you know that in this knowledge base document we have load the data from the sql table initially. And save the edited changes in the database via button click event. The below documentation link help to render the EJ1 spreadsheet with in ASP.NET MVC with VS 2019.
https://help.syncfusion.com/aspnetmvc/getting-started-vs-2019
https://help.syncfusion.com/aspnetmvc/spreadsheet/getting-started
Could you please check the above details and get back to us, if you need any further assistance on this.
Regards, Sangeetha M
ES
esayas
thank you, I will check it
Thank you that great one to try. I am try to use the mvc one, but brings error at EJ(), and some nuget required better to mention.I appreciate to know aspnet core 3 MVC using Visual studio 2019 of CSHTML and controller compatible code. I like and use on the try of syncfusion spreadsheets UI for now to choose from other EPPul, ...