[RESOLVED] vb.net improve programs

[eurostar_italia]

I,i have a little program that retrieves railway reservations for groups.
it downloads from a website getting a json.I store data in a matrix.
The object for the program is to know :
a) how many station are served from that train
b) how many people load and unload that train,selecting a particular station (ex rome,11 loaded,89 unloaded)
c) how many people load a specific car (ex car 5-10 passenger,car 7-11 passenger)
d) how many destinations and people selecting a particular station (ex from rome,11 passenger to florence,18 to milan,etc)
i have a matrix with 14 columns and setted with 5000 rows (i don't think the will be more).
i made a lot of inquiries with a for next to have all this informations,but some need a lot of time to get a response
my progam run in a company computers so i can't install or set anything,i can just place my exe on the computer.
in your opinion,wich would be the best way to improve my program ? datatable ? sql ?
thank for you cooperation
this is my code :
json download to the matrix (stands for bidimensional array)
i use newtonsoft json library

	Public matrice(5000, 15) As String
	Public matrice2(5000, 15) As String
	Public matrice3(5000, 15) As String
	Dim listadest As New List(Of Destinazioni)
	Dim listacar As New List(Of paxincarrozza)
	Dim listapax As ArrayList = New ArrayList
	
	Dim listapaxcognomi As ArrayList = New ArrayList

Dim ser As JObject = JObject.Parse(json)

			Dim data As List(Of JToken) = ser.Children().ToList



			If chiamata = 1 Then
				For Each item As JProperty In data
					item.CreateReader()

					For Each msg As JObject In item.Values

						matrice(contatore, 0) = msg("departureLocationName")
						matrice(contatore, 1) = msg("arrivalLocationName")
						matrice(contatore, 2) = msg("pnrCode")
						matrice(contatore, 3) = msg("serviceLevel")
						matrice(contatore, 4) = msg("lastName")
						matrice(contatore, 5) = msg("wagon")
						matrice(contatore, 6) = msg("seat")
						matrice(contatore, 7) = msg("firstName")
						matrice(contatore, 8) = msg("transportMeanName")
						matrice(contatore, 9) = msg("couponId")

						contatore = contatore + 1


					Next
				Next

				contatore = 0
				
				Do
					If ComboBox1.Items.IndexOf(matrice(contatore, 0)) = -1 Then
						ComboBox1.Items.Add(matrice(contatore, 0))
					End If
					If ComboBox1.Items.IndexOf(matrice(contatore, 1)) = -1 Then
						ComboBox1.Items.Add(matrice(contatore, 1))
					End If

					contatore = contatore + 1
				Loop Until matrice(contatore, 0) = Nothing

				Label1.Text = "TRENO  " & (matrice(0, 8))

then to know passenger destination or passenger load for single car :

		ListBox2.Items.Clear()

		Dim i1 As Integer

		Dim prima1 As Boolean = True
		Dim appoggio1 As paxincarrozza
		listacar.Clear()

		For i1 = 1 To 4999

			If ComboBox1.SelectedItem = (matrice(i1, 0)) Then
				If prima1 = True Then

					appoggio1.carrozza = (matrice(i1, 5))
					appoggio1.quantita = 0
					listacar.Add(appoggio1)


					prima1 = False
				End If
				Dim appo1 As New List(Of paxincarrozza)
				appo1.AddRange(listacar)
				Dim trovato As Boolean = False

				Dim a As Integer
				For Each element As paxincarrozza In appo1

					If element.carrozza = matrice(i1, 5) Then
						trovato = True
						a = listacar.IndexOf(element)
						appoggio1 = element
					End If

				Next

				If trovato = True Then
					appoggio1.quantita = appoggio1.quantita + 1
					listacar(a) = appoggio1
				Else

					appoggio1.carrozza = (matrice(i1, 5))
					appoggio1.quantita = 1
					listacar.Add(appoggio1)
				End If



			End If
		Next
		ListBox2.Items.Clear()

		For Each element As paxincarrozza In listacar
			ListBox2.Items.Add(element.carrozza & "   -   " & element.quantita)

		Next

the point is : a datatable with sql query would be better in your opinion ? or it just load memory without any speed increase ? i'm not sure about it.
we are speaking about 5000 rows nothing more...
thanks to all of you

[Peter Swinkels]

Hello eurostar_italia,

You need to rephrase your post, it says you have a program and you need it to do something?? If english isn't your first language you could try Google Translating your question, I guess.

EDIT:
I reread your post, are you looping through your data with a For/Next loop? Code?

yours,
Peter Swinkels

[.paul.]

I'd use LINQ... You might find it's significantly quicker... We need to see your code.

[Shaggy Hiker]

We'd need to see the code anyways. What's a matrix in this case? I would have expected a datatable, yet you then ask if a datatable would be an improvement. It may well be, if you aren't already using one, because there are much worse alternatives that you might be using. Since we don't know, then there isn't all that much we can suggest.

[eurostar_italia]

thanks for your suggestion,the first way to be helped is to explain the problem and show the code.
This is my first time in a so technical forum.thanks

[eurostar_italia]

We'd need to see the code anyways. What's a matrix in this case? I would have expected a datatable, yet you then ask if a datatable would be an improvement. It may well be, if you aren't already using one, because there are much worse alternatives that you might be using. Since we don't know, then there isn't all that much we can suggest.

I did it,i'm afraid i didn't do before!

[eurostar_italia]

i put the code on top.i hope you now you can help me to increase the program performance

[Peter Swinkels]

I,i have a little program that retrieves railway reservations for groups.
it downloads from a website getting a json.I store data in a matrix.
The object for the program is to know :
a) how many station are served from that train
b) how many people load and unload that train,selecting a particular station (ex rome,11 loaded,89 unloaded)
c) how many people load a specific car (ex car 5-10 passenger,car 7-11 passenger)
d) how many destinations and people selecting a particular station (ex from rome,11 passenger to florence,18 to milan,etc)
i have a matrix with 14 columns and setted with 5000 rows (i don't think the will be more).
i made a lot of inquiries with a for next to have all this informations,but some need a lot of time to get a response
my progam run in a company computers so i can't install or set anything,i can just place my exe on the computer.
in your opinion,wich would be the best way to improve my program ? datatable ? sql ?
thank for you cooperation
this is my code :
json download to the matrix (stands for bidimensional array)
i use newtonsoft json library

	Public matrice(5000, 15) As String
	Public matrice2(5000, 15) As String
	Public matrice3(5000, 15) As String
	Dim listadest As New List(Of Destinazioni)
	Dim listacar As New List(Of paxincarrozza)
	Dim listapax As ArrayList = New ArrayList
	
	Dim listapaxcognomi As ArrayList = New ArrayList

Dim ser As JObject = JObject.Parse(json)

			Dim data As List(Of JToken) = ser.Children().ToList



			If chiamata = 1 Then
				For Each item As JProperty In data
					item.CreateReader()

					For Each msg As JObject In item.Values

						matrice(contatore, 0) = msg("departureLocationName")
						matrice(contatore, 1) = msg("arrivalLocationName")
						matrice(contatore, 2) = msg("pnrCode")
						matrice(contatore, 3) = msg("serviceLevel")
						matrice(contatore, 4) = msg("lastName")
						matrice(contatore, 5) = msg("wagon")
						matrice(contatore, 6) = msg("seat")
						matrice(contatore, 7) = msg("firstName")
						matrice(contatore, 8) = msg("transportMeanName")
						matrice(contatore, 9) = msg("couponId")

						contatore = contatore + 1


					Next
				Next

				contatore = 0
				
				Do
					If ComboBox1.Items.IndexOf(matrice(contatore, 0)) = -1 Then
						ComboBox1.Items.Add(matrice(contatore, 0))
					End If
					If ComboBox1.Items.IndexOf(matrice(contatore, 1)) = -1 Then
						ComboBox1.Items.Add(matrice(contatore, 1))
					End If

					contatore = contatore + 1
				Loop Until matrice(contatore, 0) = Nothing

				Label1.Text = "TRENO  " & (matrice(0, 8))

then to know passenger destination or passenger load for single car :

		ListBox2.Items.Clear()

		Dim i1 As Integer

		Dim prima1 As Boolean = True
		Dim appoggio1 As paxincarrozza
		listacar.Clear()

		For i1 = 1 To 4999

			If ComboBox1.SelectedItem = (matrice(i1, 0)) Then
				If prima1 = True Then

					appoggio1.carrozza = (matrice(i1, 5))
					appoggio1.quantita = 0
					listacar.Add(appoggio1)


					prima1 = False
				End If
				Dim appo1 As New List(Of paxincarrozza)
				appo1.AddRange(listacar)
				Dim trovato As Boolean = False

				Dim a As Integer
				For Each element As paxincarrozza In appo1

					If element.carrozza = matrice(i1, 5) Then
						trovato = True
						a = listacar.IndexOf(element)
						appoggio1 = element
					End If

				Next

				If trovato = True Then
					appoggio1.quantita = appoggio1.quantita + 1
					listacar(a) = appoggio1
				Else

					appoggio1.carrozza = (matrice(i1, 5))
					appoggio1.quantita = 1
					listacar.Add(appoggio1)
				End If



			End If
		Next
		ListBox2.Items.Clear()

		For Each element As paxincarrozza In listacar
			ListBox2.Items.Add(element.carrozza & "   -   " & element.quantita)

		Next

the point is : a datatable with sql query would be better in your opinion ? or it just load memory without any speed increase ? i'm not sure about it.
we are speaking about 5000 rows nothing more...
thanks to all of you

Hi eurostar_italia,

Thank you for sharing your code so we can help you out. At the moment I have no access to vb.net but after a quick review of your code I have these tips for you:

1. Try experimenting with the list object in System.Collections.Generic namespace. Personally I prefer that over arrays. Especially arrays with fixed sizes. Remember, in modern Basics you can redimension an array without data loss.
2.

 matrice(contatore, 0) = msg("departureLocationName")
						matrice(contatore, 1) = msg("arrivalLocationName")
						matrice(contatore, 2) = msg("pnrCode")
						matrice(contatore, 3) = msg("serviceLevel")
						matrice(contatore, 4) = msg("lastName")
						matrice(contatore, 5) = msg("wagon")
						matrice(contatore, 6) = msg("seat")
						matrice(contatore, 7) = msg("firstName")
						matrice(contatore, 8) = msg("transportMeanName")
						matrice(contatore, 9) = msg("couponId")

In the above code you might want to use references to an enumeration rather than actual numbers:

enum columnse
     departureLocationName
     arrivalLocationName
     pnrCode
end enum

matrice(contatore, columnse. departureLocationName) = msg("departureLocationName")
matrice(contatore, columnse.arrivalLocationName) = msg("arrivalLocationName")
matrice(contatore, columnse.pnrCode) = msg("pnrCode")

3. You can shorten "contatore = contatore + 1" to "contatore += 1"
This works for all variables on which addition, subtraction, multiplication and division are performed.
4. I recommend you use meaningful names for your controls rather than List1 and Combo1.
5. You might want to change your loop from:

Do
...
Loop Until matrice(contatore, 0) = Nothing

to:

Do Until matrice(contatore, 0) = Nothing
...
Loop

I suspect your code might otherwise throw an error when the very first item contains "Nothing".

If you would like more tips or have any further questions, feel free to ask and post more code,

Hoping this helps.

yours,
Peter Swinkels

[eurostar_italia]

I suspect your code might otherwise throw an error when the very first item contains "Nothing".

If you would like more tips or have any further questions, feel free to ask and post more code,

Hoping this helps.

yours,
Peter Swinkels

thank you for your suggestions....i did them at once.
What about datatables ? do you think they would be better ? somebody told me that they are just a way of wasting memory,some other they're ok for the informations i need (lots of querys)...

[Peter Swinkels]

Hello eurostar_italia,

Thank you for your quick reply. I did a quick Google search for "datatable c# memory leak" and similar searches resulted in a lot of posts concerning memory leaks and usage. My recommendation is to keep using list objects or arrays. Other users who have more experience in these things than I do may have other suggestions for you though. Could you show the changes you made or rather not?

yours,
Peter Swinkels

[Shaggy Hiker]

Datatables give you a bunch of ways to filter, sort, and search in the data. If you want to filter out a subset, then they'd certainly be better. If you want to sort, then they'd be better. If you want to search....well, then they may be better.

Another option is the Dictionary. You could certainly switch from your matrices to dictionaries quite easily, but perhaps not all that efficiently. For example, a Dictionary(of Integer, String(15)) would be the same as your matrix. If you were to look up a single item from the dictionary, it would be considerably faster than searching through the matrix that you have, but that will be true ONLY if you know the array you want, and I think you don't. Searching through all the keys in a dictionary is no faster than searching through all the indexes in an array, so a dictionary only makes sense if you can look up items by key without looking through all the keys to find the right one.

Still, that might make sense as long as you don't do it in the trivial way I just described.

What you have is a datatable. You are filling a reader, that could fill a datatable with a single line, at which point you get all the searching advantages of the datatable. I wouldn't worry about the memory leaks. I've never run into any of that. I also wouldn't worry about wasting memory. Memory is abundant, these days, and even back when it wasn't CPU cycles were always more valuable than memory, so people would freely waste memory if it meant faster processes. Only in some embedded systems is memory more valuable than speed.

Still, the search capabilities of datatables may not gain you much. Some of them aren't all that fast, they're just really simple. For example, you can call the .Select() extension method to find a row. That's a single line, but it's 10% to 20% slower than going through the datatable row by row yourself. Using a loop would be six lines, while Select is one, but the loop is slightly faster. Normally, that doesn't matter, so people use Select. When it matters, it matters.

However, your data really fits as a class with 9 members (I'm not sure why your inner array is sized to 15) for the 9 elements you are putting into the inner element of the matrix. If you create a class rather than using a 2D array, you can have a List(of YourClass). That will be easier to work with, just as easy (or easier, but not faster) to search, and will have the same performance.

Better yet, if you do that, you can then improve the performance by wasting memory. Seeking in a Dictionary is FAR faster than iterating through a collection, especially a collection with 5000 items in it. So, for example, once you have built up your List(of YourClass) you could build a DestinationDictionary:

Private destinationDictionary As New Dictionary(of String,List(of YourClass))

Then, for each departureLocationName that you have, add a list that contains just those YourClass that have that departureLocationName. Now you can use the departureLocationName to look up that subset of the YourClass set that has that location name, and that lookup will be considerably faster than going through the list itself.

In theory, you could build a different dictionary for each element in your class, which would mean 9 dictionaries. Yeah, this uses up a bit of memory, but not all that much. The classes are just references. You aren't copying them into the dictionary, so the dictionary entry holds the key (a string), and a reference to a List, which itself just holds some number of references. That's some memory, but not all that much, really, and memory is abundant.

In practice, you probably wouldn't need to build 9 dictionaries, either. You'd probably find that you always start a search on just one or two elements, so you'd build dictionaries for just those elements, and not the rest.

[eurostar_italia]

Hello eurostar_italia,

Thank you for your quick reply. I did a quick Google search for "datatable c# memory leak" and similar searches resulted in a lot of posts concerning memory leaks and usage. My recommendation is to keep using list objects or arrays. Other users who have more experience in these things than I do may have other suggestions for you though. Could you show the changes you made or rather not?

yours,
Peter Swinkels

before changes : 10 seconds to load matrix,10 seconds to load listboxes after combobox1 changes its values.
afeter changes :6 seconds to load matrix,8 to loaad listboxes

[eurostar_italia]

Datatables give you a bunch of ways to filter, sort, and search in the data. If you want to filter out a subset, then they'd certainly be better. If you want to sort, then they'd be better. If you want to search....well, then they may be better.

Another option is the Dictionary. You could certainly switch from your matrices to dictionaries quite easily, but perhaps not all that efficiently. For example, a Dictionary(of Integer, String(15)) would be the same as your matrix. If you were to look up a single item from the dictionary, it would be considerably faster than searching through the matrix that you have, but that will be true ONLY if you know the array you want, and I think you don't. Searching through all the keys in a dictionary is no faster than searching through all the indexes in an array, so a dictionary only makes sense if you can look up items by key without looking through all the keys to find the right one.

Still, that might make sense as long as you don't do it in the trivial way I just described.

What you have is a datatable. You are filling a reader, that could fill a datatable with a single line, at which point you get all the searching advantages of the datatable. I wouldn't worry about the memory leaks. I've never run into any of that. I also wouldn't worry about wasting memory. Memory is abundant, these days, and even back when it wasn't CPU cycles were always more valuable than memory, so people would freely waste memory if it meant faster processes. Only in some embedded systems is memory more valuable than speed.

Still, the search capabilities of datatables may not gain you much. Some of them aren't all that fast, they're just really simple. For example, you can call the .Select() extension method to find a row. That's a single line, but it's 10% to 20% slower than going through the datatable row by row yourself. Using a loop would be six lines, while Select is one, but the loop is slightly faster. Normally, that doesn't matter, so people use Select. When it matters, it matters.

However, your data really fits as a class with 9 members (I'm not sure why your inner array is sized to 15) for the 9 elements you are putting into the inner element of the matrix. If you create a class rather than using a 2D array, you can have a List(of YourClass). That will be easier to work with, just as easy (or easier, but not faster) to search, and will have the same performance.

Better yet, if you do that, you can then improve the performance by wasting memory. Seeking in a Dictionary is FAR faster than iterating through a collection, especially a collection with 5000 items in it. So, for example, once you have built up your List(of YourClass) you could build a DestinationDictionary:

Private destinationDictionary As New Dictionary(of String,List(of YourClass))

Then, for each departureLocationName that you have, add a list that contains just those YourClass that have that departureLocationName. Now you can use the departureLocationName to look up that subset of the YourClass set that has that location name, and that lookup will be considerably faster than going through the list itself.

In theory, you could build a different dictionary for each element in your class, which would mean 9 dictionaries. Yeah, this uses up a bit of memory, but not all that much. The classes are just references. You aren't copying them into the dictionary, so the dictionary entry holds the key (a string), and a reference to a List, which itself just holds some number of references. That's some memory, but not all that much, really, and memory is abundant.

In practice, you probably wouldn't need to build 9 dictionaries, either. You'd probably find that you always start a search on just one or two elements, so you'd build dictionaries for just those elements, and not the rest.

@shaggy using datatables i guess would be the best way in my opinion due to several querys i shoud make.an ado connection can be the right solution ? i found a lot of examples but all of them are with external db (access or sql server).

[Peter Swinkels]

before changes : 10 seconds to load matrix,10 seconds to load listboxes after combobox1 changes its values.
afeter changes :6 seconds to load matrix,8 to loaad listboxes

Hello eurostar_italia,

Are you saying the suggested changes made your code faster?

yours,
Peter Swinkels

[eurostar_italia]

hello Peter,

a little bit faster.
But due i need several querys,I thing i would change the code to a datatable.
but i don't know how does it work the connection...i must read about ado.net

[Shaggy Hiker]

There is no connection...at least not in your case. A datatable is an in-memory database table, effectively. Normally, one would create a connection to a database, then use something like a datareader or dataadapter to fill the table from a database. You don't have a database, so that connection followed by fill doesn't need to happen.

In your case, you can create a datatable directly. This link gives an overblown example:

https://docs.microsoft.com/en-us/dot...ew=netcore-3.1

They have this example:

Private Sub AddColumn()
    ' Get the DataColumnCollection from a table in a DataSet.
    Dim columns As DataColumnCollection = _
        DataSet1.Tables("Prices").Columns
    Dim column As DataColumn = columns.Add()

    With column
       .DataType = System.Type.GetType("System.Decimal")
       .ColumnName = "Total"
       .Expression = "UnitPrice * Quantity"
       .ReadOnly = True
       .Unique = False
    End With
End Sub

That shows some of the options you can use in a datatable, but most of them don't apply to you. What you'd do would be more likely to be like this:

Private myDT As New Datatable

'Then, in something like form load:

myDT.Columns.Add("arrivalLocationName",GetType("System.String")
myDT.Columns.Add("pnrCode",GetType("System.String")
etc.

There's an even more compact way to write that, but I forget it at the moment, and don't want to steer you wrong.
Later on, when you fill the datatable you'd do something like this:

Dim nRow = yourDT.NewRow
yourDT.Rows.Add(nRow)
nRow("arrivalLocationName") = msg("arrivalLocationName")
nRow("pnrCode") = msg("pnrCode")
etc...

You can add the row to the Rows collection either before or after you fill in the fields, but it's also very easy to forget it entirely, so I like to add it right away. Forgetting it entirely wouldn't be good.

However, while a datatable can be searched, whether or not it will truly give you all you want in terms of performance remains to be seen. The truly simple means to search, such as the .Select extension method, is compact, but not super efficient. A DataView with a RowFilter may or may not be more efficient. Building dictionaries is more efficient for searching, but takes more work.

[eurostar_italia]

@shalky but what about sql querys. how can be done without an ado connection ??

[Shaggy Hiker]

What I showed has no ADO connection. It has no ADO anything.

You'd create the datatable when the form starts up. They usually get filled from a query, in which case the fields in the datatable come from the SQL, but they don't HAVE to be done that way, and what I showed is making a datatable by hand by adding the fields yourself.

The bit about building the rows would go inside the loop where you are currently filling in your array.

There's no connection involved.

[eurostar_italia]

What I showed has no ADO connection. It has no ADO anything.

You'd create the datatable when the form starts up. They usually get filled from a query, in which case the fields in the datatable come from the SQL, but they don't HAVE to be done that way, and what I showed is making a datatable by hand by adding the fields yourself.

The bit about building the rows would go inside the loop where you are currently filling in your array.

There's no connection involved.

I understand your code to fill up the datatable,but later to make queries to fill up comoboxes o list box: how can i proceed?

[Shaggy Hiker]

To fill a combobox is particularly easy, in most cases. Perhaps not, in this case. Normally, you could just set the datasource of the combobox, setting the displayvalue to the column you want. That's probably not going to work, in this case, because you probably have LOADS of duplicates, so you don't want to set the datasource to be any one column in the datatable.

Instead, you probably want to set the datasource to be some set of distinct values from the datatable. There are probably at least two ways to do that. I believe that one would be:

yourDatatable.DefaultView.ToTable(True, "TheColumnYouWantInTheComboBox")

I think that will create a datatable with a single column which you can then set as the datasource for the combobox. If that's the case, then the whole code would look like this:

yourComboBox.DisplayMember = "TheColumnYouWantInTheComboBox"
yourComboBox.DataSource = yourDatatable.DefaultView.ToTable(True, "TheColumnYouWantInTheComboBox")

When you make a selection in the combobox (SelectedIndexChanged event), you can get the SelectedValue.ToString and use that in a RowFilter for a dataview, which is kind of like a WHERE clause from a SQL statement. So, in the SelectedIndexChanged you might have:

If yourComboBox.SelectedIndex>-1 then
 yourDatatable.DefaultView.RowFilter = "TheColumnYouWantInTheComboBox = '" & yourComboBox.SelectedValue.ToString & "'"
End If

That's both freehand and I haven't used dataviews in a while, so it may be a bit off.

In fact, that might be a bit sketchy, so the alternative would be the Datatable.Select, which can be found here:

https://docs.microsoft.com/en-us/dot...System_String_

It's essentially the same kind of thing. The filter string is the same. What it will do is return an array of datarows which will match your search criteria. This will cut down the set of records you need to look through. There are a variety of ways to improve on that general design, based on what you ultimately need. For example, you might be able to sort the set in some fashion such that the ONE you want is the first one (to find the minimum value, for example). You can also use something like this to build up a filter statement using selections in multiple comboboxes. That's all dependent on what you need, though, so there's no generic solution.

[eurostar_italia]

well as u rightly understood ,in my case it's a little bit more difficult to load comboxes.
For instance : i need all the stations in "partenza" column as well ones in the Arrivo.
Most of them are duplicates .so probably an sql query wit "inner join " would be greate.
more over to load values in listbox to have "departing from ROMA : 10 people to milan,15 to florence,18 to venice" would be better with sql statement.
i'll try the code u suggest me at once ,as well
thanks for you help!!

[ChrisE]

I,i have a little program that retrieves railway reservations for groups.
it downloads from a website getting a json.I store data in a matrix.

what does you code look like to do that, did you try to import that data right into a Database?
then you could perform the SQL you need to filter the data

[jmcilhinney]

well as u rightly understood ,in my case it's a little bit more difficult to load comboxes.
For instance : i need all the stations in "partenza" column as well ones in the Arrivo.
Most of them are duplicates .so probably an sql query wit "inner join " would be greate.
more over to load values in listbox to have "departing from ROMA : 10 people to milan,15 to florence,18 to venice" would be better with sql statement.
i'll try the code u suggest me at once ,as well
thanks for you help!!

It's not going to be a SQL query because you're not querying a database. Let's say that you have a DataTable with the names of stations in Partenza and Arrivo columns and you want to list distinct values in alphabetical order. One option is to use a LINQ query:

Dim partenzaStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
 
stationComboBox.DataSource = stations

[eurostar_italia]

It's not going to be a SQL query because you're not querying a database. Let's say that you have a DataTable with the names of stations in Partenza and Arrivo columns and you want to list distinct values in alphabetical order. One option is to use a LINQ query:

Dim partenzaStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
 
stationComboBox.DataSource = stations

I thought a datatable could be used as a db to make queries.i guess the only solution would be linq to retrieve data without making long loop

[jmcilhinney]

A database is a specific thing. It's not just anything that stores data. When we say "database" we mean an actual database, not an object that stores data. Even things like Excel or text files might be considered data sources but not actual databases. A DataTable is not a database. It is generally used as a local cache for data from a database but, as has already been indicated, it doesn't have to be used for that. Generally speaking, a DataSet is an in-memory representation of your database or part of your database and a DataTable is an in-memory representation of a table or part of a table from that database. A DataTable only exists while the application is running. If you want the data it contains persisted between sessions then you need to save the data somewhere. That is generally going to be a database but it doesn't have to be. As for queries, LINQ was pretty much based on SQL, being a technology that provides abilities similar to those of SQL but for querying .NET lists instead of database tables.

[Shaggy Hiker]

Another point about LINQ is that it is very compact, it is not faster. You save a lot of time writing LINQ, but in every test I have tried, it has run slightly slower than a loop. Others have reported instances where it was faster, and I think it could be faster if there are JOIN statements involved, as loops of that sort get strange. Still, it's not magic. Under the hood, it has to do actual work, and that work can be more than you would do if you wrote the loop yourself.

If you're looking for speed, the dictionary suggestions I made earlier will get you better speed. A datatable will get you better organization, as would a list of classes, but you need to organize the data in different ways if you want to make the speed of a search faster.

[ChrisE]

I tried Shaggy's suggestion, you will have to try how fast it is with more Data
also you would have to adapt to the query(s) you want

here Query (1)
a count of the Wagons which Seat on which Station is occupied
I hardcoded the Seats to 30 for each Wagon

the debug output looks like this..

Wagon Number.(1): 
8xRome 26,67%
1xFlorence 3,33%
1xMilan 3,33%
Seat-1)-Seat-28)-Seat-8)-Seat-2)-Seat-3)-Seat-4)-Seat-11)-Seat-12)-Seat-16)-Seat-22)-occupied 
-----------------------------------------------------
Wagon Number.(2): 
2xFlorence 6,67%
2xRome 6,67%
Seat-5)-Seat-2)-Seat-15)-Seat-17)-occupied 
-----------------------------------------------------
Wagon Number.(5): 
1xFlorence 3,33%
Seat-3)-occupied 
-----------------------------------------------------

