crafuse
asked on
Make this routine more efficient
Experts,
I have the following code that looks at a CSV string of card IDs. It then populates a listbox with the CardName and the quantity of each card (that is, the number of times its ID appears in the string). The listbox items appear like this:
cardone 10
cardforty 3
cardtwentynine 6
cardsix 1
cardonehundred 9
etcetera x
The actual string can look like this: "1,1,2,2,2,2,3,3,4,1,1,8,8 ,3,3,3,4,4 ,4,4,5,6,7 ,7,7,7,7,7 ,7,8"
I would like this routine improved so that I'm not constantly opening & closing a new db connection for every pass. Is it possible?
Code:
Dim MyArray As New ArrayList()
Dim values() As String = strLibraryList.ToString.Sp lit(",")
Dim int As Integer = 1
Dim total As Integer
MyArray.AddRange(values)
MyArray.Sort()
Dim b As Integer
For b = 0 To MyArray.Count - 1
If MyArray(b) <> int AndAlso total > 0 Then
Dim CardSQL As String
Dim strCardName As String
CardSQL = "select name from merchant where cardno = " & int & ""
Dim con1 As String = strMySQLConnectString
Dim strConnect1 As String = con1
Dim conReader1 As New MySqlConnection(strConnect 1)
Dim conString1 As New MySqlCommand(CardSQL, conReader1)
conReader1.Open()
Dim drReader1 As MySqlDataReader
drReader1 = conString1.ExecuteReader(C ommandBeha vior.Close Connection )
drReader1.Read()
strCardName = drReader1.Item("name").ToS tring
lstLibraryCards.Items.Add( strCardNam e.PadRight (32, " ").Substring(0, 16) & Chr(9) & total)
conReader1.Close()
drReader1.Close()
total = 1
int = MyArray(b)
Else
total += 1
End If
Next b
Thanks!
crafuse
I have the following code that looks at a CSV string of card IDs. It then populates a listbox with the CardName and the quantity of each card (that is, the number of times its ID appears in the string). The listbox items appear like this:
cardone 10
cardforty 3
cardtwentynine 6
cardsix 1
cardonehundred 9
etcetera x
The actual string can look like this: "1,1,2,2,2,2,3,3,4,1,1,8,8
I would like this routine improved so that I'm not constantly opening & closing a new db connection for every pass. Is it possible?
Code:
Dim MyArray As New ArrayList()
Dim values() As String = strLibraryList.ToString.Sp
Dim int As Integer = 1
Dim total As Integer
MyArray.AddRange(values)
MyArray.Sort()
Dim b As Integer
For b = 0 To MyArray.Count - 1
If MyArray(b) <> int AndAlso total > 0 Then
Dim CardSQL As String
Dim strCardName As String
CardSQL = "select name from merchant where cardno = " & int & ""
Dim con1 As String = strMySQLConnectString
Dim strConnect1 As String = con1
Dim conReader1 As New MySqlConnection(strConnect
Dim conString1 As New MySqlCommand(CardSQL, conReader1)
conReader1.Open()
Dim drReader1 As MySqlDataReader
drReader1 = conString1.ExecuteReader(C
drReader1.Read()
strCardName = drReader1.Item("name").ToS
lstLibraryCards.Items.Add(
conReader1.Close()
drReader1.Close()
total = 1
int = MyArray(b)
Else
total += 1
End If
Next b
Thanks!
crafuse
ASKER
Roland,
That looks cool. Never done this kind of stuff before. Could you do me a favor, and include all of the array stuff to, all of it, say, in a button click event (the main routine, that is). i'll start to research the class and global dictionary stuff -> that's kind of Greek to me, not sure where all that stuff would go...
Thanks.
That looks cool. Never done this kind of stuff before. Could you do me a favor, and include all of the array stuff to, all of it, say, in a button click event (the main routine, that is). i'll start to research the class and global dictionary stuff -> that's kind of Greek to me, not sure where all that stuff would go...
Thanks.
Do you call this routine many times in only one execution of your program? I mean... do you populate your listbox only once or is it possible that, once populated, you call the routine again and have to sum the new values for each card to the old ones?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
apparently, this question is going nowhere. please close.
1. Create a new class to identify a card:
Public Class Card
Public ID As Integer = 0
Public Name As String = String.Empty
Public Counter As Integer = 0
End Class
2. Create a global dictionary to store cards. I don't know if you need this to be into a public or private scope, so I guess is public.
Public Cards As Dictionary(Of Integer, Card) = New Dictionary(Of Integer, Card)
3. In your code, each time you need to store a new value for the counter of a card, check if your global dictionary contains the card; if not, then go to the database to get the card's name (but this is only done once for each card).
For b = 0 To MyArray.Count - 1
If MyArray(b) <> int AndAlso total > 0 Then
If Cards.ContainsKey(int) Then
'Simply add the total
Cards(int).Counter += total
Else
Dim nCard As Card = New Card
With nCard
.ID = int
.Counter = total
.Name = ... 'use your db access code to get the name
End With
Cards.Add(nCard.ID, nCard)
End If
Else
total += 1
End If
Next b
I hope you understand the logic.
Hope that helps.