VB.NET Windows Forms Create SQL WHERE IN from delimited values with custom controls
Introduction
A fundamental operation in data-centric Window Forms application is filtering and searching for data. This article focuses on working with SQL-Server WHERE IN for a SELECT statement to return a container, in this case, the container is a DataTable of data where the user enters a delimited string of values to search a specific field in a custom TextBox control located in a class library.
Microsoft Access database uses the same syntax for a WHERE IN (see documentation). In the code for the TextBox, there are several places where casting is performed which would not be compatible with Microsoft Access but there are suitable replacement functions.
SELECT *
FROM Orders
WHERE ShipRegion In ('Avon','Glos','Som')
TextBoxWhere custom TextBox
This TextBox inherits from the standard Windows Form TextBox control with added functionality to create a SQL WHERE IN clause using ColumnName property (string) to specify which column to use for the WHERE IN with the property DataType (Enum) to specify the data type of the field in the ColumnName property. The SelectStatement property (string) is for the SELECT statement excluding the WHERE clause which the TextBoxWhere control creates.
SQL Basics
There are literally countless scenarios for performing a WHERE IN from querying a single table to many tables using joins while the base concept to perform a WHERE IN remains the same, the difference is proper aliasing tables.
User Interface Basics
Performing a WHERE IN from a user interface may be done with pre-defined base SELECT statements to providing a user interface which allows users to construct a SELECT statement from ComboBox, ListBox, and similar controls to piece together a SELECT statement which is out of the scope of this article. In this article each SELECT statement is pre-defined.
Below are the SELECT statements used in the sample code.
Private ReadOnly _selectStatementForStrings As String =
<SQL>
SELECT
supplier_id,
supplier_name,
city,
[state]
FROM WhereInSimple
</SQL>.Value
Private ReadOnly _selectStatementForDateTime As String =
<SQL>
SELECT
supplier_id,
supplier_name,
CAST(some_Date AS DATE) AS Registered
FROM WhereInSimple
</SQL>.Value
The above syntax is much better to create an SQL statement than using string concatenation. Create the statement in either SSMS (SQL-Server Management Studio) or create a new SQL statement from Visual Studio Server Explorer, ensure the statement executes then insert into a XML Literals (C# does not have this) as done above.
Embedded expressions may also be used in tangent with XML Literals as shown below. Caveat, generally in cases such as the one below pTableName (string) should be checked for spaces and apostrophes that are properly escaped with the appropriate escape token e.g. Order Details would be [Order Details].
Figure 1
Dim selectStatement =
<SQL>
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<%= pTableName %>'
</SQL>.Value
Using the TextBox
Working from the following code sample,
- Provides values for the WHERE IN clause by setting the Text property while in an application the user would enter these values with a separator between each value.
- Provide a valid SELECT statement without a WHERE clause, this will be added by the TextBox function CreateWhereStatement.
- Select the column for the WHERE IN clause. Note there is code in DataOperations.vb to get a list of column names in ColumnNames method and to compliment this method there is TableNames method to get table names for the desired table.
- Set the separator (char) if a comma is not acceptable as a comma is the default separator.
- Call CreateWhereStatement() method to create the WHERE clause.
- To validate the SELECT statement is valid to use IsValid property of the TextBox.
- If the statement is valid to pass the statement to a method to query the database (see figure 3 for an example from this article's source code).
Figure 2
Private Sub ValidButton1_Click(sender As Object, e As EventArgs) Handles ValidButton1.Click
TextBoxWhere1.Text = "Google,Kimberly-Clark,Tyson Foods"
TextBoxWhere1.SelectStatement = _selectStatementForStrings
TextBoxWhere1.DataType = DataTypes.String
TextBoxWhere1.Separator = ","c
TextBoxWhere1.ColumnName = "supplier_name"
TextBoxWhere1.CreateWhereStatement()
If TextBoxWhere1.IsValid Then
DataGridView1.DataSource = _dataOperations.PopulateDataDataGridView(TextBoxWhere1.Statement)
_dataOperations.TableNames()
If Not _dataOperations.IsSuccessFul Then
MessageBox.Show(_dataOperations.LastExceptionMessage)
End If
Else
DataGridView1.DataSource = Nothing
End If
End Sub
Figure 3
This method uses the SQL SELECT statement generated by the custom TextBox to return a DataTable to the caller. A DataTable is one option while other options may be to create a List(Of T).
has exception, mLastException, and Connection string are part of a NuGet package BaseConnectionLibrary, the full source is available at the following repository.
Public Function PopulateDataDataGridView(pSelectStatement As String) As DataTable
mHasException = False
Dim dt As New DataTable
Using cn As New SqlClient.SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlClient.SqlCommand With {.Connection = cn}
cmd.CommandText = pSelectStatement
cn.Open()
Try
dt.Load(cmd.ExecuteReader)
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return dt
End Function
Predefined code samples 
In the Visual Studio solution, within the form project several scenarios are setup. The TextBox is the custom TextBox where the buttons below feed off the TextBox to create valid and invalid SQL statements for strings, dates and numerics. By running the form project and reviewing the underlying code will allow ease of learning how to use this TextBox in your project.
Using in your project
Copy the class project FormsControl into a Visual Studio solution followed by adding a reference to FormsControl class project to a form project. Build the Visual Studio solution followed by opening a form to use the TextBox which will be displayed at the top of the IDE ToolBox.
Alternatives
There are many other ways to perform a WHERE IN for a SELECT statement where in some cases not much different than the custom TextBox presented here. One example is to use a custom control, specifically, a custom ComboBox with a dropdown of checked items as many times providing searching and filtering can take up a good deal of space on a form. If space is not limited a CheckedListBox would be a good choice other a custom ComboBox..
The first part needed is code to dynamically create a WHERE IN without regards to the type of values but instead be generic as done in BuildWhereInClause and AddParamsToCommand.
The following code module resides in the same class project as the custom TextBox which performs WHERE IN. The code within this code module can be seen either as very simple or complex depending on the knowledge of the developer. If changes are desired they always can be reverted by copying the original from the GitHub repository.
To use the custom ComboBox, install via NuGet Package Manager console.
Install-Package Common.CheckComboBox -Version 1.0.0
Once installed the ComboBox will appear in the IDE Toolbox, place one on a form as with any standard control. Next populate the Items, for example, here the Items are populated with a list of contact types read from a SQL-Server table.
Source code is taken from the GitHub repository for this article
''' <summary>
''' Get a list of all contact types
''' </summary>
''' <returns></returns>
Public Function ContactTypeList() As List(Of ContactType)
mHasException = False
Dim contactTypes As New List(Of ContactType)
Using cn As New SqlClient.SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlClient.SqlCommand With {.Connection = cn}
cmd.CommandText = "SELECT ContactTypeIdentifier, ContactTitle FROM dbo.ContactType"
Try
cn.Open()
Dim reader = cmd.ExecuteReader()
While reader.Read()
contactTypes.Add(New ContactType() With
{
.ContactTypeIdentifier = reader.GetInt32(0),
.ContactTitle = reader.GetString(1)
})
End While
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return contactTypes
End Function
Set the Items in Form load or shown event.
ContactTypesCheckedComboBox1.Items.AddRange(
_dataOperations.ContactTypeList().Select(Function(data) data.ContactTitle).ToArray())
The following method accepts a list of string representing contact types obtained from the custom ComboBox
''' <summary>
''' Get data
''' </summary>
''' <param name="pContactTitleList">List of contact titles</param>
''' <returns>Populated DataTable or empty DataTable for runtime error</returns>
Public Function ReadCustomersByContactType(pContactTitleList As List(Of String)) As DataTable
mHasException = False
' field which the WHERE IN will use
Dim parameterPrefix = "CT.ContactTitle"
' Base SELECT Statement
Dim selectStatement =
<SQL>
SELECT C.CustomerIdentifier ,
C.CompanyName ,
C.ContactName ,
C.ContactTypeIdentifier ,
FORMAT(C.ModifiedDate, 'MM-dd-yyyy', 'en-US') AS ModifiedDate,
CT.ContactTitle
FROM dbo.Customers AS C
INNER JOIN dbo.ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier
WHERE <%= parameterPrefix %> IN ({0})
ORDER BY C.CompanyName
</SQL>.Value
' Builds the SELECT statement minus values
Dim CommandText = BuildWhereInClause(selectStatement, parameterPrefix, pContactTitleList)
Dim dt As New DataTable
Using cn As New SqlClient.SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlClient.SqlCommand With {.Connection = cn}
cmd.CommandText = CommandText
'
' Add values for command parameters
'
cmd.AddParamsToCommand(parameterPrefix, pContactTitleList)
Try
cn.Open()
dt.Load(cmd.ExecuteReader)
dt.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Using
End Using
Return dt
End Function
To call the above method in a Button click event.
Private Sub ReadFromDatabaseButton_Click(sender As Object, e As EventArgs) Handles ReadFromDatabaseButton.Click
BindingSource.DataSource = Nothing
If Not String.IsNullOrWhiteSpace(ContactTypesCheckedComboBox1.Text) Then
Dim contactTypes = ContactTypesCheckedComboBox1.CheckedItems.Cast(Of String).ToList()
BindingSource.DataSource = _dataOperations.ReadCustomersByContactType(contactTypes)
If Not _dataOperations.IsSuccessFul Then
MessageBox.Show(_dataOperations.LastExceptionMessage)
End If
Else
MessageBox.Show("Must select at least one contact type")
End If
End Sub
Example using the above
Summary
This article has presented two ways to create SQL SELECT WHERE IN statements using a custom TextBox and a custom ComboBox to use in Visual Studio, VB.NET Windows Forms solutions. The TextBox resides in a class project ready to use in any Windows Forms project while the ComboBox requires a NuGet package install. Within these code samples also presented is a library to assist with creating connections to databases and a uniform method to see if any runtime exceptions are thrown along with methods to check for runtime errors and return errors via an Exception object or simply an exception message.
See also
Writing SQL for your application
.NET: Defensive data programming
Working with parameterized SQL operations
SQL-Server dynamic C#: Dynamic WHERE IN conditions in C#for SQL Server
Source code
Source code resides in the following GitHub repository created with Visual Studio 2015 and used in a Visual Studio 2017 class project.