Load from db and save to db changes from spreadsheet

[andreea giurgiu]

Hello,

How can I save input changes from my spreadsheet in my MSSQL db? Is saveAsJson the right way to go? Do you have an example of how it can be used?

Thank you

[Saranya Dhayalan]

Hi Andreea,  

  

Thank you for contacting Syncfusion support 

  

We would like to let you know that you can save the JSON data from the spreadsheet to the server using save method in the client side and in the server side you can use JSONData property of SaveSettings to store it in the database and suggest you to return the JSON data saved in the server to load it to the spreadsheet control using open method.  

  

 

 

SpreadsheetController.cs  

public string SaveFiletoDB(SaveSettings saveSettings)  

        {  

            //Save JSON to database  

            SqlConnection sqlCon = new SqlConnection(connectionString);  

            sqlCon.Open();  

            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[SpreadsheetData]([filename], [data]) VALUES (@filename, @data)", sqlCon);  

            sqlComm.Parameters.AddWithValue("@filename", saveSettings.FileName);  

            sqlComm.Parameters.AddWithValue("@data", saveSettings.JSONData);  

            sqlComm.ExecuteNonQuery();  

            sqlCon.Close();  

            return "Saved in Server";  

        }  

  

        public ActionResult LoadFromDataBase(OpenRequest openRequest)  

        {  

            //Load JSON from database  

            SqlConnection sqlCon = new SqlConnection(connectionString);  

            SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[SpreadsheetData] WHERE [filename] = '" + ((String[])openRequest.File)[0] + "'", sqlCon);  

            sqlCon.Open();  

            SqlDataReader sqlDR = sqlComm.ExecuteReader();  

            string JSONData = "";  

            while (sqlDR.Read())  

            {  

                JSONData = sqlDR.GetValue(1).ToString();  

                openRequest.File = JSONData;  

            }  

            sqlCon.Close();  

            string data = "{\"Workbook\":" + JSONData + "}";  

            return Content(data); // returning JSON data to spreadsheet control  

        }  

  

App.js  

class App extends Component {  

  saveToServer() {  

    this.spreadsheet.save({ fileName: document.getElementById("filename").value });  

  }  

  loadFromDataBase() {  

    this.spreadsheet.open({ file: document.getElementById("filename").value });  

  }  

  

  render() {  

    return (  

      <div>  

        <SpreadsheetComponent ref={(ssObj) => { this.spreadsheet = ssObj }} openUrl="http://localhost:56531/Spreadsheet/LoadFromDataBase" saveUrl="http://localhost:56531/Spreadsheet/SaveFiletoDB">  

        </SpreadsheetComponent>  

        <button className="e-btn" onClick={this.saveToServer.bind(this)}>Save Back to Server</button>  

        <button className="e-btn" onClick={this.loadFromDataBase.bind(this)}>Load Data</button>  

        <label>File name:</label>  

        <input type="text" id="filename" defaultValue="Workbook1" placeholder="Specify file name"></input>  

      </div>  

    );  

  }  

}  

  

  

Could you please check the above sample and get back to us if you need further assistance on this? 

 

Regards,  

Saranya D