SQL到JSON

休闲1980 · 2020年12月23日

你好

我有一个存储过程，它返回如下数据：

SQL:

CREATE TABLE [dbo].[test](
    [date1] [datetime] NULL,
    [number] [int] NOT NULL,
    [id] [int] NULL,
    [Description] [varchar](max) NULL,
    [date2] [datetime] NULL,
    [rowVersion] [nvarchar](max) NULL,
    [date3] [datetime] NULL,
    [id2] [int] NULL,
    [code] [varchar](10) NULL,
    [type] [varchar](10) NULL,
    [date4] [datetime] NULL,
    [Owned] [varchar](3) NULL,
    [RowVersion2] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[test] ([date1], [number], [id], [Description], [date2], [rowVersion], [date3], [id2], [code], [type], [date4], [Owned], [RowVersion2])

VALUES (CAST(N'2018-11-09T17:57:42.220' AS DateTime), 123456, 5566, N'Full description', CAST(N'2018-11-09T17:57:42.220' AS DateTime), N'0x000000000T49G19B', CAST(N'2018-12-27T14:21:42.570' AS DateTime), 1001, N'101', N'1', CAST(N'2020-01-24T16:17:09.287' AS DateTime), N'Yes', N'0x000000000E1TST89')
GO
INSERT [dbo].[test] ([date1], [number], [id], [Description], [date2], [rowVersion], [date3], [id2], [code], [type], [date4], [Owned], [RowVersion2])

VALUES (CAST(N'2018-11-09T17:57:42.220' AS DateTime), 123456, 5566, N'Full description', CAST(N'2018-11-09T17:57:42.220' AS DateTime), N'0x000000000T49G19B', CAST(N'2018-12-27T14:21:42.570' AS DateTime), 1004, N'202', N'2', CAST(N'2020-01-24T16:17:09.287' AS DateTime), N'Yes', N'0x000000000E1TST89')
GO
INSERT [dbo].[test] ([date1], [number], [id], [Description], [date2], [rowVersion], [date3], [id2], [code], [type], [date4], [Owned], [RowVersion2])

VALUES (CAST(N'2018-11-09T17:57:42.220' AS DateTime), 123456, 5566, N'Full description', CAST(N'2018-11-09T17:57:42.220' AS DateTime), N'0x000000000T49G19B', CAST(N'2018-12-27T14:21:42.570' AS DateTime), 1010, N'303', N'3', CAST(N'2020-01-24T16:17:09.287' AS DateTime), N'Yes', N'0x000000000E1TST89')
GO

我正在尝试以以下格式返回数据：

JSON:

{
    "section1": {
        "date1": "2018-11-09",
        "id": "5566",
        "Description": "Full description",
        "date2": "2018-11-09T17:57:42",
        "rowVersion": "0x000000000T49G19B"
    },
    "section2": {
        "date3": "2018-11-09",
        "codes": [
            {
                "id2": "1001",
                "Code": "101",
                "Type": "1"
            },
            {
                "id2": "1004",
                "Code": "202",
                "Type": "2"
            },
            {
                "id2": "1010",
                "Code": "303",
                "Type": "3"
            }
        ]
    },
    "section3": {
        "date4": "2020-01-24",
        "owned": "true",
        "rowVersio2": "0x000000000E1TST89"
    }
}

到目前为止，我已经检索了数据集中的数据，但是我在JSON转换方面苦苦挣扎。

我试过了

C#:

var serializedMyObjects = JsonConvert.SerializeObject(sqlResults.Tables[0]);
            var returnData = JsonConvert.DeserializeObject<Activities[]>(serializedMyObjects);

但这并没有给我想要的结果。谁能指出我正确的方向？

谢谢
保罗

SQL到JSON

休闲1980

新成员