Update Database Table with modified cells

[Karthik]

Hi,

I have programmatically binding the RadSpreadsheet control with the values from my SQL Table. When the user modifies the cells value in RadSpreadsheet, i want only those modified cell values to be updated in my table.

I browsed the forum and I found CustomDatabaseProvider can be used or this operation. However, the example that I had was truncating the entire data from table instead of updating only the modified row.

public override void SaveWorkbook(Workbook workbook)
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SpreadsheetSampleConnectionString"].ConnectionString))
            {
                conn.Open();
                string query = "DELETE FROM [Products]";
                SqlCommand clearCmd = new SqlCommand(query, conn);
                clearCmd.ExecuteNonQuery();

                string insertQuery = "INSERT INTO Products(ProductName,Description) VALUES(@param1,@param2)";
                foreach (var row in workbook.Sheets[0].Rows)
                {
                    SqlCommand insertCmd = new SqlCommand(insertQuery, conn);

                    insertCmd.Parameters.Add("@param1", SqlDbType.NVarChar, 50).Value = row.Cells[0].Value;
                    insertCmd.Parameters.Add("@param2", SqlDbType.NVarChar, 50).Value = row.Cells[1].Value;

                    insertCmd.CommandType = CommandType.Text;
                    insertCmd.ExecuteNonQuery();
                }
            }
        }

 

Can anyone let me know how to do this?

[Peter Milchev]

Hello Karthik,

The provided example is implemented to delete the records and then insert them.

In order to update the table instead of deleting the records and inserting them again, the SqlCommand should be modified accordingly. 

Here are some resources that might be helpful to create the correct query: 

• SqlDataAdapter.UpdateCommand Property;
• C# – Read, Insert, Update, Delete From SQL Database;
• SQL Insert/Update/Select/Delete using C#;
• SQL update statement in C#;

Regards,
Peter Milchev
Progress Telerik

[Karthik]

Hi Peter,

 

Thanks for your response. If i am updating a value in a grid, i will be able to pass the conditions based on primary key value or some other value in the update statement. 

 

I want to know how to implement it in the spreadsheet? Do i need to use any primary key value while updating? If yes, how can I get that from the sheet. I am a new user to spreadsheet. Hence I am confused on this part.

[Peter Milchev]

Hello Karthik,

The Spreadsheet custom provider has two methods which should be implemented - SaveWorkbook and GetSheets.

The SaveWorkbook method is used to save any changes to the database. What the SaveWorkbook method provides is a Workbook object containing the modified spreadsheet workbook. In order to save the changes, the records in the workbook should be iterated and using a custom logic to be saved in the database of choice.

The example in the SpreadsheetDataBaseProvider code library project demonstrates how every row of the first sheet is iterated and the values of the first two cells of every row are saved in the DataBase. 

Based on the given requirement, the custom SQL query in the SaveWorkbook method of the Custom Provider should be modified.

Regards,
Peter Milchev
Progress Telerik

[Karthik]

Hi Peter,

Thanks for your reply.

If I am changing multiple values and adding new rows, how can I track these things. Is there any built in method or event that I can use for tracking the updated values?

 

[Karthik]

Hi Peter,

You have mentioned that the code example you have linked demonstrates how every row of the first sheet is iterated and the values of the first two cells of every row are saved in the DataBase. However, its same as the one that I used. It deletes all rows and inserts all rows.

There is no code piece that iterates through every row of first sheet and saves values of two cells of every row in the database.

It will be good if I get a code piece of this iterating through every row and saving the values in the database. We are planning to use the spreadsheet in one of our product and it would be very helpful.

I have a purchased license with our organization and just for demo purpose i am using trial license.  

 

[quote]Peter Milchev said:Hello Karthik,

The Spreadsheet custom provider has two methods which should be implemented - SaveWorkbook and GetSheets.

The SaveWorkbook method is used to save any changes to the database. What the SaveWorkbook method provides is a Workbook object containing the modified spreadsheet workbook. In order to save the changes, the records in the workbook should be iterated and using a custom logic to be saved in the database of choice.

The example in the SpreadsheetDataBaseProvider code library project demonstrates how every row of the first sheet is iterated and the values of the first two cells of every row are saved in the DataBase. 

Based on the given requirement, the custom SQL query in the SaveWorkbook method of the Custom Provider should be modified.

Regards,
Peter Milchev
Progress Telerik

[/quote]

[Peter Milchev]

Hello Karthik,

I am afraid that there are no built-in methods that allow tracking of the changed values. This functionality should be implemented manually in the SaveWorkbook method and based on the requirements the developer should choose the most suitable approach to check which cells are modified.

Regards,
Peter Milchev
Progress Telerik

[Norbert]

Hello,
I use a class to save and read spreadsheet-data from a database. This class inherits from "SpreadsheetProviderBase".
If I enter text in cells with the keyboard, the contents of the last cell are not saved.
How can I fix the error?

Here my method "SaveWorkbook":

       public override void SaveWorkbook(Workbook workbook)
        {  
                Workbook wb = workbook.ToDocument();
                Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider provider = new  Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
                byte[] spreadSheetData = provider.Export(wb);

                //Save spreadSheetData in database...
        }

Telerik-Version: 2015.3.1111.45

Best regards

Norbert

[Norbert]

The error disappeared with the Telerik version 2017.3.913.