How to Fill a ListView with any ADO.Net Dataset

Environment: VB .Net, .Net Framework, SQL Server 2000

This article will show how to fill a ListView Control with the data contained
in any ADO.Net DataReader. You can use a DataSet bind it to a Grid Control to
show the output of a query, but data binding of controls is not the ideal
method of accessing the data. A DataSet maintains a copy of the entire
resultset in the client systems memory in case you need to make changes to a
row. In most cases, users will be updated very few records at any given time,
so holding a large resultset in memory to change one or two records is not very
efficient. Imagine what the situation would be like with a user that opens
multiple resultsets all the time, not bothering to close an old window until
his system or the program become unstable. We all know users like that and we
most plan accordingly. In addition, the DataSet will not know if the data that
the user attempts to modify has been changed on the server until it posts its
updates, making it difficult to control how the update conflict will be
handled.

Instead of using a bound grid and a DataSet, I prefer to use the listview
control with the view set to details mode (.View = lvwResults in VB6) and fill
it with the data from a DataReader and disposing of the DataReader as soon as
the listview has been filled in. This frees up the resources involved with the
DataSet and the bound control. You do have to be careful to structure your
queries so that you retrieve the primary key of the table involved if you are
planning on allowing updates to the data displayed.

Here is a empty listview waiting for our data. Getting the data into it works
in a somewhat different manner in VB .Net than you are used to if you are moving
up from previous versions of VB. With VB6, you would get a new ListItem and
fill in the data by using code as shown in Example 1.


‘Example 1 – Creating a ListItem in VB6
‘Assumes a listview control called lvwCustomers exists on form

Dim lstStuff AS ListItem

Set lstStuff = lvwCustomers.ListItems.Add(, ,”First Columns Output”)

Set lstStuff = Nothing
‘End Example Code

When creating a ListItem in VB .Net, you explicity create the ListItem and then
add it to the ListView’s Items collection.


‘Example 2 – Creating a ListItem in VB.Net
‘Assumes a listview control called lvwCustomers exists on form

Dim lstStuff AS ListItem = New ListViewItem()

lstStuff.Text = “First Columns Output”
lvwCustomers.Items.Add(lstStuff)

lstStuff = Nothing
‘End Example Code

Of course, if you only have one column of data there is no real need for a
ListView control is there? Example 3 shows a more typical block of code adding
a ListItem to a ListView in VB6.


‘Example 3 – Creating a ListItem with SubItems in VB6
‘Assumes a listview control called lvwCustomers exists on form

Dim lstStuff AS ListItem

Set lstStuff = lvwCustomers.ListItems.Add(, ,”First Columns Output”)

lstStuff.SubItems(1) = “Second Columns Output”
lstStuff.SubItems(2) = “Third Columns Output”
lstStuff.SubItems(3) = “Fourth Columns Output”
lstStuff.SubItems(4) = “Fifth Columns Output”

Set lstStuff = Nothing
‘End Example Code

It is implicit in the code above that the Add method of the ListItems
collection returns a ListItem that contains all the SubItems that the
ListView’s Columns collection requires. In VB .Net, things are different. We
must add the SubItems to our ListItem’s SubItems collection ourselves before we
attach the ListItem itself to the ListView’s Items collection.


‘Example 4 – Creating a ListItem with SubItems in VB .Net
‘Assumes a listview control called lvwCustomers exists on form

Dim lstStuff AS ListItem = New ListViewItem()

lstStuff.Text = “First Columns Output”
lstStuff.SubItems.Add(“Second Columns Output”)
lstStuff.SubItems.Add(“Third Columns Output”)
lstStuff.SubItems.Add(“Fourth Columns Output”)
lstStuff.SubItems.Add(“Fifth Columns Output”)
lvwCustomers.Items.Add(lstStuff)

lstStuff = Nothing
‘End Example Code

