C#中SQL查询拼接咨询：嵌套SQL语句代码相关问题

Hey there! Let's fix up that SQL concatenation approach you're using in C#—it's got some critical issues, plus we can make it cleaner and safer.

First off, directly inserting the id variable into your SQL string is a huge security risk: it exposes your application to SQL injection attacks, which can let attackers steal or modify your database data. It also can cause syntax errors if id ever has special characters (though in this case it's probably a numeric ID, but still—bad habit!).

Here's the safe, maintainable way to do this with parameterized queries: #

First, let's clean up your SQL to make it more readable, then wrap it in a parameterized C# command:

Step 1: Optimize the SQL (and remove unnecessary bloat)

Your original SQL can be simplified to remove redundant subqueries and parentheses, which makes it easier to debug:

SELECT Gruppi.idGruppo, SUM(Lotti.qric) AS Qsum
FROM Gruppi
INNER JOIN collectGruppo ON Gruppi.idGruppo = collectGruppo.idGruppo
INNER JOIN Lotti ON Lotti.idLotto = collectGruppo.idLotto
INNER JOIN collectTaglio ON Lotti.idLotto = collectTaglio.idLotto
WHERE collectTaglio.idTaglio = @id
GROUP BY Gruppi.idGruppo

I removed the inner subquery entirely since you don't use gruppoDim1/gruppoDim2 in the outer aggregation—this makes the query more efficient too.

Step 2: Implement parameterized query in C#

Use SqlCommand with parameters to avoid SQL injection and ensure type safety:

using (SqlConnection connection = new SqlConnection("YourDatabaseConnectionString"))
{
    connection.Open();

    // Use a verbatim string (@"") to keep SQL formatting clean
    string query = @"
        SELECT Gruppi.idGruppo, SUM(Lotti.qric) AS Qsum
        FROM Gruppi
        INNER JOIN collectGruppo ON Gruppi.idGruppo = collectGruppo.idGruppo
        INNER JOIN Lotti ON Lotti.idLotto = collectGruppo.idLotto
        INNER JOIN collectTaglio ON Lotti.idLotto = collectTaglio.idLotto
        WHERE collectTaglio.idTaglio = @id
        GROUP BY Gruppi.idGruppo";

    using (SqlCommand command = new SqlCommand(query, connection))
    {
        // Add the parameter with the correct SQL type (match your idTaglio column type!)
        // For example, if idTaglio is an int:
        command.Parameters.Add("@id", SqlDbType.Int).Value = id;

        // Execute the query and process results
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Extract your data here
                int groupId = reader.GetInt32(0);
                decimal totalQric = reader.GetDecimal(1);
                // Do something with these values...
            }
        }
    }
}

Key things to note: #

• Parameterization: The @id placeholder replaces the direct string concatenation. The database handles safely inserting the value, so no injection risks.
• Type safety: Make sure the SqlDbType matches the type of your idTaglio column (e.g., use SqlDbType.VarChar if it's a string ID, with an appropriate length).
• Readability: Using verbatim strings keeps your SQL formatted like it would be in a database tool, making it way easier to edit and debug.
• Efficiency: Removing the unnecessary subquery reduces the database's work, which can speed up execution.

If you absolutely need to keep the original nested query structure (for some specific reason), you can still apply parameterization—just replace the "+ id +" part with @id and add the parameter as shown above.

内容的提问来源于stack exchange，提问作者wally