如何从SQL Server存储过程结果中选择指定的列及其值并将其转换为JSON?

I am new to SQL Server. Here are my requirements:

我是SQL Server的新手。以下是我的要求:

Requirements:

要求:

I need to select specified column header and its values from SQL Server stored procedure result

我需要从SQL Server存储过程结果中选择指定的列标头及其值
The selected column header and its values are serialize into a JSON string object in SQL Server 2012

选择的列标头及其值被序列化为SQL Server 2012中的JSON字符串对象

What I did:

我所做的:

I need to monitor the table data changes in SQL Server Management Studio 2012. If any insert, delete and updates occur in the old table data, I need to send instant messages from SQL Server to a WCF service.

我需要监控SQL Server Management Studio 2012中的表数据更改。如果在旧表数据中出现任何插入、删除和更新，我需要将SQL Server中的即时消息发送到WCF服务。

To send instant messages, first of all, I need to select specified columns from the result set of a stored procedure, and serialize them as a JSON string.

要发送即时消息，首先，我需要从存储过程的结果集中选择指定的列，并将它们序列化为JSON字符串。

I am followed Change Data Capture feature in database and its table for tracking table data changes.

我跟踪数据库及其表中的更改数据捕获特性，以跟踪表数据的更改。

For the above, I created a database and table with Change Data Capture feature enabled. Then I inserted values in table data.

对于上述情况，我创建了一个数据库和表，其中启用了更改数据捕获功能。然后在表数据中插入值。

At last, using the stored procedure, I view the table data changes as result.

最后，使用存储过程查看表数据的更改。

My demo query:

我的演示查询:

DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_one')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT
    CT.__$start_lsn, CT.__$operation,
    CASE CT.__$operation
       WHEN 1 THEN 'Delete'
       WHEN 2 THEN 'Insert'
       WHEN 3 THEN 'Update - Pre'
       WHEN 4 THEN 'Update - Post'
    END AS Operation,
    CT.*,
    LSN.tran_begin_time, LSN.tran_end_time, LSN.tran_id
FROM
    cdc.fn_cdc_get_all_changes_dbo_one (@from_lsn, @to_lsn, N'all update old') AS CT 
INNER JOIN
    cdc.lsn_time_mapping AS LSN ON CT.__$start_lsn = LSN.start_lsn

From the above result, I need to select the type of operation, changed column header and its values.

从上面的结果中，我需要选择操作的类型、更改的列标题及其值。

After selecting, I need to serialize them all as a JSON string, then pass the JSON object from SQL Server 2012 to a WCF service via a POST method.

选择之后，我需要将它们序列化为JSON字符串，然后通过POST方法将JSON对象从SQL Server 2012传递给WCF服务。

How can I achieve this? Could anyone help me solve this?

我怎样才能做到这一点呢?谁能帮我解决这个问题吗?

1 个解决方案

#1

Actually it's simple. First you need to create an object like this:

实际上很简单。首先你需要创建这样的对象:

[DataContract]
public class Output
{
    [DataMember]
    public string Name { get; set; }
    [DataMember]
    public string ID { get; set; }
    .
    .
    .
    //all other parameters
}

Then put your stored procedure output into it. That's all. Then just return it with WCF service.

然后将存储过程输出放入其中。这是所有。然后返回WCF服务。

#1