Executing SQL Statements at Runtime in VB 2005

Excerpt from Expert One-on-One Visual Basic 2005 Design and Development

by Rod Stephens

There are many ways you can make a program extensible at run-time. One of the most flexible methods for extending an application at run-time is scripting. By allowing the program to execute new code at run-time, you can enable it to do just about anything that you could do had you written the code ahead of time, at least in theory.

This article explains how a program can execute SQL statements at run-time. Structured Query Language (SQL) is a relatively easy-to-learn language for interacting with databases. It includes commands for creating, reading, editing, and deleting data.

SQL also includes commands that manipulate the database itself. Its commands let you create and drop tables, add and remove indexes, and so forth.

Visual Studio provides objects that interact with databases by executing SQL statements, so providing scripting capabilities is fairly easy.

The first part of this article explains how a program can execute queries written by the user. Then I’ll show how a program can provide a tool that makes building queries easier and safer. The final section dealing with SQL scripts shows how a program can execute more general SQL statements to modify and delete data, and to alter the database’s structure.

Note that this article provides only brief coverage of database programming as it applies to scripting in Visual Basic, and it omits lots of details. For more in-depth coverage of database programming, see a book about database programming in Visual Basic .NET, such as my book Visual Basic .NET Database Programming (Indianapolis: Que, 2002). Also, if you are not familiar with safe scripting practices, you should read the "Scripting Safely" section of Chapter 9, "Scripting," in my book, Expert One-on-One Visual Basic 2005 Design and Development (Wrox, 2007, ISBN: 978-0-470-05341-6), because like many other powerful and flexible tools, scripting comes with a certain degree of danger.

Running Queries

Executing a SQL query is fairly easy in Visual Basic. The following code shows a minimalist approach for executing a query and displaying the results in a

DataGridView

control:

' Open the connection.
m_Connection.Open()
' Select the data.
Dim data_table As New DataTable("Books")
Dim da As New OleDbDataAdapter(query, m_Connection)
' Get the data.
da.Fill(data_table)
' Display the result.
dgvBooks.DataSource = data_table
' Close the connection.
m_Connection.Close()

The code starts by opening the connection object named

m_Connection

. It creates a

DataTable

object to hold the selected data, and makes an

OleDbDataAdapter

to execute the query on the connection. It then uses the adapter to fill the

DataTable

. The code finishes by setting the

DataGridView

control’s

DataSource

property to the

DataTable

and closing the database connection.

Example program

UserSqlQuery

uses similar code to execute ad hoc queries. It provides some additional error-handling code, and does some extra work to format the

DataGridView

‘s columns (for example, it right-justifies numbers and dates). Download the example to see how the code works. Figure 1 shows the program in action.

Figure 1
Figure 1: Program

UserSqlQuery

lets the user execute ad hoc SQL queries.

The program uses a combo box to let the user enter a query, or select from a list of previously defined queries. When the code successfully executes a query, the program saves it in the combo box’s list and in the Registry so that it will be available to the user later.

Program

UserSqlQuery

also allows the user to select the fields displayed by the

DataGridView

control. When you select the Data menu’s Select Fields command, the program displays the dialog shown in Figure 2. The dialog lists the fields returned by the query, and lets the user select the ones that should be visible in the grid.

Figure 2
Figure 2: Program

UserSqlQuery

lets the user select the fields it displays in its grid.

The following code shows how the Select Fields dialog works:

Imports System.Data.OleDb
Public Class dlgSelectFields
    ' The DataGridView on the main form.
    Public TheDataGridView As DataGridView = Nothing
    ' 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 has been initialized.
        Debug.Assert(TheDataGridView IsNot Nothing, "TheDataGridView is Nothing")
        ' Set properties. (Done here so it's easier to find.)
        clbFields.CheckOnClick = True
        ' Fill the checked list box with the fields.
        clbFields.Items.Clear()
        For i As Integer = 0 To TheDataGridView.Columns.Count - 1
            clbFields.Items.Add(TheDataGridView.Columns(i).HeaderText)
            Dim checked As Boolean = TheDataGridView.Columns(i).Visible
            clbFields.SetItemChecked(i, checked)
        Next i
    End Sub
    ' Apply the user's selections to the DataGridView.
    Private Sub btnOk_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles btnOk.Click
        For i As Integer = 0 To TheDataGridView.Columns.Count - 1
            TheDataGridView.Columns(i).Visible = clbFields.GetItemChecked(i)
        Next i
    End Sub
End Class

The main program sets the form’s

TheDataGridView

variable to a reference to the

DataGridView

control before displaying the form.

When the dialog loads, the code loops through the grid’s

Columns

collection, adding each column’s header text to the dialog’s

CheckedListBox

control

clbFields

. It checks an item if the corresponding grid column is currently visible.

If the user clicks the OK button, the dialog again loops through grid’s columns, this time setting a column’s

Visible

property to

True

if the corresponding item is checked in the dialog’s list box. (You can download this example at www.vb-helper.com/one_on_one.htm.)

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.

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *