1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
|
''' <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
|