SQL数据到自定义JSON.net结构

嘿，我有下面的SQL查询，它输出以下内容：

ID  | Name    | theRole | THrs | Mon   |....| trainH1 | train58 | train52 | train05 |etc...
===========================================================================================
152 | BOB M.  | Admin   | 40   | 1-9pm |....| 168dGrc0 | 89220E | 2FDEx56 | 5569CCz |......

理想情况下，我希望结构看起来像这样[注意：下面有其他表名]：

{
"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": {
"?": "?"
}
}

列车是我遇到问题的地方。数据库中的这些值可以在任何给定时间更改名称，也可以添加或删除。目前我对此有大约35个值。因此，我无法像使用ID、Name和theRole以及THrs等一样，对类函数中的那些值进行硬编码

VB.net类定义到目前为止是这样的：

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

当我不知道数据名称是什么时，我如何创建一个供json.net序列化的结构？我想的是一种类型的列表(作为字符串)，甚至是一个字典(字符串，字符串)但仍然不确定如何将它们添加到类中，并使用这些值的sql数据填充它。

所以我要问的是：

(1)当我不知道sql列名和值是什么时，如何让类结构接受这些列名和值？
(2) 能够根据类函数中的数据形成我想要的json结构。

最初我认为Dictionary(Of String, String)非常适合此项，但从您的评论中可以清楚地看出，培训项目可能存在重复的列名。正因为如此，Dictionary在这里不起作用，而且，您想要的JSON格式也不起作用。您需要将JSON的training部分更改为对象数组，以便处理潜在的重复项。

以下是我认为你可以让它发挥作用的方法：

将Training类更改为具有两个公共属性Name和Value。

在RootObject类中，将training属性定义为List(Of Training)，而不是单个Training。

Public Class RootObject
...
Private m_training As List(Of Training)
...
Public Property training() As List(Of Training)
Get
If m_training Is Nothing Then
m_training = New List(Of Training))
End If
Return m_training
End Get
Set(value As List(Of Training))
m_training = value
End Set
End Property
End Class

在SQL查询中，请确保所有与训练相关的列都位于SELECT子句的最后。由于您说过列名不共享一个通用前缀，所以我只能通过位置来识别它们。
```
SELECT ID, Name, theRole, THrs, Mon, ..., EMPTR.*
FROM ...
```

在读取器循环中，填充行的所有已知属性后，可以循环读取器中的其余字段，以填充该行的培训项目列表。

Const firstTrainingColumnIndex As Integer = 5 'Adjust as necessary for your actual data '
Dim i As Integer
For i = firstTrainingColumnIndex To reader.FieldCount - 1
trainingItem.Name = reader.GetName(i)
trainingItem.Value = IIf(reader.IsDBNull(i), Nothing, reader.GetString(i))
rootObj.training.Add(trainingItem)
Next

要创建JSON输出，只需像往常一样进行序列化即可。Json.Net已经知道如何处理List。

Dim json as String = JsonConvert.SerializeObject(rootObj, Formatting.Indented)

输出结果应该是这样的：

{
"scheduleName": "Bob M",
...
"training": [
{
"Name": "trainH1",
"Value": "168dGrc0"
},
{
"Name": "train58",
"Value": "89220E"
},
{
"Name": "train52",
"Value": "2FDEx56"
},
{
"Name": "train05",
"Value": "5569CCz"
}
]
}

相关内容

最新更新

热门标签：