Android Tutorial DBUtils - Android databases are now simple!

ListView1.SingleLineLayout.Label.Gravity = Gravity.RIGHT

' Determine if a table exists
' Written by C.Kemp 27th Feb 2013, Kemputer Pty Limited
' Input Params
'          SQL ~ the established connection to an existing SQLite DB
'            tagetTbl ~ the name, as a string, of the table to be verified
' Output Params
'            ans ~ the return value of the call to this routine
'
' Calling this routine
'      if (DBUtils.TableExists(SQL, "MyDBTable") = false) then
'         <Do Something>
'      End If
Sub TableExists(SQL As SQL, targetTbl As String) As Boolean
   Dim resCurs As Cursor
   Dim ans As Boolean
   Dim ExStmnt As String
   
      ExStmnt = "SELECT name FROM sqlite_master WHERE type='table' AND name='" & targetTbl & "'"
      resCurs = SQL.ExecQuery(ExStmnt)
      ans = (resCurs.RowCount = 1)
      resCurs.close
      
      Return ans
            
End Sub

Sub WebView2_OverrideUrl (Url As String) As Boolean
Dim stringa_SQL As String
Dim SQL As SQL


'parse the row and column numbers from the URL
Dim values() As String
values = Regex.Split("[.]", URL.SubString(7))

Dim col, row As Int
col = values(0) ' this is the column selected
row = values(1) ' this is the row

stringa_SQL="SELECT id_cliente, id_giro, " _
& "seq, nome_cliente as cliente, " _
& "indirizzo, note " _
& "FROM ListaGiornata " _
& "ORDER BY seq DESC"

Dim riga() As String ' this is for storing the row selected
Lista = DBUtils.ExecuteMemoryTable(SQL, "" & stringa_SQL, Null, 0) 
' lista now have the table value with in the first column the index
riga=Lista.Get(row)
main.IDRowSelected=riga(0) ' this is the index of the row selected in db

Sub WebView1_OverrideUrl (Url As String) As Boolean
   'parse the row and column numbers from the URL
    Dim Query As String
   
   Dim URLvalues() As String
   URLvalues = Regex.Split("[.]", Url.SubString(7))
   Dim col, row As Int
   col = URLvalues(0)
   row = URLvalues(1)
   
   Dim DBList As List
        Dim RowData() As String
   Query = "SELECT * FROM Flown"
   DBList = DBUtils.ExecuteMemoryTable(SQLDB, Query , Null, 0)
   RowData = DBList.Get(row)
    ToastMessageShow("User pressed on column: " & col & " and row: " & row & ", value: " & RowData(col), False)
   Return True
   
End Sub

cur = SQL.ExecQuery(Query)

WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL, "Select ROW_NUMBER() OVER (ORDER BY score) As Row, name As Name, score As Score FROM main ORDER BY score DESC" , Null, 0, True))

WebView1.LoadHtml(DBUtils.ExecuteHtml(SQL, "SELECT name As Name, score As Score FROM main ORDER BY score DESC" , Null, 0, True))

Table = DBUtils.ExecuteMemoryTable(SQL, "SELECT Id FROM Students", Null, 0)

'Executes the query and returns the result as a list of arrays.
'Each item in the list is a strings array.
'StringArgs - Values to replace question marks in the query. Pass Null if not needed.
'Limit - Limits the results. Pass 0 for all results.

' update an existing table and add columns
Sub Table_AddColumns(TableName As String, FieldsAndTypes As Map)
For i = 0 To FieldsAndTypes.Size - 1
Dim sb, field, ftype As String
field = FieldsAndTypes.GetKeyAt(i)
ftype = FieldsAndTypes.GetValueAt(i)
sb = "ALTER TABLE [" & TableName & "] ADD COLUMN [" & field & "] " & ftype
Dim fExist As Boolean = Table_FieldExists(TableName, field)
If fExist = False Then SQLite.ExecNonQuery(sb)
Next
End Sub

' return the number of rows in a database table
Sub Table_CountRows(TableName As String) As Int
Dim rTot As Int
rTot = SQLite.ExecQuerySingleResult("SELECT count(*) FROM [" & TableName & "]")
Return rTot
End Sub

' return a csv string of query results with a header if chosen
Sub Table_ExecuteCSV(Query As String, bIncludeHeader As Boolean, StringArgs() As String) As List
Dim cur As Cursor
Dim colName As String
Dim colValue As String
If StringArgs <> Null Then
cur = SQLite.ExecQuery2(Query, StringArgs)
Else
cur = SQLite.ExecQuery(Query)
End If
Log("ExecuteCSV: " & Query)
Dim nL As List
nL.Initialize
Dim sb As StringBuilder
If bIncludeHeader = True Then
sb.Initialize
For i = 0 To cur.ColumnCount - 1
colName = cur.GetColumnName(i)
sb.Append(colName).Append(",")
Next
colName = sb.ToString
If colName.EndsWith(",") Then colName = colName.SubString2(0, colName.Length-1)
nL.Add(sb)
End If

For row = 0 To cur.RowCount - 1
sb.Initialize
cur.Position = row
For i = 0 To cur.ColumnCount - 1
colValue = cur.GetString2(i)
If colValue = Null Then colValue = ""
sb.Append(colValue).Append(",")
Next
colName = sb.ToString
If colName.EndsWith(",") Then colName = colName.SubString2(0, colName.Length-1)
nL.Add(sb)
Next
cur.Close
Return nL
End Sub

