Share via


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.