How to: Bind a Spreadsheet to an MS SQL Server Database (Part 2)
- 9 minutes to read
This tutorial shows how to modify the data-bound spreadsheet application created in the How to: Bind a Spreadsheet to an MS SQL Server Database (Part 1) example to enable end-users to add, modify, and remove data in the connected data table.
Develop a User Interface
To provide the capability to interact with the database, add the following UI elements to the spreadsheet application created in the How to: Bind a Spreadsheet to an MS SQL Server Database (Part 1) example.
UI Element | Implementation | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Data Entry Form | On the template worksheet, create a data entry form that will be used to insert new records into the database. For this, do the following.
| |||||||||||||||
Ribbon Buttons | Create a new ribbon group and add the following buttons to it.
The table below contains the description of each bar button item.
|
Post Data to the Database
When you bind a control to a DataTable containing data from a database and then change data by adding, deleting or modifying records, these changes are accumulated in the DataTable but are not automatically posted to the underlying database. You have to manually call the Update method of the TableAdapter to propagate the modified data to the data source. Before calling this method, make sure that the appropriate INSERT, UPDATE, and DELETE SQL statements are specified for the data adapter. Otherwise, the Update method will generate an exception. For the SuppliersTableAdapter used in the current example, the required commands were generated automatically when the adapter was originally configured.
The table below describes how to add, modify, or delete data in the connected data source step by step.
Action | Implementation |
|---|---|
Add a record | Important The SpreadsheetControl does not support inserting rows at the end of a data-bound range, while a DataTable supports only this kind of operation. To avoid this restriction, the current example uses a data entry form to add new records to the data source.
All changes made in the data source are immediately reflected in the bound worksheet. |
Apply changes | In the Apply Changes button’s ItemClick event handler, call the Update method of the SuppliersTableAdapter to save the modified data to the database. |
Cancel changes | Handle the Cancel Changes button’s ItemClick event. In the event handler, close the cell’s in-place editor if it’s currently active and then call the Fill method of the SuppliersTableAdapter to load the latest saved data from the database. |
Remove a record |
|
Code Sample
Note
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-bind-a-spreadsheet-to-an-ms-sql-server-database-wpf-spreadsheet-t480591.
Private Sub spreadsheetControl_PreviewMouseLeftButtonDown(ByVal sender As Object, ByVal e As System.Windows.Input.MouseButtonEventArgs)
Dim winPoint As Point = e.GetPosition(spreadsheetControl)
Dim point As New System.Drawing.Point(CInt((winPoint.X)), CInt((winPoint.Y)))
Dim cell As Cell = spreadsheetControl.GetCellFromPoint(point)
If cell Is Nothing Then
Return
End If
Dim sheet As Worksheet = spreadsheetControl.ActiveWorksheet
Dim cellReference As String = cell.GetReferenceA1()
' If the "Save" cell is clicked in the data entry form,
' add a row containing the entered values to the database table.
If cellReference = "I4" Then
AddRow(sheet)
HideDataEntryForm(sheet)
ApplyChanges()
' If the "Cancel" cell is clicked in the data entry form,
' cancel adding new data and hide the data entry form.
ElseIf cellReference = "I6" Then
HideDataEntryForm(sheet)
End If
End Sub
Private Sub AddRow(ByVal sheet As Worksheet)
Try
' Append a new row to the "Suppliers" data table.
dataSet.Suppliers.AddSuppliersRow(sheet("C4").Value.TextValue, sheet("C6").Value.TextValue, sheet("C8").Value.TextValue, sheet("E4").Value.TextValue, sheet("E6").Value.TextValue, sheet("E8").Value.TextValue, sheet.Cells("G4").DisplayText, sheet.Cells("G6").DisplayText)
Catch ex As Exception
Dim message As String = String.Format("Cannot add a row to a database table." & ControlChars.Lf & "{0}", ex.Message)
MessageBox.Show(message, "Error", MessageBoxButton.OK, MessageBoxImage.Error)
End Try
End Sub
Private Sub HideDataEntryForm(ByVal sheet As Worksheet)
Dim range As Range = sheet.Range.Parse("C4,C6,C8,E4,E6,E8,G4,G6")
range.ClearContents()
sheet.Rows.Hide(2, 9)
End Sub
Private Sub ApplyChanges()
Try
' Send the updated data back to the database.
adapter.Update(dataSet.Suppliers)
Catch ex As Exception
Dim message As String = String.Format("Cannot update data in a database table." & ControlChars.Lf & "{0}", ex.Message)
MessageBox.Show(message, "Error", MessageBoxButton.OK, MessageBoxImage.Error)
End Try
End Sub
Private Sub spreadsheetControl_RowsRemoving(ByVal sender As Object, ByVal e As RowsChangingEventArgs)
Dim sheet As Worksheet = spreadsheetControl.ActiveWorksheet
Dim rowRange As Range = sheet.Range.FromLTRB(0, e.StartIndex, 16383, e.StartIndex + e.Count - 1)
Dim boundRange As Range = sheet.DataBindings(0).Range
' If the rows to be removed belong to the data-bound range,
' display a dialog requesting the user to confirm the deletion of records.
If boundRange.IsIntersecting(rowRange) Then
Dim result As MessageBoxResult = MessageBox.Show("Want to delete the selected supplier(s)?", "Delete", MessageBoxButton.YesNo, MessageBoxImage.Question)
applyChangesOnRowsRemoved = result = MessageBoxResult.Yes
e.Cancel = result = MessageBoxResult.No
Return
End If
End Sub
Private Sub spreadsheetControl_RowsRemoved(ByVal sender As Object, ByVal e As RowsChangedEventArgs)
If applyChangesOnRowsRemoved Then
applyChangesOnRowsRemoved = False
' Update data in the database.
ApplyChanges()
End If
End Sub
Private Sub buttonAddRecord_ItemClick(ByVal sender As Object, ByVal e As DevExpress.Xpf.Bars.ItemClickEventArgs)
CloseInplaceEditor()
Dim sheet As Worksheet = spreadsheetControl.ActiveWorksheet
' Display the data entry form on the worksheet to add a new record to the "Suppliers" data table.
If Not sheet.Rows(4).Visible Then
sheet.Rows.Unhide(2, 9)
End If
spreadsheetControl.SelectedCell = sheet("C4")
End Sub
Private Sub buttonRemoveRecord_ItemClick(ByVal sender As Object, ByVal e As DevExpress.Xpf.Bars.ItemClickEventArgs)
CloseInplaceEditor()
Dim sheet As Worksheet = spreadsheetControl.ActiveWorksheet
Dim selectedRange As Range = spreadsheetControl.Selection
Dim boundRange As Range = sheet.DataBindings(0).Range
' Verify that the selected cell range belongs to the data-bound range.
If (Not boundRange.IsIntersecting(selectedRange)) OrElse selectedRange.TopRowIndex < boundRange.TopRowIndex Then
MessageBox.Show("Select a record first!", "Remove Record", MessageBoxButton.OK, MessageBoxImage.Error)
Return
End If
' Remove the topmost row of the selected cell range.
sheet.Rows.Remove(selectedRange.TopRowIndex)
End Sub
Private Sub buttonApplyChanges_ItemClick(ByVal sender As Object, ByVal e As DevExpress.Xpf.Bars.ItemClickEventArgs)
CloseInplaceEditor()
' Update data in the database.
ApplyChanges()
End Sub
Private Sub buttonCancelChanges_ItemClick(ByVal sender As Object, ByVal e As DevExpress.Xpf.Bars.ItemClickEventArgs)
' Close the cell in-place editor if it's currently active.
CloseInplaceEditor()
' Load the latest saved data into the "Suppliers" data table.
adapter.Fill(dataSet.Suppliers)
End Sub
Private Sub CloseInplaceEditor()
If spreadsheetControl.IsCellEditorActive Then
spreadsheetControl.CloseCellEditor(DevExpress.XtraSpreadsheet.CellEditorEnterValueMode.Default)
End If
End Sub