' return all records of the query as a list of maps
' all column names are in lowercase
Sub Table_ExecuteMaps(Query As String, StringArgs() As String) As List
Dim cur As Cursor
Dim colValue As String
Dim colName As String
Dim Limit As Int
If StringArgs <> Null Then
cur = SQLite.ExecQuery2(Query, StringArgs)
Else
cur = SQLite.ExecQuery(Query)
End If
Log("ExecuteMaps: " & Query)
Dim xTable As List
xTable.Initialize
Limit = cur.RowCount
For row = 0 To Limit - 1
cur.Position = row
Dim m As Map
m.Initialize
For col = 0 To cur.ColumnCount - 1
colName = cur.GetColumnName(col).tolowercase
colValue = cur.GetString2(col)
If colValue = Null Then colValue = ""
m.Put(colName, colValue)
Next
xTable.Add(m)
Next
cur.Close
Return xTable
End Sub

' return a list of the table column names as a list
Sub Table_FieldNames(TableName As String) As List
Dim res1 As List
res1.Initialize
Dim cur As Cursor
cur = SQLite.ExecQuery("PRAGMA table_info ([" & TableName & "])")
If cur.RowCount = 0 Then
Log("No records found.")
Return Null
End If
For i = 0 To cur.RowCount - 1
cur.Position = i
res1.Add(cur.GetString("name").ToLowerCase)
Next
cur.close
Return res1
End Sub

' return the table information as a map
Sub Table_Information(TableName As String) As Map
Dim res1 As Map
Dim res As Map
res1.Initialize
Dim cur As Cursor
cur = SQLite.ExecQuery("PRAGMA table_info ([" & TableName & "])")
If cur.RowCount = 0 Then
Log("No records found.")
Return Null
End If
For i = 0 To cur.RowCount - 1
cur.Position = i
res.Initialize
res.put("ID", cur.GetString("cid"))
res.put("Name", cur.GetString("name"))
res.Put("Type", cur.GetString("type"))
res.Put("Default value", cur.GetString("dflt_value"))
res.Put("Not null", cur.GetString("notnull"))
res.Put("Primary key", cur.GetString("pk"))
res1.Put(i, res)
Next
cur.close
Return res1
End Sub

' inset a map into the database table
Sub Table_InsertMap(TableName As String, m As Map)
Dim sb, columns, values As StringBuilder
SQLite.BeginTransaction
Try
sb.Initialize
columns.Initialize
values.Initialize
Dim listOfValues As List
listOfValues.Initialize
sb.Append("INSERT INTO [" & TableName & "] (")
For i2 = 0 To m.Size - 1
Dim col As String
Dim value As Object
col = m.GetKeyAt(i2)
value = m.GetValueAt(i2)
If i2 > 0 Then
columns.Append(", ")
values.Append(", ")
End If
columns.Append("[").Append(col).Append("]")
values.Append("?")
listOfValues.Add(value)
Next
sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
Log("InsertMap : " & sb.ToString)
SQLite.ExecNonQuery2(sb.ToString, listOfValues)
SQLite.TransactionSuccessful
Catch
ToastMessageShow(LastException.Message, True)
Log(LastException)
End Try
SQLite.EndTransaction
End Sub

' return the list of values from a table
Public Sub Table_ListOfField(TableName As String, KeyField As String) As List
Dim c As Cursor
TableName = TableName.ToLowerCase
If TableName.StartsWith("select") = True Then
c = SQLite.ExecQuery(TableName)
Else
c = SQLite.ExecQuery("SELECT [" & KeyField & "] FROM [" & TableName & "]")
End If
Dim res As List
Dim strV As String
res.Initialize
For row = 0 To c.RowCount - 1
c.Position = row
strV = c.GetString2(0).Trim
If strV.Length > 0 Then res.Add(strV)
Next
Return res
End Sub

Public Sub Table_RecordExists(TableName As String, FldName As String, FldValue As String) As Boolean
Return SQLite.ExecQuerySingleResult2("SELECT count(" & FldName & ") FROM [" & TableName & "] WHERE [" & FldName & "] = ?", _
Array As String(FldValue)) > 0
End Sub

' rename an existing table
Sub Table_Rename(OldName As String, NewName As String)
SQLite.ExecNonQuery("ALTER TABLE [" & OldName & "] RENAME TO [" & NewName & "]")
End Sub

If colValue = Null Then colValue = ""

Dim InString As StringBuilder
InString.Initialize
For i = 0 to n-1
 InString.Append("?")
 If i < n-1 then InString.Append(", ")
Next

Dim args(n) As String
args(0) = ...
args(1) = ...

Sub spnrTests_ItemClick (Position As Int, Value As Object)
    'Show the grade of this test
    Dim m As Map
    m = DBUtils.ExecuteMap(SQL, "SELECT Grade FROM Grades WHERE id = ? AND test = ?", _
        Array As String(spnrStudentId.SelectedItem, Value))
    If m = Null Then
        txtGrade.Text = "N/A"
    Else
        txtGrade.Text = m.Get("grade")
    End If
End Sub