[Expand]General Information
[Collapse]WinForms Controls
  .NET Core Support
 [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
  [Collapse]Data Grid
   [Collapse]Get Started With Data Grid and Views
     [Expand]Appearance and Conditional Formatting
     [Expand]Data Editing
     [Expand]Hit Information
     [Collapse]Data Binding and Working with Columns
       Tutorial: ADO.NET Data
       Tutorial: Working with Columns in Code
       Tutorial: Unbound Columns
       Tutorial: Create and Manage Columns at Design Time
       Tutorial: Entity Framework Data
       Tutorial: Create and Manage Data in Code and Apply Data Annotation Attributes
       Tutorial: Large Data Sources and Instant Feedback with Server Mode
     [Expand]Grid View - Columns, Rows and Cells
     [Expand]Banded Views
     [Expand]WinExplorer View
     [Expand]Tile View
     [Expand]Filter and Search
     [Expand]Split Presentation
     [Expand]Row Preview Sections
   [Expand]Data Binding
    Unbound Columns
   [Expand]Data Editing and Validation
   [Expand]Filter and Search
   [Expand]Focus and Selection Handling
    Format Cell Values
   [Expand]Master-Detail Relationships
   [Expand]Asynchronous Image Load
   [Expand]Export and Printing
   [Expand]Appearance and Conditional Formatting
    Split Presentation
    Row Preview Sections
   [Expand]Batch Modifications
    Hit Information
    Popup Menus
   [Expand]Save and Restore Layout
   [Expand]Visual Elements
   [Expand]Design-Time Features
   [Expand]End-User Capabilities
    Included Components
  [Expand]Vertical Grid
  [Expand]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gantt Control
  [Expand]Chart Control
  [Expand]Map Control
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Sunburst 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]Office File API
[Expand]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Tutorial: ADO.NET Data

This walkthrough is a transcript of the ADO.NET Data video available on the DevExpress YouTube Channel.

In this tutorial, you will learn how to create a new ADO.NET data source in your WinForms project and how to bind the grid control to it with a BindingSource component. You will see how to sort or filter your data source at the BindingSource level and how to save the changes back to the database.

Expanded Creating a New ADO.NET Data Source

To bind the DevExpress WinForms Data Grid control to data using ADO.NET, use the GridControl.DataSource property in the Properties grid window, or invoke the control's smart tag and open the drop-down list next to the Choose Data Source label.

In the drop-down menu, you can select any existing data source or click the Add Project Data Source... link to create a new one. Clicking this link will invoke the standard dialog that will guide you through a few simple steps.

Yet another way to create a data source is by using the Data Source Wizard, that can be invoked by clicking an icon in the control's bottom-right corner.

Open the wizard and select the very first option – binding to ADO.NET Typed Dataset. Any existing datasets will be displayed on the page's right side. As you currently have no existing datasets, click the New Data Source... button to create a new one.

The wizard will pop-up a message box that tells you to re-build your solution after the data source is created and use the Wizard again to bind the control to this dataset.

Proceed and the same new data source dialog as you saw before appears. On the first page, select the Database source type.

On the second page, you can select the required database model – Dataset or Entity Data Model. The process of binding to Entity Framework models is described in a separate tutorial (Tutorial: Entity Framework Data). In this walkthrough, choose Dataset.

The third page asks you to choose the existing data connection or to create a new one. Click the New Connection… button and choose the sample AdventureWorks database provided by Microsoft. Clicking Yes in the displayed message box adds this database to your solution making it easier to modify your data, if needed.

Next, select data tables you will need in your application. Choose 3– customers, employees and products.

For each table, you can uncheck data fields that you do not need. Click Finish and rebuild your application.

Expanded Binding to the ADO.NET Data Source

If you invoke the Data Source Configuration Wizard again, you can see that your dataset is now available as an existing data source. Select this dataset and click Next.

The next page offers you two options: binding with and without the BindingSource component. This component simplifies design-time binding and allows you to sort and filter your data before it is displayed by bound UI Controls. So select the second option and click Next.

The final page contains multiple options. The topmost drop-down list allows you to select the required data table. The Sorting drop-down list enumerates all data fields in the selected data table. Select the required field, click up or down arrow to indicate ascending or descending sorting mode and click Add. Sort the data against the ListPrice column. The sorting rule is now displayed in the area below. You can select the desired rule and click Invert to reverse the sorting order, or Delete to remove sorting by this column. Finally, the Filter text box allows you to enter a text string that is the filtering condition for your data. Create a filter that selects products with values that are less than 100 in the Days To Manufacture column and their descriptions don't begin with the word Alluminum.

Click Finish to complete the setup. As you left the Show generated code-behind checkbox checked, the wizard will now show the C# file where you can see the auto-generated code.

Launch the app to see the result. You can see the required data displayed from your dataset. Note that data is sorted by the List Price column and filtering has been applied too.

If you ever decide to remove or change the sorting and filtering applied in the Wizard, select the BindingComponent at design time and look through its properties. For instance, you can change the sort order and the filter string applied previously.

Expanded Posting Data Back to the Datasource

To post changes back to the datasouce, handle the ColumnView.FocusedRowChanged event and call the adapter's Update method. This method has 4 overloads, use the one that takes the dataset as a parameter.

If now you launch the app and try to modify the DaysToManufacture column cell, you will get an exception. This one is raised because after you have excluded key columns from the query, only the SELECT command was generated. In order to push changes, you need to create an UPDATE command as well. To do so, you can open the .XSD diagram, select the required data table, right-click it and select Configure….

In the dialog, click the Advanced Options… button and check the Generate Insert, Update and Delete statements.

Click Yes to add required key columns to the query and click Finish. Now, if you launch the app, modifying row cells does not raise the exception. If you re-start the app, you can see that all your changes persist.

Expanded See Also

Is this topic helpful?​​​​​​​