Looking for a simple explanation of how to walk through a dataset in .net 2.0

  • Thread starter Thread starter SmartbizAustralia
  • Start date Start date
S

SmartbizAustralia

Hi,

This seems to be a neglected bit of info as everyone gets carried away
with data binding examples instead.

Can simply use the datareader as below:

Private Sub PopulateControls1()
Dim sSql As String
Dim cn As SqlConnection

cn = New SqlConnection(m_sConnection)
cn.Open()
sSql = "Select top 10 * from Person.Contact"
Dim sqlCmd As New SqlCommand(sSql, cn)
Dim r As SqlDataReader = sqlCmd.ExecuteReader()

'Get the first line
r.Read()
Me.Label1.Text = "First Name"
Me.TextBox1.Text = r.Item("FirstName")
'Now get the new line
r.Read()
Me.Label2.Text = "First Name"
Me.TextBox2.Text = r.Item("FirstName")
cn.Close()

End Sub

but would love to do the same with a dataset instead.

But how do you walk through each row and select particular columns in a
dataset?
 
In the DataTable that is inside the DataSet, the rows are just an array of
DataRow and all the columns are available in each 'DataRow'.

Something like this should put you on the right track:

'Given as Dataset _ds having a single Datable

Dim _dr As DataRow = _ds.Tables(0).Rows(0)
Me.Label1.Text = "First Name"
Me.TextBox1.Text = _dr("FirstName").ToString()

_dr = _ds.Tables(0).Rows(1)
Me.Label2.Text = "First Name"
Me.TextBox2.Text = _dr("FirstName").ToString()

or, instead:

Me.Label1.Text = "First Name"

Me.TextBox1.Text = _ds.Tables(0).Rows(0)("FirstName").ToString()

Me.Label2.Text = "First Name"

Me.TextBox2.Text = _ds.Tables(0).Rows(1)("FirstName").ToString()
 
This creates a DataSet; you can also use a DataTable.

---------------------------------
Dim ds As DataSet
'open the connection
Using cnn As New SqlConnection(My.Settings.PTConnectionString)
cnn.Open()

'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandText = "SELECT * FROM Product"
'define the data adapter and fill the data table
Dim da As New SqlDataAdapter(cmd)
ds = New DataSet
da.Fill(ds, "Product")
End Using

For Each dr As DataRow In ds.Tables("Product").Rows
Dim ProductID As Integer = CType(dr.Item("ProductID"), Integer)
Dim ProductName As String = dr.Item("ProductName").ToString
Dim ProductNumber As String = dr.Item("ProductNumber").ToString
'Dim Price As Nullable(Of Decimal)
'If .Item("Price") IsNot DBNull.Value Then
' Price = CType(.Item("Price"), Decimal)
'End If
Dim Description As String = dr.Item("Description").ToString
'Dim ProductType As Integer = CType(.Item("ProductType"), Integer)
'Dim StockType As String = .Item("StockType").ToString
Console.WriteLine(String.Format("ProductID {0}, " & _
"ProductName {1}, {2}ProductNumber {3}, " & _
"Description {4}", ProductID, ProductName, _
ControlChars.CrLf, ProductNumber, Description))
Next
-------------------------------

You can also load a dataset with multiple tables.
To do this, your sql would have multiple sql statements
in yhour commandtext:

SELECT * FROM PRODUCT; SELECT * FROM CUSTOMERS

and then you name the tables like this:

ds.Tables(0).TableName = "Product"
ds.Tables(1).TableName = "Customers"


Robin S.
 
Back
Top