[RESOLVED] Loading a listview with 20 items takes 4 seconds here in vbnet, vb6 its instantaneous

[sdowney1]

Why so slow.
Takes ages to populate the listview in vbnet.

This is the code.
Runs with no error, no exceptions.

	Private Sub RestoreListBox()


		'do a count everytime
		'TableCount()

		'On Error GoTo errhandler

		Me.Cursor = Cursors.WaitCursor


		If tablenameX <> frmlogonTablename Or frmlistDeletiontest = True Then
			frmlistDeletiontest = False
			tablenameX = frmlogonTablename
			StartX = 1
			PreserveX = 1
			'change this to show max in the list
			'numbertoX = 20 'Combo1.Text
			LastX = numbertoX
		Else
			'startX = PreserveX
		End If

		'preserve original start position
		PreserveX = StartX

		If LastX > frmlogonCountRecord Then LastX = frmlogonCountRecord

		If StartX > frmlogonCountRecord Then StartX = frmlogonCountRecord
		If StartX < 1 Then StartX = 1


		ListView1.Enabled = False

		With ListView1
			.Items.Clear()
			'UPGRADE_ISSUE: MSComctlLib.ListView property ListView1.Sorted was not upgraded. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="076C26E5-B7A9-4E77-B69C-B4448DF39E58"'
			'.Sorted = False
			.View = System.Windows.Forms.View.Details
			.Alignment = System.Windows.Forms.ListViewAlignment.Top
			.LabelEdit = False
		End With

		'try to load in first 25 items from the indexed table
		'open one by one and place in list
		frmlogonrsBooks.CursorLocation = ADODB.CursorLocationEnum.adUseClient
		frmlogonrsBooks.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
		rsbooks2.CursorLocation = ADODB.CursorLocationEnum.adUseClient
		rsbooks2.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
		'okay, it has the password in the string before the open, after the open, the password is removed.
		frmlogoncnConnector.ConnectionString = frmlogonConnectstring
		frmlogoncnConnector.Open()
		If frmlogonrsBooks.State = 1 Then frmlogonrsBooks.Close()

		If frmLists.ListOn Then
			Me.Text = "List Name - " & frmLists.txtListName.Text & " -- " & "Displaying -- " & StartX & " thru " & LastX & " of " & frmlogonCountRecord
		Else
			Me.Text = "Displaying -- " & StartX & " thru " & LastX & " of " & frmlogonCountRecord
		End If

		Do Until StartX > LastX Or StartX > frmlogonCountRecord

			Sqlquery = "Select * from " & frmlogonTablename & " where Id = '" & StartX & "'"
			frmlogonrsBooks.Open(Sqlquery, frmlogoncnConnector,  , ADODB.LockTypeEnum.adLockOptimistic)
			If frmlogonrsBooks.EOF = True Then
				'problem with indextable
				If frmlogonrsBooks.State = 1 Then frmlogonrsBooks.Close()
				If frmlogoncnConnector.State = 1 Then frmlogoncnConnector.Close()
				ListView1.Enabled = True
				Me.Cursor = Cursors.Default
				Exit Sub
			End If
			'only select fields you need to view
			If frmlogonWhichCallNumber = "LOC" Then
				Sqlquery = "Select titles, author, LOCNumber, localdata, Id from bookdata where Id = '" & frmlogonrsBooks.Fields("numid").Value & "'"
			Else
				Sqlquery = "Select titles, author, callnumberpre, callnumber, localdata, Id from bookdata where Id = '" & frmlogonrsBooks.Fields("numid").Value & "'"
			End If

			rsbooks2.Open(Sqlquery, frmlogoncnConnector,  , ADODB.LockTypeEnum.adLockOptimistic)

			Dim LItem As New ListViewItem()
			'deleted records
			If rsbooks2.RecordCount = 0 Then
				ListView1.Items.Add("Deleted Record", 0)
				ListView1.Items(StartX).SubItems.Add("") '1
				ListView1.Items(StartX).SubItems.Add("") '2
				ListView1.Items(StartX).SubItems.Add("") '3
				ListView1.Items(StartX).SubItems.Add("") '4
			Else
				'ListView1.Items.Add(rsbooks2.Fields("Titles").Value, 0)
				LItem.Text = rsbooks2.Fields("Titles").Value
				LItem.SubItems.Add(rsbooks2.Fields("Author").Value) '1
				If frmlogonWhichCallNumber = "LOC" Then
					LItem.SubItems.Add(IIf(IsDBNull(rsbooks2.Fields("LOCNumber").Value), "", rsbooks2.Fields("LOCNumber").Value)) '2
				Else
					tpre = IIf(IsDBNull(rsbooks2.Fields("callnumberpre").Value), "", rsbooks2.Fields("callnumberpre").Value) '2
					tcall = IIf(IsDBNull(rsbooks2.Fields("callnumber").Value), "", rsbooks2.Fields("callnumber").Value) '2
					LItem.SubItems.Add(Trim(tpre & " " & tcall)) '2
				End If

				LItem.SubItems.Add(rsbooks2.Fields("Localdata").Value) '3
				LItem.SubItems.Add(frmlogonrsBooks.Fields("ID").Value) '4, this number stored in index table is a sorted result
			End If
			LItem.ImageIndex = 0
			ListView1.Items.Add(LItem)
			StartX += 1 ' + 1

			frmlogonrsBooks.Close()
			rsbooks2.Close()

		Loop

		ListView1.Enabled = True
		frmlogoncnConnector.Close()
		Me.Cursor = Cursors.Default
		Exit Sub