and the code for the Form
add a Datagridview and a Button

Public Class Train

    Dim tb As DataTable = New DataTable

    Private Sub Train_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        tb.Columns.Add("Wagon Number")
        tb.Columns.Add("Seat Number")
        tb.Columns.Add("Total Seats in Wagon")
        tb.Columns.Add("Station")


        tb.Rows.Add("1", "1", "30", "Rome")
        tb.Rows.Add("2", "5", "30", "Florence")
        tb.Rows.Add("1", "28", "30", "Florence")
        tb.Rows.Add("2", "2", "30", "Rome")
        tb.Rows.Add("5", "3", "30", "Florence")
        tb.Rows.Add("2", "15", "30", "Florence")
        tb.Rows.Add("1", "8", "30", "Milan")
        tb.Rows.Add("2", "17", "30", "Rome")
        tb.Rows.Add("1", "2", "30", "Rome")
        tb.Rows.Add("1", "3", "30", "Rome")
        tb.Rows.Add("1", "4", "30", "Rome")
        tb.Rows.Add("1", "11", "30", "Rome")
        tb.Rows.Add("1", "12", "30", "Rome")
        tb.Rows.Add("1", "16", "30", "Rome")
        tb.Rows.Add("1", "22", "30", "Rome")

        'add the Data
        DataGridView1.DataSource = tb
        DataGridView1.AllowUserToAddRows = False
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim WagonIDs As New List(Of Integer)
        For Each row As DataGridViewRow In DataGridView1.Rows
            If (Not WagonIDs.Contains(row.Cells("Wagon Number").Value)) Then
                WagonIDs.Add(row.Cells("Wagon Number").Value)
            End If
        Next

        For Each Wagon_ID As Integer In WagonIDs
            Dim Seats As New Dictionary(Of String, Integer)()
            Dim rows As New Dictionary(Of Integer, List(Of Integer))
            For Each row As DataGridViewRow In DataGridView1.Rows
                If row.Cells("Wagon Number").Value = Wagon_ID Then
                    If Not Seats.ContainsKey(row.Cells("Station").Value) Then
                        Seats.Add(row.Cells("Station").Value, 0)
                    End If
                    Seats(row.Cells("Station").Value) += 1
                    If Not rows.ContainsKey(row.Cells("Seat Number").Value) Then
                        rows(row.Cells("Seat Number").Value) = New List(Of Integer)()
                    End If
                    rows(row.Cells("Seat Number").Value).Add(row.Cells("Total Seats in Wagon").Value)
                End If
            Next

            Debug.Write("Wagon Number.(" & Wagon_ID & ")")
            Debug.WriteLine(": ")

            For Each soldSeatTicket As KeyValuePair(Of String, Integer) In Seats
                Debug.Write(String.Format("{0}x{1} ", soldSeatTicket.Value, soldSeatTicket.Key))
                Dim TotalSeatsOnWagon As Integer = 30
                Dim DisplayPercentage = soldSeatTicket.Value / TotalSeatsOnWagon
                Debug.WriteLine(DisplayPercentage.ToString("p"))
            Next
            For Each row As KeyValuePair(Of Integer, List(Of Integer)) In rows
                Debug.Write("Seat-" & row.Key & ")")
                Debug.Write("-")
            Next
            Debug.Write("occupied")
            Debug.WriteLine(" ")
            Debug.WriteLine("-----------------------------------------------------")
        Next
    End Sub
