An Introduction to ADO.NET ( Tutorial )

[mendhak]

Continuing in Beacon's steps, I'll continue with a basic introduction to ADO.NET.

The very first thing you'll need is a database. A database sample has been provided in the attachment, which consists of a few simple fields in the table tbl_master:

EmployeeID
FirstName
LastName
Location

We will be creating a simple form for navigating through the records in the table.

Start by placing 3 labels, 3 textboxes and 4 buttons on a form as shown here. Name the textboxes txtFirstName, txtLastName and txtLocation. The buttons should be self explanatory as well, btnFirst, btnPrevious, btnNext, btnLast.



Now we begin. Declare a dataset at the class level and import the System.Data.OleDb namespace.

Dim ds As New DataSet()

In the Form's Load event, fill up the dataset. To do this, create a DataAdapter and use its Fill() method to fill up the dataset.

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\mendhak\My Documents\Visual Studio 2005\Projects\ADONetTutorial1\ADONetTutorial1\sample.mdb;User Id=admin;Password=;"
 
        Dim strSQL As String = "SELECT EmployeeID, FirstName, LastName, Location FROM tbl_Master"
        Dim da As New OleDbDataAdapter(strSQL, conn)
 
        da.Fill(ds)

(You will have to modify the connection string to point the location of the MDB file on your machine)

The dataset has now been filled up. For those of you who've worked in classic ADO, think of a dataset as something like a recordset, except that a dataset is disconnected from the dataset, so you don't need to worry about cursors, EOF, BOF or closing connections. Datasets are .NET collections as well, making them more flexible.

Anyways, now we fill up the textboxes with the data in our dataset. Remember that a dataset is a collection. More specifically, it is a collection of DataTables. A DataTable simply represents a table of data you have retrieved from the database. We'll start with the first row. Immediately after the Fill() method, do this:

If ds.Tables(0).Rows.Count > 0 Then 'Check if the table is empty
            txtFirstName.Text = ds.Tables(0).Rows(0).Item("FirstName").ToString()
            txtLastName.Text = ds.Tables(0).Rows(0).Item("LastName").ToString()
            txtLocation.Text = ds.Tables(0).Rows(0).Item("Location").ToString()
        End If

Run your form, and it should look like this.



In order to do the navigation, we will need an integer to hold our current Row position in the dataset's table. Declare an integer where you declared the dataset.

Now double click the << button (btnFirst) and in its Click event, set the textboxes to read from Row 0.

'Since 0 is the first row
        txtFirstName.Text = ds.Tables(0).Rows(0).Item("FirstName").ToString()
        txtLastName.Text = ds.Tables(0).Rows(0).Item("LastName").ToString()
        txtLocation.Text = ds.Tables(0).Rows(0).Item("Location").ToString()

Double click the < button (btnPrevious) and in its Click event,

If intCurrentIndex > 0 Then  'We move back only if we're not at the first row.
            intCurrentIndex = intCurrentIndex - 1  'Subtract one from the current index.
 
            txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
            txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
            txtLocation.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
        Else
            MessageBox.Show("You're already at the first record.")
        End If

Double click the > button (btnNext) and in its Click event,

If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            intCurrentIndex = intCurrentIndex + 1  'Add one to the current index.
 
            txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
            txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
            txtLocation.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
        Else
            MessageBox.Show("You're already at the last record.")
        End If

Double click the >> button (btnLast) and in its Click event,

intCurrentIndex = ds.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
        txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
        txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
        txtLocation.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()

That's it. You've just created a basic navigation form. You should be able to move to other rows.



There are many improvements that can be done here. The code to fill up the fields can be placed in a single method to which we pass a parameter. We'll do this in the next part of the tutorial, along with adding, updating and deleting.





Keywords:
ADO.NET
Tutorial
ADO
.NET
Database
Beacon
Mendhak
Guava
Introduction
Spoonguard