errhandler:
		ListView1.Enabled = True
		frmlogoncnConnector.Close()
		Me.Cursor = Cursors.Default

		msg = "Error # " & Str(Err.Number) & " was generated by " & Err.Source & Chr(13) & Err.Description
		MsgBox(msg,  , "Error")

	End Sub

[sdowney1]

Here is the click forward event that runs the above sub

	Private Sub cmdForward_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdForward.Click
		'*********************************************
		'for demo only
		'If frmlogondemotest = True Then NagMe
		'**********************************************
		'go ahead 20
		If LastX = frmlogonCountRecord Then StartX = PreserveX
		LastX = LastX + numbertoX
		If LastX > frmlogonCountRecord Then LastX = frmlogonCountRecord
		TrackBar1.Value = StartX

		RestoreListBox()
		TrackBar1.Focus()
	End Sub

[sdowney1]

Picture of the form

Click the arrow, 4 seconds later the from displays the new set of results

In my vb6 version, it is absolutely instant results.

[sdowney1]

I put a break point at start of sub and at exit sub and that feels instantaneous.

But the form display is abysmally slow.

[PlausiblyDamp]

For starters that code's entire data access is ADODB rather than native .Net code - that alone is going to introduce a lot of interop and data type conversions. If you adding items to the list view like that you could always use https://learn.microsoft.com/en-us/do...ew.beginupdate and https://learn.microsoft.com/en-us/do...view.endupdate to improve the screen update performance.

It is also worth considering the version of .net you are using, moving to net 8 can bring a lot of performance improvements due to improvements to the framework and the JIT compiler. If you can run your app as 64bit, rather than 32 bit you might get some additional improvements in performance as well,

[Shaggy Hiker]

It shouldn't take any noticeable time at all. Twenty items is insignificant in any UI. However, figuring out exactly where the problem is could be a bit tricky. Both of the issues that the humid one suggested are reasonable. Suspending updates will keep the control from redrawing every single time, but for 20 items, I wouldn't expect it to make all that much difference. That's just not much. Also, I don't see anything particularly off about the logic, so the database tech would be my suspicion.

One thing you might try is commenting out all the lines where you populate listview items. In other words, do all the work except for adding items to the listview. How fast does that run? If it is fast doing everything OTHER than adding to the listview, then you know that it is something about the listview. If it is still slow, then the listview probably isn't the issue.

There are several other, small, things, such as using Or and And. Switching to OrElse and AndAlso will have no noticeable impact on performance, but it WILL be faster. All the other small things are similar to that. I assume that you are using VS2008 because of the converter message. If you are using the converter, it will miss things like the OrElse/AndAlso. It will also miss structured exception handling, making use of the Using construct, and so forth. None of that will impact the performance in any noticeable way, though. For example, changing the code to make use of Using will mostly just reduce the amount of code written. Whether it has ANY performance benefit, I have no idea, but it won't be much.

[sdowney1]

oh well, I had a whole post I made seems to be gone?

I just tried this

' Members are added one at a time, so call BeginUpdate to ensure 
    ' the list is painted only once, rather than as each list item is added.
    ListView1.BeginUpdate()

'my add code here


   'Call EndUpdate when you finish adding items to the ListView.
    ListView1.EndUpdate()

And no difference, just as slow.

What if I put the data into a string array from the recordset, and then load the array into the listview box?

I have an indexed table composed of a primary key and a number (2 columns) that points to the main table's row data of many columns some are column type TEXT.
I get the number from the little indexed table, which points to the main table row of the very large table of data.

That way very quickly (in vb6) to retrieve rows one by one in the order of the little indexed table.
Take the row data of the main table (selected some columns, not them all), and load them into the listview box, 20 at a time (default)

The number retrieved can go from 20 up to 200, user's choice of how many items to view in a list box, every time they click the arrow button.

So basically, get a listview showing data in the order of the little 2 column index of the large main table of data.

