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

[mendhak]

Create another form, just like you did in part 1, with the same labels, textboxes and buttons. Add three more buttons there: btnAdd, btnUpdate, btnDelete. Don't forget to go into Project properties and set Form2 as the startup object.



The code for Form Load, btnFirst, btnPrevious, btnNext and btnLast is almost the same as in the first form with a few small differences. Instead of filling the textboxes individually each time, we call a single method, FillFields() which looks at the intCurrentIndex variable and fills up the textboxes.

Private Sub FillFields()
        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()
    End Sub

To call it from the btnPrevious click event, for example,

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.
            FillFields()
        Else
            MessageBox.Show("You're already at the first record.")
        End If

Also, move the declaration of the connection object and the dataadapter object to class level variables.

Dim ds As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim da As New OleDbDataAdapter()
    Dim conn As New OleDbConnection()

You would then specify the dataadapter's select statement in the form's load event like so:

da.SelectCommand = New OleDbCommand("SELECT EmployeeID, FirstName, LastName, Location FROM tbl_Master")
        da.SelectCommand.Connection = conn

Your form should work as before.

Getting to the guts and purpose of this form now, we have to add a record, update a record and delete a record. ADO.NET provides many objects (and therefore ways) to accomplish this. You can use stored procedures with the OledbCommand object, or you can use SQL statements directly with the OleDbCommand object, or even use the Data Adapter to perform the updates for us.

There are different reasons for using each method, but for the purpose of this tutorial, I will continue in the theme of using the Data Adapter for this.

We start with the update method. Now, because our dataadapter filled our dataset for us, we can get the dataadapter to perform the update for us. All we need to do is tell it what to do when the time comes to update.

In the form's load event, specify the UpdateCommand property.

da.UpdateCommand = New OleDbCommand("UPDATE tbl_Master SET FirstName = @FirstName, LastName = @LastName, Location =@Location WHERE EmployeeID = @EmployeeID")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
        da.UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar, 40, "LastName")
        da.UpdateCommand.Parameters.Add("@Location", OleDbType.VarChar, 40, "Location")
        da.UpdateCommand.Parameters.Add("@EmployeeID", OleDbType.Integer, 5, "EmployeeID")

If you don't understand this, don't let it daunt you. Go over it slowly and you'll see: @FirstName, @LastName, @Location and @EmployeeID are parameters in our UPDATE statement. The Parameters that we add in the subsequent lines take arguments which are the parameter name (@FirstName), the data type(OleDbType.VarChar), the size of the field (40) and the name of the column in the dataset which will contain the new value ("FirstName").


In the btnUpdate's click event, we can now ask the dataadapter to perform an udpate.

Dim dr As DataRow
        dr = ds.Tables(0).Rows(intCurrentIndex) 'This gets a reference to the row currently being edited
        dr.BeginEdit()  
        dr("FirstName") = txtFirstName.Text
        dr("LastName") = txtLastName.Text
        dr("Location") = txtLocation.Text
        dr.EndEdit()
 
        da.Update(ds)  'Ask the dataadapter to call the UpdateCommand and update the database
        ds.AcceptChanges() 'Commits the change to the dataset.

Similarly, for inserting, in the form's load event, we specify the InsertCommand:

da.InsertCommand = New OleDbCommand("INSERT INTO tbl_Master(FirstName, LastName, Location) VALUES(@FirstName,@LastName,@Location)")
        da.InsertCommand.Connection = conn
        da.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
        da.InsertCommand.Parameters.Add("@LastName", OleDbType.VarChar, 40, "LastName")
        da.InsertCommand.Parameters.Add("@Location", OleDbType.VarChar, 40, "Location")

And in the btnAdd's click event:

Dim dr As DataRow
 
        dr = ds.Tables(0).NewRow()  'Gets a reference to a new row.
        dr("FirstName") = txtFirstName.Text
        dr("LastName") = txtLastName.Text
        dr("Location") = txtLocation.Text
 
        ds.Tables(0).Rows.Add(dr)
        da.Update(ds)
        ds.AcceptChanges()

Finally, to delete. In the form's load event, specify a DeleteCommand:

da.DeleteCommand = New OleDbCommand("DELETE FROM tbl_Master WHERE EmployeeID = @EmployeeID")
        da.DeleteCommand.Connection = conn
        da.DeleteCommand.Parameters.Add("@EmployeeID", OleDbType.Integer, 5, "EmployeeID")

And in the btnDelete's click event:

Dim dr As DataRow
        dr = ds.Tables(0).Rows(intCurrentIndex)
        dr.Delete() 'Delete the row
 
        da.Update(ds)
        ds.AcceptChanges()

There's a lot more that can be done of course, but this should have given you an introduction to ADO.NET to get you started off.

Other areas that I would recommend exploring are using ExecuteNonQuery() and also using stored procedures.

More ADO.NET tutorials can be found here:
http://samples.gotdotnet.com/quickst...sOverview.aspx

Many of you will be looking to work with SQL Server instead of access. The principles remain the same, just the namespace changes from System.Data.Oledb to System.Data.SqlClient. And you'd have SqlDataAdapter instead of OleDbDataAdapter, and so on.

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

[Hack]

Here is a supporting link:

http://msdn.microsoft.com/en-us/library/aa302325.aspx