Imports System.Data
Imports System.Data.OleDb
Public Class Form1
' ** Fields
' current connection string and corresponding schema
Private _connString As String
Private _schema As OleDbSchema
' max number of records shown on the preview dialog
Private Const MAX_PREVIEW_RECORDS As Integer = 5000
' ** Ctor
Public Sub New()
Me.InitializeComponent()
' make combo owner-drawn
Dim cmb As ComboBox = Me._cmbConnString.ComboBox
cmb.DrawMode = DrawMode.OwnerDrawFixed
AddHandler cmb.DrawItem, New DrawItemEventHandler(AddressOf Me.cmb_DrawItem)
' load recently used connection strings
Dim mru As System.Collections.Specialized.StringCollection = My.Settings.RecentConnections
If (Not mru Is Nothing) Then
Dim connString As String
For Each connString In mru
Me._cmbConnString.Items.Add(connString)
Next
End If
' autosize toolstrip
AddHandler Me._toolStrip.Layout, New LayoutEventHandler(AddressOf Me._toolStrip_Layout)
AddHandler Me._tab.SelectedIndexChanged, New EventHandler(AddressOf Me._tab_SelectedIndexChanged)
End Sub
' ** Properties
''' <summary>
''' Gets or sets the connection string.
''' </summary>
Public Property ConnectionString() As String
Get
Return Me._connString
End Get
Set(ByVal value As String)
If (value <> Me.ConnectionString) Then
' this may take a while
Me.Cursor = Cursors.WaitCursor
' look for item in the list
Dim items As ComboBox.ObjectCollection = Me._cmbConnString.Items
Dim index As Integer = items.IndexOf(value)
' get schema for the new connection string
Me._schema = OleDbSchema.GetSchema(value)
' handle good connection strings
If (Not Me._schema Is Nothing) Then
' add good values to the list
If (index < 0) Then
items.Insert(0, value)
ElseIf (index > 0) Then
items.RemoveAt(index)
items.Insert(0, value)
End If
' trim list
Do While (items.Count > Me._cmbConnString.MaxDropDownItems)
items.RemoveAt((items.Count - 1))
Loop
Else ' handle bad connection strings
' remove from list
If (index >= 0) Then
items.RemoveAt(index)
End If
' do not store bad values
value = String.Empty
End If
' save new value
Me._connString = value
' show new value in combo box and table tree
Me._cmbConnString.Text = value
Me.UpdateTableTree()
' new connection, clear SQL
Me._txtSql.Text = String.Empty
' update ui
Me.UpdateUI()
' done
Me.Cursor = Nothing
End If
End Set
End Property
''' <summary>
''' Gets a SQL statement that corresponds to the element that
''' is currently selected (table, view, stored procedure, or
''' explicit sql statement).
''' </summary>
Public ReadOnly Property SelectStatement() As String
Get
' table/view/sproc
If (Me._tab.SelectedTab Is Me._pgTables) Then
Dim nd As TreeNode = Me._treeTables.SelectedNode
If nd Is Nothing Then Return String.Empty
If nd.Tag Is Nothing Then Return String.Empty
Return OleDbSchema.GetSelectStatement(TryCast(nd.Tag, DataTable))
Else ' explicit sql statement
Return Me._txtSql.Text
End If
End Get
End Property
' ** Event Handlers
' pick a new connection
Private Sub _btnConnPicker_Click(ByVal sender As Object, ByVal e As EventArgs) Handles _btnConnPicker.Click
' release mouse capture to avoid wait cursor
Me._toolStrip.Capture = False
' get starting connection string
' (if empty or no provider, start with SQL source as default)
Dim connString As String = Me._cmbConnString.Text
If (String.IsNullOrEmpty(connString) OrElse (connString.IndexOf("provider=", StringComparison.OrdinalIgnoreCase) < 0)) Then
connString = "Provider=SQLOLEDB.1;"
End If
' let user change it
Me.ConnectionString = OleDbConnString.EditConnectionString(Me, connString)
End Sub
' close the form
Private Sub _btnOK_Click(ByVal sender As Object, ByVal e As EventArgs) Handles _btnOK.Click
MyBase.DialogResult = DialogResult.OK
MyBase.Close()
End Sub
Private Sub _btnCancel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles _btnCancel.Click
MyBase.DialogResult = DialogResult.Cancel
MyBase.Close()
End Sub
' preview data when user clicks the preview button
Private Sub _btnPreviewData_Click(ByVal sender As Object, ByVal e As EventArgs) Handles _btnPreviewData.Click
Me.PreviewData()
End Sub
' invoke SQL builder
Private Sub _btnSqlBuilder_Click(ByVal sender As Object, ByVal e As EventArgs) Handles _btnSqlBuilder.Click
Using f As QueryDesignerDialog = New QueryDesignerDialog
f.Font = Me.Font
f.ConnectionString = Me.ConnectionString
f.SelectStatement = Me.SelectStatement
If (f.ShowDialog(Me) = DialogResult.OK) Then
Me._txtSql.Text = f.SelectStatement
Me._tab.SelectedTab = Me._pgSql
Me.UpdateUI()
End If
End Using
End Sub
' user pressed enter: apply new connection string
Private Sub _cmbConnString_KeyPress(ByVal sender As Object, ByVal e As KeyPressEventArgs) Handles _cmbConnString.KeyPress
If (e.KeyChar = ChrW(13)) Then
e.Handled = True
Me.ConnectionString = Me._cmbConnString.Text
End If
End Sub
' new connection picked: apply new connection string
Private Sub _cmbConnString_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles _cmbConnString.SelectedIndexChanged
Me.ConnectionString = Me._cmbConnString.Text
End Sub
' validating combo: apply new connection string
Private Sub _cmbConnString_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles _cmbConnString.Validating
Me.ConnectionString = Me._cmbConnString.Text
End Sub
' adjust combobox size to fill toolstrip
Private Sub _toolStrip_Layout(ByVal sender As Object, ByVal e As LayoutEventArgs)
Dim width As Integer = (Me._toolStrip.ClientRectangle.Width - 15)
Dim item As ToolStripItem
For Each item In Me._toolStrip.Items
If ((Not item Is Me._cmbConnString) AndAlso item.Visible) Then
width = (width - ((item.Width - item.Margin.Left) - item.Margin.Right))
End If
Next
width = Math.Max(100, Math.Min(500, width))
Me._cmbConnString.Width = width
End Sub
' enable/disable buttons when a node or tab is selected
Private Sub _treeTables_AfterSelect(ByVal sender As Object, ByVal e As TreeViewEventArgs) Handles _treeTables.AfterSelect
Me.UpdateUI()
End Sub
Private Sub _tab_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
Me.UpdateUI()
End Sub
' preview data when user double-clicks a node
Private Sub _treeTables_DoubleClick(ByVal sender As Object, ByVal e As EventArgs) Handles _treeTables.DoubleClick
Dim nd As TreeNode = Me._treeTables.SelectedNode
If (Not nd Is Nothing) Then
If TypeOf nd.Tag Is DataTable Then
Me.PreviewData()
End If
End If
End Sub
' preview data when user hits enter on the tree
Private Sub _treeTables_KeyPress(ByVal sender As Object, ByVal e As KeyPressEventArgs) Handles _treeTables.KeyPress
If (e.KeyChar = ChrW(13)) Then
e.Handled = True
Me.PreviewData()
End If
End Sub
' trim items in combo using ellipsis (they're very long)
Private Sub cmb_DrawItem(ByVal sender As Object, ByVal e As DrawItemEventArgs)
Dim fmt As New StringFormat
fmt.LineAlignment = StringAlignment.Center
fmt.Trimming = StringTrimming.EllipsisPath
Dim text As String = CStr(Me._cmbConnString.Items.Item(e.Index))
[text] = OleDbConnString.TrimConnectionString([text])
Dim brush As Brush = IIf(((e.State And DrawItemState.Selected) <> DrawItemState.None), SystemBrushes.HighlightText, SystemBrushes.WindowText)
e.DrawBackground()
e.Graphics.DrawString([text], Me._cmbConnString.Font, brush, e.Bounds, fmt)
e.DrawFocusRectangle()
End Sub
' form closing: save recently used connection strings
Protected Overrides Sub OnFormClosing(ByVal e As FormClosingEventArgs)
Dim mru As New System.Collections.Specialized.StringCollection
Dim item As String
For Each item In Me._cmbConnString.Items
mru.Add(item)
Next
My.Settings.RecentConnections = mru
My.Settings.Save()
MyBase.OnFormClosing(e)
End Sub
' preview data for currently selected node
Private Sub PreviewData()
' make sure we have a select statement
If Not String.IsNullOrEmpty(Me.SelectStatement) Then
' create table to load with data and display
Dim dt As New DataTable("Query")
' if a table/view is selected, get table name and parameters
If (Me._tab.SelectedTab Is Me._pgTables) Then
' get table/view name
Dim table As DataTable = TryCast(Me._treeTables.SelectedNode.Tag, DataTable)
dt.TableName = table.TableName
' get view parameters if necessary
Dim parms As List(Of OleDbParameter) = OleDbSchema.GetTableParameters(table)
If ((Not parms Is Nothing) AndAlso (parms.Count > 0)) Then
Dim dlg As New ParametersDialog(parms)
dlg.Font = Me.Font
If (dlg.ShowDialog(Me) <> DialogResult.OK) Then
Return
End If
End If
End If
' get data
Try
Using da As OleDbDataAdapter = New OleDbDataAdapter(Me.SelectStatement, Me.ConnectionString)
' get data
da.Fill(0, MAX_PREVIEW_RECORDS, New DataTable() {dt})
' show the data
Using dlg As DataPreviewDialog = New DataPreviewDialog(dt, Me.Font, MyBase.Size)
dlg.ShowDialog(Me)
End Using
End Using
Catch x As Exception
Me.Warning(My.Resources.ErrGettingData, New Object() {x.Message})
End Try
End If
End Sub
' update table tree to reflect new connection string
Private Sub UpdateTableTree()
' initialize table tree
Dim nodes As TreeNodeCollection = Me._treeTables.Nodes
nodes.Clear()
Dim ndTables As New TreeNode(My.Resources.Tables, 0, 0)
Dim ndViews As New TreeNode(My.Resources.Views, 1, 1)
Dim ndProcs As New TreeNode(My.Resources.StoredProcedures, 2, 2)
' populate using current schema
If (Not Me._schema Is Nothing) Then
' populate the tree
Me._treeTables.BeginUpdate()
Dim dt As DataTable
For Each dt In Me._schema.Tables
' create new node, save table in tag property
Dim node As New TreeNode(dt.TableName)
node.Tag = dt
' add new node to appropriate parent
Select Case OleDbSchema.GetTableType(dt)
Case TableType.Table
ndTables.Nodes.Add(node)
node.ImageIndex = 0
node.SelectedImageIndex = 0
Exit Select
Case TableType.View
ndViews.Nodes.Add(node)
node.ImageIndex = 1
node.SelectedImageIndex = 1
Exit Select
Case TableType.Procedure
ndProcs.Nodes.Add(node)
node.ImageIndex = 2
node.SelectedImageIndex = 2
Exit Select
End Select
Next
' add non-empty nodes to tree
Dim nd As TreeNode
For Each nd In New TreeNode() {ndTables, ndViews, ndProcs}
If (nd.Nodes.Count > 0) Then
nd.Text = String.Format("{0} ({1})", nd.Text, nd.Nodes.Count)
nodes.Add(nd)
End If
Next
' expand tables node
ndTables.Expand()
' done
Me._treeTables.EndUpdate()
Me._tab.SelectedIndex = 0
End If
End Sub
' enable/disable buttons
Private Sub UpdateUI()
' enable sql builder button if we have some tables
Me._btnSqlBuilder.Enabled = (Me._treeTables.Nodes.Count > 0)
' enable data preview if we a select statement
Me._btnPreviewData.Enabled = Not String.IsNullOrEmpty(Me.SelectStatement)
End Sub
' issue a warning
Private Sub Warning(ByVal format As String, ByVal ParamArray args As Object())
Dim msg As String = String.Format(format, args)
MessageBox.Show(Me, msg, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Sub
End Class
Submit an article or tip