Wie SQL Command bauen wenn viele Abfrageoptionen vom User variabel sind?

  • VB.NET
  • .NET (FX) 4.5–4.8

Es gibt 7 Antworten in diesem Thema. Der letzte Beitrag () ist von simpelSoft.

    Wie SQL Command bauen wenn viele Abfrageoptionen vom User variabel sind?

    Hi,

    ich möchte dem User Suchoptionen für eine Tabelle geben in Form einer Textbox. Daneben ein paar Checkboxes, die definieren, welche Spalten berücksichtig werden und eine Option für "Enthält" oder "Beginnt"mit

    Die Abfrage als wie

    Quellcode

    1. Select * from table where Name Like %suche% or Anschrift Like %suche% or Ort Like %suche"


    Je nach Auswahl des Users kann die Suche auch nur den Namen, die Anschrift oder den Ort beinhaltet oder eine Kombination daraus. Auch muß es nicht immer Like %suche% lauten sondern z.B. nur suche%, wenn nach "beginnt mit" gesuch wird.

    Wie baut man derartige Optionen möglichst elegant in ein Command, ohne in der If/Then Hölle zu landen?

    Viele Grüße

    John

    Ooh - ein Query-Builder!
    Leg am besten gleich eine Klasse dafür an, das wird bisserl umfangreicher.

    Ich würde eine Art Viewmodel basteln, was ein Control mit einem Sql-Abschnitt verknüpft. Die Viewmodel bestimmt anhand des Control-Zustandes, ob und wie sein Sql-Abschnit aussieht.
    Die gesamtabfrage wird mit String.Join aus den Abschnitten aller ViewModels zusammengesetzt.

    Es ist die Hölle, aber es ist nicht die If/Then - Hölle.

    Hi John,
    ich lege mir immer eine Klasse dafür an, die ich dann aufrufe. Hier so ein Beispiel (nicht komplett alles, nur ein Auszug):

    C#-Quellcode

    1. // ----------------------------------------------------------------------------------------------------------------------
    2. // SqlSelects zusammenbauen MsSql
    3. private string GetSqlSelect(int aiArt, int aiId, string as1, string as2, string as3, string as4, DateTime adtWtStart, DateTime adtWtEnd)
    4. {
    7. String lsSql = "";
    8. String lsWhereAdd = "";
    9. String lsWhereAdd1 = "";
    10. String lsWhereAdd2 = "";
    11. String lsWhereAdd3 = "";
    12. String lsWhereAdd4 = "";
    13. String lsWhereAdd5 = "";
    14. String lsWhereAdd6 = "";
    15. String lsWhereAdd7 = "";
    16. String lsOrder = "";
    17. String lsJoin = "";
    18. // String lsGroup = "";
    20. // DateTime ldtEnd = DateTime.Today; // Heute
    21. int liYear = DateTime.Now.Year - 1;
    22. int liStandort = 0;
    23. string lsStart = (liYear.ToString()) + "-01-01";
    24. string lsEnd = (liYear.ToString()) + "-12-31";
    25. DateTime ldtStart = DateTime.Parse(lsStart); // Jahresanfang VorJahr
    26. DateTime ldtEnd = DateTime.Parse(lsEnd);
    27. DateTime ldtAdd = DateTime.MinValue;
    29. switch (aiArt)
    30. {
    31. case 1: // Art Tiere
    32. lsSql = @"SELECT id_art_tier, art_tier
    33. FROM art_tier
    34. order by gewaehlt DESC,art_tier";
    35. break;
    36. case 2: // Bestandsliste und Treeview befüllen
    37. lsSql = @"SELECT tiere.id_tier,
    38. tiere.id_art_tier,
    39. tiere.id_gender,
    40. tiere.id_mutter,
    41. tiere.id_vater,
    42. tiere.nr_de,
    43. tiere.dt_geb,
    44. RTRIM(tiere.name) as name,
    45. tiere.generation,
    46. tiere.id_art_rasse,
    47. tiere.id_art_zeugung,
    48. tiere.id_gewerk,
    49. tiere.dt_gekauft,
    50. tiere.id_import,
    51. tiere.dt_verkauft,
    52. tiere.preis_vk,
    53. tiere.preis_ek,
    54. tiere.gewicht_vk,
    55. tiere.bemerkung,
    56. tiere.id_verbleib,
    57. tiere.dt_tod,
    58. tiere.id_standort,
    59. tiere.id_kunde,
    60. tiere.id_tragend,
    61. RTRIM(art_rasse.descr) AS RasseDescr,
    62. RTRIM(art_verbleib.descr) AS VerbleibDescr,
    63. RTRIM(standort.descr) AS Standort,
    64. RTRIM(anm_kunde.firma) AS KundeFirma,
    65. RTRIM(art_gender.gender) AS Gender,
    66. RTRIM(mutter.nr_de) AS TiereMutter,
    67. RTRIM(vater.nr_de) AS TiereVater,
    68. RTRIM(mutter.name) AS TiereMutterName,
    69. RTRIM(vater.name) AS TiereVaterName,
    70. RTRIM(art_kennung.descr) AS Kennung
    71. FROM tiere
    73. LEFT JOIN art_gender ON art_gender.id_gender = tiere.id_gender
    74. LEFT JOIN standort ON standort.id_standort = tiere.id_standort
    75. LEFT JOIN art_rasse ON art_rasse.id_art_rasse = tiere.id_art_rasse
    76. LEFT JOIN art_verbleib ON art_verbleib.id_verbleib = tiere.id_verbleib
    77. LEFT JOIN anm_kunde ON anm_kunde.id_kunde = tiere.id_kunde
    78. LEFT JOIN tiere AS mutter ON mutter.id_tier = tiere.id_mutter
    79. LEFT JOIN tiere AS vater ON vater.id_tier = tiere.id_vater
    80. LEFT JOIN art_kennung ON art_kennung.id_kennung = tiere.id_kennung";
    82. lsWhereAdd = " WHERE tiere.id_art_tier = " + aiId.ToString();
    84. // Jahr
    85. if ((adtWtStart > DateTime.Parse("2005-01-01")) && (adtWtEnd == DateTime.MinValue))
    86. {
    87. lsWhereAdd1 = " And tiere.dt_geb >= \'" + adtWtStart.Year.ToString() + "-01-01\'";
    88. }
    90. // De Nummer
    91. if (tbDeNr.Text.Length > 0)
    92. {
    93. lsWhereAdd2 = " And tiere.nr_de like \'%" + tbDeNr.Text + "%\'";
    94. }
    96. // Name
    97. if (tbName.Text.Length > 0)
    98. {
    99. lsWhereAdd3 = " AND tiere.name like \'%" + tbName.Text + "%\'";
    100. }
    102. // Verkaufte Tiere zeigen
    103. if (CbxVerkauft.IsChecked == false)
    104. {
    105. lsWhereAdd4 = " AND tiere.dt_verkauft IS NULL ";
    106. }
    108. // Verstorbene Tiere zeigen
    109. if (CbxVerstorben.IsChecked == false)
    110. {
    111. lsWhereAdd5 = " AND tiere.dt_tod IS NULL ";
    112. }
    114. if (as1 == "1") // nur tragende Tiere
    115. {
    116. lsWhereAdd6 = " AND anm_tragend.id_zustand = 1";
    117. }
    119. // Standortsuche
    120. if (cbxStandortSrch.SelectedIndex > 0)
    121. {
    122. DataRowView rowview = cbxStandortSrch.SelectedItem as DataRowView;
    124. if ((rowview.Row[0] != DBNull.Value))
    125. {
    126. liStandort = Int32.Parse(rowview.Row[0].ToString());
    127. lsWhereAdd7 = " AND tiere.id_standort = " + liStandort.ToString();
    128. }
    129. }
    131. lsOrder = " Order by tiere.nr_de ASC";
    133. break;
    134. case 21: // Parents im Treeview suchen Mutter, Vater
    135. lsSql = @"SELECT id_tier, id_art_tier, id_gender, id_mutter, id_vater,
    136. nr_de, dt_geb, name, generation, id_art_rasse, id_art_zeugung,


    Dann kommt eine Klasse, die die Daten holt oder mit den DataGrids befüllt. Das fertige SQL-Statement wird übergeben (nochn Beispiel aber auch nicht komplett):

    C#-Quellcode

    1. private Int32 FetchData(string psSql, int aiArt)
    2. {
    3. Int32 liRows = 0;
    5. try
    6. {
    7. SqlConnection dbConnection = new SqlConnection(psConnect);
    8. // Pass both strings to a new SqlCommand object.
    9. // SqlCommand command = new SqlCommand(psSql, dbConnection);
    10. dbConnection.Open();
    12. switch (aiArt)
    13. {
    14. case 1: // Art der Tiere laden
    15. tblArtTier = new DataTable();
    16. sdArtTier = new SqlDataAdapter(psSql, dbConnection);
    17. sdArtTier.Fill(tblArtTier);
    18. lbTiere.ItemsSource = tblArtTier.DefaultView;
    19. liRows = lbTiere.Items.Count;
    20. break;
    21. case 2: // Daten für Tiere (Treeview befüllen)
    22. tblTiere = new DataTable();
    23. sdTiere = new SqlDataAdapter(psSql, dbConnection);
    24. sdTiere.Fill(tblTiere);
    25. DgrBestand.ItemsSource = tblTiere.DefaultView;
    26. liRows = DgrBestand.Items.Count;
    27. // Treeview befüllen
    28. liRows = PrepareTreeview();
    29. break;
    30. case 21: // Daten für Eltern (Treeview befüllen)
    31. tblParents = new DataTable();
    32. sdParents = new SqlDataAdapter(psSql, dbConnection);
    33. sdParents.Fill(tblParents);
    34. liRows = tblParents.Rows.Count;
    35. break;
    36. case 23: // Für DataGrid List Tragend gesamt
    37. tblTragendBrs = new DataTable();
    38. sdTragendBrs = new SqlDataAdapter(psSql, dbConnection);
    39. sdTragendBrs.Fill(tblTragendBrs);
    40. liRows = tblTragendBrs.Rows.Count;
    41. DgrAbkalb.ItemsSource = tblTragendBrs.DefaultView;
    42. liRows = DgrAbkalb.Items.Count;
    43. break;


    Grüße Ulf

    John422 schrieb:

    Danke, passt...


    Naja, geht schon, ist aber recht anfällig für Angriffe, muss jeder selbst entscheiden.
    Korrekt wären SQL-Parameter für die Query.
    Je nachdem, welcher Datenbank-Provider, der Zweck der Parameter ist immer identisch.

    MS-SQL: docs.microsoft.com/de-de/dotne…?view=dotnet-plat-ext-5.0
    MySQL/Maria: dev.mysql.com/doc/dev/connecto…SqlCommand_Parameters.htm
    SQLite: docs.microsoft.com/de-de/dotne…rd/data/sqlite/parameters

    simpelSoft schrieb:

    muss jeder selbst entscheiden
    Hmm - ich seh das verbissener.
    Ein Programmierer darf kein Sql-Injection-Sicherheitsloch einbauen. Solch grenzt ans kriminielle, und die Schäden, die da fahrlässig in Kauf genommen werden sind exorbitant.
    Ich würde es befürworten, wenn Software-Hersteller bei Schäden durch Sql-Injection-Angriffe haftbar gemacht würden, wegen Fahrlässigkeit.

    Sql-Injection-Angriffe sind gänzlich unnötig, und sie zu vereiteln ist keine Mehrarbeit.
    Kein Programmierer sollte eine Db-Anwendung entwickeln dürfen, ohne gelernt zu haben, wie man Sql-Injection ausschliesst.

    Bei Interesse gugge Db-Tut-Bereich.

    Im übrigen, neben dem Problem mit SQL Injections, knallt die Suche weg wenn man da nach was mit Hochkommas (') sucht. Z.b. 3/4'' Schraube, Peter's Würstchenbude, etc.
    "Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. Lehre einen Mann zu fischen und du ernährst ihn für sein Leben."

    Wie debugge ich richtig? => Debuggen, Fehler finden und beseitigen
    Wie man VisualStudio nutzt? => VisualStudio richtig nutzen

    ErfinderDesRades schrieb:

    simpelSoft schrieb:

    muss jeder selbst entscheiden
    Hmm - ich seh das verbissener.

    Ich weiß und das ist auch gut so :thumbsup: .
    Aber das Thema ist ja seit vielen jahren hier im Forum dauerpräsent und immer wieder kommen diese merkwürdig zusammengefrickelten Querys hoch, das grenzt an Beratungsresistenz.
    Wenn Jemand für sich selbst programmiert oder seine Software nicht kommerziell vertreibt, dann soll er doch frickeln wie er möchte.
    Hinweise, wie es richtig geht, haben wir ja nun mehrmals gegeben, mehr kann man nicht tun.

Facebook
Telefonspende