Introduction

Displaying data for customers/clients is one of the common features of an application. For this article the focus will be on providing the ability to query data using dynamic WHERE conditions from SQL-Server in a VB.NET project were loading all possible data would be counterproductive. By counterproductive, meaning that all possible data would never be used at once. In these cases loading only what is needed for a specific task is where using dynamic queries is helpful. From there the user interface may provide additional functionality to farther shrink the data using filtering such as views into the data returned from the backend database.
Common SQL Statements with WHERE clauses

The most common is a WHERE clause as shown below were in this case return a specific person's last name e.g. O'Brien. In code if you didn't escape the name this statement below would throw an exception.
SELECT FirstName
      ,LastName
      ,GenderIdentifier
      ,IsDeleted
  FROM People1.dbo.Persons1
WHERE LastName = 'O'Brien'

To avoid this issue use a parameter.

DECLARE @LastName AS NVARCHAR(50) = 'O''Brien'
SELECT FirstName
      ,LastName
      ,GenderIdentifier
      ,IsDeleted
  FROM People1.dbo.Persons1
WHERE LastName = @LastName

How this would translate to code

Public Sub GetPerson()
    DefaultCatalog = "People1"
    Dim selectStatement = "SELECT FirstName,GenderIdentifier,IsDeleted  FROM dbo.Persons1 WHERE LastName = @LastName"
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
            cmd.Parameters.AddWithValue("@LastName", "O'Brien")
            cn.Open()
            Dim reader = cmd.ExecuteReader()
            If reader.HasRows Then
                reader.Read()
                Console.WriteLine(reader.GetString(0))
            End If
        End Using
    End Using
End Sub

Suppose the requirements is to find multiple countries? Use multiple parameters with OR's or IN clause, this works for writing SQL in SSMS (SQL-Server Management Studio) or in Visual Studio but not in a production application.

DECLARE @Country1 AS NVARCHAR(50) = 'France'
DECLARE @Country2 AS NVARCHAR(50) = 'Mexico'
SELECT CustomerIdentifier
      ,CompanyName
      ,ContactName
      ,ContactTitle
      ,City
      ,PostalCode
      ,Country
FROM dbo.Customers
WHERE Country IN (@Country1, @Country2)

Another option when working with a well designed database working with the above, countries would be in a reference table and joined by a foreign key e.g.

DECLARE @Country1 AS INT = 8
DECLARE @Country2 AS INT = 12
SELECT  Cust.CustomerIdentifier ,
        Cust.CompanyName ,
        Countries.CountryName
FROM    Customers AS Cust
        INNER JOIN Countries ON Cust.CountryIdentfier = Countries.id
WHERE dbo.Countries.id IN (@Country1,@Country2)

The same goes for ranges as in the example below using the BETWEEN clause.

SELECT OrderID
      ,ProductID
      ,UnitPrice
      ,Quantity
FROM dbo.OrderDetails
WHERE UnitPrice BETWEEN 9.80 AND 14.40

Taking things one step farther, a requirement is to have multiple ranges in your application. This is where this article teaches how to create muliple conditions for WHERE clauses. Not every situation is shown but plenty is shown along with the ability to try them out with the accompanying source code. 

Description

Proper database schema

The first step to building dynamic WHERE conditions is to have a proper relational schema. For example, a table for customers may have a contact type. This means there should be at least a contacts type reference table where the customers table would have a foreign key to the contacts types table using the primary key from the contact types table rather than using the contact type string value. Going without the contact types table means query execution will take longer along with maintenance e.g. a contact title changes or is spelled wrong from outside editing of one or more records.

Building blocks

Using the wrong control will make things difficult to understand and use. For instance, using a ListBox setup for multiple selections is not intuitive on how to select or deselect multiple choices while a CheckedListBox is intuitive.  Using TextBox controls in tangent with ComboBox controls to allow a user to build more than one date range presents challenges on usage by the user along with challenges for the developer on how to build and present inputs while a will thought out DataGridView done properly along with buttons is easy for the user to understand how to use and much easier for the developer to implement.

