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