And all those little index tables I save, and reuse. They can come from people doing searches. Search already done, never needs to be done again, unless the DB changes, I flag the change and it will do a new search then and delete the index table for that search.

[sdowney1]

It shouldn't take any noticeable time at all. Twenty items is insignificant in any UI. However, figuring out exactly where the problem is could be a bit tricky. Both of the issues that the humid one suggested are reasonable. Suspending updates will keep the control from redrawing every single time, but for 20 items, I wouldn't expect it to make all that much difference. That's just not much. Also, I don't see anything particularly off about the logic, so the database tech would be my suspicion.

One thing you might try is commenting out all the lines where you populate listview items. In other words, do all the work except for adding items to the listview. How fast does that run? If it is fast doing everything OTHER than adding to the listview, then you know that it is something about the listview. If it is still slow, then the listview probably isn't the issue.

There are several other, small, things, such as using Or and And. Switching to OrElse and AndAlso will have no noticeable impact on performance, but it WILL be faster. All the other small things are similar to that. I assume that you are using VS2008 because of the converter message. If you are using the converter, it will miss things like the OrElse/AndAlso. It will also miss structured exception handling, making use of the Using construct, and so forth. None of that will impact the performance in any noticeable way, though. For example, changing the code to make use of Using will mostly just reduce the amount of code written. Whether it has ANY performance benefit, I have no idea, but it won't be much.

Simply, the vb6 project I converted in vs2008.
Then I loaded project into vs2022 and it did a one way conversion.
I assume due to .net version for vs2022 being 4.72, higher than 3.5 in vs2008

I moved away from using vs2008 as I had found a serious issue with storing certain ANSI chars into the windows registry, that it truncated strings retuned from gettsetting.
Strings I had been storing and retrieving fine all the way back from win98 OS and early versions of VB, but vs2008 could not do it.

And I found vs2022 did not have that issue.

[OptionBase1]

We don't know the specifics of the connection string (local machine, local lan, across the internet, etc). I would do some testing with breakpoints, etc. to see if the lag takes place when the initial connection is being made to the database.

[ChrisE]

@sdoweny1
here a sample with the DataReader, the transaction from VB6 to .Net is not that complicated

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim sDB As String = "E:\Adressen.mdb"
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & sDB & ";"

        'or the ACE  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=xyz.accdb


        Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
        Cn.Open()

        Dim sSQL As String = "Select ProductName, UnitPrice From Products "

        Dim sWhere As String = Nothing

        If TextBox1.Text <> Nothing Then
            sWhere &= "And (ProductName Like '" & TextBox1.Text & "%') "
        End If
        'add more search Params
        'If TextBox2.Text <> Nothing Then
        '    sWhere &= "And (Fieldname Like '" & TextBox2.Text & "%') "
        'End If
        If sWhere <> Nothing Then
            sWhere = "Where " & sWhere.Substring(4)
        End If
        sSQL &= sWhere & "Order by ProductName"

        'MsgBox(sSQL) '<--take a look at your Sql
        Dim Cmd As New OleDb.OleDbCommand(sSQL, Cn)
        Dim Dr As OleDb.OleDbDataReader
        Dr = Cmd.ExecuteReader

        With ListView1
            If .Columns.Count = 0 Then
                .View = View.Details
                .Columns.Add("ProductName", 150, HorizontalAlignment.Left)
                .Columns.Add("UnitPrice", 150, HorizontalAlignment.Right)
                .FullRowSelect = True
                .ShowItemToolTips = False
            End If
            .Items.Clear()
            .BeginUpdate()

            Do While Dr.Read
                Dim Li As New ListViewItem
                Li.UseItemStyleForSubItems = False
                Li.Text = Dr("Productname").ToString
                Li.SubItems.Add(String.Format("{0:c}", Dr("UnitPrice")))
                '...
                .Items.Add(Li)
            Loop
            .EndUpdate()
        End With
        Dr.Close()
    End Sub

[jmcilhinney]

Whenever ading multiple items to a WinForms control, you should pretty much NEVER call Add in a loop. ALWAYS get all the items first, then call AddRange once. If you're adding items to a ListView by calling Add in a loop, you can add the items to a List(Of ListViewItem) in the loop instead, then call ToArray on that at the end and pass the result to a single call to AddRange.

[sdowney1]

I turned on debugging and commented out all listview items, just let it Do Loop thru opening and closing recordsets.
And that is where the slowdown is, it cannot open and close recordsets with any kind of speed, it is very very slow to process these openings even just pulling 1 row of data out from the DB by primary key.

I put in a debug.print to watch 'startx' count up and totally matches that 4 seconds of time everytime I go to the next 20 items to display in the list.

I am surprised about that extremely slow ADODB reaction in a loop, which does not happen in vb6.
It is not a slowdown in adding to the listview box.

