VB.NET: Dynamic filter (WHERE) conditions when working with SQL server queries
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
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