Retrieve Data Using DataSets and Data Adapters

Submitted by admin on Wednesday, April 29, 2009 - 13:26.

This tutorial is part of Database Programming Made Easy Series.

DataSet and Data Adapter

Dataset can be either connected to or disconnected from data sources. Data is save back to the database by using Data Adapters. Data Adapters handles the connection between Data Sources and Dataset

Remember that in the previous topic we discuss the use of Data Readers. One rule is not to use dataset if you only need to retrieve data.

In Visual Basic 6.0 you always use a connected ADO connection. Unlike with Dataset where you can process the data in an offline mode. This will eliminate redundancy of creating a new connection every time you request a data.

Note that the purpose of this tutorial is to teach you the use of Datasets and Data Adapters. I will not discuss in details all the features of Dataset.

Now let us try this example. We will populate a Dataset with a Data Adapter. We’re still using the previous code but with few modification.

In Dataset you do not need to create a command like

Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)

because Data Adapter will do that for you automatically. This is one of the features that a Dataset has.

The codes that you need to familiarize with, in using Datasets:

1. Datasets and Data Adapters – Microsoft Access

  1.         'Set up connection string
  2.         Dim cnString As String
  3.  
  4.         txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf
  5.  
  6.         cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"
  7.  
  8.         Dim sqlQRY As String = "SELECT * FROM Customers WHERE City = 'London'"
  9.  
  10.         'Create connection
  11.         Dim conn As OleDbConnection = New OleDbConnection(cnString)
  12.  
  13.         Try
  14.             ' Open connection
  15.             conn.Open()
  16.  
  17.             txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf & vbCrLf
  18.  
  19.             'create data adapter
  20.             Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)
  21.  
  22.             'create dataset
  23.             Dim ds As DataSet = New DataSet
  24.  
  25.             'fill dataset
  26.             da.Fill(ds, "Customers")
  27.  
  28.             'get data table
  29.             Dim dt As DataTable = ds.Tables("Customers")
  30.  
  31.             'display data
  32.             Dim row As DataRow
  33.  
  34.             For Each row In dt.Rows
  35.                 txtStatus.Text = txtStatus.Text & row("CompanyName") & vbCrLf
  36.             Next row
  37.         Catch ex As SqlException
  38.             txtStatus.Text = "Error: " & ex.ToString & vbCrLf
  39.         Finally
  40.             ' Close connection
  41.             conn.Close()
  42.             txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
  43.         End Try

2. Datasets and Data Adapters – Microsoft SQL Server

  1.         'Set up connection string
  2.         Dim cnString As String
  3.  
  4.         txtStatus.Text = "Connecting to database using SQL Server" & vbCrLf & vbCrLf
  5.  
  6.         cnString = "Data Source=localhost;Integrated Security=True;database=northwind"
  7.  
  8.         Dim sqlQRY As String = "SELECT * FROM Customers WHERE City = 'London'"
  9.  
  10.         'Create connection
  11.         Dim conn As SqlConnection = New SqlConnection(cnString)
  12.  
  13.         Try
  14.             ' Open connection
  15.             conn.Open()
  16.  
  17.             txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf & vbCrLf
  18.  
  19.             'create data adapter
  20.             Dim da As SqlDataAdapter = New SqlDataAdapter(sqlQRY, conn)
  21.  
  22.             'create dataset
  23.             Dim ds As DataSet = New DataSet
  24.  
  25.             'fill dataset
  26.             da.Fill(ds, "Customers")
  27.  
  28.             'get data table
  29.             Dim dt As DataTable = ds.Tables("Customers")
  30.  
  31.             'display data
  32.             Dim row As DataRow
  33.  
  34.             For Each row In dt.Rows
  35.                 txtStatus.Text = txtStatus.Text & row("CompanyName") & vbCrLf
  36.             Next row
  37.         Catch ex As SqlException
  38.             txtStatus.Text = "Error: " & ex.ToString & vbCrLf
  39.         Finally
  40.             ' Close connection
  41.             conn.Close()
  42.             txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
  43.         End Try

How it works:

First we create a new instance of Data Adapter

  1. Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)

followed by a Dataset

  1. Dim ds As DataSet = New DataSet
  2. da.Fill(ds, "Customers")

and we access the data using the Data Table

  1. Dim dt As DataTable = ds.Tables("Customers")

with its row properties

  1. Dim row As DataRow
  2.  
  3. For Each row In dt.Rows
  4. txtStatus.Text = row("CompanyName")
  5. Next row

Another important feature of a Dataset is its ability to pull the entire schema of a data table including its relationship.

Next topic we will discuss on how to save data back to the database.

Previous: How to Retrieve Data Using Data Reader Next: How to Modify Data
Tags
vb.net
tutorial
dataset
data adapter

Comments

Add new comment

About text formats