CODEWITHSUNDEEP
c#sql-server
Victor Olvera Cardoso
Victor Olvera Cardoso

Reputation: 51

Retrieving single AND multiple values from a Database in a loop into a Dictionary

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.

This is my loop.
'allQueries' is a List of class 'Queries' (Properties: name, db_engine, query) that contains every SQL query that is going to be used.
'queryData' is a Dictionary that will be filled with the results of all the queries.

using( SqlConnection connection = new SqlConnection( builder.ConnectionString ) )
{
   try { connection.Open(); } catch( Exception ex ) { Console.WriteLine( "[" + DateTime.Now + "] ERROR: " + ex.Message ); }

   Dictionary<string, string> queryData = new Dictionary<string, string>();

   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( "[" + DateTime.Now + "] ERROR: " + ex.Message );
            Console.WriteLine( "[" + DateTime.Now + "] DEBUG: Query <<" + query.name + ">>" );
        }
   }
}

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 (It's SQL Server 2008 R2):

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

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 queryData. If there's a solution in SQL that'll work for me too.

Thanks in advance

Upvotes: 0

Views: 310

Answers (1)

Victor Olvera Cardoso
Victor Olvera Cardoso

Reputation: 51

I managed to work around the problem.

After some research I found out that while Microsoft SQL Server 2008 doesn't support the FOR JSON sentence, it does support FOR XML and that makes things a lot easier.

First I added this line at the end of the SQL queries that returned a result set:

FOR XML RAW, ROOT('productos')

Then I edited my code like this. I reorganized it to put the connection sentence inside the loop, fixing the "Open DataReader" error. Then I validated the result in each iteration to see if the value started with a "<" indicating an XML value (I know there must be a less silly solution for this 😅, I'll see that later). That way I could perform an XML to JSON conversion only when needed.

// Start a loop through all the rows in 'allQueries'
foreach( dynamic query in allQueries )
{
    // Open up a SQL Connection
    using( SqlConnection connection = new SqlConnection( builder.ConnectionString ) )
{
    // Attempt to open it.
    try { connection.Open(); } catch( Exception ex ) { Console.WriteLine( "[" + DateTime.Now + "] ERROR abriendo conexión a base de datos local: " + ex.Message ); }

    // Build an SQL command
    using( SqlCommand cmd = new SqlCommand(query.query, connection) )
    {
        try
        {
            String tempResult = Convert.ToString(cmd.ExecuteScalar());

            // Validate if the current result is an XML value using a cheap first character comparison.
            if( tempResult.Substring(0,1) == "<" )
            {
                // Prepare an XmlDocument with the result of the current iteration.
                XmlDocument xmlResult = new XmlDocument();
                xmlResult.LoadXml( tempResult );

                // Convert XML to JSON
                String jsonResult = JsonConvert.SerializeXmlNode( xmlResult );
                    
                // Add the converted result to the 'queryData' Dictionary
                queryData.Add( query.name, jsonResult );
                //Console.WriteLine( "[" + DateTime.Now + "] Query <<" + query.name + ">> con el valor: " + Convert.ToString( cmd.ExecuteScalar() ) );
            }
            else
            {
                // If the current result is not an XML value just add it to the 'queryData' Dictionary.
                queryData.Add( query.name, tempResult );
            }                                  
                
        }
        catch( Exception ex )
        {
            Console.WriteLine( "[" + DateTime.Now + "] ERROR: " + ex.Message );
            Console.WriteLine( "[" + DateTime.Now + "] ON QUERY <<" + query.name + ">>" );
        }            
        
     }
     connection.Close();
   }    
}

This took me the whole day, I'll go lay down for a bit... I hope this will be helpful for someone.

Upvotes: 1

Related Questions