Working with Data Bound Controls

Working with data-bound controls using .NET ADO.NET and WinForms is such fun. You write a few lines of code and these controls take care of the rest for you. In this article, I'm 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 that can easily bind with data components. Microsoft Visual Studio.NET is a rich IDE for ADO.NET data components. I'm going to talk about these controls in a moment. In this article, I'm 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 they're 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 a 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 pass data back and forth to the controls. These components are:

1. Data Connection
2. DataSet
3. DataView
4. Data Adapters
5. Data Commands

You can create these components either at design-time or at run-time. Creating these components at design-time is a pretty simple task. You just drag these components on a form and set properties and you're 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 the same classes for a connection, data adapter, and command classes to work with and work in a 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 doesn't ship with .NET Beta 2. You need to download it separately from .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 the data source and the class names.

 

Working with Data Components

 

There are a few simple steps that include working 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 a connection object. Here I'm 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 a 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 the 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 the 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";
}