End Class

[ident]

Another point about LINQ is that it is very compact, it is not faster. You save a lot of time writing LINQ, but in every test I have tried, it has run slightly slower than a loop. Others have reported instances where it was faster,

In some cases PLINQ can speed it up, yet to see any of the long time members ever use it, which makes me wonder "why". Waste of time when it was really tested?

[Shaggy Hiker]

Techgnome has reported cases where LINQ was faster, and I think it quite likely could be for JOINS, but I have never had a good test case to time it.

[eurostar_italia]

@Chris your code look to be great!!!
thank to all of you

[ChrisE]

@Chris your code look to be great!!!
thank to all of you

well it was Shaggy's idea, and I don't know if it's great until you test it with 5000 rows as you said

[eurostar_italia]

well i don't use datatable to show datas ,i use list boxes...due one it's for destination,one is for car load and so on..
i can arrange the same code in function to be called from the different buttons you think ?

[jmcilhinney]

well i don't use datatable to show datas ,i use list boxes...due one it's for destination,one is for car load and so on..
i can arrange the same code in function to be called from the different buttons you think ?

Yes, a ListBox is for displaying data and allowing the user to interact with it. A DataTable is for storing data while your application is running. A database is for storing data while your app is not running and to allow multiple users to access the same data. It is quite common for data to be stored in a Datatable and that table to be bound to a ListBox and/or other controls. The two are not mutually exclusive and often work together.

