-
Notifications
You must be signed in to change notification settings - Fork 0
/
TextBoxWhere.vb
101 lines (85 loc) · 3.22 KB
/
TextBoxWhere.vb
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
Imports System.ComponentModel
Imports System.Windows.Forms
Public Enum DataTypes
[String]
[Integer]
[Double]
[Decimal]
Datetime2
[Date]
[DateTimeOffSet]
End Enum
Public Class TextBoxWhere
Inherits TextBox
Public Sub New()
Separator = ","c
End Sub
<Category("Data"), Description("Column type for property ColumnName")>
Public Property DataType As DataTypes
Private _InClause As String
<Browsable(False)>
Public ReadOnly Property Statement As String
Get
Return _InClause
End Get
End Property
Private _Where As String
Public ReadOnly Property Where As String
Get
Return _Where
End Get
End Property
Private _IsValid As Boolean
<Browsable(False)>
Public ReadOnly Property IsValid As Boolean
Get
Return _IsValid
End Get
End Property
<Category("Data"), Description("Column name in table")>
Public Property ColumnName As String
<Category("Data"), Description("Char separator in Text property to split on")>
Public Property Separator As Char
<Category("Data"), Description("Valid SQL SELECT with no WHERE clause")>
Public Property SelectStatement As String
''' <summary>
''' Create a full SELECT statement with a WHERE IN clause
''' </summary>
Public Sub CreateWhereStatement()
If Not String.IsNullOrWhiteSpace(Text) AndAlso Not String.IsNullOrWhiteSpace(SelectStatement) Then
If Not String.IsNullOrWhiteSpace(ColumnName) AndAlso Not Separator = vbNullChar Then
Dim splitTokensArray As String() = Text.Split(Separator)
Dim sb As New Text.StringBuilder
Dim whereConcatenate = ""
For Each item In splitTokensArray
Select Case DataType
Case DataTypes.String
sb.Append($"'{item.Replace("'", "''")}',")
whereConcatenate = $" WHERE {ColumnName} "
Case DataTypes.Integer, DataTypes.Double, DataTypes.Decimal
sb.Append($"{item},")
whereConcatenate = $" WHERE {ColumnName} "
Case DataTypes.Datetime2, DataTypes.Date
sb.Append($"'{item}',")
whereConcatenate = $" WHERE CAST({ColumnName} AS date) "
Case DataTypes.DateTimeOffSet
sb.Append($"'{item}',")
whereConcatenate = $" WHERE CAST({ColumnName} AS DATETIMEOFFSET(4))) "
End Select
Next
Dim joinedTokens As String = sb.ToString
If joinedTokens.Last = "," Then
joinedTokens = joinedTokens.Substring(0, joinedTokens.Length - 1)
End If
Dim whereTokens = "(" & joinedTokens & ")"
_Where = whereTokens
_InClause = $"{SelectStatement} {whereConcatenate} IN " & whereTokens
_IsValid = True
Else
_IsValid = False
End If
Else
_IsValid = False
End If
End Sub
End Class