Then there are third party controls with built in functionality are always a choice yet more time than not these libraries offer more functionality than a developer needs to justify the cost. When a solution is architected around a third-party library and a developer takes time to learn the library this justifies the library cost.

Code foundation

Rather than sitting down and writing code the developer needs to think ahead to what constitutes a solid code flow e.g. proper classes, interfaces and controls as needed. Considerations for code reuse.

Examples

Date range(s)


For working with a date range TSQL BETWEEN clause is easy to obtain dates within a given range, present the user with two DateTimePicker controls, validate there are two proper dates e.g. end date is not before start date, start date is not after end date or start and end date are not the same (this of course may be fine but best to work this into a WHERE date field = @DateValue.

When the requirement is to provide 1-many ranges to query the data with an easy method to provide this ability is with a DataGridView setup with calendar columns for start/end range plus a combo box column for which date column to perform the BETWEEN on which is optional if there is only one date column. The DataGridView optionally may offer a checkbox column to allow the user to create several ranges but not use them all at once. The DataGridView should also offer a method to remove a range.

With the above requirements the code sample provides all the above. Setup, a class for remembering selected date ranges.
Public Class DataItem
    Public Property Id() As Integer
    Public Property Display() As String
    Public Property Checked() As Boolean
    Public Overrides Function ToString() As String
        Return Display
    End Function
End Class

In the form, the DataGridView columns are created and configured in the designer of the DataGridView. In form shown event a BindingSource is setup to hold List(Of DataItem) and the DataGridViewComboBox DataSource is set from reading column names from a SQL-Server table.

Public Function DateColumnNames() As List(Of String)
    Dim dateColumns As New List(Of String)
    Dim selectStatement = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " &
                            "WHERE TABLE_NAME = 'Orders' AND DATA_TYPE = 'datetime'"
  
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
            cn.Open()
            Dim reader = cmd.ExecuteReader()
            While reader.Read()
                dateColumns.Add(reader.GetString(0))
            End While
        End Using
    End Using
  
    Return dateColumns
  
End Function

Form Shown event
Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
    DataGridView1.AutoGenerateColumns = False
    _bsRangeList.DataSource = New List(Of DateTimeItem)
    ColumnNameColumn.DataSource = _ops.DateColumnNames()
    DataGridView1.DataSource = _bsRangeList
End Sub

There is an "Add" new range button with a single line of code.
Private Sub cmdAddNewItem_Click(sender As Object, e As EventArgs) Handles cmdAddNewItem.Click
    _bsRangeList.AddNew()
End Sub

The above triggers the following event which adds a new row to the DataGridView.
Private Sub _bsRangeList_AddingNew(sender As Object, e As AddingNewEventArgs) Handles _bsRangeList.AddingNew
    If My.Application.IsAuthorMachineRunningUnderDebugger Then
        e.NewObject = _mockedData.NewItem(_bsRangeList)
    Else
        e.NewObject = New DateTimeItem With {.Process = True, .StartRange = Now, .EndRange = Now}
    End If
End Sub


Once all the conditions are entered, press a button to validate row data for each range followed by selecting the data.

Private Sub cmdCreateWhere_Click(sender As Object, e As EventArgs) Handles cmdCreateWhere.Click
    _validItems = New List(Of DateTimeItem)
  
    If _bsRangeList.Count > 0 Then
  
        DataGridView1.Rows.Cast(Of DataGridViewRow).ToList().ForEach(Sub(row) row.ErrorText = "")
        Dim processResult = CType(_bsRangeList.DataSource, List(Of DateTimeItem)).
                Where(Function(data) data.Process AndAlso Not String.IsNullOrWhiteSpace(data.ColumnName))
  
        Dim incorrectResults = DataGridView1.Rows.Cast(Of DataGridViewRow).
                Where(Function(row) CType(row.Cells("ProcessColumn").Value, Boolean) = True _
                                    AndAlso row.Cells("ColumnNameColumn").Value Is Nothing).
                Select(Function(row) row.Index)
  
  
        If incorrectResults.Count() > 0 Then
            For index As Integer = 0 To incorrectResults.Count()
                DataGridView1.Rows(incorrectResults(index)).ErrorText = "Missing column name"
            Next
        End If
  
        Console.WriteLine(incorrectResults.Count())
  
        For Each item In processResult
            If item.StartRange.IsValidRange(item.EndRange) Then
                _validItems.Add(item)
            End If
        Next
  
        If _validItems.Count > 0 Then
  
            Dim generator As New BetweenGenerator
  
            If My.Application.IsAuthorMachineRunningUnderDebugger Then
                Console.WriteLine(
                    generator.CreateDatesBetween(_ops.OrdersBetweenStatement(), _validItems))
            End If
  
            _sqlStatement = generator.CreateDatesBetween(_ops.OrdersBetweenStatement(), _validItems)
  
            Dim dt As DataTable = _ops.ReadDateRange(_sqlStatement)
            If _ops.IsSuccessFul Then
                MainDataGridView.DataSource = dt
                lblCount.Text = dt.Rows.Count.ToString()
            Else
                MessageBox.Show(_ops.LastExceptionMessage)
            End If
  
        ElseIf _validItems.Count = 0 AndAlso _bsRangeList.Count > 0 Then
            MessageBox.Show($"One or more ranges are invalid.{Environment.NewLine}Please correct.")
        End If
    End If
  
End Sub

Backend database operations code.
Public Function ReadDateRange(pSelectStatement As String) As DataTable
    Dim dtResults As New DataTable
    mHasException = False
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = pSelectStatement}
            Try
                cn.Open()
                dtResults.Load(cmd.ExecuteReader())
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
        End Using
    End Using
  
    Return dtResults