[eurostar_italia]

Yes, a ListBox is for displaying data and allowing the user to interact with it. A DataTable is for storing data while your application is running. A database is for storing data while your app is not running and to allow multiple users to access the same data. It is quite common for data to be stored in a Datatable and that table to be bound to a ListBox and/or other controls. The two are not mutually exclusive and often work together.

I was wrong,what i want to say was "i dont use datagridview so I can't use that code.but it's a good beginning point.

	With datatable1
					.Columns.Add("Partenza", System.Type.GetType("System.String"))
					.Columns.Add("Arrivo", System.Type.GetType("System.String"))
					.Columns.Add("PNR", System.Type.GetType("System.String"))
					.Columns.Add("Classe", System.Type.GetType("System.String"))
					.Columns.Add("Cognome", System.Type.GetType("System.String"))
					.Columns.Add("Carrozza", System.Type.GetType("System.String"))
					.Columns.Add("Posto", System.Type.GetType("System.String"))
					.Columns.Add("Nome", System.Type.GetType("System.String"))
					.Columns.Add("Treno", System.Type.GetType("System.String"))
					.Columns.Add("ID prenotazione", System.Type.GetType("System.String"))
				End With


				For Each item As JProperty In data
					item.CreateReader()

					For Each msg As JObject In item.Values
						datatable1.Rows.Add(msg("departureLocationName"), msg("arrivalLocationName"), msg("pnrCode"), msg("serviceLevel"), msg("lastName"), msg("wagon"), msg("seat"), msg("firstName"), msg("transportMeanName"), msg("Id"))
					Next
				Next

