Solved

Make this routine more efficient

Posted on 2011-09-05
5
173 Views
Last Modified: 2012-05-12
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.Split(",")
            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(strConnect1)
                    Dim conString1 As New MySqlCommand(CardSQL, conReader1)
                    conReader1.Open()

                    Dim drReader1 As MySqlDataReader
                    drReader1 = conString1.ExecuteReader(CommandBehavior.CloseConnection)

                    drReader1.Read()
                    strCardName = drReader1.Item("name").ToString

                    lstLibraryCards.Items.Add(strCardName.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
0
Comment
Question by:crafuse
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 36486814
What I would do is the following:

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.
0
 

Author Comment

by:crafuse
ID: 36486992
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.
0
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 36487100
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?
0
 

Accepted Solution

by:
crafuse earned 0 total points
ID: 36487558

yer routine doesn't work. it's only populating the first card & quantity. seems the int isn't being incremented anywhere. belwo is what i have of my own code working, but it's a real mash. for some reason i can't get it to display the last item in the array, so have to tack on that last little if, pretty ugly. can you help at all with that?

and i'm sure it will end up being called in a couple of places, but the app's only half done, and not polished, and still full of holes, so i can't really say at this point...


Dim MyArray As New ArrayList()
        Dim values() As String = strLibraryList.ToString.Split(",")
        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(strConnect1)
                Dim conString1 As New MySqlCommand(CardSQL, conReader1)
                conReader1.Open()

                Dim drReader1 As MySqlDataReader
                drReader1 = conString1.ExecuteReader(CommandBehavior.CloseConnection)

                drReader1.Read()
                strCardName = RTrim(LTrim(drReader1.Item("name").ToString))

                If strCardName <> "" Then
                    lstLibraryCards.Items.Add(strCardName.PadRight(32, " ").Substring(0, 16) & Chr(9) & total)
                Else
                    lstLibraryCards.Items.Add("No Cardname in DB" & Chr(9) & total)
                End If

                conReader1.Close()
                drReader1.Close()

                total = 1
                int = MyArray(b)
            Else
                total += 1
            End If
        Next b
        
        'here we capture the last item that is not being added to the listbox
        If b = MyArray.Count 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(strConnect1)
            Dim conString1 As New MySqlCommand(CardSQL, conReader1)
            conReader1.Open()

            Dim drReader1 As MySqlDataReader
            drReader1 = conString1.ExecuteReader(CommandBehavior.CloseConnection)

            drReader1.Read()
            strCardName = RTrim(LTrim(drReader1.Item("name").ToString))

            If strCardName <> "" Then
                lstLibraryCards.Items.Add(strCardName.PadRight(32, " ").Substring(0, 16) & Chr(9) & total)
            Else
                lstLibraryCards.Items.Add("No Cardname in DB" & Chr(9) & total)
            End If
            conReader1.Close()
            drReader1.Close()
        End If

Open in new window

0
 

Author Closing Comment

by:crafuse
ID: 36547857
apparently, this question is going nowhere. please close.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now