End Function

The following SELECT statement was generated by the code above using a modified version of Microsoft NorthWind database. Note that each table has an alias which is also used in the code to generate the date range(s). When implementing this in your project keep this in mind else you will run into an exception thrown by SQL-Server.
SELECT  C.CompanyName ,
        O.OrderID ,
        E.FirstName + ' ' + E.LastName AS Employee ,
        FORMAT(O.OrderDate,'MM-dd-yyyy') AS Ordered,
        FORMAT(O.RequiredDate,'MM-dd-yyyy') AS [Required],
        FORMAT(O.ShippedDate,'MM-dd-yyyy') AS Shipped,
        Shippers.CompanyName AS ShipperName ,
        O.Freight ,
        O.ShipAddress ,
        O.ShipCity ,
        O.ShipPostalCode ,
        O.ShipCountry
FROM    Orders AS O
        INNER JOIN Customers AS C ON O.CustomerIdentifier = C.CustomerIdentifier
        INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
        INNER JOIN Shippers ON O.ShipVia = Shippers.ShipperID
         WHERE O.ShippedDate BETWEEN '2014-07-24' AND '2014-09-05' OR O.ShippedDate BETWEEN '2014-09-05' AND '2014-10-09'

All of the above used TSQL BETWEEN clause which does not work for all client requirements.

Returning items not in a range.

Your client wants the capability to read specific country or countries and optionally by contact type. In this case the best data option is to use TSQL IN clause using primary keys for obtaining country and or contacts or just contacts.

For this a suitable controls are CheckedListBoxes and a single button. The user may check off items to include for returning data, press the button to read back data.



Code behind that starts the process to create the WHERE conditon. Note there are checks done to see if there are actual selections e.g. if there are no items checked signified by "()" then do not continue. This check is also done in the backend class.

