Ad hoc queries are great for users who know their way around SQL. For many users, however, even simple queries can be intimidating.
SelectCriteria uses the dialog shown in Figure 3 to let the user specify selection criteria in a simpler manner. The user selects a database field in the left column, picks an operator (
LIKE, and so forth) in the middle column, and enters a value string in the right column.
Figure 3: Program
SelectCriteriauses this dialog to build SQL queries.
When the user clicks OK, the program uses the selections on the dialog to build a SQL query. The selections shown in Figure 3 generate the following SQL statement:
SELECT * FROM Books WHERE Title LIKE '%Visual Basic' AND Pages >= 200 AND Rating >= 4 ORDER BY Title
The program also saves the criteria in the Registry so that the program can use them the next time it starts.
The following code shows how the Set Criteria dialog works:
Imports System.Data.OleDb Public Class dlgCriteria ' The DataGridView on the main form. Public TheDataGridView As DataGridView = Nothing ' The collection of criteria. Public TheCriteria As Collection = Nothing ' Delimiters for the field choices. Private m_Delimiters As Collection ' Load the list of database fields. Private Sub dlgSelectFields_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load ' Make sure TheDataGridView and TheCriteria have been initialized. Debug.Assert(TheDataGridView IsNot Nothing, "TheDataGridView is Nothing") Debug.Assert(TheCriteria IsNot Nothing, "TheCriteria is Nothing") ' Get the cell's template object. Dim dgv_cell As DataGridViewCell = dgvCriteria.Columns(0).CellTemplate Dim combo_cell As DataGridViewComboBoxCell = _ DirectCast(dgv_cell, DataGridViewComboBoxCell) ' Make a list of the fields. combo_cell.Items.Clear() m_Delimiters = New Collection For i As Integer = 0 To TheDataGridView.Columns.Count - 1 ' Add the name to the combo cell. Dim field_name As String = TheDataGridView.Columns(i).Name combo_cell.Items.Add(field_name) ' Get an appropriate delimiter for the data type. Dim delimiter As String = "" If TheDataGridView.Columns(i).ValueType Is GetType(String) Then delimiter = "'" ElseIf TheDataGridView.Columns(i).ValueType Is GetType(Date) Then ' Note that you need to handle dates differently in SQL Server. delimiter = "#" End If m_Delimiters.Add(delimiter, field_name) Next i ' Display current criteria. dgvCriteria.RowCount = TheCriteria.Count + 1 For r As Integer = 0 To TheCriteria.Count - 1 Dim condition As Criterion = DirectCast(TheCriteria(r + 1), Criterion) dgvCriteria.Rows(r).Cells(0).Value = condition.FieldName dgvCriteria.Rows(r).Cells(1).Value = condition.Op dgvCriteria.Rows(r).Cells(2).Value = condition.Value Next r End Sub ' Create the new Criteria collection. Private Sub btnOk_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnOk.Click ' Verify that each row has a field and operator. For r As Integer = 0 To dgvCriteria.RowCount - 2 If dgvCriteria.Rows(r).Cells(0).Value Is Nothing Then MessageBox.Show( _ "You must select a field for every row", _ "Missing Field", _ MessageBoxButtons.OK, _ MessageBoxIcon.Exclamation) dgvCriteria.CurrentCell = dgvCriteria.Rows(r).Cells(0) dgvCriteria.Select() Me.DialogResult = Windows.Forms.DialogResult.None Exit Sub End If If dgvCriteria.Rows(r).Cells(1).Value Is Nothing Then MessageBox.Show( _ "You must select an operator for every row", _ "Missing Field", _ MessageBoxButtons.OK, _ MessageBoxIcon.Exclamation) dgvCriteria.CurrentCell = dgvCriteria.Rows(r).Cells(1) dgvCriteria.Select() Me.DialogResult = Windows.Forms.DialogResult.None Exit Sub End If Next r ' Make the new criteria collection. TheCriteria = New Collection For r As Integer = 0 To dgvCriteria.RowCount - 2 Dim field_name As String = _ DirectCast(dgvCriteria.Rows(r).Cells(0).Value, String) Dim delimiter As String = _ DirectCast(m_Delimiters(field_name), String) Dim op As String = _ DirectCast(dgvCriteria.Rows(r).Cells(1).Value, String) Dim value As String = _ DirectCast(dgvCriteria.Rows(r).Cells(2).Value, String) TheCriteria.Add(New Criterion(field_name, op, value, _ delimiter)) Next r End Sub End Class
When the dialog loads, it gets the template cell for the dialog's first grid column. This cell acts as a template to define other cells in the column so that when the program defines the field names in its drop-down list, it defines the values for every drop-down in this column.
The program loops through the main program's
DataGridView columns, adding each column's name to the drop-down list. It saves a corresponding delimiter (apostrophe for strings, # for dates, an empty string for other data types) for the column in the
IS NULL, and
IS NOT NULL) were set for the second column at design time.
The program then loops through the collection named
TheCriteria, which contains
Criterion objects representing the program's current selection criteria. It uses the objects' properties to set field names, operators, and values in the dialog's grid.
When the user clicks the dialog's OK button, the program first verifies that every entered row has a non-blank field name and operator. It then loops through the grid's rows, builds
Criterion objects for each, and adds them to a new
The following code shows the
Criterion class. Its main purpose is just to store a field name, operator, and delimiter. It includes a couple of constructors to make creating objects easier. The
ToString function makes it easier to save objects in the Registry.
' Represent a condition such as FirstName >= 'Stephens'. Public Class Criterion Public FieldName As String Public Op As String Public Value As String Public Delimiter As String Public Sub New(ByVal new_field_name As String, ByVal new_op As String, _ ByVal new_value As String, ByVal new_delimiter As String) FieldName = new_field_name Op = new_op Value = new_value Delimiter = new_delimiter End Sub ' Initialize with tab-delimited values. Public Sub New(ByVal txt As String) Dim items() As String = txt.Split(CChar(vbTab)) FieldName = items(0) Op = items(1) Value = items(2) Delimiter = items(3) End Sub ' Return tab-delimited values. Public Overrides Function ToString() As String Return FieldName & vbTab & Op & vbTab & Value & vbTab & Delimiter End Function End Class
UserSqlQuery, example program
SelectCriteria lets the user select the columns that should be displayed in the main program's
DataGridView control. (You can download this example at www.vb-helper.com/one_on_one.htm.)
Though executing ad hoc queries is handy for users, developers need more powerful tools. Often, it's handy to execute more general SQL commands that add, modify, or delete data, or that modify the database's structure. Fortunately, Visual Basic's database tools make this relatively straightforward.
ExecuteNonQuery function shown in the following code executes a SQL command and returns a success or failure message. It simply creates an
OleDbCommand object associated with the command and the database connection and then executes it.
' Execute a non-query command and return a success or failure string. Public Function ExecuteNonQuery(ByVal conn As OleDbConnection, _ ByVal txt As String) As String Try ' Make and execute the command. Dim cmd As New OleDbCommand(txt, conn) cmd.ExecuteNonQuery() Return "> Ok" Catch ex As Exception Return "*** Error executing command ***" & vbCrLf & ex.Message End Try End Function
The following code shows a function that executes a query. It creates an
OleDbCommand object for the query and calls its
ExecuteReader command to run the query and get a data reader to process the results. It loops through the reader's columns, adding their names to a result string. It then uses the reader to loop through the returned rows and adds the rows' field values to the result string.
' Execute a query command and return ' the results or failure string. Public Function ExecuteQuery(ByVal conn As OleDbConnection, _ ByVal query As String) As String Try ' Make and execute the command. Dim cmd As New OleDbCommand(query, conn) Dim reader As OleDbDataReader = cmd.ExecuteReader() ' Display the column names. Dim row_txt As String = "" For c As Integer = 0 To reader.FieldCount - 1 row_txt &= ", " & reader.GetName(c) Next c ' Remove the initial ", ". Dim txt As String = "-----" & vbCrLf & _ row_txt.Substring(2) & vbCrLf & "-----" & vbCrLf ' Display the results. Do While reader.Read() row_txt = "" For c As Integer = 0 To reader.FieldCount - 1 row_txt &= ", " & reader.Item(c).ToString() Next c ' Remove the initial ", ". txt &= row_txt.Substring(2) & vbCrLf Loop reader.Close() Return txt Catch ex As Exception Return "*** Error executing SELECT statement ***" & vbCrLf & _ ex.Message End Try End Function
ExecuteSqlScript uses these functions to run general SQL scripts. It uses the following to break a script apart and call functions
ExecuteQuery to process the pieces.
' Execute the SQL script. Private Sub btnRun_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRun.Click ' Open the connection. m_Connection.Open() ' Break the script into semi-colon delimited commands. Dim commands() As String = Split(txtScript.Text, ";") ' Execute each command. Dim results As String = "" For i As Integer = 0 To commands.Length - 1 ' Clean up the command to see if it's non-blank. Dim cmd As String = _ commands(i).Replace(vbCr, " ").Replace(vbLf, " ").Trim() ' Execute only non-blank commands. If cmd.Length > 0 Then Debug.WriteLine(commands(i)) ' Display the command. results &= commands(i) & vbCrLf txtResults.Text = results txtResults.Select(results.Length, 0) txtResults.ScrollToCaret() txtResults.Refresh() ' See if this is a SELECT command. If cmd.ToUpper.StartsWith("SELECT") Then ' Execute the query. results = results & ExecuteQuery(m_Connection, commands(i)) Else ' Execute the non-query command. results = results & ExecuteNonQuery(m_Connection, commands(i)) End If results &= vbCrLf & "==========" & vbCrLf txtResults.Text = results txtResults.Select(results.Length, 0) txtResults.ScrollToCaret() txtResults.Refresh() End If Next i ' Close the connection. m_Connection.Close() results &= "Done" & vbCrLf txtResults.Text = results txtResults.Select(results.Length - 1, 10) txtResults.ScrollToCaret() End Sub
The code starts by opening a database connection. It reads the script in the
txtScript text box and splits it into semicolon-delimited commands.
For each command, the program removes carriage returns and line feeds, and decides whether the command is blank. If the command is not blank, the code determines whether the command begins with the
SELECT keyword and calls function
ExecuteQuery as appropriate. As it executes each command, it displays the command and its results in an output text box so that the user can see the results as work progresses.
Figure 4 shows the program in action. The upper text box shows the bottom of a script that drops the
Books table, creates a new
Books table, inserts several records, and then selects the records. The bottom text box shows the results.
Figure 4: Program
ExecuteSqlScriptlets you execute SQL scripts.
(You can download this example at www.vb-helper.com/one_on_one.htm.)
You may never want to give this sort of functionality to users, but you may find it useful during development and testing.
This article is excerpted from Chapter 9, "Scripting," of Expert One-on-One Visual Basic 2005 Design and Development (Wrox, 2007, ISBN: 978-0-470-05341-6) by Rod Stephens. Rod started out as a mathematician but while studying at MIT discovered the joys of programming and has been programming professionally ever since. During his career, he has worked on an eclectic assortment of applications in such fields as telephone switching, billing, repair dispatching, tax processing, wastewater treatment, and training for professional football players. Stephens has written 15 books that have been translated into half a dozen different languages, and more than 200 magazine articles covering Visual Basic, Visual Basic for Applications, Delphi, and Java. He writes three weekly newsletters (www.vb-helper.com/newsletter.html) that contain quick tips, tricks, and examples for Visual Basic developers. His popular VB Helper Web site (www.vb-helper.com) receives several million hits per month, and contains thousands of pages of tips, tricks, and example code for Visual Basic programmers, as well as example code for this book. Currently, Stephens is an author, consultant, and instructor at ITT Technical Institute.