vb.net sqlite how to loop through selected records and pass each record as a parameter to another function

Question

Hi,

I have a sqlite table with following fields:

Langauge      level       hours
German      2           50
French      3           40
English    1           60
German      1           10
English    2           50
English    3           60
German      1           20
French      2           40

I want to loop through the records based on language and other conditions and then pass the current selected record to a different function. So I have the following mixture of actual code and psudo code. I need help with converting the psudo code to actual code, please. I am finding it difficult to do so.

Here is what I have:

Private sub mainp()
   Dim oslcConnection As New SQLite.SQLiteConnection
   Dim oslcCommand As SQLite.SQLiteCommand
   Dim langs() As String = {"German", "French", "English"}
   Dim i as Integer = 0
   oslcConnection.ConnectionString = "Data Source=" & My.Settings.dbFullPath & ";"
   oslcConnection.Open()
   oslcCommand = oslcConnection.CreateCommand
   Do While i <= langs.count
    If langs(i) = "German" Then
      oslcCommand.CommandText = "SELECT * FROM table WHERE language = '" & langs(i) & "';"
      For each record selected             'psudo code
         If level = 1 Then                 'psudo code
            update level to 2              'psudo code
            minorp(currentRecord)          'psudo code: calling minorp function and passing the whole record as a parameter
         End If                            'psudo code
         If level = 2 Then                 'psudo code
            update level to 3              'psudo code
            minorp(currentRecord)          'psudo code: calling minorp function and passing the whole record as a parameter
         End If                            'psudo code
      Next                                 'psudo code
    End If

    If langs(i) = "French" Then
      oslcCommand.CommandText = "SELECT * FROM table WHERE language = '" & langs(i) & "';"
      For each record selected             'psudo code
         If level = 1 Then                 'psudo code
            update level to 2              'psudo code
            minorp(currentRecord)          'psudo code: calling minorp function and passing the whole record as a parameter
         End If                            'psudo code
         If level = 2 Then                 'psudo code
            update level to 3              'psudo code
            minorp(currentRecord)          'psudo code: calling minorp function and passing the whole record as a parameter
         End If                            'psudo code
      Next                                 'psudo code
    End If
Loop 
End Sub

Many thanks for your help.

Answer 1

The datatable has a DataRow object which you can pass to your functions.

Answer 2

I would suggest you create a class, e.g.

public class LanguageCourse

   'Prob better to make these properties
   public Language as string
   public Level as integer
   public Hours as integer

   public sub new(language as string, level as integer, hours as integer)
     Language = language
     Level = level
     Hours  = hours
   end sub

end class

Your code above could then become the following:

Private sub mainp()
   Dim oslcConnection As New SQLite.SQLiteConnection
   Dim oslcCommand As SQLite.SQLiteCommand
   Dim langs() As String = {"German", "French", "English"}
   Dim i as Integer = 0
   oslcConnection.ConnectionString = "Data Source=" & My.Settings.dbFullPath & ";"
   oslcConnection.Open()
   oslcCommand = oslcConnection.CreateCommand

  'Not sure why you were looping round these like this.  It's also not a great idea to 
  'build up your sql queries by concactenating strings, better to parameteris them, but
  'seeing as how this seems to be hard coded anyway, better even like this:

      dim course as LanguageCourse

      oslcCommand.CommandText = "SELECT * FROM table WHERE language IN ("German", "French", "English");"
      For each record selected             'psudo code

         course = new LanguageCourse(record.language, record.level, record.hours)

         'This function should handle your update as you just seem to be adding one to
         'something, for certain criteria.
         minorp(course)

      Next                                 'psudo code


End Sub

Note that this is still pseudo code, as this will not compile :)

Answer 3

I would suggest you move this logic to the database. SQL can perform these checks and update the data via an update statement with a where clause.