Executing SQL Statements at Runtime in VB 2005
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.
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
' 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
DataSource property to the
DataTable and closing the database connection.
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: Program
UserSqlQuerylets 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.
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: Program
UserSqlQuerylets 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
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.)