The simplest query tool ever

A colleague wanted to do a database query from a computer without any database client. He needed a tool that allowed him to type in a SQL query, and receive a sortable grid with the result set. All of these features are built-in in the .NET Framework, and it didn’t take me more than 2 minutes to do an exe file with these features using Visual Studio 2010. I used .NET 2.0 because these basic features are available in that version, and he did not want to install a newer version of the .NET Framework.

This is the user interface: A tab strip with three tabs. One for a connection string, one for the query and one for the result grid. The first two contains a textbox each, and the third contains a DataGridView control. Also, the program has a status bar with a label and a toolstrip with two buttons; one for testing the connectionstring that the user enters in the textbox of the first tab, and one for executing the query and presenting the result in the grid.

The program consist one variable and four event handlers in one form. The variable holds the result set.

Private Ds As DataSet

The first function responds to the Load event of the form. This function restores the last values that from the textboxes. This is just for user convenience.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
    txtCn.Text = CType(Application.UserAppDataRegistry.GetValue("Cn", ""), String)
    txtQuery.Text = CType(Application.UserAppDataRegistry.GetValue("Query", ""), String)
End Sub

The second function responds to the Close event of the form. This function saves the values from the textboxes so that they can be restored (in the Load event) in the next session. Also, if needed, it disposes the dataset variable.

Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) _
Handles Me.FormClosed
    Application.UserAppDataRegistry.SetValue("Cn", txtCn.Text)
    Application.UserAppDataRegistry.SetValue("Query", txtQuery.Text)
    If Not Ds Is Nothing Then
        Ds.Dispose()
    End If
End Sub

This is the handler for the test button. It simply connects to the given data source, and tells if it succeeds or fails.

Private Sub btnTestConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnTestConnection.Click
    'Jump to the connection tab (the first one).
    TabControl1.SelectedTab = tabConnection

    'Do the test.
    Me.Cursor = Cursors.WaitCursor
    Dim Success As Boolean = False
    Try
        Using Cn As New SqlClient.SqlConnection(txtCn.Text)
            Cn.Open()
            Success = (Cn.State = ConnectionState.Open)
            Cn.Close()
        End Using
    Catch ex As Exception
    End Try
    Me.Cursor = Cursors.Default
    If Success Then
        lblStatus.Text = "Connection test succeeded."
        MessageBox.Show("Connection test succeeded.", Me.Text, _
            MessageBoxButtons.OK, MessageBoxIcon.Information)
    Else
        lblStatus.Text = "Connection test failed."
        MessageBox.Show("Connection test failed.", Me.Text, MessageBoxButtons.OK, _
            MessageBoxIcon.Error)
    End If
End Sub

Finally, this is the handler for the execute button.

Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnExecute.Click
    'Remove the current data table as data source of the grid.
    DataGridView1.DataSource = Nothing

    If Not Me.Ds Is Nothing Then
        'If there is an old data set in memory, dispose that.
        Me.Ds.Dispose()
        'To remember that no data is present, set the variable to NULL.
        Me.Ds = Nothing
    End If
  
    'Jump to the result tab (the third one).
    TabControl1.SelectedTab = tabResult

    'Do the query and bind the result. Quick and dirty error "handling".
    Me.Cursor = Cursors.WaitCursor
    Try
        Using Cn As New SqlClient.SqlConnection(txtCn.Text)
            Cn.Open()
            Using Cmd As New SqlClient.SqlCommand(txtQuery.Text, Cn)
                Using Da As New SqlClient.SqlDataAdapter(Cmd)
                    Ds = New DataSet()
                    Da.Fill(Ds)
                    If Ds.Tables.Count > 0 Then
                        DataGridView1.DataSource = Ds.Tables(0)
                        If Ds.Tables.Count > 1 Then
                            Me.Cursor = Cursors.Default
                            MessageBox.Show("More than one dataset was returned.", "Query", _
                                MessageBoxButtons.OK, MessageBoxIcon.Information)
                        End If
                    Else
                        Me.Cursor = Cursors.Default
                        MessageBox.Show("No dataset was returned.", "Query", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
                    End If
                End Using
            End Using
            Cn.Close()
        End Using
        lblStatus.Text = "Success."
    Catch ex As Exception
        lblStatus.Text = "Failed. " & ex.Message
            Me.Cursor = Cursors.Default
        MessageBox.Show(ex.Message, "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
    Me.Cursor = Cursors.Default
End Sub

So, making a personal query tool doesn’t have to take more than a couple of minutes in .NET.

Using the .NET Framework from PowerShell

If you want to call a static function (Shared in Visual Basic) in the .NET Framework, you type in the full name of the class (including the namespaces) followed by double colon (::) and the name of the function you want to call.

The function Load in the Assembly class can be used to load .NET assemblys from the GAC, so if you want to use the .NET Framework, it’s a good start to load the parts of the .NET Framework you need.

[System.Reflection.Assembly]::Load(“System.Windows.Forms, Version=2.0.50727.1433, Culture=neutral, PublicKeyToken=b77a5c561934e089″)

Notice that the string argument that is passed to the Load function is fairly complex. There is a chicken version of Load available where you only have to specify the path to the namespace you want to load. The name of that function is LoadWithPartialName, but it is not recommended that you use that function, because it is obsolete. If you are looking for the parameters that needs to be included in the string passed to the Loadfunction, check the properties window of the assembly in your GAC (usually C:\Windows\assembly).

When this is done, you can access the methods of the classes in System.Windows.Forms. This example will show a message box:

[System.Windows.Forms.MessageBox]::Show(“Hello”)

To create instances from classes in any loaded part of .NET Framework, use the New-Object cmdlet.

$x=New-Object System.Windows.Forms.SaveFileDialog

To use New-Object to create COM objects, just add the -comobject parameter to New-Object. If you have Excel installed, this will load and view it. First type:

$x=New-Object -comobject Excel.Application

…then:

$x.Visible=$True