SQL Server JSON object and C#

Question

I have created a JSON object using SQL Server with the following query:

SELECT 
    Attribute 1 ,Attribute 2, Attribute 3,.... AS Identifier 
FROM 
    Table_1  tbl1_obj
INNER JOIN 
    Table_2 tbl2_obj ON tbl2_obj.Id = tbl1_obj.Id 
FOR JSON AUTO

I wanted to get a JSON object in C# from SQL Server. Wanted to know how ...

The same way you get any result, by using ADO.NET and executing a query. Since you return a string, you'll have to parse it using a JSON parser. If you didn't return a string you could read the individual fields directly. If you used an ORM like Dapper or Entity Framework you'd get strongly typed objects without the overhead of deserialization — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 20, 2022 at 10:44

Vivek Nuna · Accepted Answer · 2022-09-20 10:50:35Z

1

You can use Dapper, Its high-performance Micro-ORM. And your code should be similar to the one below. You can define properties and Use the proper overloaded method based on your requirement. The below code is just for reference.

public class Identifier 
{
    public int Attribute1;
    public int Attribute2;
    public int Attribute3;
}

using (var connection = new SqlConnection(_onpremisesFXecuteDBConnectionString))
{
    CommandDefinition command = new CommandDefinition(<YourQuery>, null, null, null, CommandType.Text);
    var identifier= connection.QueryFirstOrDefault<Identifier>(command);
}

Other options are to use Ado.Net code or EF as well, you can explore them as well.

answered Sep 20, 2022 at 10:50

Vivek Nuna

30.6k34 gold badges162 silver badges259 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server JSON object and C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related