How to convert SQL Server query to JSON

Question

I need to build a table in a view using the result of this SQL Query

    SELECT f.empresaid,
       e.fantasia                  AS Empresa,
       f.filialid,
       f.fantasia                  AS Filial,
       u.consultorid               AS ConsultorID,
       cs.nome                     AS Consultor,
       ''                          AS Midia,
       fp.campoextra,
       Count(DISTINCT p.usuarioid) AS Total,
       Count(DISTINCT u.ativacao)  AS TotalAtivos,
       Iif(Count(DISTINCT p.usuarioid) > 0, ( Cast(
       Count(DISTINCT u.ativacao) AS DECIMAL) / Count(DISTINCT p.usuarioid) ), 0
       ) * 100
                                   AS Porcentagem
FROM   formas_pagamento fp
       INNER JOIN usuarios u
               ON u.forma_pagamentoid = fp.forma_pagamentoid
       INNER JOIN pagamentos p
               ON p.usuarioid = u.usuarioid
                  AND p.tipo = 'MTU'
       INNER JOIN cartoes c
               ON c.usuarioid = u.usuarioid
                  AND c.dependenteid = 0
       INNER JOIN midias m
               ON m.descricao = u.midia
       INNER JOIN consultores cs
               ON cs.consultorid = u.consultorid
       INNER JOIN filiais f
               ON f.filialid = u.filialid
       INNER JOIN empresas e
               ON e.empresaid = f.empresaid
WHERE  c.dependenteid = 0
       AND u.cadastro > '2019-07-01'
       AND u.cadastro < '2019-08-01'
       AND u.filialid = 3
GROUP  BY f.empresaid,
          e.fantasia,
          f.filialid,
          f.fantasia,
          u.consultorid,
          cs.nome,
          fp.campoextra
ORDER  BY empresaid,
          filialid,
          consultor,
          campoextra

Which gives me this result, the table in the view should be created with the structure as follows table structure and organization. I was trying to load this query data directly from my ViewModel List but that went heavy, if I could have a json array as following : JSON I could build the table with that desired structure more easily, can anyone help ? Thanks !

----------------------------------EDIT-------------------------------------

public List<ViewModelOrigemEConsultor> GetConsultorOrigem ()
    {
        var Lista = new List<ViewModelOrigemEConsultor>();
        /*string SQL = SQL QUERY GOES HERE */

        var cnn = CnnDB.OpenDB();
        using (cnn)
        {
            var cmd = new SqlCommand(SQL, cnn);
            cnn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                decimal Porcentagem = 0;
                while (reader.Read())
                {
                    Porcentagem = (decimal)(Convert.ToInt32(reader["TotalAtivos"]) / Convert.ToInt32(reader["Total"]));
                    Lista.Add(new ViewModelOrigemEConsultor
                    {
                        EmpresaID = Convert.ToInt32(reader["EmpresaID"]),
                        Empresa = reader["Empresa"].ToString(),
                        FilialID = Convert.ToInt32(reader["FilialID"]),
                        Filial = reader["Filial"].ToString(),
                        ConsultorID = Convert.ToInt32(reader["ConsultorID"]),
                        Consultor = reader["Consultor"].ToString(),
                        Midia = reader["Midia"].ToString(),
                        FormaPagamento = reader["FormaPagamento"].ToString(),
                        Total = Convert.ToString(string.Format("{0:N}", reader["Total"])),
                        TotalAtivos = Convert.ToString(string.Format("{0:N}", reader["TotalAtivos"])),
                        Porcentagem = Convert.ToString(string.Format("{0:N}", Porcentagem))
                    });
                };
            }

        }; 

        return Lista; 
    }

MODEL

public class ViewModelOrigemEConsultor
{
    public int EmpresaID { get; set; }
    public string Empresa { get; set; }
    public int FilialID { get; set; }
    public string Filial { get; set; }
    public int ConsultorID { get; set; }
    public string Consultor { get; set; } 
    public string Midia { get; set; }
    public string FormaPagamento { get; set; }
    public string Total { get; set; }
    public string TotalAtivos { get; set; }
    public string Porcentagem { get; set; }
}

You need to create a model/class array representing that structure and populate it with your query. After that, it's a simple case of using something like JSON.NET to serialise the class into JSON. — Steve, Sep 18 '19 at 14:29
See this post for a hint : https://stackoverflow.com/questions/6201529/how-do-i-turn-a-c-sharp-object-into-a-json-string-in-net — Steve, Sep 18 '19 at 14:30
This site will convert the JSON you already have to a c# class. http://json2csharp.com/ — Steve, Sep 18 '19 at 14:33
Have you tried to use Newtonsoft.Json? Here's some example: https://www.newtonsoft.com/json/help/html/SerializingJSON.htm — Ivan-San, Sep 18 '19 at 16:29

score 0 · Answer 1 · answered Sep 18 '19 at 16:45

If you've got the list of objects populated and are just looking to convert it to JSON simply use something like https://www.nuget.org/packages/Newtonsoft.Json and then serialize the list to json. In this case it would be similar to:

var json = JsonConvert.SerializeObject(model);

How to convert SQL Server query to JSON

1 Answers1