Data Binding in Windows Forms DataGrid (SfDataGrid)

SfDataGrid control is designed to display the bounded data in a tabular format. The data binding can be achieved by assigning the data sources to SfDataGrid.DataSource property.

OrderInfoCollection collection = new OrderInfoCollection();
this.sfDataGrid1.DataSource = collection.OrdersListDetails;
Dim collection As New OrderInfoCollection()
Me.sfDataGrid1.DataSource = collection.OrdersListDetails

If the data source implements INotifyCollectionChanged interface, then SfDataGrid control will automatically refresh the UI when item is added, removed or while list cleared.
When an item is added/removed in ObservableCollection, SfDataGrid automatically refresh the UI as ObservableCollection implements INotifyCollectionChanged. But when an item is added/removed in List, SfDataGrid will not refresh the UI automatically.

Binding to IEnumerable

SfDataGrid control supports to bind any collection that implements the IEnumerable interface. All the data operations such as sorting, grouping, filtering, summaries are supported when binding collection derived from IEnumerable.

Binding with DataTable

SfDataGrid control supports to bind the DataTable. SfDataGrid control automatically refresh the UI when binding DataTable as DataSource when rows are added, removed or cleared.

DataTable dataTable = this.GetDataTable();
this.sfDataGrid1.DataSource = dataTable;
Dim dataTable As DataTable = Me.GetDataTable()
Me.sfDataGrid1.DataSource = dataTable

Below are the limitations when binding DataTable as DataSource to SfDataGrid.

SfDatagrid bounded with data table itemsSource in windowsforms

Binding to XML Data

SfDataGrid can be bound to data from XML files. This can be achieved by using the DataSet object which provides the necessary methods that is used to read XML data into dataset. After loading the data, the SfDataGrid can be bind to this dataset by setting data binding properties such as DataSource and DataMember to the dataset and table name respectively. It is also possible to save the changes back to XML file.

DataSet dataSet = new DataSet();
dataSet.ReadXml(@"..\\..\\Data\\Datasource.xml");
this.sfDataGrid1.DataSource = dataSet.Tables[0];
Dim dataSet As New DataSet()
dataSet.ReadXml("..\\..\\Data\\Datasource.xml")
Me.sfDataGrid1.DataSource = dataSet.Tables(0)

SfDataGrid bounded with DataSet itemsSource in WinForms

Binding Complex Properties

SfDataGrid control provides support to bind complex property to its columns. To bind the complex property to GridColumn, set the complex property path to MappingName.

this.sfDataGrid1.Columns.Add(new GridTextColumn() { MappingName = "EmployeeComplexModel.EmployeeName", HeaderText = "Employee Name" });
Me.sfDataGrid1.Columns.Add(New GridTextColumn() With {.MappingName = "EmployeeComplexModel.EmployeeName", .HeaderText = "Employee Name"})

All the data operations (sorting, grouping, filtering and etc.) are supported when binding complex property.

Limitations when Binding Complex Property

Binding Indexer Properties

SfDataGrid control provides support to bind an indexer property to its columns. To bind an indexer property to GridColumn, set the indexer property path to MappingName.

this.sfDataGrid1.Columns.Add(new GridTextColumn() { MappingName = "EmployeeDetails[0].Title", HeaderText = "Title" });
Me.sfDataGrid1.Columns.Add(New GridTextColumn() With {.MappingName = "EmployeeDetails[0].Title", .HeaderText = "Title"})

All the data operations (sorting, grouping, filtering and etc.) are supported when binding indexer property.

Limitations when Binding Indexer Property

Events

DataSourceChanged

SfDataGrid.DataSourceChanged event occurs when the data source is changed by using DataSource property.
This event receives two arguments namely sender that handles SfDataGrid and DataSourceChangedEventArgs as objects.
The DataSourceChangedEventArgs object contains the following properties:

  • OldValue - Gets the value of old data source
  • NewValue - Get the value of new data source
  • OldView – Gets the old view of the data grid.
  • NewView – Gets the new view of the data grid.

View

