title: Retrieving single AND multiple values from a Database in a loop into a Dictionary
body: <p>I'm currently retrieving a set of values using a loop of queries. The problem is that some queries are JOIN sentences that return a set of values instead of single one and thus ExecuteScalar method won't work for me.</p>
  <p>This is my loop.<br />
  '<strong>allQueries</strong>' is a <strong>List of class 'Queries'</strong> (Properties: name, db_engine, query) that contains every SQL query that is going to be used.<br />
  '<strong>queryData</strong>' is a <strong>Dictionary</strong> that will be filled with the results of all the  queries.</p>
  <pre><code>using( SqlConnection connection = new SqlConnection( builder.ConnectionString ) )
  {
     try { connection.Open(); } catch( Exception ex ) { Console.WriteLine( &quot;[&quot; + DateTime.Now + &quot;] ERROR: &quot; + ex.Message ); }
  
     Dictionary&lt;string, string&gt; queryData = new Dictionary&lt;string, string&gt;();
  
     foreach( dynamic query in allQueries )
     {
          // Execute every query and fill the 'queryData' Dictionary with the results of each one.
          SqlCommand cmd = new SqlCommand( query.query, connection);
          try
          {
              queryData.Add( query.name, Convert.ToString( cmd.ExecuteScalar() ) );
          }
          catch( Exception ex )
          {
              Console.WriteLine( &quot;[&quot; + DateTime.Now + &quot;] ERROR: &quot; + ex.Message );
              Console.WriteLine( &quot;[&quot; + DateTime.Now + &quot;] DEBUG: Query &lt;&lt;&quot; + query.name + &quot;&gt;&gt;&quot; );
          }
     }
  }
  </code></pre>
  <p>This is an example of one of the queries that return a set of values, it basically returns a 3 column table with 10 rows <strong>(It's SQL Server 2008 R2)</strong>:</p>
  <pre><code>BEGIN
  DECLARE @LunesSem0 DATETIME
  DECLARE @DomSem0 DATETIME
  SET @LunesSem0 = DATEADD( HOUR, 6, DATEADD(wk, DATEDIFF(wk, 0, CURRENT_TIMESTAMP), 0) )
  SET @DomSem0 = DATEADD( SECOND, -1, DATEADD(HOUR, 6, DATEADD(wk, DATEDIFF(wk, 0, 
  CURRENT_TIMESTAMP), 7) ) )
  END
  
  SELECT TOP 10 productos.descripcion AS descripcion,
  sum(cheqdet.cantidad) AS cantidad,
  sum(cheqdet.cantidad * (cheqdet.precio - (cheqdet.precio * (cheqdet.descuento/100)))) AS monto
  FROM productos
  INNER JOIN cheqdet ON cheqdet.idproducto = productos.idproducto
  INNER JOIN grupos ON productos.idgrupo = grupos.idgrupo
  WHERE grupos.clasificacion = '2' AND cheqdet.hora BETWEEN  @LunesSem0 AND @DomSem0
  GROUP BY cheqdet.idproducto,productos.descripcion
  ORDER BY cantidad DESC
  </code></pre>
  <p>I need the program to get that set of values and turn them into a JSON so it can fit in a single value in its corresponding cell in <strong>queryData</strong>. If there's a solution in SQL that'll work for me too.</p>
  <p>Thanks in advance</p>