An Introduction to ADO.NET ( Tutorial )-VBForums
Results 1 to 3 of 3

Thread: An Introduction to ADO.NET ( Tutorial )

  1. #1

    Thread Starter
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Post An Introduction to ADO.NET ( Tutorial )

    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.

    vb Code:
    1. 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.


    vb Code:
    1. 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=;"
    2.  
    3.         Dim strSQL As String = "SELECT EmployeeID, FirstName, LastName, Location FROM tbl_Master"
    4.         Dim da As New OleDbDataAdapter(strSQL, conn)
    5.  
    6.         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:


    vb Code:
    1. If ds.Tables(0).Rows.Count > 0 Then 'Check if the table is empty
    2.             txtFirstName.Text = ds.Tables(0).Rows(0).Item("FirstName").ToString()
    3.             txtLastName.Text = ds.Tables(0).Rows(0).Item("LastName").ToString()
    4.             txtLocation.Text = ds.Tables(0).Rows(0).Item("Location").ToString()
    5.         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.

    vb Code:
    1. 'Since 0 is the first row
    2.         txtFirstName.Text = ds.Tables(0).Rows(0).Item("FirstName").ToString()
    3.         txtLastName.Text = ds.Tables(0).Rows(0).Item("LastName").ToString()
    4.         txtLocation.Text = ds.Tables(0).Rows(0).Item("Location").ToString()

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

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

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

    vb Code:
    1. If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
    2.             intCurrentIndex = intCurrentIndex + 1  'Add one to the current index.
    3.  
    4.             txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
    5.             txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
    6.             txtLocation.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
    7.         Else
    8.             MessageBox.Show("You're already at the last record.")
    9.         End If
    Double click the >> button (btnLast) and in its Click event,

    vb Code:
    1. intCurrentIndex = ds.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
    2.         txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
    3.         txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
    4.         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
    Attached Files Attached Files
    Last edited by penagate; Nov 20th, 2007 at 11:48 PM. Reason: fix attachment link
    Share on Google+

  2. #2

    Thread Starter
    ASP.NET Moderator mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: An Introduction to ADO.NET

    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.

    vb Code:
    1. Private Sub FillFields()
    2.         txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
    3.         txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
    4.         txtLocation.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
    5.     End Sub

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

    vb Code:
    1. If intCurrentIndex > 0 Then  'We move back only if we're not at the first row.
    2.             intCurrentIndex = intCurrentIndex - 1  'Subtract one from the current index.
    3.             FillFields()
    4.         Else
    5.             MessageBox.Show("You're already at the first record.")
    6.         End If

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

    vb Code:
    1. Dim ds As New DataSet()
    2.     Dim intCurrentIndex As Integer = 0
    3.     Dim da As New OleDbDataAdapter()
    4.     Dim conn As New OleDbConnection()

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

    vb Code:
    1. da.SelectCommand = New OleDbCommand("SELECT EmployeeID, FirstName, LastName, Location FROM tbl_Master")
    2.         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.


    vb Code:
    1. da.UpdateCommand = New OleDbCommand("UPDATE tbl_Master SET FirstName = @FirstName, LastName = @LastName, Location =@Location WHERE EmployeeID = @EmployeeID")
    2.         da.UpdateCommand.Connection = conn
    3.         da.UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
    4.         da.UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar, 40, "LastName")
    5.         da.UpdateCommand.Parameters.Add("@Location", OleDbType.VarChar, 40, "Location")
    6.         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.

    vb Code:
    1. Dim dr As DataRow
    2.         dr = ds.Tables(0).Rows(intCurrentIndex) 'This gets a reference to the row currently being edited
    3.         dr.BeginEdit()  
    4.         dr("FirstName") = txtFirstName.Text
    5.         dr("LastName") = txtLastName.Text
    6.         dr("Location") = txtLocation.Text
    7.         dr.EndEdit()
    8.  
    9.         da.Update(ds)  'Ask the dataadapter to call the UpdateCommand and update the database
    10.         ds.AcceptChanges() 'Commits the change to the dataset.

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


    vb Code:
    1. da.InsertCommand = New OleDbCommand("INSERT INTO tbl_Master(FirstName, LastName, Location) VALUES(@FirstName,@LastName,@Location)")
    2.         da.InsertCommand.Connection = conn
    3.         da.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 40, "FirstName")
    4.         da.InsertCommand.Parameters.Add("@LastName", OleDbType.VarChar, 40, "LastName")
    5.         da.InsertCommand.Parameters.Add("@Location", OleDbType.VarChar, 40, "Location")

    And in the btnAdd's click event:


    vb Code:
    1. Dim dr As DataRow
    2.  
    3.         dr = ds.Tables(0).NewRow()  'Gets a reference to a new row.
    4.         dr("FirstName") = txtFirstName.Text
    5.         dr("LastName") = txtLastName.Text
    6.         dr("Location") = txtLocation.Text
    7.  
    8.         ds.Tables(0).Rows.Add(dr)
    9.         da.Update(ds)
    10.         ds.AcceptChanges()



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

    vb Code:
    1. da.DeleteCommand = New OleDbCommand("DELETE FROM tbl_Master WHERE EmployeeID = @EmployeeID")
    2.         da.DeleteCommand.Connection = conn
    3.         da.DeleteCommand.Parameters.Add("@EmployeeID", OleDbType.Integer, 5, "EmployeeID")

    And in the btnDelete's click event:

    vb Code:
    1. Dim dr As DataRow
    2.         dr = ds.Tables(0).Rows(intCurrentIndex)
    3.         dr.Delete() 'Delete the row
    4.  
    5.         da.Update(ds)
    6.         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
    Last edited by mendhak; May 3rd, 2007 at 01:23 AM.
    Share on Google+

  3. #3
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,334

    Re: An Introduction to ADO.NET ( Tutorial )

    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.