Private Sub cmdCreateWhere_Click(sender As Object, e As EventArgs) Handles cmdCreateWhere.Click
  
    MainDataGridView.DataSource = Nothing
  
    Dim contacts = $"({String.Join(",", contactsCheckedListBox.Items.Cast(Of DataItem).
                                        Where(Function(item) item.Checked).Select(Function(item) item.Id))})"
  
    Dim countries = $"({String.Join(",", contriesCheckedListBox.Items.Cast(Of DataItem).
                                        Where(Function(item) item.Checked).Select(Function(item) item.Id))})"
  
    If contacts = "()" AndAlso countries = "()" Then
        MessageBox.Show("Please make one or more selections")
        Exit Sub
    End If
  
    Dim dt As DataTable = _ops.ReadInContactsAndCountries(contacts, countries)
  
    If _ops.IsSuccessFul AndAlso dt.Rows.Count > 0 Then
        MainDataGridView.DataSource = dt
    ElseIf _ops.HasException Then
        MessageBox.Show(_ops.LastExceptionMessage)
    End If
  
End Sub

Code to read data
Public Function ReadInContactsAndCountries(pContacts As String, pCountries As String) As DataTable
    Dim contactsClause = ""
    If pContacts <> "()" Then
        contactsClause = $"CT.ContactTypeIdentifier IN {pContacts}"
    End If
  
    Dim countriesClause = ""
    If pCountries <> "()" Then
        countriesClause = $"dbo.Countries.id IN {pCountries}"
    End If
  
    Dim whereCondition = ""
  
    If Not String.IsNullOrWhiteSpace(contactsClause) AndAlso Not String.IsNullOrWhiteSpace(countriesClause) Then
        whereCondition = String.Join(" AND ", {contactsClause, countriesClause})
    ElseIf Not String.IsNullOrWhiteSpace(contactsClause) Then
        whereCondition = contactsClause
    ElseIf Not String.IsNullOrWhiteSpace(countriesClause) Then
        whereCondition = countriesClause
    End If
  
    Dim dtResults As New DataTable
  
    mHasException = False
  
    If Not String.IsNullOrWhiteSpace(whereCondition) Then
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                Try
                    cmd.CommandText = String.Concat(InClauseStatement(), " WHERE ", whereCondition)
                    cn.Open()
  
                    dtResults.Load(cmd.ExecuteReader())
  
                    ' hide some columns as in this code sample DataGridView columns are auto generated
                    ' as aposed to the code sample for dates where columns are generated in the designer.
                    dtResults.Columns("CustomerIdentifier").ColumnMapping = MappingType.Hidden
                    dtResults.Columns("ContactIdentifier").ColumnMapping = MappingType.Hidden
                    dtResults.Columns("ContactTypeIdentifier").ColumnMapping = MappingType.Hidden
                    dtResults.Columns("CountryIdentfier").ColumnMapping = MappingType.Hidden
                    dtResults.Columns("InUse").ColumnMapping = MappingType.Hidden
  
                Catch ex As Exception
                    mHasException = True
                    mLastException = ex
                End Try
            End Using
        End Using
    End If
  
    Return dtResults
End Function

Testing

Everything starts requirements, not jumping in and writing code and cross your fingers that it works. Once the requirements are approved the next step is to write writing your SQL statements in your choice environment e.g. SSMS (SQL-Server Management Studio), Visual Studio etc., validate the statements against your requirements and be forewarned that the client most likely will see the results and want modifications and/or more.

The next step is to write code using what has been presented here adding your own flavor if you want too. Follow this up by either first writing unit test against your code which take a good deal of time and may more easily find problems and or bugs which need to be taken care of.

Summary

This article along with source code has given you the tools to begin to write dynamic SQL statements for your applications. As stated not all variations have been shown but the most common have been presented. The use of classes to break-up responsibilities provides leverage to move this code into projects other than windows forms.

See also

Transact SQL

Source code

Note, in the solution there is a script to create the database, tables and data under the project SqlScripts.
https://github.com/karenpayneoregon/vbDynamicWhereConditions