Unless anyone knows how to speed this up, it's going to need to be rewritten some other way to pull the data and not open and close recordsets in a loop.

I also wonder if MS deliberately slowed down ADODB to discourage it's use.
I see also MS is calling ADO.NET legacy now and want you to use entity framework core.

	Do Until StartX > LastX Or StartX > frmlogonCountRecord

		Sqlquery = "Select * from " & frmlogonTablename & " where Id = '" & StartX & "'"
		frmlogonrsBooks.Open(Sqlquery, frmlogoncnConnector,  , ADODB.LockTypeEnum.adLockOptimistic)

		If frmlogonrsBooks.EOF = True Then
			'problem with indextable
			If frmlogonrsBooks.State = 1 Then frmlogonrsBooks.Close()
			If frmlogoncnConnector.State = 1 Then frmlogoncnConnector.Close()
			ListView1.Enabled = True
			Me.Cursor = Cursors.Default
			Exit Sub
		End If

		'only select fields you need to view
		If frmlogonWhichCallNumber = "LOC" Then
			Sqlquery = "Select titles, author, LOCNumber, localdata, Id from bookdata where Id = '" & frmlogonrsBooks.Fields("numid").Value & "'"
		Else
			Sqlquery = "Select titles, author, callnumberpre, callnumber, localdata, Id from bookdata where Id = '" & frmlogonrsBooks.Fields("numid").Value & "'"
		End If

		rsbooks2.Open(Sqlquery, frmlogoncnConnector,  , ADODB.LockTypeEnum.adLockOptimistic)

[jmcilhinney]

I see also MS is calling ADO.NET legacy now and want you to use entity framework core.

I suspect that what you read was referring to typed DataSets specifically, rather than ADO.NET in general. EF uses ADO.NET under the hood, as does pretty much every .NET ORM. Dapper is probably a thinner layer over ADO.NET than most other options. ADO.NET is not going anywhere but there are better ways to do data access than using it directly yourself for all but very simple projects.

[sdowney1]

I suspect that what you read was referring to typed DataSets specifically, rather than ADO.NET in general. EF uses ADO.NET under the hood, as does pretty much every .NET ORM. Dapper is probably a thinner layer over ADO.NET than most other options. ADO.NET is not going anywhere but there are better ways to do data access than using it directly yourself for all but very simple projects.

I think ado.execute command is fast, have not noticed a slowdown, but then was not looking for one.
I have a bunch of those in my code. And they can be used to update data.
I found recordsets useful for looping in vb6.


Since this form is simply displaying data from a table, how would the form be written to use ADO.net?
Are these some simple examples I can look at?

[OptionBase1]

Database chapters from Beginning Visual Basic 2010 book:

https://archive.org/details/Microsof...p?view=theater

Download all the source code for the book here:
http://media.wiley.com/product_ancil...28.AllCode.zip

[sdowney1]

For starters that code's entire data access is ADODB rather than native .Net code - that alone is going to introduce a lot of interop and data type conversions. If you adding items to the list view like that you could always use https://learn.microsoft.com/en-us/do...ew.beginupdate and https://learn.microsoft.com/en-us/do...view.endupdate to improve the screen update performance.

It is also worth considering the version of .net you are using, moving to net 8 can bring a lot of performance improvements due to improvements to the framework and the JIT compiler. If you can run your app as 64bit, rather than 32 bit you might get some additional improvements in performance as well,

Converting to NET8 breaks a bunch of things such as getsetting from the registry.
Also does it if selecting version 6

The program only runs on version 4.7.2

Why is that?
Why does it turn the value of 'My.Application.Info.Title' to an empty string?
Do those versions of net 5 and above drop all those things that now show exceptions when running?
Program shows no errors before running targetting ver 6, or ver 8



System.ArgumentException
HResult=0x80070057
Message=Argument 'Path' is Nothing or empty.
Source=Microsoft.VisualBasic.Core
StackTrace:
at Microsoft.VisualBasic.Interaction.CheckPathComponent(String s)
at Microsoft.VisualBasic.Interaction.GetSetting(String AppName, String Section, String Key, String Default)
at WindowsApplication1.frmLogon.Form_Initialize_Renamed() in J:\vbnetJess1\fonttest-marcbreaker test\FontTestVS2008 - testver8net\FontTestVS2008\frmLogon.vb:line 98
at WindowsApplication1.frmLogon..ctor() in J:\vbnetJess1\fonttest-marcbreaker test\FontTestVS2008 - testver8net\FontTestVS2008\frmLogon.designer.vb:line 7
at System.RuntimeType.CreateInstanceOfT()

[wes4dbt]

I found recordsets useful for looping in vb6.

