Feedback

VB - Mini-Sql-Command-Generator

Veröffentlicht von am 15.09.2008
(1 Bewertungen)
Erstellt die häufigsten Command-Strings einschl. einem typgerechten Parameter-Objekt.
Mit Beispiel.
GFU-Schulungen  [Anzeige]

VB.NET 2017/2015/2013 Komplett

Ziel des Seminars ist der flexible Umgang mit VB.NET, wie z.B. die damit verbundene eigenständige Entwicklung von Programmen oder das Verwenden von Datenbanken.

VB.NET Komplett

Sie stehen vo der Aufgabe, individuelle Anwendungen für Windows zu schreiben. Hier ist VB.NET die optimale Sprache. Sie erlernt sich recht leicht und passt sich komplett in die .NET Umgebung von Microsoft ein. Nach der Schulung entwickeln Sie anwenderfreundliche Programme in VB.NET . Mit den objektorientierten Modellen in VB.NET erzeugen Sie außerdem wiederverwendbare Komponenten.

    ''' <summary>
    ''' Erstellt die wichtigsten SQL-Statesments einschl. Parameter.
    ''' Feldübergabe als ENUM-Werte in T
    ''' </summary>
    ''' <typeparam name="T">ENUM-Werte</typeparam>
    ''' <remarks></remarks>
    Public Class SqlTextFormer(Of T)
        Implements IDisposable

        ''' <summary>
        ''' Datenbanktyp für Paramter-Liste
        ''' </summary>
        ''' <remarks></remarks>
        Enum ParamterTyp
            OleDb
            SqlCe
            SqlServer
        End Enum


        ''' <summary>
        ''' das Palzhalter-Element für den Tabellennamen
        ''' </summary>
        ''' <remarks></remarks>
        Public TablenamePlaceholder As String = "#tab"

        ''' <summary>
        ''' Ob Felder mit Brackets [] versehen werden sollen
        ''' </summary>
        ''' <remarks></remarks>
        Public AllowFieldsBrackets As Boolean = True
        ''' <summary>
        ''' Ob Tabellenname mit Brackets [] versehen werden sollen
        ''' </summary>
        ''' <remarks></remarks>
        Public AllowTablenameBrackets As Boolean = True

        ''' <summary>
        ''' Felder einer Tabelle
        ''' </summary>
        ''' <remarks></remarks>
        Private Columns As List(Of T)

        Public Sub New()
            Columns = New List(Of T)
            '   Übernahme der Spaltennamen aus der Eunumerierung
            Columns.AddRange(CType([Enum].GetValues(GetType(T)), _
                             Global.System.Collections.Generic.IEnumerable(Of T)))

        End Sub

        ''' <summary>
        ''' InsertInto-Funktion
        ''' alle Felder werden berücksichtigt
        ''' </summary>
        ''' <returns> z.B: INSERT INTO #tab (ID,ParentID,Bez,Flag) VALUES(?,?,?,?)</returns>
        ''' <remarks></remarks>
        Public Function InsertInto() As String
            Dim fields As String = _getFields(Me.Columns.ToArray)
            Dim values As String = ""
            For Each p As T In Me.Columns
                values += If(values = "", "", ",") + "?"
            Next
            Return String.Format("INSERT INTO {0} ({1}) VALUES ({2})", _getBrackets(TablenamePlaceholder), fields, values)
        End Function
        ''' <summary>
        ''' InsertInto-Funktion
        ''' </summary>
        ''' <param name="eFields">die ausgewählten Felder</param>
        ''' <returns> z.B: INSERT INTO #tab (ID,ParentID,Bez,Flag) VALUES(?,?,?,?)</returns>
        ''' <remarks></remarks>
        Public Function InsertInto(ByVal eFields() As T) As String
            Dim fields As String = _getFields(eFields)
            Dim values As String = ""
            For Each p As T In eFields
                values += If(values = "", "", ",") + "?"
            Next
            Return String.Format("INSERT INTO {0} ({1}) VALUES ({2})", _getBrackets(TablenamePlaceholder), fields, values)
        End Function

        ''' <summary>
        ''' Delete Funktion
        ''' Where -Funktion immer mit Gleichheit -> WHERE ID=?
        ''' </summary>
        ''' <param name="WhereFields">Feldenamen für WHERE</param>
        ''' <returns>z.B: DELETE FROM #tab WHERE ID=?</returns>
        ''' <remarks></remarks>
        Public Function Delete(ByVal WhereFields() As T) As String
            Dim fields As String = _getFields(WhereFields, "=?")
            Return String.Format("DELETE FROM {0} WHERE {1}", _getBrackets(TablenamePlaceholder), fields)
        End Function

        ''' <summary>
        ''' Update Funktion
        ''' </summary>
        ''' <param name="SetFields">Feldenamen für SET</param>
        ''' <param name="WhereFields">Feldenamen für WHERE</param>
        ''' <returns>z.B: UPDATE #tab SET Bez='?' WHERE ID=?</returns>
        ''' <remarks></remarks>
        Public Function Update(ByVal SetFields() As T, ByVal WhereFields() As T) As String
            Dim sSET As String = _getFields(SetFields)
            Dim sWHERE As String = _getFields(WhereFields,""," AND ")
            Return String.Format("UPDATE {0} SET {1} WHERE {2}", _getBrackets(TablenamePlaceholder), sSET, sWHERE)
        End Function



        ''' <summary>
        ''' Select Funktion
        ''' </summary>
        ''' <param name="OnlyStar">FALSE: Aufzählung der Felder TRUE: Sternchen für alle Felder</param>
        ''' <returns>z.B: SELECT ID,ParentID,Bez,Flag FROM #tab
        ''' z.B: SELECT * FROM #tab</returns>
        ''' <remarks></remarks>
        Public Function [Select](Optional ByVal OnlyStar As Boolean = False) As String
            Dim fields As String = _getFields(Me.Columns.ToArray)
            If OnlyStar Then fields = "*"
            Return String.Format("SELECT {0} FROM {1} ", fields, _getBrackets(TablenamePlaceholder))
        End Function
        ''' <summary>
        ''' Select Funktion
        ''' Where -Funktion immer mit Gleichheit -> WHERE ID=?
        ''' </summary>
        ''' <param name="SelectFields">Feldenamen für SELECT</param>
        ''' <param name="WhereFields">Feldenamen für WHERE</param>
        ''' <returns>z.B: SELECT ID,ParentID,Bez,Flag FROM FROM #tab WHERE ParentID =?</returns>
        ''' <remarks></remarks>
        Public Function [Select](ByVal SelectFields() As T, ByVal WhereFields() As T) As String
            Dim sSET As String = _getFields(SelectFields)
            Dim sWHERE As String = _getFields(WhereFields, "=?"," AND ")
            Return String.Format("SELECT {0} FROM {1} WHERE {2}", sSET, _getBrackets(TablenamePlaceholder), sWHERE)
        End Function

        ''' <summary>
        ''' SELECT-INTO Funktion
        ''' Eine neue Tabelle aus einer Select-Abfrage einer alten Tabelle
        ''' </summary>
        ''' <param name="SelectFields">Feldenamen für SELECT</param>
        ''' <param name="IntoNewTabel">Neuer Tabellennamen</param>
        ''' <param name="FromTable">vorhandene Auswahltabelle</param>
        ''' <param name="WhereFields">Feldenamen für WHERE</param>
        ''' <returns>z.B. SELECT Nachname,Geb INTO NeueTab FROM Personal WHERE Nachname=?</returns>
        ''' <remarks></remarks>
        Public Function SelectInto(ByVal SelectFields() As T, ByVal IntoNewTabel As String, ByVal FromTable As String, ByVal WhereFields() As T) As String
            Dim sSET As String = _getFields(SelectFields)
            Dim sWHERE As String = _getFields(WhereFields, "=?")
            Return String.Format("SELECT {0} INTO {1} FROM {2} WHERE {3}", sSET, _getBrackets(IntoNewTabel), _getBrackets(FromTable), sWHERE)
        End Function

        ''' <summary>
        ''' Erstellt anhand eines CommandString und der ParameterWerten eine
        ''' typgerechte Parameteraufstellung für OleDb,SqlCe,SqlServer.
        ''' Dies muss dann nur noch dem Command-Objekt eingefügt werden.
        ''' </summary>
        ''' <param name="p"></param>
        ''' <param name="CmdStrg"></param>
        ''' <param name="ParameterValues"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function Parameters(ByVal p As ParamterTyp, ByVal CmdStrg As String, ByVal ParamArray ParameterValues() As Object) As List(Of IDbDataParameter)
            Dim PARA As IDbDataParameter = Nothing
            Select Case p
                Case SqlTextFormer(Of T).ParamterTyp.OleDb
                    PARA = New OleDb.OleDbParameter
                Case SqlTextFormer(Of T).ParamterTyp.SqlCe
                    PARA = New SqlServerCe.SqlCeParameter
                Case SqlTextFormer(Of T).ParamterTyp.SqlServer
                    PARA = New SqlClient.SqlParameter
            End Select


            Dim Liste As New List(Of IDbDataParameter)
            For i As Integer = 0 To ParameterValues.Length - 1
                Dim paramName As String = "@param" + i.ToString()
                Dim paramIndex As Integer = CmdStrg.IndexOf("?")

                If paramIndex <> -1 Then
                    With PARA
                        .ParameterName = paramName
                        .Value = ParameterValues(i)
                    End With

                    Liste.Add(PARA)
                    'löschen von '?' anstelle paramIndex 
                    CmdStrg = CmdStrg.Remove(paramIndex, 1)
                    'anstelle paramIndex 'paramName' einfügen
                    CmdStrg = CmdStrg.Insert(paramIndex, paramName)
                End If

            Next i
            Return Liste
        End Function

        ''' <summary>
        ''' ORDER BY-Funktion anhängen
        ''' </summary>
        ''' <param name="CmdString">Command-String</param>
        ''' <param name="SortFields">Felder, die sortiert werden sollen</param>
        ''' <param name="byDESC"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function AppendSorting(ByVal CmdString As String, ByVal SortFields() As T, Optional ByVal byDESC As Boolean = True) As String
            If String.IsNullOrEmpty(CmdString) Then Return CmdString
            Dim s As String = ""
            For Each t In SortFields
                s += String.Format("{0} {1} ", _getBrackets(t.ToString), If(byDESC, "DESC", "ASC"))
            Next
            Return CmdString + " ORDER BY " + s
        End Function

        ''' <summary>
        ''' GROUP BY-Funktion anhängen
        ''' </summary>
        ''' <param name="CmdString">Command-String</param>
        ''' <param name="GroupFields">Felder, die gruppiert werden sollen</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function AppendGrouping(ByVal CmdString As String, ByVal GroupFields() As T) As String
            If String.IsNullOrEmpty(CmdString) Then Return CmdString
            Return CmdString + " GROUP BY " + _getFields(GroupFields)
        End Function

        ''' <summary>
        ''' Alle Where-Bedingungen wurden oben mit '=?' gesetzt.
        ''' Wenn man andere Vergleichsoperatoren an einer bestimmten Stelle setzten möchte
        ''' kann man dazu diese Funktion benutzen
        ''' </summary>
        ''' <param name="CmdString">Command-String</param>
        ''' <param name="NewString">neuer Vergleichsoperatore zb. Ungleich oder Like</param>
        ''' <param name="BypPos">an welcher Stelle =? auftritt</param>
        ''' <param name="sEqual">=? wird ersetzt</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function ReplaceEqual(ByVal CmdString As String, ByVal NewString As String, _
                                    ByVal BypPos As Integer, Optional ByVal sEqual As String = "=?") As String
            If String.IsNullOrEmpty(CmdString) Or String.IsNullOrEmpty(NewString) Then Return CmdString

            Dim i As Integer = 0
            Dim Counter As Integer = 0
            Do While i <> -1
                i = CmdString.IndexOf(sEqual)
                If i = -1 Then Exit Do
                Counter += 1
                If Counter = BypPos Then
                    If Not NewString.EndsWith("?") Then NewString += "?"
                    CmdString = CmdString.Replace("=?", "")
                    Return CmdString.Insert(i, NewString)
                End If
            Loop
            Return CmdString
        End Function

#Region " private "

        Private Function _getBrackets(ByVal s As String) As String
            If String.IsNullOrEmpty(s) Then Return ""
            Dim withbrackets As Boolean = True
            If Not AllowTablenameBrackets Then
                If s = TablenamePlaceholder Then withbrackets = False
            End If
            If withbrackets Then
                If Not s.StartsWith("[") Then s = "[" + s
                If Not s.EndsWith("]") Then s += "]"
            End If
            Return s
        End Function

        Private Function _getFields(ByVal e() As T, Optional ByVal AppendToField As String = "", Optional ByVal FieldsSeparator As String = ",") As String
            Dim fields As String = ""
            For Each p In e
                Dim f As String = If(AllowFieldsBrackets, _getBrackets(p.ToString), p.ToString)
                fields += If(fields = "", "", FieldsSeparator) + f + AppendToField
            Next
            Return fields
        End Function

#End Region

#Region " IDisposable Support "
        Private disposedValue As Boolean = False        ' To detect redundant calls
        ' IDisposable
        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: free other state (managed objects).
                End If
            End If
            Me.disposedValue = True
        End Sub
        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region


    End Class

    Public Enum enTreeFields
        ID
        ParentID
        Bez
        Flag
    End Enum

    Public Class Testing

        Public Shared Sub Example()
            Dim s As String = ""
            Dim ERG As New System.Text.StringBuilder

            Dim Sql As New SqlTextFormer(Of enTreeFields)
            With Sql
                .AllowTablenameBrackets = False

                s = .Select
                ERG.AppendLine(s)
                s = .AppendSorting(s, New enTreeFields() {enTreeFields.Bez})
                ERG.AppendLine(s)
                s = .Delete(New enTreeFields() {enTreeFields.ID})
                ERG.AppendLine(s)
                s = .AppendGrouping(s, New enTreeFields() {enTreeFields.Bez})
                ERG.AppendLine(s)
                s = .InsertInto
                ERG.AppendLine(s)

                Dim SE() As enTreeFields = {enTreeFields.ID, enTreeFields.Flag, enTreeFields.ID, enTreeFields.ParentID}
                s = .Select(SE, New enTreeFields() {enTreeFields.ID})
                ERG.AppendLine(s)

                Dim PL As List(Of IDbDataParameter) = .Parameters(SqlTextFormer(Of enTreeFields).ParamterTyp.SqlCe, s, 125)

                s = .SelectInto(SE, "NeuTab", "AltTab", New enTreeFields() {enTreeFields.ID})
                ERG.AppendLine(s)
                s = .ReplaceEqual(s, "<>", 1)
                ERG.AppendLine(s)

            End With

            MessageBox.Show(ERG.ToString)
        End Sub
    End Class

Abgelegt unter Datenbank, OleDb, SQL.

Kommentare zum Snippet

 

Logge dich ein, um hier zu kommentieren!