[rgfirefly24]

Here is an example of a Class that will fit into what you want:

public class Dog
    public property Name as String
    public property Breed as String
    public property Tricks as List(of Trick)
    public property Age as Integer
    .
    .
    .
End Class

now if i wanted to have a list of dogs I would do this

Dim dogs as new List(Of Dog)

then lets say I'm reading in from reader a record set of Dogs that was stored in the database:

While reader.Read()
    dogs.Add(new Dog(){ Name = reader("Name"), Breed = reader("Breed"), Tricks = new List(Of Trick), Age = reader("Age")})
End While

So now if i wanted to turn that into Json i could call this:

Dim jss as new JavascriptSerializer()

string jsonObject = jss.Serialize(dogs)

if i had 2 dogs:
Name= Fido, Breed=Yorkie, Tricks = RollOver, Age = 10
Name = Rex, Breed= BullDog - English, Tricks= Bite, Age = 2

This is what the output would look like as a JSON Object:

[{"Name":"Fido","Breed":"Yorkie","Tricks":[{"Name":"RollOver"}],"Age":"10"},{"Name":"Rex","Breed":"BullDog - English","Tricks":[{"Name":"Bite"}],"Age":"2"}]

This post has been edited by rgfirefly24: 23 October 2012 - 01:11 PM

[maddtechwf]

Okay, so evidently I'm doing something wrong. I have added the following code but I keep getting the blue squigly lines under the FirstName, LastName, and email wanting me to declare them as objects.

    Public Class Employee
        Dim firstName As String
        Dim lastName As String
        Dim email As String
    End Class

    Public Function Staff(param As String) As String Implements CentralService.Staff
        SQLStr = "SELECT Name_First, Name_Last, email_MSU FROM tbl_people WHERE Name_First LIKE '%" & param & "%' OR Name_Last LIKE '%" & param & "%'"
        Dim SQLConn As New SqlConnection
        Dim SQLCmd As New SqlCommand
        Dim SQLdr As SqlDataReader
        Dim jss As JavascriptSerializer
        Dim Employees As New List(Of Employee)
        Dim StaffList As String = ""

        jss = New JavascriptSerializer()

        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = SQLStr

        SQLConn.ConnectionString = ConnString 'Set the Connection String
        SQLConn.Open() 'Open the connection

        SQLdr = SQLCmd.ExecuteReader

        Try

            While SQLdr.Read()
                Employees.Add(New Employee() {FirstName = SQLdr.Item("Name_First"), lastName = SQLdr.Item("Name_Last"), email = SQLdr.Item("email_MSU")})
                'StaffList += jss.Serialize(SQLdr.Item("Name_First") & " , " & SQLdr.Item("Name_Last") & " , " & SQLdr.Item("email_MSU"))
            End While
        Catch ex As Exception
            Return ex.ToString
        End Try

        SQLdr.Close()
        SQLConn.Close()

        Return StaffList

    End Function

[rgfirefly24]

take that class and put it in a separate class file called Employee.vb

Also you cant just return staffList, you have to serialize it


Change line 39 from return StaffList to return jss.Serialize(StaffList)

This post has been edited by rgfirefly24: 23 October 2012 - 02:18 PM

[maddtechwf]

I did that. I'm still getting prompted to either: Generate Method, Generate Property, or Field for firstName, lastName, and email.

[rgfirefly24]

What version of .NET are you running? If it is an earlier version then 4.0 then you will need to change your class to the old way of creating properties

Class Example
    Private _count As Integer

    Public Property Number() As Integer
	Get
	    Return _count
	End Get
	Set(ByVal value As Integer)
	    _count = value
	End Set
    End Property
End Class

[maddtechwf]

I'm running .NET 4.0 for this project.

[h4nnib4l]

@rgfirefly24 - looks like you were crossing up C# and VB syntax on your object initializer. I had to look it up, I have almost no experience with VB. To continue with your example:

dogs.Add(New Dog() With {
            .Name = reader("Name"),
            .Breed = reader("Breed"),
            .Tricks = New List(Of Trick),
            .Age = reader("Age")
        })

This post has been edited by h4nnib4l: 24 October 2012 - 05:22 AM

[rgfirefly24]

yea, I'm a C# developer so trying to give examples in VB will often times get mixed up. My apologies maddtechwf for that. Take h4nnib4l's snippet and put it in your while loop and it should work.

