SQL-Server: Dynamische where-Klausel

Problem:

Ajax suggest-Suche auf [n] Zutaten in den Rezepten. Das heißt: match Rezepte gegen mehrere Zutaten.

Zum Beispiel: SELECT Recipes using "flower", "salt" produzieren würde: "Pizza", "Bread", "Saltwater" und so weiter.

Tabellen:

Ingredients [
    IngredientsID INT [PK],
    IngredientsName VARCHAR
]

Recipes [
    RecipesID INT [PK],
    RecipesName VARCHAR
]

IngredientsRecipes [
    IngredientsRecipesID INT [PK],
    IngredientsID INT,
    RecipesID INT
]

- Abfrage:

SELECT
    Recipes.RecipesID,
    Recipes.RecipesName,
    Ingredients.IngredientsID,
    Ingredients.IngredientsName
FROM
    IngredientsRecipes

    INNER JOIN Ingredients
    ON IngredientsRecipes.IngredientsID = Ingredients.IngredientsID

    INNER JOIN Recipes
    ON IngredientsRecipes.RecipesID = Recipes.RecipesID
WHERE
    Ingredients.IngredientsName IN ('salt', 'water', 'flower')

Ich bin derzeit am Bau meine Abfrage mit ASP.NET C# aufgrund der dynamischen Natur des WHERE - Klausel.

Ich Bisse, die ich zum erstellen der Abfrage in meinem code-Ebene anstelle der Verwendung einer gespeicherten Prozedur/Reine SQL -, die in der Theorie sollte viel schneller sein.

Haben Sie Jungs haben keine Gedanken darüber, wie ich würde zu bewegen alle Logik aus meinem code-Ebene zu reinem SQL, oder zumindest, wie kann ich optimieren der Leistung von dem, was ich mache?

Ich denke entlang der Linien von temporären Tabellen:

Schritt eins: SELECT IngredientsID FROM Ingredients und INSERT INTO temp-table

Schritt zwei: SELECT RecipesName FROM Recipes trat mit IngredientsRecipes trat mit temp-table.IngredientsID

InformationsquelleAutor cllpse | 2008-09-27
  1. 7

    Haben Sie zwei Möglichkeiten. Wenn Sie mit SQL Server 2008 (oder Oracle) Sie kann in eine Tabelle value-parameter.

    Wenn Sie SQL Server 2005 verwenden, können Sie XML simulieren diese Funktion

    Wenn Sie etwas früher als 2005, müssen Sie zum verketten von ids in einem einzigen string und erstellen einer UDF-Datei zu analysieren Sie.

    • Wunderbare Neuigkeiten, Mike. Dank einem Haufen! 🙂
    InformationsquelleAutor Michael Brown
  2. 3

    Konnte man sich wenigstens einstellen, wo die clausule zu vermeiden, SQL-injection, etwas gleichermaßen:

    using System.Data;
using System.Data.SqlClient;
using System.Text;

class Foo
{
    public static void Main ()
    {
        string[] parameters = {"salt", "water", "flower"};
        SqlConnection connection = new SqlConnection ();
        SqlCommand command = connection.CreateCommand ();
        StringBuilder where = new StringBuilder ();
        for (int i = 0; i < parametes.Length; i++)
        {
            if (i != 0)
                where.Append (",");
            where.AppendFormat ("@Param{0}", i);
            command.Parameters.Add (new SqlParameter ("Param" + i, parameters [i]));
        }
    }
}
    • Ja. Ich kümmerte mich um potenzielle Injektionen. Mike Brown kam mit einem Geniestreich. Aber danke für deine Mühe, Kumpel.
    InformationsquelleAutor albertein
  3. 2

    Je nachdem, wie Sie der Verarbeitung der Eingabe Inhaltsstoffe ich denke, das aktuelle Methode hat einige sql-injection-Risiken.

    Könnte man anfügen, die ingrediant Namen der join-Bedingungen, die möglicherweise schneller sein.

    Könnte man auch hash-Kombinationen von Zutaten für Rezepte verwenden, die für eine schnelle Suche.

    • Ich bin damit einverstanden. Script-injection-Problemen ist auch eines meiner Anliegen. Können Sie erläutern, auf "hasing"?
    • Wenn Sie den Benutzer wählen Sie aus einer Liste von möglichen Zutaten, die man in die Zutaten-Tabelle, könnte man programmaticly verwenden diese ids, die in Ihrer where-Anweisung und keine injection-Problem.
    • E. g. Wenn Sie eine MD5-oder SHA1-hash durch anfügen der Worte, die Salz-Wasser-Mehl bekommst du einen Wert (dein hash). In Ihrem lookup könnte man dann nur für das suchen eines Elements passende dabei. Wie Sie vergleichen einen Wert statt einer Liste wäre es schneller.
    • Clever, Alexmac 🙂
    • Das würde nur geben ihm eine genaue übereinstimmung. die Logik der Beispiel-query bedeutet er will die Rezepte enthalten in den Zutaten. Nun, da Sie erwähnen, obwohl es ein trick wäre, um eine Bitmaske für Ihre Werte.
    InformationsquelleAutor alexmac
Schreibe einen Kommentar