JoachimPetersen
asked on
ASP .net JSON webservice - Problems with returning multiple results (select sql output)
Hello, I am having troubles with modifying this code to return multiple sql select output in json:
Thank you in advance
Imports System.IO
Imports System.Runtime.Serialization
Imports System.ServiceModel
Imports System.ServiceModel.Activation
Imports System.Data.SqlClient
Imports System.Runtime.Serialization.Json
<ServiceContract([Namespace]:="")> _
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class GetFriends
Inherits System.Web.UI.Page
<OperationContract()> _
Public Function GetFriendLocation(friendID As Integer) As String
Dim friendinfo As New FriendInfo()
Dim connectionString As String = "server=localhost;uid=sa;pwd=thiru;database=AdventureWorks;"
Using connection As New SqlConnection(connectionString)
Dim sql As String = "Select FullName, FriendLocation from Friend.Place " & " Where FriendID = " & friendID.ToString()
connection.Open()
Dim command As New SqlCommand(sql, connection)
Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
friendinfo.FriendName = reader("FriendName").ToString()
friendinfo.FriendLocation = reader("FriendLocation").ToString()
friendinfo.FriendID = friendID
End While
End Using
Dim stream As New MemoryStream()
Dim serializer As New DataContractJsonSerializer(GetType(FriendInfo))
serializer.WriteObject(stream, friendinfo)
stream.Position = 0
Dim streamReader As New StreamReader(stream)
Return streamReader.ReadToEnd()
End Function
End Class
<DataContract()> _
Public Class FriendInfo
<DataMember()> _
Public FriendID As Integer
<DataMember()> _
Public FriendName As String
<DataMember()> _
Public FriendLocation As String
End Class
I am getting a return like this(it selects the last as it is not multiple): {
"FriendID": "11102",
"FriendName": "Darwin NoName"
"FriendLocation": "Fakestreet 211"
}
I would like a result like this(multiple in a json tag named data):{
"data": [
{
"FriendID": "11101",
"FriendName": "Newton NoName"
"FriendLocation": "Fakestreet 3321"
},
{
"FriendID": "11102",
"FriendName": "Darwin NoName"
"FriendLocation": "Fakestreet 211"
}
]
}
How can this be done, I can not get this to work.Thank you in advance
ASKER
Very good answer, I have tried something simular to this but failed.
when I try your provided code I get this error: "Value of type 'FriendInfo' cannot be converted to '1-dimensional array of FriendInfo'" at line "friendinfoList.Add(friend info)"
I am using framework 4.0
when I try your provided code I get this error: "Value of type 'FriendInfo' cannot be converted to '1-dimensional array of FriendInfo'" at line "friendinfoList.Add(friend
I am using framework 4.0
Can you please supply your exact code?
ASKER
Imports System.IO
Imports System.Runtime.Serialization
Imports System.ServiceModel
Imports System.ServiceModel.Activation
Imports System.Data.SqlClient
Imports System.Runtime.Serialization.Json
Imports System.Collections.Generic
<ServiceContract([Namespace]:="")> _
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class App_Default
Inherits System.Web.UI.Page
<OperationContract()> _
Public Function GetFriendLocation(ByVal friendID As Integer) As String
Dim friendinfoList As New List(Of FriendInfo())
Dim connectionString As String = "server=localhost;uid=sa;pwd=thiru;database=AdventureWorks;"
Using connection As New SqlConnection(connectionString)
Dim sql As String = "Select FullName, FriendLocation from Friend.Place " & " Where FriendID = " & friendID.ToString()
connection.Open()
Dim command As New SqlCommand(sql, connection)
Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
Dim friendinfo As New FriendInfo()
friendinfo.FriendName = reader("FriendName").ToString()
friendinfo.FriendLocation = reader("FriendLocation").ToString()
friendinfo.FriendID = friendID
friendinfoList.Add(friendinfo)
End While
End Using
'Dim stream As New MemoryStream()
'Dim serializer As New DataContractJsonSerializer(GetType(FriendInfo))
'serializer.WriteObject(stream, friendinfo)
'stream.Position = 0
'Dim streamReader As New StreamReader(stream)
'Return streamReader.ReadToEnd()
End Function
End Class
<DataContract()> _
Public Class FriendInfo
<DataMember()> _
Public FriendID As Integer
<DataMember()> _
Public FriendName As String
<DataMember()> _
Public FriendLocation As String
End Class
Error: Value of type 'FriendInfo' cannot be converted to '1-dimensional array of FriendInfo'. at "friendinfoList.Add(friend
A small error while copying and pasting....
should be
The parenthesis mean that the List would expect Arrays of FriendInfo.
Giannis
Dim friendinfoList As New List(Of FriendInfo())
should be
Dim friendinfoList As New List(Of FriendInfo)
The parenthesis mean that the List would expect Arrays of FriendInfo.
Giannis
ASKER
Okay, when using a List(T), how would you then convert it into an JSON result, any automatic way ot should it be done by looping through each item?
After a quick search I saw an example, but something is wrong with the system.web.extension dll as it cannot find it, I can add it to reference but it can not find it when I write Import System.Web.Extensions
here is the code I would use if it worked, any similar method that will work?
After a quick search I saw an example, but something is wrong with the system.web.extension dll as it cannot find it, I can add it to reference but it can not find it when I write Import System.Web.Extensions
here is the code I would use if it worked, any similar method that will work?
Dim jSearializer As New System.Web.Script.Serialization.JavaScriptSerializer()
Return jSearializer.Serialize(friendinfoList)
The following seems to work:
Giannis
Dim stream As New MemoryStream()
Dim serializer As New DataContractJsonSerializer(GetType(List(Of FriendInfo)))
serializer.WriteObject(stream, friendinfoList)
stream.Position = 0
Dim streamReader As New StreamReader(stream)
Return streamReader.ReadToEnd()
Giannis
ASKER
got any advice on adding them under a tag called data?
The output is:
The output is:
[
{
"FriendID": "11101",
"FriendName": "Newton NoName"
"FriendLocation": "Fakestreet 3321"
},
{
"FriendID": "11102",
"FriendName": "Darwin NoName"
"FriendLocation": "Fakestreet 211"
}
]
I would like it to return it like:{
"data": [
{
"FriendID": "11101",
"FriendName": "Newton NoName"
"FriendLocation": "Fakestreet 3321"
},
{
"FriendID": "11102",
"FriendName": "Darwin NoName"
"FriendLocation": "Fakestreet 211"
}
]
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, very helpfull and very good solutions
One major issue i see and it leads to your problem is the way you handle your data.
In your code you have:
Open in new window
This creates one instance of a FriendInfo object.Then you iterate through your db results in your While...End While loop:
Open in new window
What you do here is getting this one instance and changing its properties with each record you get from your DB, so you finally end up with the last record of your DB.
I would suggest defining a List(Of FriendInfo) and then adding in it each new FriendInfo instances.
Open in new window
Then you end up with a List of your objects. Even better would be to use LINQ, but this is out of scope.
Then you would serialize your List object in JSON.
One last thing would be a suggestion on a change on your code.
The way you fetch data from your db could be prone to injection:
Open in new window
It would be better if you would use SQLParameters:
Open in new window
Giannis