This article, part one of a series, discusses the workings of ADO.NET. With the help of an example application, you will learn to manipulate data in a Microsoft Access database, including how to insert, search, and navigate records.
ADO.NET is an exciting technology provided by the .NET Framework. Prior to ADO.NET, we used ADO (Active Data Objects) with Visual Studio 6. Even though it performs the required task, developers needed to write long lines of code. With ADO.NET, data access is simpler than ever. The .NET Framework ships with the required namespaces for working with ADO.NET. It also comes with two different sets of providers for OLEDB (MS Access) and SQL Server databases. The syntaxes of both these providers are the same except a few changes to the controls. Once you master the working of any one of the providers, it is easy to port the code to other databases.
In this article, you will learn about the workings of ADO.NET with the help of a Windows Forms project titled Customer Database Application. The application uses a Microsoft Access database to manipulate data and Visual Basic .NET as the programming language. With the help of this application, you can perform the following tasks:
Inserting records
Navigating records (previous, next, first and last records)
Searching records from the database
User Interface
The sample application used for this article consists of two forms. The first form is the core of our application, which will prompt you to enter Name and E-mail. You can add additional fields depending upon your requirements. It also includes the required buttons which will perform the required actions such as addition and record navigation (see figure 1).
The second form of our application consists of a ComboBox and a TextBox control. It will be populated with all of the names which are in the database. It will perform a search on the database, and the text box will display the relevant e-mail address, which matches the corresponding name in the database (see figure 2).
You can edit and delete the data using the second form. This can be done only after searching for the appropriate record using the search functionality. I will discuss editing and deleting data in the next part of this article.
I have developed the sample application using Visual Studio .NET 2003. Hence, I have used the built-in controls for connecting to the database on the first form, which is used for data entry and the traditional coding approach for the search functionality. This will help you to learn about the usage of both techniques for connecting to a database.
Launch Visual Studio .NET 2003 and create a new Windows Forms project. Design a user interface as shown in the first figure above and supply the required properties as shown in the table given below. You have to connect the form to the required database. For this purpose, you can use the built-in data controls which ship with the .NET Framework. Follow the procedure as shown in the next section to properly connect to the database.
In order to establish a connection to the database you have to follow a three step procedure as outlined below.
The first step is to add the OleDbDataAdapter control to the form. Double click the control from the Data section of the Toolbox (see figure 3).
The Data Adapter Configuration Wizard opens up (see figure 4).
Click Next to continue and select the New Connection button as shown in the figure below.
Select the Provider tab from the DataLink Properties dialog and click the Microsoft Jet 4.0 OLE DB Provider option (see figure 6).
Click the Next button to continue and select your MS Access database, which will be used for this application. You can click the Test Connection button to verify the connection, which will show any errors which will possibly occur while establishing a connection to a database (see figure 7).
Click the Next button on the adapter wizard until you see a dialog called Generate the SQL Statements. You can use the Query Builder to generate the required SQL Statement. Add the table name from the Add Table dialog and click the * mark on the builder. The system will generate a SQL statement similar to one shown below:
SELECT <Your Table Name>.*
FROM <Your Table Name>
The above statement will be sufficient for our sample application. You can now click the Next button on the wizard and the whole process is over as soon as you click the Finish button. The wizard will display any errors which may occur during the generation of the SQL statement. I have not selected a column as primary key while creating the database. Hence the wizard will show a warning. This is not an error (see figure 8).
The second step is to add the OleDBConnection control to the form. You need not perform any additional work for this control because this will be used for connecting to the database.
The final step is to generate a dataset. Select the Generate DataSet menu option from the Data menu. The Generate DataSet dialog opens. Choose the option New, give a meaningful name, and close the dialog by clicking the OK button.
You will notice that all three Data controls will appear on the bottom tray of the form, as they are not visible during run time. The above procedure is not yet complete, because you have to fill the data adapter with the dataset. This can be done using one line of code as shown below:
OleDbDataAdapter1.Fill(DsCustomers1)
You need not provide any further code because everything will be automatically done in the background by Visual Studio .NET 2003.
You can easily add records using a few lines of code. First, create an instance of the DataRow class and call it NewRow() method.
Dim drnew As System.Data.DataRow
drnew = Me.DsCustomers1.customer_list.NewRow()
This will create a blank row in the database. The next step is to bind the relevant column name with the required WinForm control and call the Add() method of Rows collection as shown below.
drnew.Item("Name") = txtName.Text
drnew.Item("E-mail") = txtEmail.Text
Me.DsCustomers1.customer_list.Rows.Add(drnew)
The final step is to update the dataset with the created object of the DataAdapter.
OleDbDataAdapter1.Update(DsCustomers1)
Navigating Records
With ADO.NET, you can move to the next record, previous record, and also directly to the first and last records using a single line of code.
The second part of our application is the Search functionality. For this purpose, add a new Windows Form to the application (Project | Add Windows Form). Select the menu item titled Windows Form, give a meaningful name and click the Open button. Create a user interface as shown in figure 2 and suply the required properties.
The application links the new form you have created from the main form. For this purpose, double click the Search button and add the following line of code:
Dim search1 As Search = New Search
search1.Show()
We make use of a ComboBox and a TextBox for our search application. The ComboBox populates the list of all names from the database, and the textbox will display the corresponding e-mail address by searching the records from our database.
In the previous session, you learned about the use of built-in data controls for establishing a connection to the database. In this session, we will see how to do this job with the help of .NET code. The code for establishing the connection and populating a ComboBox is shown in the listing given below. Please be sure to supply the code inside the Load event of the form.
m_oledbdataadapter = New OleDbDataAdapter("Select * from customer_list", m_oledbconnection)
Dim cmd As New OleDbCommand("Select * from customer_list", m_oledbconnection)
Dim m_oledbcommandbuilder As OleDbCommandBuilder
m_oledbcommandbuilder = New OleDbCommandBuilder(m_oledbdataadapter)
Dim m_datatable As New DataTable
m_oledbdataadapter.Fill(m_datatable)
Dim objreader As OleDbDataReader = cmd.ExecuteReader()
'ComboBox Population
Do While objreader.Read()
cboName.Items.Add(objreader("Name"))
Loop
m_oledbconnection.Close()
cboName.SelectedIndex = 0
Please note that you will have to modify the path name of the database before attempting to execute the application. Double click the ComboBox control on the form and add the following lines of code:
Try
Dim strsql As String
strsql = "SELECT * FROM customer_list WHERE Name = '" & cboName.Text & "' "
m_oledbconnection.Open()
Dim cmd As New OleDbCommand(strsql, m_oledbconnection)
Dim objreader As OleDbDataReader = cmd.ExecuteReader
objreader.Read()
txtEmail.Text = objreader("E-mail")
objreader.Close()
m_oledbconnection.Close()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, Me.Text)
End Try
The above code will search for the field named ‘Name’ from the database customer_list. If it matches with the one listed on the ComboBox, the corresponding e-mail will be displayed on the TextBox control with the code given below:
txtEmail.Text = objreader("E-mail")
In the next part of this article, you will learn how to edit and delete data from a database using ADO.NET.
/ 23 
ASP.NET Export to Excel and Word using VB.NET and C#
