CODEWITHSUNDEEP
sql-serverjsonwcfstored-procedures
Thiru Arasu
Thiru Arasu

Reputation: 293

How to select specified columns and its values from SQL Server stored procedure result and convert them into JSON?

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

Requirements:

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

  2. The selected column header and its values are serialize into a JSON string object in SQL Server 2012

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.

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.

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

Demo SP Result

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.

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

Upvotes: 2

Views: 413

Answers (1)

Mohammad
Mohammad

Reputation: 2764

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.

Upvotes: 1

Related Questions