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
void spreadsheetControl_PreviewMouseLeftButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
{
Point winPoint = e.GetPosition(spreadsheetControl);
System.Drawing.Point point = new System.Drawing.Point((int)winPoint.X, (int)winPoint.Y);
Cell cell = spreadsheetControl.GetCellFromPoint(point);
if (cell == null)
return;
Worksheet sheet = spreadsheetControl.ActiveWorksheet;
string cellReference = 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")
{
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.
else if (cellReference == "I6")
{
HideDataEntryForm(sheet);
}
}
void AddRow(Worksheet sheet)
{
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 (Exception ex)
{
string message = string.Format("Cannot add a row to a database table.\n{0}", ex.Message);
MessageBox.Show(message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
void HideDataEntryForm(Worksheet sheet)
{
CellRange range = sheet.Range.Parse("C4,C6,C8,E4,E6,E8,G4,G6");
range.ClearContents();
sheet.Rows.Hide(2, 9);
}
void ApplyChanges()
{
try
{
// Send the updated data back to the database.
adapter.Update(dataSet.Suppliers);
}
catch (Exception ex)
{
string message = string.Format("Cannot update data in a database table.\n{0}", ex.Message);
MessageBox.Show(message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
void spreadsheetControl_RowsRemoving(object sender, RowsChangingEventArgs e)
{
Worksheet sheet = spreadsheetControl.ActiveWorksheet;
CellRange rowRange = sheet.Range.FromLTRB(0, e.StartIndex, 16383, e.StartIndex + e.Count - 1);
CellRange boundRange = 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))
{
MessageBoxResult result = MessageBox.Show("Want to delete the selected supplier(s)?", "Delete",
MessageBoxButton.YesNo, MessageBoxImage.Question);
applyChangesOnRowsRemoved = result == MessageBoxResult.Yes;
e.Cancel = result == MessageBoxResult.No;
return;
}
}
void spreadsheetControl_RowsRemoved(object sender, RowsChangedEventArgs e)
{
if (applyChangesOnRowsRemoved)
{
applyChangesOnRowsRemoved = false;
// Update data in the database.
ApplyChanges();
}
}
void buttonAddRecord_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
CloseInplaceEditor();
Worksheet sheet = spreadsheetControl.ActiveWorksheet;
// Display the data entry form on the worksheet to add a new record to the "Suppliers" data table.
if (!sheet.Rows[4].Visible)
sheet.Rows.Unhide(2, 9);
spreadsheetControl.SelectedCell = sheet["C4"];
}
void buttonRemoveRecord_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
CloseInplaceEditor();
Worksheet sheet = spreadsheetControl.ActiveWorksheet;
CellRange selectedRange = spreadsheetControl.Selection;
CellRange boundRange = sheet.DataBindings[0].Range;
// Verify that the selected cell range belongs to the data-bound range.
if (!boundRange.IsIntersecting(selectedRange) || selectedRange.TopRowIndex < boundRange.TopRowIndex)
{
MessageBox.Show("Select a record first!", "Remove Record", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
// Remove the topmost row of the selected cell range.
sheet.Rows.Remove(selectedRange.TopRowIndex);
}
void buttonApplyChanges_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
CloseInplaceEditor();
// Update data in the database.
ApplyChanges();
}
void buttonCancelChanges_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
// 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);
}
void CloseInplaceEditor()
{
if (spreadsheetControl.IsCellEditorActive)
spreadsheetControl.CloseCellEditor(DevExpress.XtraSpreadsheet.CellEditorEnterValueMode.Default);
}