I've read most of your posts and you seem determined the create a VB6 app using VB .Net. Lots of people have told you that's a mistake but you've kept on this approach. And you keep running into problems. You think you'll save time this way but eventually you'll end up writing a true .Net app. Your not the first to take this approach, I did the same thing. The sooner you bit the bullet and learn .Net the better.

[sdowney1]

I've read most of your posts and you seem determined the create a VB6 app using VB .Net. Lots of people have told you that's a mistake but you've kept on this approach. And you keep running into problems. You think you'll save time this way but eventually you'll end up writing a true .Net app. Your not the first to take this approach, I did the same thing. The sooner you bit the bullet and learn .Net the better.

Until I ran into this really slow issue, things were working acceptably.

Any ideas on my prior post and net8? What is the proper Net8 format for retrieving strings from the registry?

[PlausiblyDamp]

Why does it turn the value of 'My.Application.Info.Title' to an empty string?

No idea, it isn't an empty string when I try it here. What is the assembly name configured as in your Application's properties (Properties, Application, General)

Converting to NET8 breaks a bunch of things such as getsetting from the registry.

How does GetSetting break? Have you tried reading from the registry directly using something like https://learn.microsoft.com/en-us/do...o-the-registry or https://learn.microsoft.com/en-us/do...in32-namespace - GetSetting is really a hold over from VB6 and not ideal in a net application.

I would however agree with wes4dbt - writing a .net app and using so much legacy VB6 functionality is only going to cause problems.

[sdowney1]

No idea, it isn't an empty string when I try it here. What is the assembly name configured as in your Application's properties (Properties, Application, General)



How does GetSetting break? Have you tried reading from the registry directly using something like https://learn.microsoft.com/en-us/do...o-the-registry or https://learn.microsoft.com/en-us/do...in32-namespace - GetSetting is really a hold over from VB6 and not ideal in a net application.

I would however agree with wes4dbt - writing a .net app and using so much legacy VB6 functionality is only going to cause problems.

$(MSBuildProjectName)

What is broken is the formatting of getsetting I suppose, after moving it to a Net8 target.
I made a copy and did that, still have the original. When I ? the myapp var, it is just an empty string.

Regarding database .NET, it is simply a pattern I need to follow to do a conversion, up till today I was thinking might get by without that. But also these forms can be modded individually to get a form working by itself, no need to try to mod whole program at once, (just overwhelming). I can't learn that way, everything has to have a small beginning and you add on code later to mod other forms. I suppose other people have done this, so it can't be impossibly hard.

[sdowney1]

See, nothing there anymore when running as .net 8

When running in ver4.7.2, that says

?My.Application.Info.Title
"WindowsApplication1"

[sdowney1]

Is this dot NET Mysql, the way it is done?
I thought had to make entire database copy in memory?
Or does it?

https://mysqlconnector.net/tutorials...er%20class.%20

[PlausiblyDamp]

See, nothing there anymore when running as .net 8

When running in ver4.7.2, that says

?My.Application.Info.Title
"WindowsApplication1"

I have no idea why your application isn't returning the title, I can only assume it is something that has broken during the various upgrades - if you can post the contents of the net 8 .vbproj file there might be somethin in there that could explain this.

[dday9]

I've read most of your posts and you seem determined the create a VB6 app using VB .Net. Lots of people have told you that's a mistake but you've kept on this approach. And you keep running into problems. You think you'll save time this way but eventually you'll end up writing a true .Net app. Your not the first to take this approach, I did the same thing. The sooner you bit the bullet and learn .Net the better.

To be honest, this is why I have disengaged. Any suggestion I gave related to the .NET way of doing things was completely ignored. At some point it became obvious that my advice was not going to be used so I gave up.

[PlausiblyDamp]

Is this dot NET Mysql, the way it is done?
I thought had to make entire database copy in memory?
Or does it?

https://mysqlconnector.net/tutorials...er%20class.%20

Not sure what the question is there...

The link you provided is to a library that allows a .net application to connect to a MySQL database. So if you are using MySQL then that is probably the best way to do things, if you are using a different database then you would need a different connector.

Not sure what you mean about making an entire database copy in memory though.

[Shaggy Hiker]

Just to confuse things further, I would say you might as well stay with Framework 4.7.2 unless your goal is to move off of Windows eventually. The framework is built into the OS, and will likely be around for longer than us. Sure, .NET 6 through N have been adding some compiler advantages, but these will make no appreciable difference to most programs. The languages are very similar between framework and .NET Core (or whatever generic name we can come up with for .NET 6 on), but as you have seen, there are some slight changes.

