SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure
SQL data to custom JSON.net structure SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
Go Back  Xtreme Visual Basic Talk > > > SQL data to custom JSON.net structure


Reply
 
Thread Tools Display Modes
  #1  
Old 05-31-2016, 08:55 AM
StealthRT's Avatar
StealthRT StealthRT is offline
Contributor
 
Join Date: Aug 2002
Posts: 785
Question SQL data to custom JSON.net structure


Hey all I have the following SQL query that outputs the following:
Code:
ID  | Name    | theRole | THrs | Mon   |....| trainH1 | train58 | train52 | train05 |etc...
===========================================================================================
152 | BOB M.  | Admin   | 40   | 1-9pm |....| 168dGrc0 | 89220E | 2FDEx56 | 5569CCz |......
Ideally I would like the structure to look like this [Note: With additional table names below]:
Code:
{
    "scheduleName": "",
    "firstName": "",
    "lastName": "",
    "theRole": "",
    "linker": "",
    "Schedule": {
        "ID": "",
        "totalHrs": "",
        "Mon": "",
        "Tue": "",
        "Wed": "",
        "Thu": "",
        "Fri": "",
        "Sat": ""
    },
    "empInfo": {
        "ID": "",
        "Email": "",
        "Phone": "",
        "Active": "",
        "Img": "",
        "Badge": ""
    },
    "availability": {
        "ID": "",
        "Mon": "",
        "Tue": "",
        "Wed": "",
        "Thu": "",
        "Fri": "",
        "Sat": ""
    },
    "training": {
        "?": "?"
    }
}
The train is where I am having issues. These values from the database can change names at any given time or be added or deleted. Currently I have around 35 values for this. Therefore, I am unable to hard code those values within my class function as I can do with ID, Name, theRole, THrs, etc...

The VB.net class definition looks like this so far:
Code:
Public Class Schedule
    Private m_ID As String
    Private m_totalHrs As String
    Private m_Mon As String
    Private m_Tue As String
    Private m_Wed As String
    Private m_Thu As String
    Private m_Fri As String
    Private m_Sat As String

    Public Property ID() As String
        Get
            Return m_ID
        End Get
        Set
            m_ID = Value
        End Set
    End Property

    Public Property totalHrs() As String
        Get
            Return m_totalHrs
        End Get
        Set
            m_totalHrs = Value
        End Set
    End Property

    Public Property Mon() As String
        Get
            Return m_Mon
        End Get
        Set
            m_Mon = Value
        End Set
    End Property

    Public Property Tue() As String
        Get
            Return m_Tue
        End Get
        Set
            m_Tue = Value
        End Set
    End Property

    Public Property Wed() As String
        Get
            Return m_Wed
        End Get
        Set
            m_Wed = Value
        End Set
    End Property

    Public Property Thu() As String
        Get
            Return m_Thu
        End Get
        Set
            m_Thu = Value
        End Set
    End Property

    Public Property Fri() As String
        Get
            Return m_Fri
        End Get
        Set
            m_Fri = Value
        End Set
    End Property

    Public Property Sat() As String
        Get
            Return m_Sat
        End Get
        Set
            m_Sat = Value
        End Set
    End Property
End Class

Public Class EmpInfo
    Private m_ID As String
    Private m_Email As String
    Private m_Phone As String
    Private m_Active As String
    Private m_Img As String
    Private m_Badge As String

    Public Property ID() As String
        Get
            Return m_ID
        End Get
        Set
            m_ID = Value
        End Set
    End Property

    Public Property Email() As String
        Get
            Return m_Email
        End Get
        Set
            m_Email = Value
        End Set
    End Property

    Public Property Phone() As String
        Get
            Return m_Phone
        End Get
        Set
            m_Phone = Value
        End Set
    End Property

    Public Property Active() As String
        Get
            Return m_Active
        End Get
        Set
            m_Active = Value
        End Set
    End Property

    Public Property Img() As String
        Get
            Return m_Img
        End Get
        Set
            m_Img = Value
        End Set
    End Property

    Public Property Badge() As String
        Get
            Return m_Badge
        End Get
        Set
            m_Badge = Value
        End Set
    End Property
End Class

