Using Detached Recordsets to Sort Data

'                                                                               
'  Name:                                                                        
'                                                                               
'    Detached                                                                   
'                                                                               
'  Description:                                                                 
'                                                                               
'    Sample code that shows how to use detached recordsets to do ad hoc sorts   
'    on multiple records of data not associated with a traditional database.    
'                                                                               
'  Audit:                                                                       
'                                                                               
'    2012-07-21  Reverend Jim  Original code                                    
'                                                                               

Imports ADODB   'add reference to adodb under .NET tab of "Add Reference"       

Public Class Form1

    Const TESTDATA = "D:\temp\test.txt"

    Private mydata As ADODB.Recordset

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        mydata.Close()
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        'Create a recordset object. Cursors can be server side or client side.  
        'Because the recordset will not connect to a source (it is detached) we 
        'have to specify a client side cursor (and no active connection).       

        mydata = New ADODB.Recordset
        mydata.CursorLocation = CursorLocationEnum.adUseClient
        mydata.LockType = LockTypeEnum.adLockBatchOptimistic
        mydata.CursorType = CursorTypeEnum.adOpenStatic
        mydata.ActiveConnection = Nothing

        'Add one field for each field of data in the text file

        mydata.Fields.Append("Name", DataTypeEnum.adVarChar, 32)    '32 is max length
        mydata.Fields.Append("BirthDate", DataTypeEnum.adDate)
        mydata.Fields.Append("Salary", DataTypeEnum.adInteger)

        mydata.Open()

        For Each line As String In System.IO.File.ReadAllLines(TESTDATA)

            'Split the input line into fields at the comma separator. Note that 
            'this sample code does no error checking to ensure that the number  
            'of fields is correct and that the fields contain valid data.       

            Dim flds() As String = line.Split(",")

            'Add a new record and populate the fields

            mydata.AddNew()

            'mydata.Fields("Name").Value = flds(0)
            'mydata.Fields("BirthDate").Value = flds(1)
            'mydata.Fields("Salary").Value = flds(2)

            For i As Integer = 0 To UBound(flds)
                mydata(i).Value = flds(i)
            Next

            mydata.Update()

        Next

    End Sub

    Private Sub btnByName_Click(sender As System.Object, e As System.EventArgs) Handles btnByName.Click

        'Sort the records in ascending order by name

        mydata.Sort = "Name ASC"
        PopulateListView()

    End Sub

    Private Sub btnByDate_Click(sender As System.Object, e As System.EventArgs) Handles btnByDate.Click

        'Sort the records in descending order by birth date

        mydata.Sort = "BirthDate DESC"
        PopulateListView()

    End Sub

    Private Sub btnBySalary_Click(sender As System.Object, e As System.EventArgs) Handles btnBySalary.Click

        'Sort the data on two columns - first by salary in ascending order, then by
        'name in descending order.

        mydata.Sort = "Salary ASC, Name DESC"
        PopulateListView()

    End Sub

    Private Sub PopulateListView()

        'Clear out the existing listview and repopulate it with records from the
        'recordset.

        ListView1.Items.Clear()
        mydata.MoveFirst()

        Do Until mydata.EOF
            Dim item As New ListViewItem()
            item.Text = mydata("Name").Value
            item.SubItems.Add(mydata("BirthDate").Value)
            item.SubItems.Add(mydata("Salary").Value)
            ListView1.Items.Add(item)
            mydata.MoveNext()
        Loop

    End Sub

End Class