Update Database Table with modified cells

9 posts, 0 answers
  1. Karthik
    Karthik avatar
    10 posts
    Member since:
    Nov 2011

    Posted 11 Sep 2017 Link to this post

    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?

  2. Peter Milchev
    Admin
    Peter Milchev avatar
    354 posts

    Posted 14 Sep 2017 Link to this post

    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: 
    Regards,
    Peter Milchev
    Progress Telerik
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Karthik
    Karthik avatar
    10 posts
    Member since:
    Nov 2011

    Posted 21 Sep 2017 in reply to Peter Milchev Link to this post

    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.

  4. Peter Milchev
    Admin
    Peter Milchev avatar
    354 posts

    Posted 26 Sep 2017 Link to this post

    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
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  5. Karthik
    Karthik avatar
    10 posts
    Member since:
    Nov 2011

    Posted 19 Oct 2017 in reply to Peter Milchev Link to this post

    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?

     

  6. Karthik
    Karthik avatar
    10 posts
    Member since:
    Nov 2011

    Posted 20 Oct 2017 in reply to Peter Milchev Link to this post

    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.  

     

    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

    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  7. Peter Milchev
    Admin
    Peter Milchev avatar
    354 posts

    Posted 24 Oct 2017 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  8. Norbert
    Norbert avatar
    4 posts
    Member since:
    Sep 2015

    Posted 05 Jan Link to this post

    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

  9. Norbert
    Norbert avatar
    4 posts
    Member since:
    Sep 2015

    Posted 15 Jan in reply to Norbert Link to this post

    The error disappeared with the Telerik version 2017.3.913.
Back to Top