What concerns me about the .NET Core approach is that I'm not convinced that it is stable, in that I fear that MS will make changes that will require re-compiling into newer and newer numbers. There was a bit of this with framework, as you had to move from version to version if you wanted to take advantage of new features. However, with Framework becoming essentially stationary, that race isn't happening so much any more, so Framework might be the more stable option for lots of people.

I'm not really set in that view, though, so I'd be interested in other opinions. I just don't know where the future is going.

[jmcilhinney]

Since this form is simply displaying data from a table, how would the form be written to use ADO.net?
Are these some simple examples I can look at?

I suggest that you follow the Database FAQ link in my signature below. One of the links there is my own CodeBank thread that provides simple ADO.NET examples for the most common scenarios.

[ChrisE]

Whenever ading multiple items to a WinForms control, you should pretty much NEVER call Add in a loop. ALWAYS get all the items first, then call AddRange once. If you're adding items to a ListView by calling Add in a loop, you can add the items to a List(Of ListViewItem) in the loop instead, then call ToArray on that at the end and pass the result to a single call to AddRange.

I wont bother with a second sample and show "AddRange" since the OP wants to use a MashUp of VB6 and .Net

[sdowney1]

Not sure what the question is there...

The link you provided is to a library that allows a .net application to connect to a MySQL database. So if you are using MySQL then that is probably the best way to do things, if you are using a different database then you would need a different connector.

Not sure what you mean about making an entire database copy in memory though.

Read that from a poster few months ago, as in don't deal with database except to get all rows and work only with disconnected datasets.

[sdowney1]

I suggest that you follow the Database FAQ link in my signature below. One of the links there is my own CodeBank thread that provides simple ADO.NET examples for the most common scenarios.

I gleaned some dotNet? coding examples and got this basic functionality working here on my Mysql community 8 DB server

MSLearn line here though errors from this as it does not know what a SqlDataReader is.

'Dim reader As SqlDataReader = cmd.ExecuteReader()

https://learn.microsoft.com/en-us/do...g-a-datareader

So I did it another way as this worked, why is that?

Using RDR = cmd.ExecuteReader()

	Dim myConnectionString As String = "DATABASE=front;SERVER=localhost;UID=root;PASSWORD=root;PORT=3307;"

	'"server=127.0.0.1;uid=root;" pwd=root;database=front"
	
       Dim conn As New MySqlConnection(myConnectionString)
	Try
		Console.WriteLine("Connecting to MySQL...")
		conn.Open()
	Catch ex As Exception
		Console.WriteLine(ex.ToString())
	End Try

	Dim cmd As New MySqlCommand
	
	cmd.Connection = conn
	cmd.CommandText = "SELECT *  FROM bookdata" 


	'Dim reader As SqlDataReader = cmd.ExecuteReader()
	 
        'Titles is a column in the table
	Dim textnametext As String = ""
	Using RDR = cmd.ExecuteReader()
		If RDR.HasRows Then
			Do While RDR.Read
				textnametext = RDR.Item("Titles").ToString()
			Loop
		End If
	End Using

[Shaggy Hiker]

Yes, that would be one way to look at it. Working with memory is VASTLY faster than keeping a connection open to the database, no matter which language. In VB6, it was common to keep a connection open and get only those bits that you wanted, which was pretty efficient, but had issues in multi-user scenarios. The approach taken in .NET was that you get in, get your data into a datatable (or a few), then get out. You work with the data in the datatables, which is much faster, then push any changes back to the DB at the end. This scales better in multi-user scenarios.

However, it wouldn't be all that efficient if you were bringing the entire DB into memory every time. The key is to get just what you need, no more, and no less. Opening and closing the DB is virtually free, thanks to connection pooling, but figuring out what the most efficient amount of data to get, is more complicated.

Is it read only? Then a Scalar is the fastest so long as you want only one value. If you want multiple values, then a DataReader, which is similar in nature to the recordsets you are used to (except that they are read only).

If it is not read only, then a datatable, which can be in a dataset (a collection of datatables). The datatable can be populated from a DataReader or a DataAdapter. The DataAdapter can assist with getting changes back into the database, though it doesn't have to, so it isn't the case that you choose a DataReader for read only data and a DataAdapter for read/write. You can use either to fill a datatable, and might want to. It's just that, if you will be pushing changes back to the database, there can be some organizational advantages to filling the datatable from a DataAdapter.

Still, you get what you need, which is almost never the whole database. Might be a single value, a set of records from a table, or a set of records composed of data from a variety of tables.

[Shaggy Hiker]

You don't need to check HasRows in this code:

Using RDR = cmd.ExecuteReader()
		If RDR.HasRows Then
			Do While RDR.Read
				textnametext = RDR.Item("Titles").ToString()
			Loop
		End If
	End Using

RDR.Read will return True if there is a record to read and False if there is not. Therefore, the HasRows is redundant.

[sdowney1]