[eurostar_italia]

Dim WagonIDs As New List(Of String)
				For Each row In treno1.Rows
					If (Not WagonIDs.Contains(("Carrozza"))) Then
						WagonIDs.Add(("Carrozza"))
					End If
				Next

this code add the word Carrozza,but if i want to find out the real value ????

[eurostar_italia]

It's not going to be a SQL query because you're not querying a database. Let's say that you have a DataTable with the names of stations in Partenza and Arrivo columns and you want to list distinct values in alphabetical order. One option is to use a LINQ query:

Dim partenzaStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
Dim arrivoStations = table.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()
 
stationComboBox.DataSource = stations

it's strange .. stations it's correctly filled with all stations,but stationcombobox doesn't load anything
stations = {Length=26}

[Shaggy Hiker]

Where's that code located?

[eurostar_italia]

it's in the backgroundworker
station has 26 records as they should be....but the combobox is empty
i must say,25 are stations but record 0 is ""

Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted


		FormAttesa.Close()
		If e.Cancelled = True Then
			Exit Sub

		Else


			Dim ser As JObject = JObject.Parse(json)

			Dim data As List(Of JToken) = ser.Children().ToList



			If chiamata = 1 Then





				With treno1
					.Columns.Add("Partenza", System.Type.GetType("System.String"))
					.Columns.Add("Arrivo", System.Type.GetType("System.String"))
					.Columns.Add("PNR", System.Type.GetType("System.String"))
					.Columns.Add("Classe", System.Type.GetType("System.String"))
					.Columns.Add("Cognome", System.Type.GetType("System.String"))
					.Columns.Add("Carrozza", System.Type.GetType("System.String"))
					.Columns.Add("Posto", System.Type.GetType("System.String"))
					.Columns.Add("Nome", System.Type.GetType("System.String"))
					.Columns.Add("Treno", System.Type.GetType("System.String"))
					.Columns.Add("ID prenotazione", System.Type.GetType("System.String"))
				End With


				For Each item As JProperty In data
					item.CreateReader()

					For Each msg As JObject In item.Values
						treno1.Rows.Add(msg("departureLocationName"), msg("arrivalLocationName"), msg("pnrCode"), msg("serviceLevel"), msg("lastName"), msg("wagon"), msg("seat"), msg("firstName"), msg("transportMeanName"), msg("Id"))
					Next
				Next



				Dim partenzaStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
				Dim arrivoStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
				Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()

				ComboBox1.DataSource = stations

