Modified TextBox for forms generating SQL statements

Question

An overview of what I've done:

I've inherited System.Windows.Forms.TextBox and added a few properties to help me out in creating forms that generate SQL statements.

I use to create a large function that would check for changes in the TextBox compared to a string. Then it would take text and concatenate it to a SQL statement. Now it's simple and easier to use.

I've added four properties:

• A String property to hold the default string that the textbox is initially set to and will reset back if the text is left empty.
• A String property to hold the text's associated SQL statement.
• A Boolean to check if the text has changed from the default.
• Finally, an Integer to hold an ID if there is a reason to need one, such as saving and loading text from a file by the ID.

I have not fully implemented the new textbox in my code, but I am working on it.

It may only be slightly modified, but it should cut down my code from 500+ lines of code down to less than +-30 if ran in a for-loop.

SQLTextBox.vb

Public Class ModifiedTextBox
 Inherits System.Windows.Forms.TextBox
 Private _strDefaultText As String
 Private _strSqlText As String
 Private _nID As Integer
 Private _bModified As Boolean = False
 Property ID()
 Set(nID)
 _nID = nID
 End Set
 Get
 Return _nID
 End Get
 End Property
 Property TextModified()
 Set(bModified)
 _bModified = bModified
 End Set
 Get
 Return _bModified
 End Get
 End Property
 Property SqlText()
 Set(strSqlText)
 _strSqlText = strSqlText
 End Set
 Get
 Return _strSqlText
 End Get
 End Property
 Property DefaultText()
 Set(strDefaultText)
 _strDefaultText = strDefaultText
 Me.Text = _strDefaultText
 End Set
 Get
 Return _strDefaultText
 End Get
 End Property
End Class

I've pulled an excerpt from my code. It's not much, but here is the new textbox vs the old one.

In the SQL text, I have something like ""Material Type"" in('INSERTTEXT'). SQLTextBox.Modified gets set when the user leaves the textbox (set to false b default).

'This should run every new textbox (untested)
For i As Integer = 0 To Me._icControls.txtMain.Length - 1
 If Me._icControls.txtMain(i).Modified = True Then
 sqlWhere += " AND " + Me._icControls.txtMain(i).SqlText
 Replace(sqlWhere, "INSERTTEXT", Me._icControls.txtMain(i).Text)
 End If
Next
'Old, long way around where I had split up
'the textboxes into 7 groups they belonged too.
If Me._icControls.txtMaterial(0).Text.ToString() <> DefaultStrings.Material(0) Then
 sqlWhere += " AND ""Material Type"" in('" + Me._icControls.txtMaterial(0).Text.ToString() + "')"
End If
If Me._icControls.txtMaterial(4).Text.ToString() <> DefaultStrings.Material(4) Then
 sqlWhere += " AND ""Grade"" in('" + Me._icControls.txtMaterial(4).Text.ToString() + "')"
End If
If Me._icControls.txtMaterial(5).Text.ToString() <> DefaultStrings.Material(5) Then
 sqlWhere += " AND ""PIW"" in('" + Me._icControls.txtMaterial(5).Text.ToString() + "')"
End If

I went from 200 lines of code to 6, with a minor change. That does not include the lines spent setting up the default text.

Let me know what you think of this. I'm not sure if it's really anything someone could use, but it's been very useful to me.

Answer 1

There's not much to review here really. I'd be more interested in reviewing the code that's actually doing the work, but here goes.

1. Lose the Systems Hungarian notation. The IDE/Code tells me what the data type is. That is if you...

2. Declare the data types of the properties

3. Use auto properties. There's way too much code here for how simple this is.

---

Public Class ModifiedTextBox
 Inherits System.Windows.Forms.TextBox
 Property ID() As Integer
 Property TextModified() As Boolean
 Property SqlText() As String
 Property DefaultText() As String
 Set(strDefaultText)
 _strDefaultText = strDefaultText
 Me.Text = _strDefaultText
 End Set
 Get
 Return _strDefaultText
 End Get
 End Property
End Class

• CodeMonkey: I'll throw some code excerpts from my program soon. Thanks for your response. I've always done the naming this way, even before I knew it was a thing.
• CodeMonkey: I've added the except to my original post.

Answer 2

• You don't need to prefix your variables with b\n\str.

• I would get the business logic out of the UI and into business classes

It's hard to know exactly what you are doing but this might help. I would have a SqlBuilder class and a SqlPart for each section of the where clause.

Class SqlBuilder
 Private _gradePart As New SqlPart("Grade IN ('{0}')")
 Private _piwPart As New SqlPart("PIW IN ('{0}')")
 Public ReadOnly Property GradePart As SqlPart
 Get
 Return _gradePart
 End Get
 End Property
 Public ReadOnly Property PiwPart As SqlPart
 Get
 Return _piwPart
 End Get
 End Property
 Public Function GetSqlWhere() As String
 Dim sqlWhere As String = ""
 sqlWhere &= " AND " & _gradePart.GetSqlPart()
 sqlWhere &= " AND " & _piwPart.GetSqlPart()
 Return sqlWhere
 End Function
End Class
Class SqlPart
 Public Property ID As Integer
 Public Property SqlText As String
 Private _defaultText As String
 Private _sqlValue As String
 Private _modified As Boolean = False
 Public ReadOnly Property DefaultText As String
 Get
 Return _defaultText
 End Get
 End Property
 Public ReadOnly Property Modified As Boolean
 Get
 Return _modified
 End Get
 End Property
 Public Property SqlValue As String
 Get
 Return _sqlValue
 End Get
 Set(value As String)
 If String.IsNullOrEmpty(value) Then
 value = _defaultText
 End If
 If value <> _sqlValue Then
 _sqlValue = value
 _modified = True
 End If
 End Set
 End Property
 Public Sub New(ByVal defaultText As String)
 _defaultText = defaultText
 End Sub
 Public Function GetSqlPart() As String
 Return String.Format(SqlText, _sqlValue)
 End Function
End Class

Your form just need an instance of SqlBuilder and then you just need to bind each "Part" to it's proper TextBox. All of your sql building logic would be inside these classes.

• CodeMonkey: Good suggestion, but it seems like a lot more coding and a bit more complex that what I have with the modified text box.
• the_lotus: @CodeMonkey it's a really good practice to separate the UI from the business rules/logic. As your program keep getting more complex, you will see the benefit pretty quickly.
• CodeMonkey: That does make sense. I'll keep this in mind for future works. I do have a project coming up that could benefit from this. Thank you very much.