Top of frmLists has these imports, and these 2 vs2022 says are not needed?

Well, it works anyway.

Goal here is to see if I can get frmList's listview to populate fast using .net style DB access commands.
If It does, then will consider migrating over other forms.
I also noticed .getrows in ADODB for vs2022 is also very very slow which means changing that also in other subs.


BC40056: Namespace or type specified in the Imports '<qualifiedelementname>' doesn't contain any public member or cannot be found


Imports MySql.Data
Imports MySql.Data.MySqlClient

Imports System.Data

Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports System.Diagnostics

I also added here for Mysql these 2 and had added what I showed on an ealier post for Mysql connector but it may only be for C#

[sdowney1]

You don't need to check HasRows in this code:

Using RDR = cmd.ExecuteReader()
		If RDR.HasRows Then
			Do While RDR.Read
				textnametext = RDR.Item("Titles").ToString()
			Loop
		End If
	End Using

RDR.Read will return True if there is a record to read and False if there is not. Therefore, the HasRows is redundant.

ok, makes sense, I have been copying code from web examples to see how this works.

[sdowney1]

You don't need to check HasRows in this code:

Using RDR = cmd.ExecuteReader()
		If RDR.HasRows Then
			Do While RDR.Read
				textnametext = RDR.Item("Titles").ToString()
			Loop
		End If
	End Using

RDR.Read will return True if there is a record to read and False if there is not. Therefore, the HasRows is redundant.

How to detect rdr.read has returned true or false?

[PlausiblyDamp]

How to detect rdr.read has returned true or false?

That is what

Do While RDR.Read

is doing.

[sdowney1]

That is what

Do While RDR.Read

is doing.

Ok, I understand.

[sdowney1]

Nice to see that mysql.net connector also works for MariaDB.
https://mariadb.com/kb/en/net-connector/

I like MariaDB as right from the download for 11.3 GA version it is a lot faster than the free Mysql 8 community server.
Significantly faster. When I found that out, I made my VB6 program work with that.
I had programed for 3 DB, MSSQL, MySQL and MariaDB. And at one time SQLLite which at the time back then was super fast, but unstable, sometimes it screwed up the DB.

I only ever had one user using MSSQL. Everyone else was on earlier than ver 8 of MySQL. It seems like after Oracle bought MySQL, the free MySQL Server super speed it used to have was gone.

The free Learning version of MSSQL I have, is also really slow, much slower the MySQL community 8. You really notice the speed issue when doing like 10000 row updates or inserts. MSSQL learning is 10 times more time than MAriaDB

[sdowney1]

Ok, Instantaneous filling of listview box using dot net coding

However, the code is not optimized, has extra lines, can be improved

Like each reader will only ever have one row returned and it's in a do loop

How to do it without that loop, I read something somewhere...

And could assign to Listview columns maybe without first assigning it to some string vars, but see I am feeling out the coding.
How it flows as it executes.

		Dim cmd1 As New MySqlCommand

		Dim LnumID As String, Ltitles = "", Lauthor As String = "", LLOCNumber As String = "", Lcallnumberpre As String = "", Lcallnumber As String = "", Llocaldata As String = "", LId As String = ""

		cmd1.Connection = conn
		Do Until StartX > LastX Or StartX > frmlogonCountRecord
			Dim LItem As New ListViewItem()

			cmd1.CommandText = "Select NumID from " & frmlogonTablename & " where Id = '" & StartX & "'"
                       'will only ever be one row returned, so why loop
			Using RDR = cmd1.ExecuteReader()
				Do While RDR.Read
					LnumID = RDR.Item("NumId").ToString()
				Loop
			End Using

			'only select fields you need to view
			cmd1.CommandText = "Select titles, author, LOCNumber, callnumberpre, callnumber, localdata, Id from bookdata where Id = '" & LnumID & "'"

			

                      'will only ever be one row returned, so why loop
                       Using RDR = cmd1.ExecuteReader()
				Do While RDR.Read
					Ltitles = RDR.Item("Titles").ToString()
					Lauthor = RDR.Item("author").ToString()
					LLOCNumber = RDR.Item("LOCNumber").ToString()
					Lcallnumberpre = RDR.Item("callnumberpre").ToString()
					Lcallnumber = RDR.Item("callnumber").ToString()
					Llocaldata = RDR.Item("localdata").ToString()
					LId = RDR.Item("Id").ToString()
				Loop
			End Using

			LItem.Text = Ltitles
			LItem.SubItems.Add(Lauthor) '1
			If frmlogonWhichCallNumber = "LOC" Then
				LItem.SubItems.Add(LLOCNumber) '2
			Else
				tpre = Lcallnumberpre '2
				tcall = Lcallnumber '2
				LItem.SubItems.Add(Trim(tpre & " " & tcall)) '2
			End If

			LItem.SubItems.Add(Llocaldata) '3
			LItem.SubItems.Add(LId) '4, this number stored in index table is a sorted result

			StartX += 1
			LItem.ImageIndex = 0
			ListView1.Items.Add(LItem)
		Loop
		ListView1.EndUpdate()
		conn.Close()

		Exit Sub