SfDataGrid has the View property of type ICollectionViewAdv interface that implements IEditableCollectionView interface. View is responsible for maintaining and manipulating the data and other advanced operations like Sorting, Grouping, Filtering etc.
When binding Collection to DataSource property of SfDataGrid, then View will be created and maintains the operations on data such as Grouping, Filtering, Sorting, Insert, Delete, and Modification.
Following are some important properties that can be used for various purposes.

NOTE

SfDataGrid creates different types of views derived from ICollectionViewAdv interface based on DataSource.

Property

Type

Description

Records RecordsList Maintains the Records that are displayed in View when SfDataGrid is not Grouped.
TopLevelGroup TopLevelGroup Maintains the Group information when SfDataGrid is Grouped.
TopLevelGroup.DisplayElements GroupDisplayElements Maintains the Records and Group information that are displayed in View when SfDataGrid is Grouped.
Filter Predicate<object> Get or sets the method that determines the data is suitable to be displayed in View.
FilterPredicates ObservableCollection<IFilterDefinition> Maintains the FilterPredicates that are created while filtering using Filtering UI.
GroupDescriptions ObservableCollection<GroupDescription> Maintains the GroupDescription collection information. It describes how the items in the collection are grouped in the view.
SortDescriptions SortDescriptionCollection Maintains the SortDescription collection information. It describes how the items in the collection are sort in the view.
SourceCollection IEnumerable Maintains the underlying source collection.
TableSummaryRows ObservableCollection<ISummaryRow> Maintains the TableSummaryRows collection information.
SummaryRows ObservableCollection<ISummaryRow> Maintains the SummaryRows collection information.
CaptionSummaryRow ISummaryRow Maintains the CaptionSummaryRow information.

Events

The following events are associated with View.

  • RecordPropertyChanged
  • CollectionChanged
  • SourceCollectionChanged

RecordPropertyChanged

RecordPropertyChanged event is raised when the DataModel property value is changed, if the DataModel implements the INotifyPropertyChanged interface. The event receives with two arguments namely sender that handles the DataModel and PropertyChangedEventArgs as object.
PropertyChangedEventArgs has below property,

  • PropertyName – It denotes the PropertyName of the changed value.

CollectionChanged

CollectionChanged event is raised whenever that is some change in Records / DisplayElements collection. The event receives two arguments namely sender that handles View object and NotifyCollectionChangedEventArgs as object.
NotifyCollectionChangedEventArgs has below properties,

  • Action - It contains the current action. (i.e.) Add, Remove, Move, Replace, Reset.

  • NewItems - It contains the list of new items involved in the change.

  • OldItems - It contains the list of old items affected by the Action.

  • NewStartingIndex - It contains the index at which the change occurred.

  • OldStartingIndex - It contains the index at which the Action occurred.

SourceCollectionChanged

SourceCollectionChanged event is raised when source collection is changed for example add or remove the collection. The event receives two arguments namely sender that handles QueryableCollectionViewWrapper object and NotifyCollectionChangedEventArgs as object.
NotifyCollectionChangedEventArgs has below properties,

  • Action - It contains the current action. (i.e.) Add, Remove, Move, Replace and Reset.

  • NewItems - It contains the list of new items involved in the change.

  • OldItems - It contains the list of old items affected by the Action.

  • NewStartingIndex - It contains the index at which the change occurred.

  • OldStartingIndex - It contains the index at which the Action occurred.

The following is the methods that are associated with View which can be used to defer refresh the view.

Method Name

Description

DeferRefresh Enter the defer cycle so that all data operations can be performed in view and update once.
BeginInit & EndInit When BeginInit method is called it suspends all the updates until EndInit method is called. All the updation can be performed with in these methods and update the view at once.

NOTE

View has properties that already defined in SfDataGrid. It recommended setting those properties via SfDataGrid.

Binding Data from ADO .NET Entity Framework

SfDataGrid control supports to bind data from ADO.NET Entity Framework. This walk-through describe about binding data from ADO.NET Entity Framework and save back the changes to the database.
References:
https://msdn.microsoft.com/en-us/library/jj682076(v=vs.113).aspx

Defining Data Model using Entity Framework 4.0

