VB.NET get data from ODBC as JSON

Question

I'm not very familiar with .NET so I'm not even sure I know how to ask this question in the proper terms, because I have to believe this is a common use case, but my google-fu is pathetic.

In PHP if I want to get data from a database, and format it as JSON I would use PDO something like this:

$query = $this->dbConn->prepare("SELECT * FROM Customers");
$query->execute();
$resultsSet = $query->fetchALL(PDO::FETCH_ASSOC);
foreach ( $resultsSet as $row  ){
    $json = json_encode( $row );
    //POST $json to web service or something
}

This will create JSON in the format of {"Column Name": "Column Value", "Column Name": "Column Value", ...} which is exactly what I want.

How would I do this in VB.NET? I can retrieve data from my datasource using OdbcDataReader like this:

Dim Conn As New OdbcConnection("[connection string]")
Dim Cmd As New OdbcCommand("SELECT * FROM Customers", Conn)
Dim Reader As OdbcDataReader
Dim serializer As New JavaScriptSerializer()

Conn.Open()
Reader = Cmd.ExecuteReader()

While (Reader.Read())
    json = serializer.Serialize(Reader).ToString 
    'this doesn't work - returns [{"FieldCount":126},{"FieldCount":126},{"FieldCount":126}...]  
    'POST json to web service
End While

Essentially what I need to do is periodically read straight through about 100 rather large files (million plus records) from a legacy application using that app's ODBC driver, converting it to JSON and posting it to a remote web service.

It's important that the JSON contains the name/value pairs in the form of "column name": "column value". I'm open to alternative suggestions.

I'm using VS 2017, .NET 4.6

Answer 1

Add Newtonsoft.Json nuget package to the project Instead of a reader use a DataAdapter and a DataTable

DataAdapter.Fill(DataTable)

Then pass the DataTable to this little function

Public Function DataTableToJSONWithJSONNet(table As DataTable) As String
        Dim JSONString As String = String.Empty
        JSONString = JsonConvert.SerializeObject(table)
        Return JSONString
End Function