Retrieve Data Using DataSets and Data Adapters

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

'Set up connection string Dim cnString As String txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb" Dim sqlQRY As String = "SELECT * FROM Customers WHERE City = 'London'" 'Create connection Dim conn As OleDbConnection = New OleDbConnection(cnString) Try ' Open connection conn.Open() txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf & vbCrLf 'create data adapter Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn) 'create dataset Dim ds As DataSet = New DataSet 'fill dataset da.Fill(ds, "Customers") 'get data table Dim dt As DataTable = ds.Tables("Customers") 'display data Dim row As DataRow For Each row In dt.Rows txtStatus.Text = txtStatus.Text & row("CompanyName") & vbCrLf Next row Catch ex As SqlException txtStatus.Text = "Error: " & ex.ToString & vbCrLf Finally ' Close connection conn.Close() txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed." End Try

2. Datasets and Data Adapters – Microsoft SQL Server

'Set up connection string Dim cnString As String txtStatus.Text = "Connecting to database using SQL Server" & vbCrLf & vbCrLf cnString = "Data Source=localhost;Integrated Security=True;database=northwind" Dim sqlQRY As String = "SELECT * FROM Customers WHERE City = 'London'" 'Create connection Dim conn As SqlConnection = New SqlConnection(cnString) Try ' Open connection conn.Open() txtStatus.Text = txtStatus.Text & "Connection opened" & vbCrLf & vbCrLf 'create data adapter Dim da As SqlDataAdapter = New SqlDataAdapter(sqlQRY, conn) 'create dataset Dim ds As DataSet = New DataSet 'fill dataset da.Fill(ds, "Customers") 'get data table Dim dt As DataTable = ds.Tables("Customers") 'display data Dim row As DataRow For Each row In dt.Rows txtStatus.Text = txtStatus.Text & row("CompanyName") & vbCrLf Next row Catch ex As SqlException txtStatus.Text = "Error: " & ex.ToString & vbCrLf Finally ' Close connection conn.Close() txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed." End Try

How it works:

First we create a new instance of Data Adapter

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

followed by a Dataset

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

and we access the data using the Data Table

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

with its row properties

Dim row As DataRow For Each row In dt.Rows txtStatus.Text = row("CompanyName") 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

Comments

Add new comment