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";
}
