Anders Hesselbom

The simplest query tool ever

2009-12-21

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.

Categories: VB.NET

Tags: .NET Framework, QTool

One response to “The simplest query tool ever”

1. Switch between connections in QTool - WinSoft.se says:

   2010-04-20 at 17:30

   […] added a new feature to the simple portable SQL Server query tool, QTool, that I made some time ago (here). Now, you can enter several connections strings in the Connection String tab, and use a drop down […]