To create Data Model using Entity Framework in Windows Forms application, follow the below steps.

1) Right click the project, select Add option and then click New Item.

2) The Add New Item wizard appears, select “ADO.NET Entity Data Mode” from the Data node.

3) Name the file as Model1.edmx and then select Add button.
Choose the the ADO.Net entity model application from visual studio

4) The Entity Data Model Wizard appears. In the Choose Model Contents, select EF Designer from database option and then click Next.

Choose the model contents from visual studio

5) In the Choose Your Data Connection, select Northwind database from the drop-down list for data connection. To configure/modify connection, refer: How to: Create Connections to SQL Server Databases.

Choose the Northwind database from visual studio

6) Enable the Save entity connection settings in App.config as check box and then click Next.
7) In the Choose Your Database Objects and Settings, expand the Tables node and select the Order Details table.

Choose the database objects and setting from Northwind database through the visual studio

8) Click Finish button to add the Model1.edmx file to the application. The Entity diagram for the Order Detail table is opened.

To show the entity diagram of database

Loading Data from Entity Framework Data Service

The data from the defined entity model can be loaded as a data source to the SfDataGrid by loading the Order Details table by the created entity for the Northwnd database.

NORTHWNDEntities northWind = new NORTHWNDEntities();
this.sfDataGrid1.DataSource = northWind.Order_Details;
Dim northWind As New NORTHWNDEntities()
Me.sfDataGrid1.DataSource = northWind.Order_Details

Now, run the application. The following screenshot shows the SfDataGrid control populated with data from Entity Framework data service.

SfDataGrid bounded with Entity framework model in windowsforms

Binding Data from LINQ to SQL

SfDataGrid control supports to bind data from LINQ to SQL. This walkthrough, describes about binding data from LINQ to SQL and save back the changes to the Database.

Adding Data Model using LINQ to SQL

To create data model using LINQ to SQL in WF project follow the below steps.
1) Right click on the project, Select Add option and then click New Item.
2) The Add New Item wizard appears, Select LINQ to SQL Classes from the Visual C#.
3) Name the file as Northwind and then select Add option to add the Northwind.dbml in the project.

Choose the data  model from LINQ to SQL from the visual studio

4) Once the Northwind.dbml is added in the project, then the design view is opened.

Connect the database through the visual studio

5) New Database connection can be added by clicking add icon button in Server Explorer.

6) The Add Connection wizard appeared with the default data source as Microsoft SQL Server Database File (SqlClient).

Connect the database through the visual studio

7) Click Change to open the Change Data Source dialog box and select the type of data source as Microsoft SQL Server Database File (SqlClient).

Change the database through the visual studio

8) Select the Database file name and click on Test Connection to check the connection with the database.

Choose the database through the visual studio

9) Once the connection is succeeded, click OK button to add database in the server explorer.

Connected databases in the projects

10) Drag Shippers table in to design view of Northwind.dbml. The Entity model diagram for Shippers table is generated once it is dropped in to design view.

Loading Data from LINQ to SQL Classes

The data from the defined data model of the LINQ to SQL classes can be loaded as a data source to the SfDataGrid by loading the Shippers table by the created data context for the Northwnd database.

NorthwindDataContext northWindDataContext = new NorthwindDataContext();
this.sfDataGrid1.DataSource = northWindDataContext.Shippers;
Dim northWindDataContext As New NorthwindDataContext()
Me.sfDataGrid1.DataSource = northWindDataContext.Shippers

SfDataGrid with LinQ to SQL in windowsforms

Binding Data from ADO.NET

SfDataGrid control supports to load the data using ADO.NET. This walk-through, describe about binding data from ADO.NET service

Connecting Application to Databases

To connect SQL database to WinForms application, refer the below MSDN link or follow the below steps,
https://msdn.microsoft.com/en-us/library/s4yys16a.aspx

1) Select the Connect to Database option from the Tools menu.

2) The Add Connection wizard appeared with the default data source as Microsoft SQL Server (SqlClient).

Choose the SqlClient to the prjoect through the visual studio

