Working with ADO.NET Database Components in VS.NET

Working with ADO.NET Data Components in VS.NET and WinForms is so fun. You write few lines of code and these controls take care of rest for you. In this article, I'm going to show you how to use these Data Components 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. 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 different kind of data components as datasource property of a control. For example, you can set a DefaultViewManager or a DataView as this property.

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

' Attach dataset's DefaultView to the datagrid control
Dim dv As DataView = 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 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 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 doesn't ship with .NET Beta 2. You need to download it separately than .NET SDK. You can download it Microsoft's 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 I'm using MS-Access 2000 as my data source and OleDB Data Adapters to work with the data source.

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.

Dim sqlStr As String = "SELECT * FROM Employees"
' Create data adapter object
Dim da As OleDbDataAdapter = 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 adapter's Fill method.

' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = 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

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 -

' Creating connection and command sting
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=c:\\northwind.mdb"
Dim sqlStr As String = "SELECT * FROM Employees"
' Create connection object
Dim conn As OleDbConnection = New OleDbConnection(conStr)
' Create data adapter object
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
da.Fill(ds, "Employees")
' Attach dataset's DefaultView to the datagrid control
DataGrid1.DataSource = ds.DefaultViewManager
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
' Creating connection and command sting
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=c:\\northwind.mdb"
Dim sqlStr As String = "SELECT * FROM Employees"
' Create connection object
Dim conn As OleDbConnection = New OleDbConnection(conStr)
' Create data adapter object
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
da.Fill(ds, "Employees")
' Attach dataset's DefaultView to the datagrid control
Dim dv As DataView = ds.Tables("Employees").DefaultView
ListBox1.DataSource = dv
ListBox1.DisplayMember = "FirstName"
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
' Creating connection and command sting
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=c:\\northwind.mdb"
Dim sqlStr As String = "SELECT * FROM Employees"
' Create connection object
Dim conn As OleDbConnection = New OleDbConnection(conStr)
' Create data adapter object
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
da.Fill(ds, "Employees")
' Attach dataset's DefaultView to the datagrid control
Dim dv As DataView = ds.Tables("Employees").DefaultView
ComboBox1.DataSource = dv
ComboBox1.DisplayMember = "FirstName"
End Sub 

References:

1. MSDN Magazine Articles 
2. MSDN Library Samples and Documentation