嘿所有我有以下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创建一个序列化结构？ 我在考虑一种List（作为String）或者甚至是Dictionary（String，String），但仍然不确定如何将这些添加到类中并使用sql数据为这些值填充它。
所以这就是我要问的问题：
（1）当我不知道那些表名是什么时，如何让类结构接受sql列名和值？
（2）能够根据类函数中的数据形成json结构。
Hey all I have the following SQL query that outputs the following:
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]:
{ "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:
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 ClassHow 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:
(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.
最满意答案
最初我认为Dictionary(Of String, String)对此非常完美，但从您的评论中可以清楚地看出，培训项目可能存在重复的列名。 因此，字典在这里不起作用，而且，您想要的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" } ] }Originally I was thinking a Dictionary(Of String, String) would be perfect for this, but it has become clear from your comments that there could be duplicate column names for the training items. Because of this, a Dictionary won't work here, and moreover, the JSON format you want won't work either. You will need to change the training section of the JSON to an array of objects in order to handle the potential duplicates.
Here's how I'm thinking you can make it work:
Change your Training class to have two public properties, Name and Value.
In your RootObject class, define the training property as List(Of Training) instead of a single 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 ClassIn your SQL query, make sure that all the training-related columns come last in the SELECT clause. Since you said the column names don't share a common prefix, they only way I can see to identify them is by position.
SELECT ID, Name, theRole, THrs, Mon, ..., EMPTR.* FROM ...In your reader loop, after populating all the known properties for the row, you can loop over the remaining fields in the reader to populate the list of training items for that row.
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) NextTo create the JSON output, just serialize like you normally would. Json.Net already knows how to handle a List.
Dim json as String = JsonConvert.SerializeObject(rootObj, Formatting.Indented)The output should end up looking like this:
{ "scheduleName": "Bob M", ... "training": [ { "Name": "trainH1", "Value": "168dGrc0" }, { "Name": "train58", "Value": "89220E" }, { "Name": "train52", "Value": "2FDEx56" }, { "Name": "train05", "Value": "5569CCz" } ] }更多推荐
发布评论