Home > Articles

Executing Ad Hoc Queries with VB .NET

  • Print
  • + Share This
Database applications typically include an assortment of prepackaged queries and reports, but no application can anticipate every user's needs. An ad hoc query system allows an application to meet unforeseen requirements. Rod Stephens explains how to use VB .NET database objects to perform queries and manipulate the database by executing SQL scripts.
From the author of

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.

  ' 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
        ' 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, _
  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), _
  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>")
        ' Display the field's value.
        row_string &= String.Format( _
          col_format(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
  • + Share This
  • 🔖 Save To Your Account