Tipp & Trick: DbParameter

    • VB.NET

    Es gibt 5 Antworten in diesem Thema. Der letzte Beitrag () ist von ErfinderDesRades.

      Tipp & Trick: DbParameter

      (Vorbemerkung: Nicht, dass jmd denkt, ich sei der einzige Spinner, der DbParameter als "Must-Use - No-Excuces" propagiert - dasselbe kann man zB auch hier nachlesen - (halt mit imo umständlicheren Code-Lösungen, und für c#).)

      Immer wieder sieht man DB-Zugriffe, bei denen Werte an die Datenbank gesendet werden, die direkt in den CommandText eingefrickelt sind.

      Also mal paar willkürliche Beispiele der letzten Tage:

      VB.NET-Quellcode

      1. dt = fillControl("Select distinct(Zahl) from Training where system = '" & getSystem() & "' and versuchsleiter='" & Form1.comboTechnican.Text & "' and session_date > sysdate - 28 and tier_nr < 9999 order by Zahl asc")
      2. 'oder
      3. Dim sql As String = "SELECT COUNT (*) FROM Tabelle WHERE NichtsGefangen = FALSE " & If(UserId <> 0, "User = " & UserId & """, "")
      4. 'oder
      5. Dim SQLAbfrage As String = "UPDATE `benutzer` SET akku = '" + tb_akku.Text - 1 + "' WHERE `Benutzername` = '" + Eingang.lbl_name.Text + "'"

      Diese Vorgehensweise ist unleserlich, fehleranfällig und v.a. kriminell fahrlässig im Umgang mit der Datenbank des Datenbankbesitzers und den Daten der Programm-Nutzer.
      NoGo ists, weil vollkommen unnötig, denn man kann mit demselben oder sogar weniger Aufwand auch eine sicherere Kommunikation proggen, indem man DbParameter verwendet.
      Und ist ja auch klar, wo man solchen X/ X/ X/ lernt: Galileio Openbook, wo sonst? (oder - ganz hardcore: YouTube :cursing: ).

      Also falls jmd. VB erlernen möchte, ohne sich von Galileio verderben zu lassen, empfehle ich dieses Buch Lesen

      Ich persönlich halte von selbstgebastelten DbCommands ja insgesamt garnichts.
      Bei mir gibts nur DataAdapter, mit denen man gleich ganze DataTables befüllt, und über die man auch die Insert-, Delete- und Update-Commands laufen lässt - ohne eine Extra-Zeile dafür coden zu müssen.

      Aber da typisierte Datasets und datengebundene Datagridviews sich einiger Unbeliebtheit erfreuen, stelle ich hier wenigstens mal eine Extension-Methode vor, mit der man sich ein parametrisiertes Command erzeugen kann, mit weniger Aufwand, und lesbarer als das String-Gefrickel.

      Die Methode:

      VB.NET-Quellcode

      1. Public Module SqlCeExtensions
      3. ''' <summary> allgemeine Methode, aus einer SqlCeConnection, einem CommandText und optionalen Werten
      4. ''' ein parametrisiertes SqlCeCommand zu erstellen </summary>
      5. ''' <remarks>Vorrausgeetzt ist, dass die übergebenen values Datentypen haben, die zu den in der Datenbank
      6. ''' festgelegten Spalten passen. Eine Überprüfung findet nicht statt.</remarks>
      7. <Runtime.CompilerServices.Extension()> _
      8. Public Function CreateCommandX(ByVal con As SqlCeConnection, ByVal commandText As String, ByVal ParamArray values As Object()) As SqlCeCommand
      9. Dim splits = commandText.Split("?"c) ' Parameter-Platzhalter '?' identifizieren
      10. If splits.Count <> values.Length + 1 Then Throw New ArgumentException( _
      11. "Anzahl der Argumente passt nicht zur Anzahl der Parameter-Platzhalter im CommandText")
      12. Dim cmd = New SqlCeCommand()
      13. ' für jeden Platzhalter einen DbParameter adden, und den Param-Namen an den Platzhalter schreiben
      14. For i = 0 To values.Length - 1
      15. Dim param = cmd.Parameters.AddWithValue("@a" & i, values(i))
      16. splits(i) = splits(i) & param.ParameterName
      17. Next
      18. cmd.CommandText = String.Concat(splits)
      19. cmd.Connection = con
      20. Return cmd
      21. End Function
      22. End Module
      Das ist jetzt die Lösung für SqlCe. Für andere DBProvider muß natürlich jeweils eine annere Extension geschrieben werden, etwa für Access müsste SqlCeConnection, SqlCeCommand ausgetauscht werden durch OleDbConnection, OleDbCommand.

      Also eine string-frickelige Abfrage mag so aussehen:

      VB.NET-Quellcode

      1. 'Der normale NoGo-Progger täte gar Textboxen verwenden (statt hier: DateTimePicker), sodass Angreifer dort ihre Sql-Injection-Angriffe reinschreiben können.
      2. Dim sql = "Select * From [Bestellung] where [Datum] between #" & dtpVon.Value & "# and #" & dtBis.Value & "#"
      3. Dim cmd = New SqlCeCommand(sql, _Con)


      Demgegenüber sähe die Verwendung meiner Extension so aus:

      VB.NET-Quellcode

      1. Dim cmd = _Con.CreateCommandX( _
      2. "Select * From [Bestellung] where [Datum] between ? and ?", dtpVon.Value, dtBis.Value)


      Wie gesagt, ich halte insgesamt nicht viel davon, aber ich habs mirmal gegeben, ein paar ListViews damit zu befüllen:
      viel Code für wenig Funktionalität

      VB.NET-Quellcode

      1. Imports System.Data.SqlServerCe
      3. Public Class frmOhneDataset
      4. Private _Con As New SqlCeConnection(My.Settings.BestellungenConnectionString)
      6. Private Sub frmOhneDataset_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
      7. Me.AlignOnTop()
      8. End Sub
      10. Private Sub LadeKundenToolStripMenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) Handles LadeKundenToolStripMenuItem.Click, LadeBearbeiterToolStripMenuItem.Click, LadeBestellungenToolStripMenuItem.Click, btLadeGefiltert.Click, ClearAllToolStripMenuItem.Click
      11. Select Case True
      12. Case sender Is LadeKundenToolStripMenuItem
      13. LadeKunden()
      14. Case sender Is LadeBearbeiterToolStripMenuItem
      15. LadeBearbeiter()
      16. Case sender Is LadeBestellungenToolStripMenuItem
      17. LadeBestellungen()
      18. Case sender Is ClearAllToolStripMenuItem
      19. For Each lv In New ListView() {lvKunde, lvBearb, lvBest}
      20. lv.Items.Clear()
      21. Next
      22. Case sender Is btLadeGefiltert
      23. LadeBestellungenGefiltert()
      24. End Select
      25. End Sub
      27. Private Sub LadeKunden()
      28. _Con.Open()
      29. Using cmd = _Con.CreateCommandX("Select * From [Kunde]"), rd = cmd.ExecuteReader
      30. While rd.Read
      31. Dim lvi = lvKunde.Items.Add(rd(0).ToString)
      32. For i = 1 To rd.VisibleFieldCount - 1
      33. lvi.SubItems.Add(rd(i).ToString)
      34. Next
      35. End While
      36. End Using
      37. _Con.Close()
      38. End Sub
      40. Private Sub LadeBearbeiter()
      41. _Con.Open()
      42. Using cmd = _Con.CreateCommandX("Select * From [Bearbeiter]"), rd = cmd.ExecuteReader
      43. While rd.Read
      44. Dim lvi = lvBearb.Items.Add(rd(0).ToString)
      45. For i = 1 To rd.VisibleFieldCount - 1
      46. lvi.SubItems.Add(rd(i).ToString)
      47. Next
      48. End While
      49. End Using
      50. _Con.Close()
      51. End Sub
      53. Private Sub LadeBestellungen()
      54. _Con.Open()
      55. Using cmd = _Con.CreateCommandX("Select * From [Bestellung]"), rd = cmd.ExecuteReader
      56. While rd.Read
      57. Dim lvi = lvBest.Items.Add(rd(0).ToString)
      58. For i = 1 To rd.VisibleFieldCount - 1
      59. lvi.SubItems.Add(rd(i).ToString)
      60. Next
      61. End While
      62. End Using
      63. _Con.Close()
      64. End Sub
      66. Private Sub LadeBestellungenGefiltert()
      67. _Con.Open()
      68. Using cmd = _Con.CreateCommandX( _
      69. "Select * From [Bestellung] where [Datum] between ? and ?", dtpVon.Value, dtBis.Value), _
      70. rd = cmd.ExecuteReader
      71. While rd.Read
      72. Dim lvi = lvBest.Items.Add(rd(0).ToString)
      73. For i = 1 To rd.VisibleFieldCount - 1
      74. lvi.SubItems.Add(rd(i).ToString)
      75. Next
      76. End While
      77. End Using
      78. _Con.Close()
      79. End Sub
      81. Private Sub NoGo()
      82. 'würde vmtl. crashen, weil die Datumse automatisch konvertiert würden, und zwar falsch.
      83. 'Aber normale NoGo-Progger verwenden eh Textboxen, sodass Angreifer dort ihre Sql-Injection-Angriffe reinschreiben können.
      84. Dim sql = "Select * From [Bestellung] where [Datum] between " & dtpVon.Value & " and " & dtBis.Value
      85. Dim cmd = New SqlCeCommand(sql, _Con)
      86. '...
      87. End Sub
      89. Private Sub LadeGefiltert_OhneExtensionMethode()
      90. 'klassische Verwendung von DbParametern: etwas umständlich, weil zw. Command erstellen und Command abfahren noch die Parameter geaddet wern müssen
      91. 'etwas anfällig, weil die Parameternamen zweimal identisch festgelegt werden müssen.
      92. Dim sql = "Select * From [Bestellung] where [Datum] between @von and @bis"
      93. _Con.Open()
      94. Using cmd = New SqlCeCommand(sql, _Con)
      95. 'DBParameter vereiteln Sql-Injection und konvertieren i.a. typ-richtig
      96. cmd.Parameters.AddWithValue("@von", dtpVon.Value)
      97. cmd.Parameters.AddWithValue("@bis", dtBis.Value)
      98. Using rd = cmd.ExecuteReader
      99. While rd.Read
      100. Dim lvi = lvBest.Items.Add(rd(0).ToString)
      101. For i = 1 To rd.VisibleFieldCount - 1
      102. lvi.SubItems.Add(rd(i).ToString)
      103. Next
      104. End While
      105. End Using
      106. End Using
      107. _Con.Close()
      108. End Sub
      110. End Class
      112. Public Module SqlCeExtensions
      114. ''' <summary>
      115. ''' allgemeine Methode, aus einer SqlCeConnection, einem CommandText und optionalen Werten ein parametrisiertes SqlCeCommand zu erstellen
      116. ''' </summary>
      117. <Runtime.CompilerServices.Extension()> _
      118. Public Function CreateCommandX(ByVal con As SqlCeConnection, ByVal commandText As String, ByVal ParamArray values As Object()) As SqlCeCommand
      119. Dim splits = commandText.Split("?"c)
      120. If splits.Count <> values.Length + 1 Then Throw New ArgumentException( _
      121. "Anzahl der Argumente passt nicht zur Anzahl der Parameter-Platzhalter im CommandText")
      122. Dim cmd = New SqlCeCommand()
      123. For i = 0 To values.Length - 1
      124. Dim param = cmd.Parameters.AddWithValue("@a" & i, values(i))
      125. splits(i) = splits(i) & param.ParameterName
      126. Next
      127. cmd.CommandText = String.Concat(splits)
      128. cmd.Connection = con
      129. Return cmd
      130. End Function
      131. End Module


      Hier dieselbe Funktionalität, zuzüglich Ändern und Abspeichern, unter Verwendung der DBExtensions:
      wenig Code für viel Funktionalität

      VB.NET-Quellcode

      1. Imports System.Data.Common
      2. Imports System.Data.SqlServerCe
      4. Public Class frmMainM_N
      6. Private Sub Form_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
      7. Dim adp = New DatasetAdapter( _
      8. SqlCeProviderFactory.Instance, My.Settings.BestellungenConnectionString, _
      9. ConflictOption.OverwriteChanges)
      10. Me.AlignOnTop.BestellungenDts.Register(Me).Adapter(adp).Fill()
      11. AddHandler Me.FormClosing, BestellungenDts.HandleFormClosing
      12. End Sub
      14. Private Sub MenuItem_Click(ByVal sender As Object, ByVal e As EventArgs) _
      15. Handles ReloadToolStripMenuItem.Click, SaveToolStripMenuItem.Click, btLadeGefiltert.Click
      16. Select Case True
      17. Case sender Is ReloadToolStripMenuItem
      18. BestellungenDts.Fill()
      19. Case sender Is SaveToolStripMenuItem
      20. BestellungenDts.Save(Me)
      21. Case sender Is btLadeGefiltert
      22. BestellungenDts.Bestellung.Fill("where [Datum] between ? and ?", dtpVon.Value, dtBis.Value)
      23. End Select
      24. End Sub
      26. End Class
      Das ist jetzt nur ein Viertel des anderen Codes, kann dafür aber auch Sortieren, Editieren und Abspeichern.
      Auch die Darstellung ist inhaltsreicher, es werden nämlich die Namen der Bearbeiter und Kunden angezeigt, wo eine ListView nur mit ID-Nummern aufwarten kann (oder extra-Aufwand erforderte).
      Vergleiche: mit:
      Die Combo-DropDowns im DGV deuten übrigens an, dass man auch die Bearbeiter und Kunden editieren kann, nämlich indem man das DropDown öffnet, und einen anneren Bearbeiter/Kunden anwählt.

      Zur Solution:
      Habe ich in VB2008 gemacht, und als DB eine SqlCe-Datenbank genommen. Der SqlCe-Provider scheint allmählich Access an allgemeiner Verfügbarkeit zu überholen.
      Im Sample ist auch die neuesete Version der DBExtensions drin - ich bin einfach zu faul, die ganze Funktionalität immer wieder neu "from the Scratch" zu erfinden.

      Anhang:
      Da OleDB als einziges Datenbank-System benannte Parameter im CommandText nicht akzeptiert, muß die CreateConnectionX-Funktion für OleDb etwas abweichend formuliert werden:

      VB.NET-Quellcode

      1. ''' <summary>
      2. ''' allgemeine Methode, aus einer OleDbConnection, einem CommandText und optionalen Werten ein parametrisiertes OleDbCommand zu erstellen
      3. ''' </summary>
      4. ''' <remarks>Vorrausgeetzt ist, dass die übergebenen values Datentypen haben, die zu den in der Datenbank
      5. ''' festgelegten Spalten passen. Eine Überprüfung findet nicht statt.</remarks>
      6. <Runtime.CompilerServices.Extension()> _
      7. Public Function CreateCommandX(ByVal con As OleDb.OleDbConnection, ByVal commandText As String, ByVal ParamArray values As Object()) _
      8. As OleDb.OleDbCommand
      9. If commandText.Split("?"c).Count <> values.Length + 1 Then Throw New ArgumentException( _
      10. "Anzahl der Argumente passt nicht zur Anzahl der Parameter-Platzhalter im CommandText")
      11. CreateCommandX = New OleDb.OleDbCommand() With {.CommandText = commandText, .Connection = con}
      12. For i = 0 To values.Length - 1
      13. CreateCommandX.Parameters.AddWithValue("@a" & i, values(i))
      14. Next
      15. End Function


      Zu Antwort-Posts:
      Ich täte euch bitten, Fragen hierzu im HauptForum zu klären. Diese VBP-Abteilung heißt "Tipps & Tricks und Tutorials", und ist nicht dazu gedacht, spezielle Einzelfall-Probleme zu lösen.
      Dateien

      • SqlCeSample.zip

        (120,6 kB, 311 mal heruntergeladen, zuletzt: )

      Dieser Beitrag wurde bereits 13 mal editiert, zuletzt von „ErfinderDesRades“ ()

      Mit der Extension Methode mache ich es auch, jedoch halte ich nur das ? als Splitter etwas umständlich. Ich nutze da lieber die Methode ähnlich der String.Format()-Methode, wo man die übergebenen Values mittels {1}, {2} usw. zuweisen kann. Der Vorteil liegt auf der Hand:

      1. Man muss sich nicht an die Reihenfolge der übergebenen Parameter halten
      2. Öfters vorkommende Parameter müssen nur einmal angegeben werden

      VB.NET-Quellcode

      1. Dim param = cmd.Parameters.AddWithValue("@a" & i, values(i))


      Option Strict Off?? Ansonsten müsste doch bei "@a" & i ein Fehler kommen das i nicht in String konvertiert werden kann.

      Dodo schrieb:

      1. Man muss sich nicht an die Reihenfolge der übergebenen Parameter halten

      SOLLTE man aber -> Access interessiert sich nicht für Parameternamen, sondern nur für die Reihenfolge. Wenn man den Code dann zb später mal umstellen will, knallt es evtl. Deswegen lieber in solchen Fällen: kleinster gemeinsamer Nenner ...

      Es geht hierbei um die VB Funktion die btw. noch nichts mit der Datenbank zu tun hat.
      Der CommantText bleibt ja identisch und DORT kann man sich meinetwegen auch an die Reihenfolge halten, mir gehts aber eher an die Reihenfolge der Parameter für die Funktion, beispiel

      VB.NET-Quellcode

      1. Dim cmd = _Con.CreateCommandX("Select t1.* From [Bestellung] AS `t1` LEFT JOIN `tabelle` AS `t2` ON t2.name = ? where [Datum] t1.aId = ? AND t2.bId = ?", "Dodo", 1, 1)


      Hier wird fiktiv einmal 2 mal die ID abgefragt und die 1 muss der Funktion ja nun 2 mal übergeben werden.
      Meine gemeinte Methode

      VB.NET-Quellcode

      1. Dim cmd = _Con.CreateCommandX("Select t1.* From [Bestellung] AS `t1` LEFT JOIN `tabelle` AS `t2` ON t2.name = {1} where [Datum] t1.aId = {0} AND t2.bId = {0}", 1, Dodo)


      Ein weiterer Vorteil, wenn man irgendwo noch ein Parameter einfügen will, müsste man erstmal zählen wieviele ?-Replacer vor meinem eingefügten neuen Parameter sind um dann beim ParamArray an der entsprechenden Stelle auch ein neuen Wert einzufügen. Mit meiner Methode fügt man den neuen Wert einfach ans Ende des ParamArrays und den Replacer mit dem Index, der ja einfach ausfindig zu machen ist an die Stelle im SQL Query.

      Edit: Hier mal meine umgeschriebene Funktion

      VB.NET-Quellcode

      1. Public Module SqlCeExtensions
      3. ''' <summary>
      4. ''' allgemeine Methode, aus einer SqlCeConnection, einem CommandText und optionalen Werten ein parametrisiertes SqlCeCommand zu erstellen
      5. ''' </summary>
      6. <Runtime.CompilerServices.Extension()> _
      7. Public Function CreateCommandX(ByVal con As SqlCeConnection, ByVal commandText As String, ByVal ParamArray values As Object()) As SqlCeCommand
      8. Dim cmd = New SqlCeCommand()
      9. ' für jeden Platzhalter einen DbParameter adden, und den Param-Namen an den Platzhalter schreiben
      10. Dim paramNames(values.Length - 1) As String
      11. For i = 0 To values.Length - 1
      12. ' ParameterNamen setzten
      13. paramNames(i) = "@a" & i
      14. Dim param = cmd.Parameters.AddWithValue(paramNames(i), values(i))
      15. Next
      16. cmd.CommandText = String.Format(commandText, paramNames)
      17. cmd.Connection = con
      18. Return cmd
      19. End Function
      20. End Module


      Rauskommen wird das selbe, nur das die Funktion meiner Meinung nach einfacherer zu Handhaben ist.

      Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „Dodo“ ()

      Deine Alternative mit der an String.Format angelehnten Syntax (statt meiner Anlehnung an die Access-Sql-Syntax) gefällt mir auch nicht schlecht, nur halt mit Access kann das scheitern, also müssteman für OleDbCommands die Methode anders formulieren.

      Dodo schrieb:

      EDR schrieb:

      VB.NET-Quellcode

      1. Dim param = cmd.Parameters.AddWithValue("@a" & i, values(i))
      Option Strict Off?? Ansonsten müsste doch bei "@a" & i ein Fehler kommen das i nicht in String konvertiert werden kann.
      nee - dassis schön Strict On: der & - Operator arbeitet wie String.Concat(): akzeptiert jedes Objekt und ruft dessen .Tostring() methode auf.
      .ToString() anhängen ist also nicht erforderlich.

      Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „ErfinderDesRades“ ()

      Update

      Habe die Extension mal fett überarbeitet:

      VB.NET-Quellcode

      1. Imports System.Data.Common
      3. Public Module CommonDataExtensions
      5. ''' <summary> befüllt die DbParameter eines DbCommands mit Werten </summary>
      6. <Runtime.CompilerServices.Extension()> _
      7. Public Function FillParams(ByVal cmdWithParams As DbCommand, ByVal ParamArray values As Object()) As DbCommand
      8. If cmdWithParams.Parameters.Count <> values.Length Then Throw New ArgumentException( _
      9. "Anzahl der Argumente passt nicht zur Anzahl der Parameter")
      10. For i = 0 To values.Length - 1
      11. cmdWithParams.Parameters(i).Value = values(i)
      12. Next
      13. Return cmdWithParams
      14. End Function
      16. ''' <summary> erzeugt ein DBCommand mit DBParametern. '?' im commandText ist Platzhalter für einen DBParameter </summary>
      17. <Runtime.CompilerServices.Extension()> _
      18. Public Function CreateCommandWithParams(ByVal con As DbConnection, ByVal commandText As String) As DbCommand
      19. Dim splits = commandText.Split("?"c)
      20. Dim cmd = con.CreateCommand
      21. For i = 0 To splits.Length - 2
      22. Dim param = cmd.CreateParameter
      23. param.ParameterName = "@a" & i
      24. cmd.Parameters.Add(param)
      25. Next
      26. If TypeOf con Is OleDb.OleDbConnection Then
      27. cmd.CommandText = commandText
      28. Else
      29. For i = 0 To splits.Length - 2
      30. splits(i) = splits(i) & cmd.Parameters(i).ParameterName
      31. Next
      32. cmd.CommandText = String.Concat(splits)
      33. End If
      34. Return cmd
      35. End Function
      37. ''' <summary> erzeugt ein DBCommand inklusive bereits mit Werten beschickter DBParameter. '?' im commandText ist Platzhalter für einen DBParameter </summary>
      38. <Runtime.CompilerServices.Extension()> _
      39. Public Function CreateCompleteCommand(ByVal con As DbConnection, ByVal commandText As String, ByVal ParamArray values As Object()) As DbCommand
      40. Return con.CreateCommandWithParams(commandText).FillParams(values)
      41. End Function
      43. End Module
      Nun ist sie ganz allgemein formuliert für glaub alle DBProvider.
      Ausserdem kann man nun das Erzeugen eines parametrisierten Commands trennen von der Befüllung der Parameter.
      Das ist "sinnvoll", wenn man etwa viele einzelne Insert- oder Update- Statements absetzen möchte, mit demselben CommandText, aber immer unterschiedlichen Argumenten.

      (Sofern man hier ühaupt von "sinnvoll" sprechen kann, denn gleich DataAdapter zu nehmen wäre natürlich ungleich viel sinnvoller)

    Facebook
    Telefonspende