Now that we know the basics of adding a ListItem to a ListView control in
VB.net, lets move on to filling a ListView with data from a DataSet. The basic
steps in this procedure are:

  1. Clear old contents from ListView
  2. Add Columns to ListView to support the Dataset
  3. Fill ListView with ListItems containing the data
    1. The code for step one is pure simplicity!


      ‘clearing the contents of the ListView lvwCustomers
      ‘Note that the Clear method removes all ListItems & Columns!!!
      lvwCustomers.Clear()

      That was pretty easy, how hard can the rest of this be? Lets add the Columns to
      the ListView. We will accomplish this by looping through our DataSet’s fields
      and using the names as the Column titles. Note that the columns are created and
      then added to the ListView’s Columns collection in a very similar fashion to
      attaching the ListItems to the ListView. Also note that with an ADO Recordset,
      the Fields collection was the Default Member of the recordset. With an
      SqlDataReader every method acts upon the Fields collection and you can not
      explicity call it.


      ‘Adding the needed columns to the ListView
      ‘Using VB .Net and an ADO.Net SqlDataReader named MyData

      Dim shtFieldCntr As Short
      Dim lvwColumn As ColumnHeader

      For shtFieldCntr = 0 To myData.FieldCount() – 1
      ‘Create Column Header
      lvwColumn = New ColumnHeader()
      ‘Set its text to the name of the field
      lvwColumn.Text = myData.GetName(shtFieldCntr)
      ‘add Column to ListView
      MyListView.Columns.Add(lvwColumn)
      Next

      lvwColumn = Nothing
      ‘End Example Code

      Whew, no sweat and we are almost done. Adding the ListItems to the ListView is
      a simple matter of looping through the SqlDataReader’s records and adding them
      to the ListView. To add the data to a ListItem we will use an inner loop going
      through the fields, creating SubItems as we go. Note the test for a Null value
      before putting the data in the ListItem. The ListItem will throw an exception
      every time you try and assign a Null value to a Text property.


      ‘Adding the ListItems to the ListView with the data
      ‘Using VB .Net and an ADO.Net SqlDataReader named MyData

      Dim itmListItem as ListViewItem
      Dim shtFieldCntr As Short

      Do While myData.Read
      itmListItem = New ListViewItem()

      If myData.IsDBNull(myData(0)) Then
      itmListItem.Text = “”
      Else
      itmListItem.Text = myData(0)
      End If

      For shtFieldCntr = 1 To myData.FieldCount() – 1
      If myData.IsDBNull(shtFieldCntr) Then
      itmListItem.SubItems.Add(“”)
      Else
      itmListItem.SubItems.Add(myData.GetString(shtFieldCntr))
      End If
      Next shtCntr

      MyListView.Items.Add(itmListItem)
      Loop
      ‘End Example Code

      Well, there you have it. The basics of using the ListView Control to Display a
      DataReaders resultset. Here is a class definition with a method called
      FillListView that encapsulates it all, in the tradition of OOP.



      Imports System.Data.SqlClient

      Public Class ListViewData
      Public Sub FillListView(ByRef MyListView As ListView, _
      ByRef myData As SqlDataReader)
      Dim lvwColumn As ColumnHeader
      Dim itmListItem As ListViewItem

      Dim shtCntr As Short

      MyListView.Clear()
      For shtCntr = 0 To myData.FieldCount() – 1
      lvwColumn = New ColumnHeader()
      lvwColumn.Text = myData.GetName(shtCntr)
      MyListView.Columns.Add(lvwColumn)
      Next

      Do While myData.Read
      itmListItem = New ListViewItem()
      itmListItem.Text = myData(0)

      For shtCntr = 1 To myData.FieldCount() – 1
      If myData.IsDBNull(shtCntr) Then
      itmListItem.SubItems.Add(“”)
      Else
      itmListItem.SubItems.Add(myData.GetString(shtCntr))
      End If
      Next shtCntr

      MyListView.Items.Add(itmListItem)
      Loop
      End Sub
      End Class

      Downloads

      Download Complete Source Code

More by Author

Must Read