VB.NET - Listview row must be colored according to MySQL data (how do I implement it to my code?)

Question

I have a listview populated with data from MySQL. Some of these records are "disabled". I want their row in the list view to be colored as red. How do I implement it to my existing display code?

    Private Sub DisplayEmployeeRecords()
    connect()
    Dim BSource As New BindingSource

    Dim cmd As New MySqlCommand
    Dim conn As New MySqlConnection
    Dim Reader As MySqlDataReader

    MD = "SELECT * From users"
    cmd = New MySqlCommand(MD, con)
    SDA.SelectCommand = cmd
    SDA.Fill(DBDataSet)
    BSource.DataSource = DBDataSet
    SDA.Update(DBDataSet)
    connect()
    Reader = cmd.ExecuteReader

    ListView1.Items.Clear()

    Do While Reader.Read
        With ListView1.Items.Add(Reader.Item("UserID").ToString)
            .UseItemStyleForSubItems = False
            .SubItems.Add(Reader.Item("Username").ToString)
            .SubItems.Add(Reader.Item("FirstName").ToString)
            .SubItems.Add(Reader.Item("MiddleName").ToString)
            .SubItems.Add(Reader.Item("LastName").ToString)
            .SubItems.Add(Reader.Item("PersonalQuestion").ToString)
            .SubItems.Add(Reader.Item("PersonalAnswer").ToString)
            .SubItems.Add(Reader.Item("Password").ToString)
            .SubItems.Add(Reader.Item("Address").ToString)
            .SubItems.Add(Reader.Item("ContactNumber").ToString)
            .SubItems.Add(Reader.Item("EmailAddress").ToString)
            .SubItems.Add(Reader.Item("Age").ToString)
            .SubItems.Add(Reader.Item("BirthDate").ToString)
            .SubItems.Add(Reader.Item("Gender").ToString)
            .SubItems.Add(Reader.Item("AccessLevel").ToString)
            .SubItems.Add(Reader.Item("RegistrationDate").ToString)
            .SubItems.Add(Reader.Item("OnlineOffline").ToString)
            .SubItems.Add(Reader.Item("AccountStatus").ToString)

        End With

    Loop


    Reader.Close()

End Sub

Answer 1

Keep your database objects local so you ensure that they are closed and disposed. Using ... End Using blocks take care of this for you even if there is an error.

Pass your connection string directly to the constructor of the connection and the command text and connection to the constructor of the command.

Since you can't bind a ListView there is not much point in a BindingSource or DataAdapter.

When you fill a DataTable you can close and dispose your connect as soon as it is finished. The connection needs to stay open as long as a DataReader is active. This can take quit a while manually filling a ListView.

It is slow to fill a ListView one by one. Create a List(Of ListViewItem) and at the end change it to an Array with .ToArray use .AddRange on the ListView. This will speed up your display a great deal.

As far as the color is concerned, just test the value of the column that has "disabled" in it and set the color of the ListItem accordingly.

Private Sub DisplayEmployeeRecords()
    'Retrieve data from database
    Dim dt As New DataTable
    Using con As New MySqlConnection("Your connection string."),
            cmd As New MySqlCommand("SELECT * From users;", con)
        con.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    'Build an in memory list of the data in ListViewItem format
    Dim lst As New List(Of ListViewItem)
    For Each row As DataRow In dt.Rows
        Dim li As New ListViewItem
        li.Text = row("UserID").ToString
        li.UseItemStyleForSubItems = False
        With li.SubItems
            .Add(row("Username").ToString)
            .Add(row("FirstName").ToString)
            .Add(row("MiddleName").ToString)
            .Add(row("LastName").ToString)
            .Add(row("PersonalQuestion").ToString)
            .Add(row("PersonalAnswer").ToString)
            .Add(row("Password").ToString)
            .Add(row("Address").ToString)
            .Add(row("ContactNumber").ToString)
            .Add(row("EmailAddress").ToString)
            .Add(row("Age").ToString)
            .Add(row("BirthDate").ToString)
            .Add(row("Gender").ToString)
            .Add(row("AccessLevel").ToString)
            .Add(row("RegistrationDate").ToString)
            .Add(row("OnlineOffline").ToString)
            .Add(row("AccountStatus").ToString)
            If row("AccoutnStatus").ToString = "disabled" Then
                li.BackColor = Color.Red
            End If
        End With
        lst.Add(li)
    Next
    'Display the data
    ListView1.Items.Clear()
    ListView1.Items.AddRange(lst.ToArray)
End Sub