JSON and Data Access

JSON with System.Text.Json

Imports System.Text.Json

Public Class Customer
    Public Property Name As String
    Public Property Age As Integer
    Public Property Tags As List(Of String)
End Class

Module Program
    Sub Main()
        Dim ada As New Customer With {.Name = "Ada", .Age = 36, .Tags = New List(Of String) From {"vip"}}

        Dim options As New JsonSerializerOptions With {
            .PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
            .WriteIndented = True
        }

        Dim json = JsonSerializer.Serialize(ada, options)
        Console.WriteLine(json)

        Dim back = JsonSerializer.Deserialize(Of Customer)("{""name"":""Bob"",""age"":41,""tags"":[]}", options)
        Console.WriteLine($"{back.Name} is {back.Age}")
    End Sub
End Module

{
  "name": "Ada",
  "age": 36,
  "tags": [
    "vip"
  ]
}
Bob is 41

Properties become JSON fields; CamelCase turns Name into name. Reuse one JsonSerializerOptions object. Older projects often use Newtonsoft.Json (JsonConvert.SerializeObject), which works much the same way. The JSON Formatter helps inspect the output.

Databases with ADO.NET

ADO.NET is .NET's low-level database API: a connection, a command, and a reader. The same pattern works for SQL Server (Microsoft.Data.SqlClient), SQLite (Microsoft.Data.Sqlite), PostgreSQL (Npgsql), and others:

' illustrative: needs the Microsoft.Data.Sqlite NuGet package
Imports Microsoft.Data.Sqlite

Module Program
    Sub Main()
        Using conn As New SqliteConnection("Data Source=shop.db")
            conn.Open()

            Using create = conn.CreateCommand()
                create.CommandText = "CREATE TABLE IF NOT EXISTS products (name TEXT, price REAL)"
                create.ExecuteNonQuery()
            End Using

            Using insert = conn.CreateCommand()
                insert.CommandText = "INSERT INTO products (name, price) VALUES ($name, $price)"
                insert.Parameters.AddWithValue("$name", "Tea")
                insert.Parameters.AddWithValue("$price", 3.5)
                insert.ExecuteNonQuery()
            End Using

            Using query = conn.CreateCommand()
                query.CommandText = "SELECT name, price FROM products WHERE price < $max"
                query.Parameters.AddWithValue("$max", 10)
                Using reader = query.ExecuteReader()
                    While reader.Read()
                        Console.WriteLine($"{reader.GetString(0)}: {reader.GetDouble(1)}")
                    End While
                End Using
            End Using
        End Using
    End Sub
End Module

On the first run with an empty database, this prints:

Tea: 3.5

Always use parameters ($name, or @name for SQL Server) for values. Building SQL by joining strings with user input allows SQL injection attacks. The SQL course covers the queries themselves.

Higher-level options

• Dapper maps query results to your classes with one call: conn.Query(Of Product)("SELECT * FROM products").
• Entity Framework Core is a full ORM. Its tools generate C# code, but a VB application can use a model defined in a C# class library.
• Older Windows Forms apps often use DataSet/DataTable with designer-generated table adapters — you'll meet them when maintaining existing code.