Project 2 for Chapter 17.5 ColumnView demonstrates the use of properties, methods and events of the ColumnView control as well as memory management for data export and data import.

• When the programme is first started, the ColumnView data comes from a DB table. In this context, the static class DBC (DataBaseConnection) is interesting. The class DBC is based on an idea by Christof Thalhofer and is described in → Chapter 22. Databases.
• The data in the ColumnView is saved in a text file after the first closing of the programme window.
• The JSON format was chosen as the data format. Other possibilities include CSV files, settings files or XML files.

Figure 17.5.2.1: Main programme with the GridView

• A settings file (/home/username/.config/gambas3/IContacts.conf) stores in a value Imported=1 in the slot [ImportDBData] the switching from one-time reading of data from a DB table to data import/data export via the text file DataBase/json.data in the project directory.
• The image in the first column symbolises the status of a contact (private (green), business (red) and other contacts (grey)).
• The contact data is always sorted by the 3rd column 'Last name' when the programme is started. All other columns can be sorted in the same way. Note: Sorting by symbols in the 1st column has no effect, as only the column text is sorted and no text has been assigned to the symbols.
• You can create new contacts, change existing contact data or delete a contact. The entry of a surname is mandatory. The entries in the input boxes are not checked syntactically.

Figure 17.5.2.2: Change a contact

For notes on valid data checks, see → Chapters 16.6.1 TextBox and its specialisations, 16.6.2 Valid data and 19.6.5 Checking the syntax of strings.

Figure 17.5.2.3: Inserting a new contact

The source code is given in selected extracts showing iteration over all the elements of a ColumnView, implemented by different control structures:

[1] Private Sub SetDBData()
[2]
[3]   Dim rDBResult As Result
[4]   Dim iKey As Integer
[5]   Dim sDBTableName, sSQL_Command, sKey As String
[6]   Dim hItem As _TreeView_Item
[7]
[8]   sDBTableName = "kontakte" ' Name of the DB
[9]   sSQL_Command = Subst("&1 &2&3", "SELECT vorname, nachname, email, web, pbo FROM", sDBTableName, ";")
[10]
[11]   rDBResult = DBC.DBConnection.Exec(sSQL_Command)
[12]
[13]   If rDBResult.Count = 0 Then
[14]      Message.Warning(Subst("&1 &2 &3", "<h1>", ("The number of selected records is zero!"), "</h1>"))
[15]      Return
[16]   Endif
[17]
[18]   If rDBResult.Available Then
[19]      For Each rDBResult
[20]        Inc iKey  ' Der erste Key ist "1", weil eine Kopfzeile definiert ist
[21]        sKey = Str(iKey)
[22]        Select rDBResult["pbo"]
[23]          Case "p"
[24]            covData.Add(sKey, "", Picture["green16.png"]) ' 1. Spalte
[25]          Case "b"
[26]            covData.Add(sKey, "", Picture["red16.png"])
[27]          Case "o"
[28]            covData.Add(sKey, "", Picture["gray16.png"])
[29]        End Select
[30]      ' Weitere ColumnView-Spalten einfügen ...
[31]        covData[sKey][1] = rDBResult["vorname"]
[32]        covData[sKey][2] = rDBResult["nachname"]
[33]        covData[sKey][3] = rDBResult["email"]
[34]        covData[sKey][4] = rDBResult["web"]
[35]      Next
[36]   Endif
[37]
[38]   ' If rDBResult.Available Then
[39]   '    For Each rDBResult
[40]   '      Inc iKey
[41]   '      sKey = Str(iKey)
[42]   '      Select rDBResult["pbo"]
[43]   '        Case "p"
[44]   '          hItem = covData.Add(sKey, "", Picture["green16.png"])
[45]   '        Case "b"
[46]   '          hItem = covData.Add(sKey, "", Picture["red16.png"])
[47]   '        Case "o"
[48]   '          hItem = covData.Add(sKey, "", Picture["gray16.png"])
[49]   '      End Select
[50]   '    ' Weitere ColumnView-Spalten einfügen ...
[51]   '      hItem[1] = rDBResult["vorname"]
[52]   '      hItem[2] = rDBResult["nachname"]
[53]   '      hItem[3] = rDBResult["email"]
[54]   '      hItem[4] = rDBResult["web"]
[55]   '    Next
[56]   ' Endif
[57]
[58]   CVSettings["ImportDBData/Imported"] = 1
[59]   CVSettings.Save()
[60]
[61]   Catch
[62]   Message.Error(Error.Text)
[63]
[64] End

Notes:

In line 11 the DB connection defined in the (static) class DBC is used and the result of the DB query is stored in the variable rDBResult.The reading of the selected fields of the DB table 'kontakte' is done in lines 18 to 36.An alternative can be found in lines 38 to 56.In line 58 the value in the settings file in the slot 'ImportDBData' is changed from 0 to 1 and stored in line 59. By changing this value, all exported data from the ColumnView - stored in a file in JSON format - will be read back into the ColumnView from this file in the future.

The next two paragraphs will introduce you to the export and import procedures:

Data Export:

[1]   Private Sub ExportData(sJSONPath As String)
[2]
[3]   Dim i As Integer
[4]   Dim cJSONCollection As JSONCollection
[5]   Dim cJSON As JSONCollection
[6]
[7]   cJSON = New JSONCollection ' Ein neues JSONCollection-Objekt erzeugen
[8]   Repeat
[9]     cJSONCollection = New JSONCollection
[10]     cJSONCollection["pbo"] = covData.Item[0]
[11]
[12]     Select covData.Item.Picture ' Bild in der 1. Spalte (Index=0)
[13]       Case Picture["red16.png"]
[14]         cJSONCollection["picture"] = "red16.png"
[15]       Case Picture["green16.png"]
[16]         cJSONCollection["picture"] = "green16.png"
[17]       Case Picture["gray16.png"]
[18]         cJSONCollection["picture"] = "gray16.png"
[19]     End Select
[20]
[21]     cJSONCollection["vorname"] = covData.Item[1] ' Text in der 2. Spalte (Index=1)
[22]     cJSONCollection["nachname"] = covData.Item[2]
[23]     cJSONCollection["email"] = covData.Item[3]
[24]     cJSONCollection["web"] = covData.Item[4]
[25]     Inc i
[26]   ' In die JSONCollection 'cJSON' eine JSONCollection als Element einfügen
[27]     cJSON["RECORD_" & Str(i)] = cJSONCollection
[28]   Until covData.MoveBelow() ' ... das letzte Element in der ColumnView erreicht ist
[29]
[30]   File.Save(sJSONPath &/ "json.data", JSON.Encode(cJSON)) ' Den JSONText in einer Datei speichern
[31]
[32] End

Data import:

[1] Private Sub ImportData(sJSONPath As String, hColumnView As ColumnView)
[2]
[3]   Dim iElement As Integer = 1
[4]   Dim cJSONCollection As JSONCollection
[5]   Dim cCollection As JSONCollection
[6]
[7]   If Not Exist(sJSONPath) Then Return
[8]   hColumnView.Clear()
[9]   cJSONCollection = JSON.Decode(File.Load(sJSONPath), True)
[10]
[11]   For Each cCollection In cJSONCollection
[12]     hColumnView.Add(Str(iElement), cCollection["pbo"], Picture[cCollection["picture"]])
[13]     hColumnView[iElement][1] = cCollection["vorname"]
[14]     hColumnView[iElement][2] = cCollection["nachname"]
[15]     hColumnView[iElement][3] = cCollection["email"]
[16]     hColumnView[iElement][4] = cCollection["web"]
[17]     Inc iElement
[18]   Next
[19] End