lvfunc.vb

LISTVIEW + DATABASE PROGRAMMING FUNCTIONS IN A NUTSHELL This solution contains a classLibrary projec

'********************************************
'FileName : lvFunc.vb                       *
'Purpose  : To enhance coding with listview *
'Author   : Kunal Mukherjee                 *
'Mobile   : [phone]                *
'E-Mail   : [e-mail] *
'********************************************
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Drawing

Public Class lvFunc
    'This sub is used to fill up a list view with data from the database(MSACCESS VERSION)
    Public Sub FillListView_MSA(ByVal strSQL As String, ByVal CON As OleDbConnection, _
    ByVal LV As ListView, ByVal altRowColor As Color, ByVal startAtOddRow As Boolean, _
    ByVal boolShowGrid As Boolean, ByVal lblToShowTotRec As Label, _
    Optional ByVal intColCount As Integer = 0)

        'set the properties of the listview
        LV.FullRowSelect = True
        LV.GridLines = boolShowGrid
        LV.HideSelection = False
        LV.HoverSelection = True
        LV.View = View.Details

        'object declaration required for this sub
        Dim CMD As OleDbCommand = New OleDbCommand(strSQL, CON)
        Dim DR As OleDbDataReader
        Dim lvItem As ListViewItem
        Dim i As Integer
        Dim CurrentRow As Integer
        Dim myResult As Double

        'try to open the connection
        Try
            If CON.State = ConnectionState.Closed Then
                CON.Open()
            End If
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try

        'now try to load the data into the listview
        Try

            DR = CMD.ExecuteReader 'execute the command to get the datareader
            'first check if the datareader has rows or not
            If Not DR.HasRows Then Exit Sub
            'validate the mentioned column number to show in the list view
            If intColCount = 0 Then intColCount = DR.FieldCount - 1
            If intColCount > DR.FieldCount Then intColCount = DR.FieldCount

            'clear the listview before loading the data
            If LV.Items.Count > 0 Then
                LV.Items.Clear()
            End If

            'now read and load the data
            While DR.Read
                'check if the datareader item is null or not
                If DR(0) Is System.DBNull.Value Then
                    lvItem = New ListViewItem("") 'if null then add blank
                Else
                    'else add the field value
                    lvItem = New ListViewItem(CStr(DR(0))) 'add the main listitem for each row
                End If

                'add the subitems to the mentioned limit
                For i = 1 To intColCount
                    'check if the datareader item is null or not
                    If DR(i) Is System.DBNull.Value Then
                        lvItem.SubItems.Add("") 'if null then add blank
                    Else
                        lvItem.SubItems.Add(DR(i)) 'else add the field value
                    End If
                Next

                'code for coloring the alternative rows of a listbox
                myResult = CurrentRow Mod 2
                If startAtOddRow Then 'if the user want to start coloring from the odd row
                    If myResult <> 0 Then
                        lvItem.BackColor = altRowColor
                        lvItem.UseItemStyleForSubItems = True
                    End If
                Else
                    If myResult = 0 Then
                        lvItem.BackColor = altRowColor
                        lvItem.UseItemStyleForSubItems = True
                    End If
                End If

                'add the listviewitem to the items collection of the listview
                LV.Items.Add(lvItem)
                'increase the row counter
                CurrentRow += 1
            End While
            'show the total records found
            lblToShowTotRec.Text = "Total " & CurrentRow & " records found."
        Catch ex As Exception
            CustomExceptionMessage(ex)
        Finally
            DR.Close() 'close the datareader when done
            CON.Close() 'close the connection when done
            CMD.Dispose() 'destroy the command object
        End Try
    End Sub
    'This sub is used to fill up a list view with data from the database(MSACCESS VSERSION)
    'Now With support of showing small icons left of each row.
    'NOTE: You must manualy set the SmallImageList property of the targeted listview to the imagelist
    'from which you will access the icons
    Public Sub FillListViewWithIcon_MSA(ByVal strSQL As String, ByVal CON As OleDbConnection, _
    ByVal LV As ListView, ByVal altRowColor As Color, ByVal startAtOddRow As Boolean, _
    Optional ByVal intImgIndex As Integer = 0, Optional ByVal intColCount As Integer = 0)

        'object declaration required for this sub
        Dim CMD As OleDbCommand = New OleDbCommand(strSQL, CON)
        Dim DR As OleDbDataReader
        Dim i As Integer
        Dim CurrentRow As Integer
        Dim myResult As Double

        'try to open the connection
        Try
            If CON.State = ConnectionState.Closed Then
                CON.Open()
            End If
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try

        'now try to load the data into the listview
        Try
            'clear the listview before loading the data
            If LV.Items.Count > 0 Then
                LV.Items.Clear()
            End If

            DR = CMD.ExecuteReader 'execute the command to get the datareader
            If Not DR.HasRows Then Exit Sub 'first check if the datareader has rows or not

            'validate the mentioned column number
            If intColCount = 0 Then intColCount = DR.FieldCount - 1
            If intColCount > DR.FieldCount Then intColCount = DR.FieldCount

            'now read and load the data
            While DR.Read

                'check if the datareader item is null or not
                If DR(0) Is System.DBNull.Value Then
                    'add the listviewitem to the items collection of the listview
                    LV.Items.Add("", intImgIndex) 'if null then add blank
                Else
                    'else add the field value
                    'add the listviewitem to the items collection of the listview
                    LV.Items.Add(DR(0), intImgIndex) 'add the main listitem for each row
                End If

                'add the subitems to the mentioned limit
                For i = 1 To intColCount
                    'check if the datareader item is null or not
                    If DR(i) Is System.DBNull.Value Then
                        LV.Items(CurrentRow).SubItems.Add("") 'if null then add blank
                    Else
                        LV.Items(CurrentRow).SubItems.Add(DR(i)) 'else add the field value
                    End If
                Next

                '..... code for coloring the alternative rows of a listbox.....
                myResult = CurrentRow Mod 2
                'MsgBox(startAtOddRow)
                If startAtOddRow Then 'if the user want to start coloring from the odd row
                    If myResult <> 0 Then
                        LV.Items(CurrentRow).BackColor = altRowColor
                        LV.Items(CurrentRow).UseItemStyleForSubItems = True
                    End If
                    'MsgBox(CurrentRow)
                    'MsgBox(myResult)
                    'MsgBox(LV.Items(CurrentRow).BackColor.ToString)
                Else
                    If myResult = 0 Then
                        LV.Items(CurrentRow).BackColor = altRowColor
                        LV.Items(CurrentRow).UseItemStyleForSubItems = True
                    End If
                    'MsgBox(CurrentRow)
                    'MsgBox(myResult)
                    'MsgBox(LV.Items(CurrentRow).BackColor.ToString)
                End If

                'increase the row counter
                CurrentRow += 1
            End While
        Catch ex As Exception
            CustomExceptionMessage(ex)
        Finally
            DR.Close() 'close the datareader when done
            CON.Close() 'close the connection when done
            CMD.Dispose() 'destroy the command object
        End Try
    End Sub
    'This sub is used to fill up a list view with data from the database(SQL SERVER VERSION)
    Public Sub FillListView_SQLSRV(ByVal strSQL As String, ByVal CON As SqlConnection, _
    ByVal LV As ListView, ByVal altRowColor As Color, ByVal startAtOddRow As Boolean, _
    ByVal boolShowGrid As Boolean, ByVal lblToShowTotRec As Label, _
    Optional ByVal intColCount As Integer = 0)

        'set the properties of the listview
        LV.FullRowSelect = True
        LV.GridLines = boolShowGrid
        LV.HideSelection = False
        LV.HoverSelection = True
        LV.View = View.Details

        'object declaration required for this sub
        Dim CMD As SqlCommand = New SqlCommand(strSQL, CON)
        Dim DR As SqlDataReader
        Dim lvItem As ListViewItem
        Dim i As Integer
        Dim CurrentRow As Integer
        Dim myResult As Double

        'try to open the connection
        Try
            If CON.State = ConnectionState.Closed Then
                CON.Open()
            End If
        Catch ex As Exception
            CustomExceptionMessage(ex)
        End Try

        'now try to load the data into the listview
        Try

            DR = CMD.ExecuteReader 'execute the command to get the datareader
            'first check if the datareader has rows or not
            If Not DR.HasRows Then Exit Sub
            'validate the mentioned column number to show in the list view
            If intColCount = 0 Then intColCount = DR.FieldCount - 1
            If intColCount > DR.FieldCount Then intColCount = DR.FieldCount

            'clear the listview before loading the data
            If LV.Items.Count > 0 Then
                LV.Items.Clear()
            End If

            'now read and load the data
            While DR.Read
                'check if the datareader item is null or not
                If DR(0) Is System.DBNull.Value Then
                    lvItem = New ListViewItem("") 'if null then add blank
                Else
                    'else add the field value
                    lvItem = New ListViewItem(CStr(DR(0))) 'add the main listitem for each row
                End If

                'add the subitems to the mentioned limit
                For i = 1 To intColCount
                    'check if the datareader item is null or not
                    If DR(i) Is System.DBNull.Value Then
                        lvItem.SubItems.Add("") 'if null then add blank
                    Else
                        lvItem.SubItems.Add(DR(i)) 'else add the field value
                    End If
                Next

                'code for coloring the alternative rows of a listbox
                myResult = CurrentRow Mod 2
                If startAtOddRow Then 'if the user want to start coloring from the odd row
                    If myResult <> 0 Then
                        lvItem.BackColor = altRowColor
                        lvItem.UseItemStyleForSubItems = True
                    End If
                Else