SQL queries through VB.NET and JSON

Question

I'm completely new to both VB.NET and JSON, and I'm trying to figure out how to do SQL queries towards an SQL 2005 Express server and format the return value to JSON. I got the queries working using this (perhaps very newbie-like) code;

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
Imports System.Web.UI.WebControls

Partial Public Class SQLConnect
Inherits System.Web.UI.Page
'Defines SQL variables
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Dim ReadData As String
Dim i As Integer


Sub Click(ByVal s As Object, ByVal e As EventArgs)

    'Define SQL address, database, username and password
    con.ConnectionString = "Data Source=localhost\SQLEXPRESS;Initial Catalog=tempdb;User ID=tesst;Password=test"
    Try
        'Initialize connection
        con.Open()
        'Specify SQL query
        cmd = New SqlCommand("SELECT * FROM Member", con)
        'Execute query, dump result array into variable
        dr = cmd.ExecuteReader()
        messageLabel.Text = "<br />Connection established<br />"

        While (dr.Read)
            i = 0
            For i = 0 To dr.FieldCount - 1
                'Dump query results into a variable
                ReadData += dr.Item(i).ToString
            Next
        End While

        'Print query results
        messageLabel2.Text = ReadData
        'Close connection
        con.Close()
    Catch ex As Exception
        messageLabel.Text = "<br />Connection failed<br />"
    End Try


    End Sub
End Class

I have been looking at this, and I would love to see some code examples using this class or any other good method.

Any help would be much appreciated, any points and tips you might have.

Answer 1

It's pretty easy to use the JsonResult. You can just call the Json() method from your controller, and it JSONifies the object you pass to it, and gives you back a JsonResult. So here's an easy example, using your variable:

Public Class MyController
   Inherits Controller

   Public Function GetDataStuff As ActionResult
        ....
        ....
       Return Json(ReadData)
   End Function

End Class

Answer 2

You'd be better off looking at this on MS's site

JavaScriptSerializer Class

Its then simply a case of Serialize(Your Object)

You may find it easier to go straight from your data (as a datatable/set rather than reader) straight to JSON e.g Serialize(datatable)

and also have a look at

James Newton King's JSON.NET Library

Rick Strahl - JSON Serialization of a DataReader

Hope this helps

Answer 3

Yeah you should use JavaScriptSerializer. Besides the example on MS's help site you could download the asp.net mvc source code and see how JsonResult was implemented.