[sdowney1]

I improved it, assigned Listview columns more directly
Well it works fast, likely just leave it as is for now.
It does not need the me.cursors, it is so fast.

Even if I set to display 200 into the list box for every next move, it is instantaneous, so very fast.

frmlogonTablename is an index. It is a search ordered by titles loading the results, that way it matches a user search on the DB, otherwise it would be all unordered lists of search results.
Even just listing without a search, it is technically a search as I use that to let people browse the entire db rows of the main table, ordered by titles, or authors, etc...

So the little index table of ordered results points to the primary key of the main table holding everything related to the item.
It really needs that to keep things in human readable orders.

Here is the complete sub

	Private Sub RestoreListBox()

		Dim myConnectionString As String = "DATABASE=front;SERVER=localhost;UID=root;PASSWORD=root;PORT=3307;"

		Dim conn As New MySqlConnection(myConnectionString)
		Try
			Console.WriteLine("Connecting to MySQL...")
			conn.Open()

			'do a count everytime
			TableCount()

			Me.Cursor = Cursors.WaitCursor

			If tablenameX <> frmlogonTablename Or frmlistDeletiontest = True Then
				frmlistDeletiontest = False
				tablenameX = frmlogonTablename
				StartX = 1
				PreserveX = 1
				'change this to show max in the list
				'numbertoX = 20 'Combo1.Text
				LastX = numbertoX
			Else
				'startX = PreserveX
			End If

			'preserve original start position
			PreserveX = StartX

			If LastX > frmlogonCountRecord Then LastX = frmlogonCountRecord

			If StartX > frmlogonCountRecord Then StartX = frmlogonCountRecord
			If StartX < 1 Then StartX = 1

			With ListView1
				.Items.Clear()
				.View = System.Windows.Forms.View.Details
				.Alignment = System.Windows.Forms.ListViewAlignment.Top
				.LabelEdit = False
			End With

			ListView1.BeginUpdate()

			If frmLists.ListOn Then
				Me.Text = "List Name - " & frmLists.txtListName.Text & " -- " & "Displaying -- " & StartX & " thru " & LastX & " of " & frmlogonCountRecord
			Else
				Me.Text = "Displaying -- " & StartX & " thru " & LastX & " of " & frmlogonCountRecord
			End If

			Dim cmd1 As New MySqlCommand

			Dim LnumID As String, Lcallnumberpre As String = "", Lcallnumber As String = ""

			cmd1.Connection = conn
			Do Until StartX > LastX Or StartX > frmlogonCountRecord
				Dim LItem As New ListViewItem()

				cmd1.CommandText = "Select NumID from " & frmlogonTablename & " where Id = '" & StartX & "'"

				Using RDR = cmd1.ExecuteReader()
					Do While RDR.Read
						LnumID = RDR.Item("NumId").ToString()
					Loop
				End Using

				'only select fields you need to view
				cmd1.CommandText = "Select titles, author, LOCNumber, callnumberpre, callnumber, localdata, Id from bookdata where Id = '" & LnumID & "'"

				Using RDR = cmd1.ExecuteReader()
					Do While RDR.Read
						LItem.Text = RDR.Item("Titles").ToString() '0
						LItem.SubItems.Add(RDR.Item("author").ToString()) '1

						If frmlogonWhichCallNumber = "LOC" Then
							LItem.SubItems.Add(RDR.Item("LOCNumber").ToString()) '2

						Else
							Lcallnumberpre = RDR.Item("callnumberpre").ToString() '2
							Lcallnumber = RDR.Item("callnumber").ToString() '2
							LItem.SubItems.Add(Trim(Lcallnumberpre & " " & Lcallnumber)) '2
						End If


						LItem.SubItems.Add(RDR.Item("localdata").ToString()) '3
						LItem.SubItems.Add(RDR.Item("Id").ToString()) '4
					Loop
				End Using

				StartX += 1
				LItem.ImageIndex = 0
				ListView1.Items.Add(LItem)
			Loop
			ListView1.EndUpdate()
			conn.Close()
			Me.Cursor = Cursors.Default
			Exit Sub

		Catch ex As Exception
			If conn.State = 1 Then conn.Close()
			Console.WriteLine(ex.ToString())
			msg = ex.ToString()
			MsgBox(msg,  , "Error")

		End Try

		Me.Cursor = Cursors.Default

	End Sub