3) Click Change to open the Change Data Source dialog box and select the type of data source as Microsoft SQL Server Database File (SqlClient).

Change the SqlClient for the prjoect through the visual studio

4) Select the Database file name and click on Test Connection to check the connection with the database.

Test the database connection through the visual studio

5) Once the connection is succeeded, click OK button to add database in the server explorer.

Connected databases in the projects

6) To get connection string for the database, right-click on the database and then click Properties option.

Loading Data from ADO.NET

To access the data from data source using ADO.NET, follow the below steps.

1) Create a connection through any of the .NET Framework data provider based on the type of data source.

2) Fill the data from the database to a data set using the data adapter.

3) Set the DataSource as Suppliers table from the data set.

DataSet dataSet = new DataSet();
SqlConnection sqlConnection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=NORTHWND.MDF;Integrated Security=True;Connect Timeout=30");
sqlConnection.Open();
SqlDataAdapter sqlAdapter = new SqlDataAdapter("Select * from Suppliers", sqlConnection);
sqlAdapter.Fill(dataSet, "Suppliers");
this.sfDataGrid1.DataSource = dataSet.Tables["Suppliers"];
Dim dataSet As New DataSet()
Dim sqlConnection As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=NORTHWND.MDF;Integrated Security=True;Connect Timeout=30")
sqlConnection.Open()
Dim sqlAdapter As New SqlDataAdapter("Select * from Suppliers", sqlConnection)
sqlAdapter.Fill(dataSet, "Suppliers")
Me.sfDataGrid1.DataSource = dataSet.Tables("Suppliers")

SfDataGrid bounded with SQL Client in windowsforms

Binding Data from Microsoft Access

SfDataGrid control supports to bind data from Microsoft Access database. This section describes about how to bind the data from Microsoft Access database to SfDataGrid.

Importing Microsoft Access Database

To load the data from Microsoft Access database, follow the below steps.

1) On the View menu, select Other Windows > Data Sources.

2) In the Data Sources window, click Add New Data Source, the DataSource configuration wizard will be opened.

Choose the datasource type through the visual studio

3) Select Database on the Choose a Data Source Type page, and then select Next.

Choose the datasource model through the visual studio

4) On the Choose your Data Connection page, select New Connection to configure a new data connection and Change the Data source to .NET Framework Data Provider for OLE DB.

Choose the data connetion through the visual studio

5) In OLE DB Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. In Server or file name, specify the path and name of the .accdb file to which want to connect, and then select OK.

Choose the data provider through the visual studio

6) Select Next on the Choose your Data Connection page.

7) Select Next on the Save connection string to the Application Configuration file page.

8) Expand the Tables node on the Choose your Database Objects page.

Choose the data base objects through the visual studio

9) Select whatever tables or views in the dataset, and then select Finish.

Loading Data from Microsoft Access Database

To access the Microsoft Access database, follow the below steps.

1) Create a OleDbConnection with the Microsoft Access database.

2) Fill the data from the database to a data set using the OleDbDataAdapter.

3) Set the DataSource as Employees table from the data set.

OleDbConnection oleConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\SfWinforms\SfDataGrid_UserGuide\Sample\DataBinding\Data\Employees.accdb;Persist Security Info=True");
OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter("SELECT * FROM Employees", oleConnection);
EmployeesDataSet employeeDataSet = new EmployeesDataSet();
oleConnection.Open();
oleDataAdapter.Fill(employeeDataSet, "Employees");
oleConnection.Close();
this.sfDataGrid1.DataSource = employeeDataSet.Tables[Employees];
Dim oleConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\SfWinforms\SfDataGrid_UserGuide\Sample\DataBinding\Data\Employees.accdb;Persist Security Info=True")
Dim oleDataAdapter As New OleDbDataAdapter("SELECT * FROM Employees", oleConnection)
Dim employeeDataSet As New EmployeesDataSet()
oleConnection.Open()
oleDataAdapter.Fill(employeeDataSet, "Employees")
oleConnection.Close()
Me.sfDataGrid1.DataSource = employeeDataSet.Tables(Employees)

SfDataGrid bounded with MS Access in windowsforms