Re: Sort multi-dimensional array using vb.net by VnsChng
VnsChng
Mon Feb 26 21:21:02 CST 2007
Thanks to Anthony and ekkehard.
For ekkehard solution, I have one question: how about if I have employees
with the same name (but different [EmployeeNo], which is unique)? Is it able
to match the correct record after split and sort in the 1-dimensional array?
(therefore I hesitate using the .sort() method in vb.net)
Again, thanks very much.
"ekkehard.horner" wrote:
> VnsChng wrote:
> > Hi all,
> > I have a recordset (result after executing a SQL query) containing names of
> > employees and their information. The [EmployeeName] column is in unicode.
> >
> > Due to limition of sorting unicode results in T-SQL, I would like to dump
> > the recordset in an array and sort them according to the [EmployeeName].
> > Sample data will be as follows:
> >
> > [EmployeeNo]......[EmployeeName]......[Dept]......[Title]
> > 193......Peter......Finance.....Manager
> > 632......Amy......Marketing......(blank)
> > 8765......Chris......Marketing......Clerk
> > 87......John......HR......(blank)
> >
> > I guess the data should be put in an array (arr(no.ofRecords,4))similar to
> > the one below:
> > arr(0,0) = 193
> > arr(0,1) = Peter
> > arr(0,2) = Finance
> > arr(0,3) = Manager
> > arr(1,0) = 632
> > ....
> >
> > I find it impossible to use the "sort" method in vb.net because it deals
> > with 1-dimensional array only. May I know how to sort the above data in vb
> > .net (2003) so I can have the [EmployeeName] in ascending order? I will need
> > to display the rest of the information as well.
> >
> > Thanks very much.
> Because I'd like some feedback/discussion on "Using .NET from VBScript" and
> the question *could* be understand as to touch this subject: This main code:
>
> Dim aEmplSQL : aEmplSQL = getEmplSQL()
> aEmplSQL = getEmplFAKE()
> display2D "aEmplSQL (from database or faked):", aEmplSQL
>
> Dim aEmplNET : aEmplNET = transNET( 1, aEmplSQL )
> WScript.Echo "transNETed:" + vbCrLf + vbTab + Join( aEmplNET, vbCrLf + vbTab )
> sortNET aEmplNET
> WScript.Echo "sorted (NAL):" + vbCrLf + vbTab + Join( aEmplNET, vbCrLf + vbTab )
>
> Dim aEmplSORT : aEmplSORT = orderNET( aEmplSQL, aEmplNET )
> display2D "aEmplSQL sorted:", aEmplSORT
>
> and its output for getEmplSQL():
>
> === SortEmplNet: sort employees via .NET =======================
> aEmplSQL (from database or faked):
> 172-32-1176 White 408 496-7223 Menlo Park
> 213-46-8915 Green 415 986-7020 Oakland
> 238-95-7766 Carson 415 548-7723 Berkeley
> 267-41-2394 O'Leary 408 286-2428 San Jose
> transNETed:
> White 0
> Green 1
> Carson 2
> O'Leary 3
> sorted (NAL):
> Carson 2
> Green 1
> O'Leary 3
> White 0
> aEmplSQL sorted:
> 238-95-7766 Carson 415 548-7723 Berkeley
> 213-46-8915 Green 415 986-7020 Oakland
> 267-41-2394 O'Leary 408 286-2428 San Jose
> 172-32-1176 White 408 496-7223 Menlo Park
> === SortEmplNet: 0 done (00:00:00) =============================
>
> resp. for getEmplFAKE():
>
> === SortEmplNet: sort employees via .NET =============
> aEmplSQL (from database or faked):
> 193 Peter Finance Manager
> 632 Amy Marketing
> 8765 Chris Marketing Clerk
> 87 John HR
> 1234 Aby Sorting Knecht
> transNETed:
> Peter 0
> Amy 1
> Chris 2
> John 3
> Aby 4
> sorted (NAL):
> Aby 4
> Amy 1
> Chris 2
> John 3
> Peter 0
> aEmplSQL sorted:
> 1234 Aby Sorting Knecht
> 632 Amy Marketing
> 8765 Chris Marketing Clerk
> 87 John HR
> 193 Peter Finance Manager
> === SortEmplNet: 0 done (00:00:00) ===================
>
> can be used to explore/experiment with:
>
> (a) get a col-row-orientated array of data from MS SQLServer (MSDE):
>
> Function getEmplSQL()
> Dim aRVal : aRVal = Array()
> Dim sCS : sCS = "Provider=SQLOLEDB;Data
> Source=<*SERVER*>;Trusted_Connection=Yes;Initial Catalog=pubs;"
> Dim oCN : Set oCN = CreateObject( "ADODB.Connection" )
> oCN.Open sCS
> Dim sSQL : sSQL = "SELECT * FROM authors"
> sSQL = "SELECT TOP 4 au_id, au_lname, phone, city FROM authors"
> Dim oRS : Set oRS = oCN.Execute( sSQL )
> aRVal = oRS.GetRows()
> getEmplSQL = aRVal
> End Function
>
> (with minimal fuss, if your computer is in a good mood). You need some
> version of MS SQLServer, the pubs database installed, and a trusted connection
> to you server (<*SERVER*>).
>
> (b) get some data (same format) to play with, if the conditions for (a) aren't met:
>
> Function getEmplFAKE()
> Dim aRVal( 3, 4 ) ' To emphasize: Cols, Rows!
> ' 193......Peter......Finance.....Manager
> ' 632......Amy........Marketing...(blank)
> '8765......Chris......Marketing...Clerk
> ' 87......John.......HR..........(blank)
> '1234......Aby........Sorting.....Knecht
> aRVal( 0, 0 ) = 193
> aRVal( 0, 1 ) = 632
> aRVal( 0, 2 ) = 8765
> aRVal( 0, 3 ) = 87
> aRVal( 0, 4 ) = 1234
> aRVal( 1, 0 ) = "Peter"
> aRVal( 1, 1 ) = "Amy"
> aRVal( 1, 2 ) = "Chris"
> aRVal( 1, 3 ) = "John"
> aRVal( 1, 4 ) = "Aby"
> aRVal( 2, 0 ) = "Finance"
> aRVal( 2, 1 ) = "Marketing"
> aRVal( 2, 2 ) = "Marketing"
> aRVal( 2, 3 ) = "HR"
> aRVal( 2, 4 ) = "Sorting"
> aRVal( 3, 0 ) = "Manager"
> aRVal( 3, 1 ) = "" ' not NULL - or more work ahead
> aRVal( 3, 2 ) = "Clerk"
> aRVal( 3, 3 ) = "" ' not NULL - or more work ahead
> aRVal( 3, 4 ) = "Knecht"
> getEmplFAKE = aRVal
> End Function
>
> (c) transform such an array (aSQL) into a one-dimensional array in such
> a way that the data neded for sorting *and* the index to access the
> orignal array in sorted order are kept. Putting keys and indexes in
> one string separatd by vbTab is certainly a very dirty hack, but it
> keeps it simple.
>
> Function transNET( nKeyIdx, aSQL )
> ReDim aRVal( UBound( aSQL, 2 ) )
> Dim nIdx
> For nIdx = 0 To UBound( aRVal )
> aRVal( nIdx ) = CStr( aSQL( 1, nIdx ) ) + vbTab + CStr( nIdx )
> Next
> transNET = aRVal
> End Function
>
> (d) sorting a one-dimensional array via .NET (System.Collections.ArrayList)
>
> Sub sortNET( a1D )
> Dim oNAL: Set oNAL = CreateObject( "System.Collections.ArrayList" )
> Dim nIdx
> oNAL.Capacity = UBound( a1D ) + 1
> For nIdx = 0 To UBound( a1D )
> oNAL.Add a1D( nIdx )
> Next
> oNAL.Sort
> For nIdx = 0 To UBound( a1D )
> a1D( nIdx ) = oNAL( nIdx )
> Next
> End Sub
>
> (e) undoing the trick: creating a new array (aRVal) and filling its rows
> (i.e. cols) in the order of indices 'hidden' the the one-dimensional
> array (aEmplNET):
>
> Function orderNET( aEmplSQL, aEmplNET )
> ReDim aRVal( UBound( aEmplSQL, 1 ), UBound( aEmplSQL, 2 ) )
> Dim nIdx, nIdxRef, nCol
> For nIdx = 0 To UBound( aEmplNET )
> nIdxRef = CLng( Split( aEmplNET( nIdx ), vbTab )( 1 ) )
> For nCol = 0 To UBound( aEmplSQL, 1 )
> aRVal( nCol, nIdx ) = aEmplSQL( nCol, nIdxRef )
> Next
> Next
> orderNET = aRVal
> End Function
>
> (f) display a col-row-orientated array:
>
> Sub display2D( sTitle, a2D )
> WScript.Echo sTitle
> Dim nRow, nCol, sLine
> For nRow = 0 To UBound( a2D, 2 )
> sLine = ""
> For nCol = 0 To UBound( a2D, 1 )
> sLine = sLine + vbTab + CStr( a2D( nCol, nRow ) )
> Next
> WScript.Echo sLine
> Next
> End Sub
>