Update Database Table with modified cells

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

    Posted 11 Sep 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
    300 posts

    Posted 14 Sep 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 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
    300 posts

    Posted 26 Sep 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 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 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
    300 posts

    Posted 24 Oct 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.
Back to Top