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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Monica PSoftware 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.