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.
    ''' <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!