How to: Bind a Spreadsheet to an MS SQL Server Database (Part 1)

• Oct 15, 2019
• 4 minutes to read

This example demonstrates how to create a data-bound spreadsheet application that retrieves data from an MS SQL Server Database and inserts it into a worksheet. To learn how to extend this example to provide the capability to add, modify or remove data in the data source, refer to the How to: Bind a Spreadsheet to an MS SQL Server Database (Part 2) article.

This tutorial includes the following sections.

• Create a Spreadsheet Application
• Connect to a Database
• Create a Template Document
• Bind the Template to the Data Source
• Result

Create a Spreadsheet Application

To get started, create a new spreadsheet application by following the steps below:

• Create a new WPF Application project, specify its name, and open the MainWindow.xaml file in the Designer.

• Add the SpreadsheetControl object to your project. You can do this by dragging the SpreadsheetControl item from the DX.19.1: Spreadsheet Toolbox tab to the canvas.

  

• Right-click the SpreadsheetControl object and select Layout | Reset All in the context menu to stretch the SpreadsheetControl so that it fills the entire window.

• Convert the Window to ThemedWindow. To do this, click the Window’s smart tag and select the Convert to ThemedWindow task.

  

As a result, you have a ready-to-use spreadsheet application with the ribbon user interface.

Connect to a Database

• In the Visual Studio menu, click Project | Add New Data Source…

• In the invoked Data Source Configuration Wizard, select Database and then click Next.

  

• Select Dataset to specify the type of a database model and click Next.

  

• On the next page, click New Connection… to specify the data connection to be used.

  

• In the invoked Add Connection dialog, set your data source to Microsoft SQL Server Database File (SqlClient) and specify the database file path. This example uses a connection to the Northwind database (the NWind.mdf file is included in the sample project created based on the current tutorial and available at http://www.devexpress.com/example=T480591).

  

  Click OK to apply the changes. In the Data Source Configuration Wizard, click Next, and then click Yes to copy the local data file to the project and modify the connection.

  

• Click Next on the following page to save the newly created connection string to the configuration file.

  

• On the final wizard page, select the required data fields from the Suppliers table and click Finish.

  

• Earlier, you copied the database file to the project folder. Specify that it should be copied to the output directory only if the source file in the project folder is newer than the working database in the output folder, otherwise your data will be overwritten when the project runs next time. Select the NWind.mdf file in the Solution Explorer and set its Copy to Output Directory property to Copy if newer.

  

Create a Template Document

Create a template document for receiving data from the data source. Each column in the worksheet will contain data from the corresponding field in the Suppliers data table. Hide rows 3 through 10. They are reserved for the future data entry form that will be used for adding new records to the database (for details, refer to the How to: Bind a Spreadsheet to an MS SQL Server Database (Part 2) article).

Bind the Template to the Data Source

Populate the underlying SuppliersDataTable with data from the database using the Fill method of the SuppliersTableAdapter object.

Bind a cell range on the template worksheet to the created data source using the WorksheetDataBindingCollection.BindToDataSource method.

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.

MainWindow.xaml.vb

Imports DevExpress.Spreadsheet
Imports System
Imports System.Windows
Imports WpfSpreadsheet_BindToDataSource.NWindDataSetTableAdapters
' ...
        Private dataSet As NWindDataSet
        Private adapter As SuppliersTableAdapter
        Public Sub New()
            InitializeComponent()
            BindWorksheetToDataSource()
        End Sub

        Private Sub BindWorksheetToDataSource()
            dataSet = New NWindDataSet()
            adapter = New SuppliersTableAdapter()
            ' Populate the "Suppliers" data table with data.
            adapter.Fill(dataSet.Suppliers)

            Dim workbook As IWorkbook = spreadsheetControl.Document
            ' Load the template document into the SpreadsheetControl.
            workbook.LoadDocument("Documents\Suppliers_template.xlsx", DocumentFormat.Xlsx)
            Dim sheet As Worksheet = workbook.Worksheets(0)
            ' Load data from the "Suppliers" data table into the worksheet starting from the cell "B12".
            sheet.DataBindings.BindToDataSource(dataSet.Suppliers, 11, 1)
        End Sub

MainWindow.xaml.cs

using DevExpress.Spreadsheet;
using System;
using System.Windows;
using WpfSpreadsheet_BindToDataSource.NWindDataSetTableAdapters;
// ...
        NWindDataSet dataSet;
        SuppliersTableAdapter adapter;
        public MainWindow()
        {
            InitializeComponent();
            BindWorksheetToDataSource();
        }

        private void BindWorksheetToDataSource()
        {
            dataSet = new NWindDataSet();
            adapter = new SuppliersTableAdapter();
            // Populate the "Suppliers" data table with data.
            adapter.Fill(dataSet.Suppliers);

            IWorkbook workbook = spreadsheetControl.Document;
            // Load the template document into the SpreadsheetControl.
            workbook.LoadDocument("Documents\\Suppliers_template.xlsx", DocumentFormat.Xlsx);
            Worksheet sheet = workbook.Worksheets[0];
            // Load data from the "Suppliers" data table into the worksheet starting from the cell "B12".
            sheet.DataBindings.BindToDataSource(dataSet.Suppliers, 11, 1);
        }

Result

Run the project. The following image shows the running application.