[ChrisE]

your making things far to complicated, why don't you supply some Data (see Post#22 you have chosen to ignore)
also the columns you add to your DataTable are all Strings ?

see this

 Protected Function FormatDataTable() As DataTable
        Dim dt As DataTable = New DataTable()
        ' Create Columns
        dt.Columns.Add("Date", GetType(Date))
        dt.Columns.Add("Week", GetType(Integer))
        dt.Columns.Add("Day", GetType(String))
        dt.Columns.Add("Day No.", GetType(Integer))
        Return dt
    End Function

[eurostar_italia]

your making things far to complicated, why don't you supply some Data (see Post#22 you have chosen to ignore)
also the columns you add to your DataTable are all Strings ?

see this

 Protected Function FormatDataTable() As DataTable
        Dim dt As DataTable = New DataTable()
        ' Create Columns
        dt.Columns.Add("Date", GetType(Date))
        dt.Columns.Add("Week", GetType(Integer))
        dt.Columns.Add("Day", GetType(String))
        dt.Columns.Add("Day No.", GetType(Integer))
        Return dt
    End Function

it's ok but it's still a datatable,i can only use linq to make queries.I don't want to use an external db cause i cannot install anything on the machine.It's a company machine...so i can only place .exe on desktop....
this code seems to work,but it doesn't load anything even if stations array is correctly populated

Dim partenzaStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Partenza"))
				Dim arrivoStations = treno1.AsEnumerable().Select(Function(row) row.Field(Of String)("Arrivo"))
				Dim stations = partenzaStations.Concat(arrivoStations).Distinct().OrderBy(Function(s) s).ToArray()

				ComboBox1.DataSource = stations