I have a table with two column, I am trying to convert a query to an object with key value pairs to save it to a dictionary in c #.

C# code convert Json Object to dictionary

string JsonDictionary;
JsonConvert.DeserializeObject<Dictionary<string, string>>(JsonDictionary);

My table in sql server:

My query:

SELECT 
    C.[Key]
    ,C.[Value]
FROM dbo.Settings C
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Output query:

{
    "Key": "userName",
    "Value": "userNameValue"
},
{
    "Key": "password",
    "Value": "passwordValue"
},
{
    "Key": "domain",
    "Value": "domainValue"
}

Expected output:

{
    "userName": "userNameValue",
    "password": "passwordValue",
    "domain": "domainValue"
}

I've searched but can't find a solution

CodePudding user response：

SQL Server doesn't deal well with dynamic keys. You would need to do this with dynamic SQL:

DECLARE @paths nvarchar(max) = (
    SELECT STRING_AGG(
      QUOTENAME(c.[Key])   N' = MAX(CASE WHEN c.[Key] = '   QUOTENAME(c.[Key], '''')   ' THEN c.Value END)', ',
    ')

    FROM (SELECT DISTINCT [Key] FROM dbo.Settings) C
);

DECLARE @sql nvarchar(max) = N'
SELECT 
    '   @cols   '
FROM dbo.Settings C
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
';

PRINT @sql; --for testing

EXEC sp_executesql @sql;

CodePudding user response：

Have you tried to pivot your thinking?

create table dbo.Settings (
  [Key] nvarchar(20),
  [Value] nvarchar(20)
);

insert dbo.Settings ([Key], [Value]) values
  ('userName', 'userNameValue'),
  ('password', 'passwordValue'),
  ('domain', 'domainValue');

select [userName], [password], [domain]
from dbo.Settings src
pivot (max([Value]) for [Key] in ([userName], [password], [domain])) pvt
for json path, without_array_wrapper;

This yields the JSON result:

{
    "userName": "userNameValue",
    "password": "passwordValue",
    "domain": "domainValue"
}

CodePudding user response：

Since Post Owner is open for the C# solution.

When the data is from List<T>, you can use Enumerable.ToDictionary to convert to Dictionary<string, string>.

From List

using System.Linq;

// Result from DB
List<KeyValue> data = new List<KeyValue>
{
    new KeyValue
    {
        Key = "userName", Value = "userNameValue"
    },
    new KeyValue
    {
        Key = "password", Value = "passwordValue"
    },
    new KeyValue
    {
        Key = "domain", Value = "domainValue"
    }
};
        
Dictionary<string, string> dict = data.ToDictionary(x => x.Key, x => x.Value);

foreach (var kvp in dict)
{
    Console.WriteLine($"Key: {kvp.Key}, Value: {kvp.Value}");
}

public class KeyValue
{
    public string Key {get;set;}
    public string Value {get;set;}
}

Sample program (From List<T>)

When data is from DataTable, you iterate with dt.Rows and add into Dictionary<string, string>.

From DataTable

using System.Data;

#region Mock DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Key", typeof(string));
dt.Columns.Add("Value", typeof(string));
        
var rowOne = dt.NewRow();
rowOne["Key"] = "userName";
rowOne["Value"] = "userNameValue";
        
var rowTwo = dt.NewRow();
rowTwo["Key"] = "password";
rowTwo["Value"] = "passwordValue";
        
var rowThree = dt.NewRow();
rowThree["Key"] = "domain";
rowThree["Value"] = "domainValue";
        
dt.Rows.Add(rowOne);
dt.Rows.Add(rowTwo);
dt.Rows.Add(rowThree);
#endregion
        
Dictionary<string, string> dict = new Dictionary<string, string>();
        
foreach(DataRow row in dt.Rows)
{
    dict.Add(row["Key"].ToString(), row["Value"].ToString());
}
                
foreach (var kvp in dict)
{
    Console.WriteLine($"Key: {kvp.Key}, Value: {kvp.Value}");
}

Sample program (From DataTable)

CodePudding user response：

This is how you would do it if you are using EntityFramework:

var dict = _context.Settings.ToDictionary(k => k.Key, v => v.Value);

If you are using Dapper, then it would look something like this:

var dict = conn.Query<KeyValuePair<string, string>>("SELECT Key, Value FROM Settings")
  .ToDictionary(k => k.Key, v => v.Value);

Sample using the Northwind Database here: https://dotnetfiddle.net/22sZ7H

CodePudding user response：

Use concatenation to construct a simple json object

DECLARE
    @Settings VARCHAR(MAX);

SELECT 
    @Settings = COALESCE(@Settings   ', ', '')   '"'   C.[Key]   '":"'    C.[Value]   '"'
FROM dbo.Settings C

SELECT CONCAT('{', @Settings, '}')