Public Class Availability
    Private m_ID As String
    Private m_Mon As String
    Private m_Tue As String
    Private m_Wed As String
    Private m_Thu As String
    Private m_Fri As String
    Private m_Sat As String

    Public Property ID() As String
        Get
            Return m_ID
        End Get
        Set
            m_ID = Value
        End Set
    End Property

    Public Property Mon() As String
        Get
            Return m_Mon
        End Get
        Set
            m_Mon = Value
        End Set
    End Property

    Public Property Tue() As String
        Get
            Return m_Tue
        End Get
        Set
            m_Tue = Value
        End Set
    End Property

    Public Property Wed() As String
        Get
            Return m_Wed
        End Get
        Set
            m_Wed = Value
        End Set
    End Property

    Public Property Thu() As String
        Get
            Return m_Thu
        End Get
        Set
            m_Thu = Value
        End Set
    End Property

    Public Property Fri() As String
        Get
            Return m_Fri
        End Get
        Set
            m_Fri = Value
        End Set
    End Property

    Public Property Sat() As String
        Get
            Return m_Sat
        End Get
        Set
            m_Sat = Value
        End Set
    End Property
End Class

Public Class Training
    Private m_something1 As String
    Private m_something2 As String
    Private m_something3 As String

    Public Property something1() As String
        Get
            Return m_something1
        End Get
        Set
            m_something1 = Value
        End Set
    End Property

    Public Property something2() As String
        Get
            Return m_something2
        End Get
        Set
            m_something2 = Value
        End Set
    End Property

    Public Property something3() As String
        Get
            Return m_something3
        End Get
        Set
            m_something3 = Value
        End Set
    End Property
End Class

Public Class RootObject
    Private m_scheduleName As String
    Private m_firstName As String
    Private m_lastName As String
    Private m_theRole As String
    Private m_linker As String
    Private m_Schedule As Schedule
    Private m_empInfo As EmpInfo
    Private m_availability As Availability
    Private m_training As Training

    Public Property scheduleName() As String
        Get
            Return m_scheduleName
        End Get
        Set
            m_scheduleName = Value
        End Set
    End Property

    Public Property firstName() As String
        Get
            Return m_firstName
        End Get
        Set
            m_firstName = Value
        End Set
    End Property

    Public Property lastName() As String
        Get
            Return m_lastName
        End Get
        Set
            m_lastName = Value
        End Set
    End Property

    Public Property theRole() As String
        Get
            Return m_theRole
        End Get
        Set
            m_theRole = Value
        End Set
    End Property

    Public Property linker() As String
        Get
            Return m_linker
        End Get
        Set
            m_linker = Value
        End Set
    End Property

    Public Property Schedule() As Schedule
        Get
            Return m_Schedule
        End Get
        Set
            m_Schedule = Value
        End Set
    End Property

    Public Property empInfo() As EmpInfo
        Get
            Return m_empInfo
        End Get
        Set
            m_empInfo = Value
        End Set
    End Property

    Public Property availability() As Availability
        Get
            Return m_availability
        End Get
        Set
            m_availability = Value
        End Set
    End Property

    Public Property training() As Training
        Get
            Return m_training
        End Get
        Set
            m_training = Value
        End Set
    End Property
End Class
How can I create a structure for the json.net to serialize when I do not know what the data names will be? I was thinking of a type of List(as String) or even a Dictionary(String, String) but still am unsure how to go about adding those to the class and populating it using the sql data for those values.

So here is what I am asking:
Quote:
(1) How can I get the class structure to accept sql column names and values when I do not know what those table names will be?

(2) Able to form the json structure as I am wanting from the data in the class functions.

Last edited by StealthRT; 05-31-2016 at 09:13 AM.
Reply With Quote
  #2  
Old 05-31-2016, 01:32 PM
PlausiblyDamp's Avatar
PlausiblyDampSQL data to custom JSON.net structure PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

How much variety is there likely to be in the results? Are you mainly dealing with a fixed number of columns with consistent names or are even the number and name of columns liable to change?

Is it only the training part that is the problem or is the entire structure this flexible?
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
Reply

Tags
property, set, string, public, return, private, class, m_id, m_wed, m_mon, availability, m_thu, m_tue, m_fri, m_sat, values, empinfo, structure, names, mon, schedule, data, fri, sat, therole


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
 
SQL data to custom JSON.net structure
SQL data to custom JSON.net structure
 
-->