Executing Ad Hoc Queries with VB .NET
- Statements that Select Data
- Statements that Do Not Select Data
- Other Methods for Selecting Data
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
In today's rapidly changing business world, it is common for requirements to change during the time it takes you to build a database application. Just when you think you've finished, the users drop a whole pile of new requirements on your desk. Sometimes, new requirements come before you've documented the old requirements. The users need new reports, new ways to modify the data, and new configuration tables.
You can satisfy some of these needs, for the more advanced users at least, by adding an ad hoc query capability to the application. The user enters a series of SQL commands, and the application executes them.
Many of the applications I've built contained tools for executing SQL scripts. These tools were restricted so only the more advanced users and database administrators could use them, and certain more powerful commands such as DROP TABLE were deactivated. Although I first thought these tools would be only rarely used, the "power users" took full advantage of them. In some cases, they were running their own customized reports to perform their daily duties while the application was still in beta.
With all of the different kinds of SQL statements that might be included in a script (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, etc.), you might think it would be hard to build a program to handle them all. Fortunately, the database itself does most of the work, so your program doesn't need to do everything itself. All it really needs to do is tell the database what to do and then process the results.
You can group SQL statements into to two categories: those that select data and those that do not. Whether a statement returns data determines how a VB .NET program can process the statement.
Statements that Select Data
If the statement selects data, the program must use some method to retrieve the results. One technique is to use a data adapter's Fill method to execute the statement and place the results in a DataTable.
The ExecuteQuery function shown in Listing 1 does exactly that. It begins by creating an OleDbDataAdapter object attached to an Access database. You could easily modify the code to use a SQL Server database by using an SqlDataAdapter object and by changing the connect string generated by the ConnectString function.
Function ExecuteQuery uses the data adapter's Fill method to perform the query and place the results in a DataTable object. The rest of its code deals with formatting the results.
The function loops through the results to find the length of the longest entry in each column. It saves the length of the columns' entries in the col_len array.
Next, the subroutine builds a format string for each column. For a column with maximum length of 7, the FormatString function returns a string of the form {0,-7} if the column has a string value, and {0,7} if the column has a numeric value. When it later uses these strings to format field values for output, these values left-justify strings and right-justify numbers.
ExecuteQuery then loops through the DataTable's Columns collection, adding the column names (appropriately justified) to its output string. It follows the names with a new line and a row of dashes. For each row of data in the DataTable, the routine adds the justified data values. It displays NULL values with the string NULL and byte array values with the string <byte>.
After it has added all of the values to the result string, ExecuteQuery returns the results. The main program displays them in a TextBox with a fixed-width font, so the data columns line up nicely. Listing 2 shows some sample output. All of the values in this example are strings, so they are left-justified.
Listing 1—Function ExecuteQuery executes a SQL statement that selects records
' Execute a query and return the results.
Private Function ExecuteQuery(ByVal query As String) As String
' (To use SQL Server, use a SqlDataAdapter object.)
Dim data_adapter As New OleDbDataAdapter( _
query, ConnectString())
Dim data_table As New DataTable()
' Execute the query and place the results in the DataSet.
data_adapter.Fill(data_table)
' Make room for the column sizes.
Dim max_col As Integer = data_table.Columns.Count - 1
Dim col_len() As Integer
ReDim col_len(max_col)
' Find the maximum length for each column.
Dim col As Integer
Dim row As Integer
Dim max_len As Integer
Dim new_len As Integer
Dim max_row As Integer = data_table.Rows.Count - 1
For col = 0 To max_col
' See how long the column name is.
max_len = data_table.Columns(col).ColumnName.Length
' Allow at least 4 characters so we can say NULL.
If max_len < 4 Then max_len = 4
' See how much room we need for this column's data type.
If data_table.Columns(col).DataType Is GetType(Byte()) Then
' Allow room for the text <byte>.
new_len = 6
End If
' Examine the entries in this column.
For row = 0 To max_row
' See if the value is NULL.
If data_table.Rows(row).ItemArray(col).GetType Is GetType(System.DBNull) Then
' Allow room for the text NULL.
new_len = 4
Else
' Allow room for the value as a string.
new_len = CStr(data_table.Rows(row).ItemArray(col)).Length
End If
If max_len < new_len Then max_len = new_len
Next row
' Save the column's maximum length.
col_len(col) = max_len
Next col
' Build column format strings.
Dim col_format() As String
ReDim col_format(max_col)
For col = 0 To max_col
col_format(col) = " " & _
FormatString( _
data_table.Columns(col).DataType, _
col_len(col))
Next col
' Add the column names to the result.
Dim row_string As String
row_string = ""
For col = 0 To max_col
row_string &= String.Format( _
col_format(col), _
data_table.Columns(col).ColumnName)
Next col
Dim results As String = row_string.Substring(1) & vbCrLf
' Add a row header separator row.
row_string = ""
For col = 0 To max_col
row_string &= " " & New String("-"c, col_len(col))
Next col
results &= row_string.Substring(1) & vbCrLf
' Add the values.
For row = 0 To max_row
row_string = ""
For col = 0 To max_col
If data_table.Rows(row).ItemArray(col).GetType _
Is GetType(System.DBNull) Then
' Display NULL.
row_string &= String.Format( _
col_format(col), "NULL")
ElseIf data_table.Rows(row).ItemArray(col).GetType _
Is GetType(Byte()) Then
' Display <byte>.
row_string &= String.Format( _
col_format(col), "<byte>")
Else
' Display the field's value.
row_string &= String.Format( _
col_format(col), _
data_table.Rows(row).Item(col))
End If
Next col
results &= row_string.Substring(1) & vbCrLf
Next row
Return vbCrLf & results
End Function
Listing 2—Sample output from the query SELECT * FROM Books
Title URL -------------------------------------- ---------------------------------- Advanced Visual Basic Techniques http://www.vb-helper.com/avbt.htm Bug Proofing Visual Basic http://www.vb-helper.com/err.htm Custom Controls Library http://www.vb-helper.com/ccl.htm Prototyping With Visual Basic http://www.vb-helper.com/proto.htm Ready-to-Run Delphi Algorithms http://www.vb-helper.com/da.htm Ready-to-Run Visual Basic Algorithms http://www.vb-helper.com/vba.htm Ready-to-Run Visual Basic Code Library http://www.vb-helper.com/vbcl.htm Visual Basic .NET and XML http://www.vb-helper.com/xml.htm Visual Basic .NET Database Programming http://www.vb-helper.com/vbdb.htm Visual Basic Graphics Programming http://www.vb-helper.com/vbgp.htm