[maddtechwf]

Hey guys, sorry about that. I probably should have said that I was working in VB.NET. My apologies. I know that some of my fields are going to have a NULL value in them. What would be the best way to with with that.

[maddtechwf]

I got the DBNull figured out with a IsDBNull check before adding it to the Employees. I was looking at my data output and I noticed that I have a bunch of \ in the data. Why is that?

[h4nnib4l]

So am I rgfirefly24, I shudder every time I see the word Dim.

Where exactly are you seeing these forward slashes? Are they coming from the DB? Are you accidentally overwriting values in your Employee instances' properties?

This post has been edited by h4nnib4l: 24 October 2012 - 07:04 AM

[maddtechwf]

I've gone through my code and can't find where the \'s are coming from. Here is an example of the data output.

"[{\"firstName\":\"John\",\"lastName\":\"Doe\",\"email\":\"john.doe@me.net\",\"photo\":\"John.Doe.jpg\"}]"

[h4nnib4l]

Will you post the code where you're formatting it like that? I suspect that there's something going on there, because if the slashes aren't in the DB, then they won't be in the Employee instances' properties.

Also, I just took a closer look at your query, and saw that you're concatenating the query parameters rather than using SQL parameters. Check out Curtis Rutland's tutorial on parameterizing your queries. It's cleaner, easier to modify, and it protects you from SQL injection.

[rgfirefly24]

You are looking at a string object. A string is surrounded by double quotes. Because JSON also uses double quotes to denote an object and it's data, when you view the string the Quotes within the JSON string have to be escaped. I would say that the escape character is being added by the .Serialize method.

In doing some testing, I'm not sure where the \s are coming from actually. When I look at my output of .Serialize it doesn't have the escape character. What I'll do now is try and build your environment as closely as possible and see if i can't reproduce it. I do have one question though. The JSON string you supplied. Where did you get it from?

This post has been edited by rgfirefly24: 24 October 2012 - 07:40 AM

[maddtechwf]

This is my Service.svc.vb

'**************************************************************
    '
    '                       Staff Functions
    '
    '**************************************************************
    Public Function Staff(param As String) As String Implements CentralService.Staff
        SQLStr = "SELECT Name_First, Name_Last, email_MSU, PhotoFileName FROM tbl_people WHERE Name_First LIKE '%" & param & "%' OR Name_Last LIKE '%" & param & "%'"
        Dim SQLConn As New SqlConnection
        Dim SQLCmd As New SqlCommand
        Dim SQLdr As SqlDataReader
        Dim jss As JavascriptSerializer
        Dim Employees As New List(Of Employee)
        Dim StaffList As String = ""

        Dim FName As String = ""
        Dim LName As String = ""
        Dim Email As String = ""
        Dim PhotoURL As String = ""

        jss = New JavascriptSerializer()

        SQLCmd.Connection = SQLConn
        SQLCmd.CommandText = SQLStr

        SQLConn.ConnectionString = ConnString 'Set the Connection String
        SQLConn.Open() 'Open the connection

        SQLdr = SQLCmd.ExecuteReader

        Try
            While SQLdr.Read()
                If IsDBNull(SQLdr.Item("Name_First")) Then
                    FName = ""
                Else
                    FName = SQLdr.Item("Name_First")
                End If
                If IsDBNull(SQLdr.Item("Name_Last")) Then
                    LName = ""
                Else
                    LName = SQLdr.Item("Name_Last")
                End If
                If IsDBNull(SQLdr.Item("email_MSU")) Then
                    Email = ""
                Else
                    Email = SQLdr.Item("email_MSU")
                End If
                If IsDBNull(SQLdr.Item("PhotoFileName")) Then
                    PhotoURL = ""
                Else
                    PhotoURL = SQLdr.Item("PhotoFileName")
                End If
                Employees.Add(New Employee() With {.firstName = FName, .lastName = LName, .email = Email, .photo = PhotoURL})
            End While
        Catch ex As Exception
            Return ex.ToString
        End Try

        SQLdr.Close()
        SQLConn.Close()

        Return jss.Serialize(Employees)

    End Function

This is my Employee.vb

Public Class Employee
    Public Property firstName As String
    Public Property lastName As String
    Public Property email As String
    Public Property photo As String
End Class

This post has been edited by maddtechwf: 24 October 2012 - 07:32 AM