Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheets
    [Expand]Rows and Columns
    [Expand]Cells
    [Expand]Formulas
    [Expand]Import and Export Data
    [Collapse]Data Binding
      How to: Bind a Spreadsheet to an MS SQL Server Database (Part 1)
      How to: Bind a Spreadsheet to an MS SQL Server Database (Part 2)
      How to: Bind a Data Grid to a Cell Range
      How to Bind a Spreadsheet to a List of Objects
    [Expand]Mail Merge
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Tables
    [Expand]Pivot Tables
    [Expand]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Customization
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

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

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.

Expanded Create a Spreadsheet Application

To get started, create a new spreadsheet application using the DevExpress Template Gallery.

  • Start a new project in Visual Studio and select the DevExpress v17.2 Template Gallery item in the invoked New Project dialog.

  • In the DevExpress Template Gallery, select the Spreadsheet Application template in the WinForms Business Solutions group.

  • Specify the project name and click Create Project.

A ready-to-use spreadsheet application with the ribbon UI will be automatically generated.

Expanded 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=T472324).

    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.

Expanded 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).

Expanded 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.

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T472324.

Expanded Result

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

Expanded See Also

How would you rate this topic?​​​​​​​