Working with Data Bound Controls

Working with data-bound controls using .NET ADO.NET and WinForms is so fun. You write few lines of code and these controls take care of rest for you. In this article, Im going to show you how to use these data-bound controls in your .NET WinForms applications using VB.NET.

Data-bound Controls

Data-bound controls are WinForms controls those can easily bind with data components. Microsoft Visual Studio.NET is a rich IDE for ADO.NET data components. Im going to talk about these controls in a moment. In this article, Im going to talk about three main data-bound controls DataGrid, ListBox, and a ComboBox.

Data-bound controls have properties, which you can set as a data component and theyre ready to present your data in WinForms. DataSource and DisplayMemeber are two important properties.

DataSource property of these controls plays a major role. You can set different kind of data components as datasource property of a control. For example, you can set a DefaultViewManager or a DataView as this property.

DataSet ds = new DataSet();
dataGrid1.DataSource = ds.DefaultViewManager;

DisplayMember property can be set to a database table field name if you want to bind a particular field to the control.

DataSet ds = new DataSet();
// Attach dataset's DefaultView to the datagrid control
DataView dv = ds.Tables["Employees"].DefaultView;
listBox1.DataSource = dv;
listBox1.DisplayMember = "FirstName";

ADO.NET Data Components in VS.NET

Microsoft Visual Studio.NET provides a rich set of ADO.NET data components. These components sit between WinForms data-bound controls and the data source and passes data back and forth to the controls. These components are:

Data Connection
DataSet
DataView
Data Adapters
Data Commands

You can create these components in either at design-time or at run-time. Creating these components at design-time is pretty simple task. You just drag these components on a form and set properties and youre all set.

Connection, data adapter, and command components are specific to a data provider and dataview and dataset are common components.

ADO.NET Data Providers

In Microsoft .NET Beta 2, ADO.NET has three types of data providers. Each data provider is designed to work with different types of data sources. All of these data providers provide same classes for a connection, data adapter and command classes to work with and work in similar fashion.

These data providers are:

SQL Data Providers:

SQL data providers are designed to work with SQL Server 70 or later databases. The connection, command and data adapter classes are SqlConnection, SqlCommand, and SqlDataAdapter.

OLE DB Data Providers

Ole-db data providers are designed to work with any OLE-DB data source. You need to have an OLE-DB provider to work with a data source. The connection, command and data adapter classes are OleDbConnection, OleDbCommand, and OleDbDataAdapter.

ODBC Data Providers

ODBC data providers is a recent addition to the .NET SDK. This API doesnt ship with .NET Beta 2. You need to download it separately than .NET SDK. You can download it Microsofts site at ODBC SDK. ODBC providers are designed to work with any ODBC data source. You need to have an ODBC driver to work with a data source. The connection, command and data adapter classes are ODBCConnection, ODBCCommand, and ODBCDataAdapter.

As I mentioned earlier, working with all of these data providers is similar accept the class names and data sources. So if you know one of them, you can just replace data source and the class names.

Working with Data Components

There are few simple steps include to work with data components. Just follow these steps one by one.

Step 1: Connect to a data source

First step is to create a connection to the data source. You use a Connection object to connect to a data source. You neat to create a connection string and create connection object. Here Im using MS-Access 2000 as my data source and OleDB Data Adapters to work with the data source.

// Creating connection and command sting
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=c:\\northwind.mdb";
// Create connection object
OleDbConnection conn = new OleDbConnection(conStr);

Step 2: Creating a Data Adapter

Now you create a data adapter. A data adapter constructor takes two arguments A SQL
string and a connection object.

string sqlStr = "SELECT * FROM Employees";
// Create data adapter object
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conn);

Step 3: Creating and Filling a DataSet

Now next step is to create a dataset and fill it by using data adapters Fill method.

// Create a dataset object and fill with data using data adapter's Fill method
DataSet ds = new DataSet();
da.Fill(ds, "Employees");

Step 4: Bind to a data-bound control

The last step is to bind the data set to a data-bound control using above discussed
methods.

// Attach dataset's DefaultView to the datagrid control
dataGrid1.DataSource = ds.DefaultViewManager; 
Sample Application
Source Code: DBCSMCB.zip

This sample application is a Windows application which three controls a DataGrid, a ListBox, and a ComboBox and three buttons Fill DataGrid, Fill ListBox, and Fill ComboBox respectively.

When you click on these buttons, the fill the data from the data source to the control. The code is shown in the below table -

private void button1_Click(object sender, System.EventArgs e)
{
// Creating connection and command sting
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=c:\\northwind.mdb";
string sqlStr = "SELECT * FROM Employees";
// Create connection object
OleDbConnection conn = new OleDbConnection(conStr);
// Create data adapter object
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr,conn);
// Create a dataset object and fill with data using data adapter's Fill method
DataSet ds = new DataSet();
da.Fill(ds, "Employees");
// Attach dataset's DefaultView to the datagrid control
dataGrid1.DataSource = ds.DefaultViewManager;
}
private void button2_Click(object sender, System.EventArgs e)
{
// Creating connection and command sting
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=c:\\northwind.mdb";
string sqlStr = "SELECT * FROM Employees";
// Create connection object
OleDbConnection conn = new OleDbConnection(conStr);
// Create data adapter object
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr,conn);
// Create a dataset object and fill with data using data adapter's Fill method
DataSet ds = new DataSet();
da.Fill(ds, "Employees");
// Attach dataset's DefaultView to the datagrid control
DataView dv = ds.Tables["Employees"].DefaultView;
listBox1.DataSource = dv;
listBox1.DisplayMember = "FirstName";
}
private void button3_Click(object sender, System.EventArgs e)
{
// Creating connection and command sting
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source=c:\\northwind.mdb";
string sqlStr = "SELECT * FROM Employees";
// Create connection object
OleDbConnection conn = new OleDbConnection(conStr);
// Create data adapter object
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr,conn);
// Create a dataset object and fill with data using data adapter's Fill method
DataSet ds = new DataSet();
da.Fill(ds, "Employees");
// Attach dataset's DefaultView to the datagrid control
DataView dv = ds.Tables["Employees"].DefaultView; comboBox1.DataSource = dv;
comboBox1.DisplayMember = "FirstName";
}