How does one return JSON from a datareader in a function


I have an SQL stored procedure that returns results based on a "like" comparison.
The results look like:

CustomerID      TheAddress
25                  "45 Main Street"
98                  "37 Main Street"
98                  "45 Main Avenue"

This is the VB code to get the data but I cannot know how to covert the resultant data to json format.

Maybe you could help.


  Protected Function GetSuggestedCustomers(ByVal FilterString As String)

        Dim cn As New SqlConnection
        Dim ConnString As String = "Data Source=WATCORP-SQLDBS0;Initial Catalog=museumBookings;Integrated Security=True"
        cn.ConnectionString = ConnString

        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "SuggestSearchForCustomers"

        cmd.Parameters.AddWithValue("@FilterString", FilterString)

        cmd.Connection = cn

        Dim TheJson As String
        Dim dr As SqlDataReader

        dr = cmd.ExecuteReader()

        While dr.Read()

                     'some code to translate the datareader to json
        End While

        Return TheJson


    End Function
Who is Participating?
Monica PConnect With a Mentor Software DeveloperCommented:
To convert any object or object list into JSON, we have to use the function JsonConvert.SerializeObject.
check this to Create JSON from C# using JSON Library

We can aslo append json format like string in the StringBuilder

Dim json As New StringBuilder()
Dim test As String

use this statement inside the While loop and make formatting as you needed

json.AppendFormat("{{""name"": ""{0}""}}", reader("name"))

At last u can view the formatted json like string value as below:

test = json.ToString
How do you want the data to appear? In other words, what will the value of TheJson be at the end of the function?

For starters, you will definitely want to move "cn.close()" above "Return TheJson".
I'd also expect that using a stringbuilder instead of a string might be wise if you are concatenating each value.

dim sbJSON as new system.text.stringbuilder
'I don't expect it is this simple but not sure what kind of delimiters you need to use for JSON
end while

return sbJSON.tostring
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.