Wrox Home  
Search
Expert One-on-One Visual Basic 2005 Design and Development
by Rod Stephens
February 2007, Paperback


Generating Queries

Ad hoc queries are great for users who know their way around SQL. For many users, however, even simple queries can be intimidating.

Example program 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 (>, >=, IS NULL, LIKE, and so forth) in the middle column, and enters a value string in the right column.

Figure 3
Figure 3: Program SelectCriteria uses 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 m_Delimiters collection.

The allowed operators (<, <=, =, >=, >, LIKE, 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 TheCriteria collection.

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

Like 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.)

Running Commands

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.

The 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

Example program ExecuteSqlScript uses these functions to run general SQL scripts. It uses the following to break a script apart and call functions ExecuteNonQuery and 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 ExecuteNonQuery or 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
Figure 4: Program ExecuteSqlScript lets 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.