Standard Binding (to Simple Data Types)
- 18 minutes to read
Lookup editors and other DevExpress data-aware controls are compatible with any data access technology (ADO.NET, Entity Framework, XPO, etc.) and capable to display data from any data source that implements the IList, IBindingList, or ITypedList interface. Refer to these help topics for more details: Traditional Data Binding Methods and Data Binding Common Concepts.
This topic describes how to set up a lookup editor in standard binding mode, in which the editor’s value is of a simple data type (e.g., integer).
Consider an example where two data tables are linked by column values.
You may want to substitute raw data in the Category ID field in the first (main) table with corresponding values from the second table. For instance, you may want to display “Confections” in the main table’s Category ID column instead of value 3, and display “Grains/Cereals” instead of value 5. This can be performed by using a lookup editor for editing the Category ID column.
Besides this value substitution feature, a lookup editor provides the editing functionality using a built-in dropdown window, which shows all the available records from the secondary (lookup) data table. An end-user can open the editor’s dropdown and change the main editor value by selecting a certain lookup record.
Setting Up
The following properties need to be specified for a lookup editor in standard binding mode.
- RepositoryItemLookUpEditBase.DataSource - This identifies the lookup data source - a list of records (objects) displayed in the editor’s dropdown.
RepositoryItemLookUpEditBase.ValueMember - Specifies a key field (from the lookup data source) that stores unique values used for record identification. This field’s values must match the editor’s edit value (the data types must match).
In standard binding mode, a match for the editor’s edit value among the lookup records is found by comparing the edit value with values of the ValueMember field of the lookup records. When an end-user picks up a lookup record in the dropdown, the value of this record’s ValueMember field is assigned to the editor’s edit value.
- RepositoryItemLookUpEditBase.DisplayMember - Specifies the field (from the lookup data source) whose values are displayed in the lookup editor’s edit box.
For standalone lookup editors, these properties are available from the editor’s Properties object (e.g., LookUpEdit.Properties).
lookUpEdit1.Properties.DataSource = categoriesBindingSource1;
lookUpEdit1.Properties.ValueMember = "ID";
lookUpEdit1.Properties.DisplayMember = "CategoryName";
In-place lookup editors are presented by corresponding RepositoryItem class descendants - RepositoryItemLookUpEdit, RepositoryItemGridLookUpEdit, RepositoryItemSearchControl and RepositoryItemTreeListLookUpEdit. Thus, the DataSource, ValueMember and DisplayMember properties are explicitly available.
Certain lookup editors may need additional customization (e.g., customize columns in the dropdown, specify the dropdown window’s width and height, enable text editing, etc.). See the Lookup Editors and Main Settings topic and the LookUpEdit, GridLookUpEdit, SearchLookUpEdit and TreeListLookUpEdit class descriptions for more information.
You may want to bind a standalone lookup editor to a data source. To do this, bind to the editor’s edit value, which is specified by the BaseEdit.EditValue inherited property. For an inplace editor, its edit value is provided by a cell in which the editor is embedded.
Example - LookUpEdit (standalone)
The following example shows how to use a standalone LookUp editor to select a city from a dropdown list.
The example performs the following:
- creates two lists (Persons and Cities) that contain Person and CityInfo objects. Each Person object is linked to a CityInfo object using the Person.CityID and the matching CityInfo.ID field.
- uses the TextEdit and LookUpEdit controls to edit the Person.Name and Person.CityID properties, respectively.
- uses a DataNavigator control to navigate between the Persons list items.
- sets up the lookup data source to display the Cities list
- handles the RepositoryItem.CustomDisplayText event to display the CityInfo.City and CityInfo.Country field values in the edit box. If this event is not handled, the edit box displays the RepositoryItemLookUpEditBase.DisplayMember property’s value (CityInfo.City).
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Controls;
using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace WindowsFormsApplication9 {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
List<Person> Persons = new List<Person>();
Persons.Add(new Person() { Name = "Carlos Gonzalez", CityID = 0 });
Persons.Add(new Person() { Name = "Mario Pontes", CityID = 5 });
Persons.Add(new Person() { Name = "Horst Kloss", CityID = 2 });
Persons.Add(new Person() { Name = "Martin Sommer", CityID = 3 });
Persons.Add(new Person() { Name = "Diego Roel", CityID = 3 });
Persons.Add(new Person() { Name = "Catherine Dewey", CityID = 4 });
Persons.Add(new Person() { Name = "Pascale Cartrain", CityID = 4 });
Persons.Add(new Person() { Name = "Bernardo Batista", CityID = 1 });
List<CityInfo> Cities = new List<CityInfo>();
Cities.Add(new CityInfo() { ID = 0, City= "Barquisimeto", Country= "Venezuela", Region="Lara" });
Cities.Add(new CityInfo() { ID = 1, City = "Rio de Janeiro", Country = "Brazil", Region = "RJ" });
Cities.Add(new CityInfo() { ID = 2, City = "Cunewalde", Country = "Germany", Region = "" });
Cities.Add(new CityInfo() { ID = 3, City = "Madrid", Country = "Spain", Region = "" });
Cities.Add(new CityInfo() { ID = 4, City = "Charleroi", Country = "Belgium", Region = "" });
Cities.Add(new CityInfo() { ID = 5, City = "Sao Paulo", Country = "Brazil", Region = "SP" });
DataNavigator dataNavigator1 = new DataNavigator();
dataNavigator1.Parent = this;
dataNavigator1.Location = new System.Drawing.Point(12, 12);
dataNavigator1.Size = new System.Drawing.Size(385, 25);
TextEdit textEdit1 = new TextEdit();
textEdit1.Parent = this;
textEdit1.Location = new System.Drawing.Point(198, 52);
textEdit1.Size = new System.Drawing.Size(199, 20);
LookUpEdit lookUpEdit1 = new LookUpEdit();
lookUpEdit1.Parent = this;
lookUpEdit1.Location = new System.Drawing.Point(198, 78);
lookUpEdit1.Size = new System.Drawing.Size(199, 20);
dataNavigator1.DataSource = Persons;
textEdit1.DataBindings.Add(new Binding("EditValue", Persons, "Name"));
lookUpEdit1.DataBindings.Add(new Binding("EditValue", Persons, "CityID"));
lookUpEdit1.Properties.DataSource = Cities;
lookUpEdit1.Properties.ValueMember = "ID";
lookUpEdit1.Properties.DisplayMember = "City";
lookUpEdit1.Properties.PopulateColumns();
lookUpEdit1.Properties.Columns["ID"].Visible = false;
lookUpEdit1.CustomDisplayText += LookUpEdit1_CustomDisplayText;
}
// Display the city and country in the edit box
private void LookUpEdit1_CustomDisplayText(object sender, CustomDisplayTextEventArgs e) {
LookUpEdit lookUpEdit = sender as LookUpEdit;
CityInfo city = lookUpEdit.Properties.GetDataSourceRowByKeyValue(e.Value) as CityInfo;
if(city!=null) {
e.DisplayText = city.City + ", " + city.Country;
}
}
}
public class Person {
public string Name { get; set; }
public int CityID { get; set; }
}
public class CityInfo {
public int ID { get; set; }
public string City { get; set; }
public string Country { get; set; }
public string Region { get; set; }
}
}
Example - LookUpEdit (in-place)
The following example demonstrates how to create and customize an in-place LookUpEdit control (RepositoryItemLookUpEdit) to edit cell values in the CategoryID column.
The lookup editor displays category names in the edit box instead of category IDs (see the DisplayMember setting).
Play the animation to see the result:
using DevExpress.XtraEditors.Repository;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace LookupEdit_StandardBinding {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
InitData();
gridControl1.DataSource = Products;
gridView1.Columns["UnitPrice"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
gridView1.Columns["UnitPrice"].DisplayFormat.FormatString = "c2";
// Create an in-place LookupEdit control.
RepositoryItemLookUpEdit riLookup = new RepositoryItemLookUpEdit();
riLookup.DataSource = Categories;
riLookup.ValueMember = "ID";
riLookup.DisplayMember = "CategoryName";
// Enable the "best-fit" functionality mode in which columns have proportional widths and the popup window is resized to fit all the columns.
riLookup.BestFitMode = DevExpress.XtraEditors.Controls.BestFitMode.BestFitResizePopup;
// Specify the dropdown height.
riLookup.DropDownRows = Categories.Count;
// Enable the automatic completion feature. In this mode, when the dropdown is closed,
// the text in the edit box is automatically completed if it matches a DisplayMember field value of one of dropdown rows.
riLookup.SearchMode = DevExpress.XtraEditors.Controls.SearchMode.AutoComplete;
// Specify the column against which an incremental search is performed in SearchMode.AutoComplete and SearchMode.OnlyInPopup modes
riLookup.AutoSearchColumnIndex = 1;
// Optionally hide the Description column in the dropdown.
// riLookup.PopulateColumns();
// riLookup.Columns["Description"].Visible = false;
gridControl1.RepositoryItems.Add(riLookup);
// Assign the in-place LookupEdit control to the grid's CategoryID column.
// Note that the data types of the "ID" and "CategoryID" fields match.
gridView1.Columns["CategoryID"].ColumnEdit = riLookup;
gridView1.BestFitColumns();
}
List<Product> Products = new List<Product>();
List<Category> Categories = new List<Category>();
private void InitData() {
Products.Add(new Product() { ProductName = "Sir Rodney's Scones", CategoryID = 3, UnitPrice = 10 });
Products.Add(new Product() { ProductName = "Gustaf's Knäckebröd", CategoryID = 5, UnitPrice = 21 });
Products.Add(new Product() { ProductName = "Tunnbröd", CategoryID = 5, UnitPrice = 9 });
Products.Add(new Product() { ProductName = "Guaraná Fantástica", CategoryID = 1, UnitPrice = 4.5m });
Products.Add(new Product() { ProductName = "NuNuCa Nuß-Nougat-Creme", CategoryID = 3, UnitPrice = 14 });
Products.Add(new Product() { ProductName = "Gumbär Gummibärchen", CategoryID = 3, UnitPrice = 31.23m });
Products.Add(new Product() { ProductName = "Rössle Sauerkraut", CategoryID = 7, UnitPrice = 45.6m });
Products.Add(new Product() { ProductName = "Thüringer Rostbratwurst", CategoryID = 6, UnitPrice = 123.79m });
Products.Add(new Product() { ProductName = "Nord-Ost Matjeshering", CategoryID = 8, UnitPrice = 25.89m });
Products.Add(new Product() { ProductName = "Gorgonzola Telino", CategoryID = 4, UnitPrice = 12.5m });
Categories.Add(new Category() { ID = 1, CategoryName = "Beverages", Description = "Soft drinks, coffees, teas, beers, and ales" });
Categories.Add(new Category() { ID = 2, CategoryName = "Condiments", Description = "Sweet and savory sauces, relishes, spreads, and seasonings" });
Categories.Add(new Category() { ID = 3, CategoryName = "Confections", Description = "Desserts, candies, and sweet breads" });
Categories.Add(new Category() { ID = 4, CategoryName = "Dairy Products", Description = "Cheeses" });
Categories.Add(new Category() { ID = 5, CategoryName = "Grains/Cereals", Description = "Breads, crackers, pasta, and cereal" });
Categories.Add(new Category() { ID = 6, CategoryName = "Meat/Poultry", Description = "Prepared meats" });
Categories.Add(new Category() { ID = 7, CategoryName = "Produce", Description = "Dried fruit and bean curd" });
Categories.Add(new Category() { ID = 8, CategoryName = "Seafood", Description = "Seaweed and fish" });
}
}
public class Product {
public string ProductName { get; set; }
public decimal UnitPrice { get; set; }
public int CategoryID { get; set; }
}
public class Category {
public int ID { get; set; }
public string CategoryName { get; set; }
public string Description { get; set; }
}
}
Example - GridLookUpEdit
The following example demonstrates how to create and customize a GridLookUpEdit control at runtime.
In the example, a lookup editor will be used to edit the values of the “ProductID” field in the “Order Details” table (NWind database). It must display a list of the available products in the dropdown, which are stored in the “Products” table in the NWind database. By selecting a row from the dropdown an end-user will change the current order’s product. Also a data navigator will be created that will assist an end-user to navigate through the “Order Details” table.
To implement the required functionality the following key properties of the lookup editor must be set:
- The editor’s BaseEdit.EditValue property is bound to the “ProductID” field in the “Order Details” table via the DataBindings property.
- The editor’s RepositoryItemLookUpEditBase.DataSource property is set to a DataView object which contains rows from the “Products” table in the NWind database.
- The editor’s RepositoryItemLookUpEditBase.ValueMember property is set to the “ProductID” field in the “Products” table. This field’s value must match the editor’s edit value, and so the “ProductID” field in the “Order Details” table.
- The editor’s RepositoryItemLookUpEditBase.DisplayMember property is set to the “ProductName” field in the “Products” table. This identifies the field in the DataSource whose values match the editor’s display text.
- Two columns are created within the underlying View via the ColumnView.Columns property. These will display values from the “ProductID” and “ProductName” fields in the dropdown data source.
The result of the example is shown in the following image:
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using System.Data.OleDb;
// A lookup editor created at runtime.
GridLookUpEdit gridLookup;
// A navigator control to navigate the "Order Details" table.
DataNavigator dataNav;
// DataView for the "Order Details" table.
DataView dvMain;
// DataView for the "Products" table.
DataView dvDropDown;
//...
private void Form1_Load(object sender, System.EventArgs e) {
gridLookup = new GridLookUpEdit();
gridLookup.Bounds = new Rectangle(10, 40, 200, 20);
this.Controls.Add(gridLookup);
dataNav = new DataNavigator();
dataNav.Bounds = new Rectangle(10, 10, 250, 20);
this.Controls.Add(dataNav);
InitData();
InitLookUp();
dataNav.DataSource = dvMain;
}
private void InitData() {
// Dataset to provide data from the database
DataSet ds = new DataSet();
string connestionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DB\\nwind.mdb";
// Connect to the "Order Details" table
System.Data.OleDb.OleDbDataAdapter dbAdapter =
new OleDbDataAdapter("SELECT * FROM [Order Details]", connestionString);
// Load data from the "Order Details" table to the dataset
dbAdapter.Fill(ds, "Order Details");
// Connect to the "Products" table
dbAdapter = new OleDbDataAdapter("SELECT * FROM Products", connestionString);
// Load data from the "Products" table into the dataset
dbAdapter.Fill(ds, "Products");
DataViewManager dvm = new DataViewManager(ds);
dvMain = dvm.CreateDataView(ds.Tables["Order Details"]);
dvDropDown = dvm.CreateDataView(ds.Tables["Products"]);
}
private void InitLookUp() {
// Bind the edit value to the ProductID field of the "Order Details" table;
// the edit value matches the value of the ValueMember field.
gridLookup.DataBindings.Add("EditValue", dvMain, "ProductID");
// Prevent columns from being automatically created when a data source is assigned.
gridLookup.Properties.PopupView.OptionsBehavior.AutoPopulateColumns = false;
// The data source for the dropdown rows
gridLookup.Properties.DataSource = dvDropDown;
// The field for the editor's display text.
gridLookup.Properties.DisplayMember = "ProductName";
// The field matching the edit value.
gridLookup.Properties.ValueMember = "ProductID";
// Add two columns in the dropdown:
// A column to display the values of the ProductID field;
GridColumn col1 = gridLookup.Properties.PopupView.Columns.AddField("ProductID");
col1.VisibleIndex = 0;
col1.Caption = "Product ID";
// A column to display the values of the ProductName field.
GridColumn col2 = gridLookup.Properties.PopupView.Columns.AddField("ProductName");
col2.VisibleIndex = 1;
col2.Caption = "Product Name";
// Set column widths according to their contents.
gridLookup.Properties.PopupView.BestFitColumns();
// Specify the total dropdown width.
gridLookup.Properties.PopupFormWidth = 300;
}