时间戳

首页 > 解决方案 > 在循环中从数据库中检索单个和多个值到字典中

c# - 在循环中从数据库中检索单个和多个值到字典中

问题描述

我目前正在使用查询循环检索一组值。问题是某些查询是返回一组值而不是单个值的 JOIN 语句，因此 ExecuteScalar 方法对我不起作用。

这是我的循环。
' allQueries ' 是类 'Queries' 的列表（属性：名称、db_engine、查询），其中包含将要使用的每个 SQL 查询。
' queryData ' 是一个字典，将填充所有查询的结果。

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 + ">>" );
        }
   }
}

这是返回一组值的查询的一个示例，它基本上返回一个 3 列的表，其中包含 10 行（它是 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

我需要该程序来获取该组值并将它们转换为 JSON，以便它可以适合queryData中相应单元格中的单个值。如果 SQL 中有一个对我也有用的解决方案。

提前致谢

标签： c#sql-server

解决方案

我设法解决了这个问题。

经过一番研究，我发现虽然 Microsoft SQL Server 2008 不支持 FOR JSON 语句，但它确实支持 FOR XML，这让事情变得容易多了。

首先，我在返回结果集的 SQL 查询末尾添加了这一行：

FOR XML RAW, ROOT('productos')

然后我像这样编辑了我的代码。我对其进行了重组，将连接语句放入循环中，修复了“Open DataReader”错误。然后我在每次迭代中验证了结果，以查看该值是否以表示 XML 值的“<”开头（我知道必须有一个不那么愚蠢的解决方案，我稍后会看到）。这样我就可以仅在需要时执行 XML 到 JSON 的转换。

// 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();
   }    
}

这花了我一整天的时间，我会躺下……我希望这对某人有所帮助。

推荐阅读