Option Strict On Imports System.Data.OleDb Imports System.Data.SqlClient Public Class Form1 Inherits System.Windows.Forms.Form Dim blnTableDataChanged As Boolean Dim dsCurrentTable As New DataSet ' 'The intent of this program is to create a general-purpose SQL server explorer that can be used 'to display, browse, and update the data in all tables in all databases on a SQL server. 'This will be particularly useful for an MSDE server lacking client tools. ' 'The program will be able to open a SQL server and diplay a treeview of all databases on the 'server in a treeview. The treeview will allow a drill-down to the individual tables which 'when clicked will display in a data grid in the left pane of the form. This data grid will 'allow a complete view of the entire table and if it is updated the changes can be written 'back to the SQL server. 'May 22, 2004 #Region " Windows Form Designer generated code " Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call End Sub 'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub 'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer 'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu Friend WithEvents MenuItem1 As System.Windows.Forms.MenuItem Friend WithEvents TreeView1 As System.Windows.Forms.TreeView Friend WithEvents btnSave As System.Windows.Forms.Button Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid Friend WithEvents Label1 As System.Windows.Forms.Label Friend WithEvents lblDatabase As System.Windows.Forms.Label Friend WithEvents Label3 As System.Windows.Forms.Label Friend WithEvents lblTable As System.Windows.Forms.Label Friend WithEvents mnuNewServer As System.Windows.Forms.MenuItem Friend WithEvents mnuExit As System.Windows.Forms.MenuItem Friend WithEvents ImageList1 As System.Windows.Forms.ImageList Friend WithEvents grpGridInfo As System.Windows.Forms.GroupBox Friend WithEvents lblStatus As System.Windows.Forms.Label Private Sub InitializeComponent() Me.components = New System.ComponentModel.Container Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(Form1)) Me.MainMenu1 = New System.Windows.Forms.MainMenu Me.MenuItem1 = New System.Windows.Forms.MenuItem Me.mnuNewServer = New System.Windows.Forms.MenuItem Me.mnuExit = New System.Windows.Forms.MenuItem Me.TreeView1 = New System.Windows.Forms.TreeView Me.ImageList1 = New System.Windows.Forms.ImageList(Me.components) Me.btnSave = New System.Windows.Forms.Button Me.DataGrid1 = New System.Windows.Forms.DataGrid Me.Label1 = New System.Windows.Forms.Label Me.lblDatabase = New System.Windows.Forms.Label Me.Label3 = New System.Windows.Forms.Label Me.lblTable = New System.Windows.Forms.Label Me.grpGridInfo = New System.Windows.Forms.GroupBox Me.lblStatus = New System.Windows.Forms.Label CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit() Me.grpGridInfo.SuspendLayout() Me.SuspendLayout() ' 'MainMenu1 ' Me.MainMenu1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem1}) ' 'MenuItem1 ' Me.MenuItem1.Index = 0 Me.MenuItem1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.mnuNewServer, Me.mnuExit}) Me.MenuItem1.Text = "&File" ' 'mnuNewServer ' Me.mnuNewServer.Index = 0 Me.mnuNewServer.Text = "&New Server" ' 'mnuExit ' Me.mnuExit.Index = 1 Me.mnuExit.Text = "E&xit" ' 'TreeView1 ' Me.TreeView1.ImageList = Me.ImageList1 Me.TreeView1.Location = New System.Drawing.Point(0, 0) Me.TreeView1.Name = "TreeView1" Me.TreeView1.Size = New System.Drawing.Size(208, 416) Me.TreeView1.Sorted = True Me.TreeView1.TabIndex = 0 ' 'ImageList1 ' Me.ImageList1.ImageSize = New System.Drawing.Size(16, 16) Me.ImageList1.ImageStream = CType(resources.GetObject("ImageList1.ImageStream"), System.Windows.Forms.ImageListStreamer) Me.ImageList1.TransparentColor = System.Drawing.Color.Transparent ' 'btnSave ' Me.btnSave.Location = New System.Drawing.Point(8, 40) Me.btnSave.Name = "btnSave" Me.btnSave.Size = New System.Drawing.Size(456, 23) Me.btnSave.TabIndex = 1 Me.btnSave.Text = "Save Changes to Server" ' 'DataGrid1 ' Me.DataGrid1.DataMember = "" Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.DataGrid1.Location = New System.Drawing.Point(224, 96) Me.DataGrid1.Name = "DataGrid1" Me.DataGrid1.Size = New System.Drawing.Size(472, 320) Me.DataGrid1.TabIndex = 2 ' 'Label1 ' Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label1.Location = New System.Drawing.Point(8, 16) Me.Label1.Name = "Label1" Me.Label1.Size = New System.Drawing.Size(72, 16) Me.Label1.TabIndex = 3 Me.Label1.Text = "Database:" ' 'lblDatabase ' Me.lblDatabase.BackColor = System.Drawing.Color.White Me.lblDatabase.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D Me.lblDatabase.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.lblDatabase.Location = New System.Drawing.Point(88, 16) Me.lblDatabase.Name = "lblDatabase" Me.lblDatabase.Size = New System.Drawing.Size(120, 16) Me.lblDatabase.TabIndex = 3 ' 'Label3 ' Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.Label3.Location = New System.Drawing.Point(272, 16) Me.Label3.Name = "Label3" Me.Label3.Size = New System.Drawing.Size(48, 16) Me.Label3.TabIndex = 3 Me.Label3.Text = "Table:" ' 'lblTable ' Me.lblTable.BackColor = System.Drawing.Color.White Me.lblTable.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D Me.lblTable.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.lblTable.Location = New System.Drawing.Point(328, 16) Me.lblTable.Name = "lblTable" Me.lblTable.Size = New System.Drawing.Size(136, 16) Me.lblTable.TabIndex = 3 ' 'grpGridInfo ' Me.grpGridInfo.Controls.Add(Me.lblTable) Me.grpGridInfo.Controls.Add(Me.Label3) Me.grpGridInfo.Controls.Add(Me.Label1) Me.grpGridInfo.Controls.Add(Me.lblDatabase) Me.grpGridInfo.Controls.Add(Me.btnSave) Me.grpGridInfo.Controls.Add(Me.lblStatus) Me.grpGridInfo.Location = New System.Drawing.Point(224, 0) Me.grpGridInfo.Name = "grpGridInfo" Me.grpGridInfo.Size = New System.Drawing.Size(472, 96) Me.grpGridInfo.TabIndex = 5 Me.grpGridInfo.TabStop = False ' 'lblStatus ' Me.lblStatus.BackColor = System.Drawing.Color.DimGray Me.lblStatus.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte)) Me.lblStatus.ForeColor = System.Drawing.Color.SkyBlue Me.lblStatus.Location = New System.Drawing.Point(8, 64) Me.lblStatus.Name = "lblStatus" Me.lblStatus.Size = New System.Drawing.Size(456, 24) Me.lblStatus.TabIndex = 3 Me.lblStatus.TextAlign = System.Drawing.ContentAlignment.MiddleCenter ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(704, 425) Me.Controls.Add(Me.grpGridInfo) Me.Controls.Add(Me.DataGrid1) Me.Controls.Add(Me.TreeView1) Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon) Me.Menu = Me.MainMenu1 Me.Name = "Form1" Me.Text = "SQL Server Explorer" CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() Me.grpGridInfo.ResumeLayout(False) Me.ResumeLayout(False) End Sub #End Region Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load GetServerInfo() End Sub Private Sub GetServerInfo() 'Processing here will include: ' Display a window to accept SQL connection data (server, connection type, userid, password) ' Call a routine to populate the tree view ClearForm() Dim frmGetServerInfo As New frmSQLServer frmGetServerInfo.ShowDialog() If Not blnServerDataEntered Then Exit Sub GetDatabaseList() End Sub Private Sub GetDatabaseList() 'Open the server, look inside the Master database to get list of all other 'databases on the server Dim RootNode As TreeNode Dim intRowCounter As Integer Dim strSQLSelect As String Dim strSQLConnect As String ' Dim cnnGenericSQLConnection As New SqlConnection Dim sdaGenericSQLDataAdapter As New SqlDataAdapter Dim dsDatabaseList As New DataSet If blnSQLAuthentication Then strSQLConnect = "Initial Catalog=master;Data Source=" & strServerName & _ ";User ID=" & strUserID & ";password=" & strPassword & ";" Else 'For Windows NT authentication we omit all userid information strSQLConnect = "Initial Catalog=master;Data Source=" & strServerName & _ ";User ID=;" End If strSQLSelect = "SELECT name FROM sysdatabases" 'initialize the SQLDataAdapter with the SQL and connection strings 'and then use the SQLDataAdapter to fill the dataset with data StatusLine("Logon") Me.Refresh() Me.Activate() sdaGenericSQLDataAdapter = New SqlClient.SqlDataAdapter(strSQLSelect, strSQLConnect) Try sdaGenericSQLDataAdapter.Fill(dsDatabaseList) Catch ex As Exception MsgBox("Cannot get list of databases from server, error from Windows is: " & _ vbNewLine & ex.Message) StatusLine("Critical") Exit Sub End Try 'we now have the SQL server open, let's create our treeview root node StatusLine("Reading") Me.Refresh() Me.Activate() TreeView1.Nodes.Clear() RootNode = TreeView1.Nodes.Add(strServerName) RootNode.ImageIndex = 0 For intRowCounter = 0 To dsDatabaseList.Tables(0).Rows.Count() - 1 GetDatabaseTables(dsDatabaseList.Tables(0).Rows(intRowCounter).Item("Name").ToString) Next End Sub Private Sub GetDatabaseTables(ByVal DatabaseName As String) Dim cn As New OleDbConnection Dim schemaTable As DataTable Dim CurrentNode, TableNode As TreeNode Dim i As Integer 'This routine was copied from the Microsoft web site. I'm not sure if there is 'a better way to do this..... 'Be sure to use an account that has permission to list tables. If blnSQLAuthentication Then cn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & strServerName & _ ";User ID=" & strUserID & ";Password=" & strPassword & ";Initial Catalog=" & _ DatabaseName & ";" Else cn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & strServerName & _ ";User ID=;Initial Catalog=" & _ DatabaseName & ";" End If Try cn.Open() Catch ex As Exception MsgBox("Could not open database = " & DatabaseName & ", error from Windows is: " & ex.Message) StatusLine("Critical") Exit Sub End Try 'Retrieve schema information about tables. 'Because tables include tables, views, and other objects, 'restrict to just TABLE in the Object array of restrictions. schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _ New Object() {Nothing, Nothing, Nothing, "TABLE"}) 'We have the database open, let's add this database to the treeview CurrentNode = TreeView1.Nodes(0).Nodes.Add(DatabaseName) CurrentNode.ImageIndex = 1 CurrentNode.SelectedImageIndex = 1 'List the table name from each row in the schema table. For i = 0 To schemaTable.Rows.Count - 1 'for each table in the database, we want to add one node 'to our treeview TableNode = CurrentNode.Nodes.Add(schemaTable.Rows(i)!TABLE_NAME.ToString) TableNode.ImageIndex = 2 TableNode.SelectedImageIndex = 2 Next i 'Explicitly close - don't wait on garbage collection. cn.Close() StatusLine("Clear") End Sub Private Sub TreeView1_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect 'When the program first starts, the selected node is the root. When the root node 'is selected, we don't want to do anything If TreeView1.SelectedNode.Text = strServerName Then Exit Sub 'If the node clicked on is a table, we don't want to do anything If TreeView1.SelectedNode.Parent.Text = strServerName Then Exit Sub 'The user clicked on a table in the treeview, they want to abandon the 'current table If dsCurrentTable.HasChanges Then Dim response As MsgBoxResult response = MsgBox("The current table has been updated. If you navigate away " & vbNewLine & _ "from this table, your changes will be lost. Are you sure you " & vbNewLine & _ "want to do this?", MsgBoxStyle.YesNo) If response = MsgBoxResult.No Then MsgBox("Please save your changes and then proceed") Exit Sub End If End If 'we have a valid database name and table name so now we can fill the datagrid DatagridLoad(TreeView1.SelectedNode.Parent.Text, TreeView1.SelectedNode.Text) End Sub Private Sub DatagridLoad(ByVal DatabaseName As String, ByVal Tablename As String) Dim strSQLSelect As String Dim strSQLConnect As String Dim sdaGenericSQLDataAdapter As New SqlDataAdapter 'At this point we are going to populate the dataset with new data. Since 'it is a global variable it still has data from the last transaction - to 'clear it out we create a fresh dataset and copy it Dim BlankDataset As New DataSet dsCurrentTable = BlankDataset.Copy DataGrid1.Refresh() If blnSQLAuthentication Then strSQLConnect = "Initial Catalog=" & DatabaseName & _ ";Data Source=" & strServerName & ";User ID=" & strUserID & _ ";password=" & strPassword & ";" Else strSQLConnect = "Initial Catalog=" & DatabaseName & _ ";Data Source=" & strServerName & ";User ID=;" End If strSQLSelect = "SELECT * FROM [" & Tablename & "]" 'initialize the SQLDataAdapter with the SQL and connection strings 'and then use the SQLDataAdapter to fill the dataset with data sdaGenericSQLDataAdapter = New SqlClient.SqlDataAdapter(strSQLSelect, strSQLConnect) StatusLine("Reading") DataGrid1.Visible = True Me.Refresh() Me.Activate() Try sdaGenericSQLDataAdapter.Fill(dsCurrentTable) DataGrid1.DataSource = dsCurrentTable.Tables(0) Catch ex As Exception MsgBox("Error filling data grid, error from Windows is: " & vbNewLine & _ ex.Message) StatusLine("Critical") Exit Sub End Try 'Update the form with the database and table name we just loaded lblDatabase.Text = DatabaseName lblTable.Text = Tablename StatusLine("GridFilled") End Sub Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click ' If dsCurrentTable.HasChanges Then WriteDatabaseChanges() Else MsgBox("There are no table changes to save.") End If End Sub Private Sub WriteDatabaseChanges() Dim DatabaseName, Tablename, ErrorMsg, NoPrimaryKeyMsg As String Dim dsTableChanges As DataSet Dim strSQLSelect As String Dim strSQLConnect As String ' Dim cnnGenericSQLConnection As New SqlConnection Dim sdaGenericSQLDataAdapter As New SqlDataAdapter DatabaseName = lblDatabase.Text Tablename = lblTable.Text If blnSQLAuthentication Then strSQLConnect = "Initial Catalog=" & DatabaseName & _ ";Data Source=" & strServerName & ";User ID=" & strUserID & _ ";password=" & strPassword & ";" Else strSQLConnect = "Initial Catalog=" & DatabaseName & _ ";Data Source=" & strServerName & ";User ID=;" End If strSQLSelect = "SELECT * FROM [" & Tablename & "]" 'initialize the SQLDataAdapter with the SQL and connection strings sdaGenericSQLDataAdapter = New SqlClient.SqlDataAdapter(strSQLSelect, strSQLConnect) 'We dynamically build the update command without knowing the table schema Dim builder As New SqlCommandBuilder(sdaGenericSQLDataAdapter) 'Only write changed rows back to the server dsTableChanges = dsCurrentTable.GetChanges StatusLine("Writing") Me.Refresh() Me.Activate() Try sdaGenericSQLDataAdapter.Update(dsTableChanges) Catch ex As Data.DBConcurrencyException MsgBox("You tried to save data that someone else was modifying." & vbNewLine & _ "Please re-read the table, then re-apply your changes, and re-try your save") StatusLine("Concurrency") Exit Sub Catch ex As Exception NoPrimaryKeyMsg = "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information" ErrorMsg = ex.Message If InStr(1, ErrorMsg, NoPrimaryKeyMsg, CompareMethod.Text) > 0 Then MsgBox("This table does not have a primary key and due to the design" & vbNewLine & _ "of this program, changes cannot be saved to this table on the server") StatusLine("ProgramLimit") Else MsgBox("Error writing table changes back to server, error from Windows is: " & _ vbNewLine & ex.Message) StatusLine("Critical") End If Exit Sub End Try dsCurrentTable.AcceptChanges() StatusLine("Success") End Sub Private Sub ClearForm() 'clear everything on the form, for instance when connecting 'to a new server TreeView1.Nodes.Clear() StatusLine("Clear") ClearDatagrid() lblDatabase.Text = "" lblTable.Text = "" Me.Refresh() Me.Activate() End Sub Private Sub ClearDatagrid() Dim BlankDataset As New DataSet dsCurrentTable = BlankDataset.Copy DataGrid1.DataBindings.Clear() DataGrid1.Refresh() 'after all that, the datagrid is still showing old data 'Let's hide until we re-fill it DataGrid1.Visible = False End Sub Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing If dsCurrentTable.HasChanges Then Dim response As MsgBoxResult response = MsgBox("The current table has been updated. If you close the " & vbNewLine & _ "program now, your changes will be lost. Are you sure you " & vbNewLine & _ "want to do this?", MsgBoxStyle.YesNo) If response = MsgBoxResult.No Then MsgBox("Please save your changes and then proceed") e.Cancel = True End If End If End Sub Private Sub mnuNewServer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuNewServer.Click If dsCurrentTable.HasChanges Then Dim response As MsgBoxResult response = MsgBox("The current table has been updated. If you navigate away " & vbNewLine & _ "from this table, your changes will be lost. Are you sure you " & vbNewLine & _ "want to do this?", MsgBoxStyle.YesNo) If response = MsgBoxResult.No Then MsgBox("Please save your changes and then proceed") Exit Sub End If End If GetServerInfo() End Sub Private Sub Form1_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Resize 'when the size of the form changes, we need to change the size of the 'treeview and listview to adjust TreeView1.Width = CInt(Me.ClientSize.Width / 4) TreeView1.Height = Me.ClientSize.Height DataGrid1.Width = Me.ClientSize.Width - TreeView1.Width DataGrid1.Left = TreeView1.Width DataGrid1.Height = Me.ClientSize.Height - grpGridInfo.Height grpGridInfo.Width = Me.ClientSize.Width - TreeView1.Width grpGridInfo.Left = TreeView1.Width End Sub Private Sub StatusLine(ByVal MsgType As String) 'By putting all the messages here in one routine we can easily change the 'color and wording of messages without hunting around in the code Dim CriticalColor As Color = Color.Orange Dim AdvisoryColor As Color = Color.Yellow Dim StatusColor As Color = Color.LightBlue Dim SuccessColor As Color = Color.LightGreen MsgType = UCase(MsgType) Select Case MsgType Case "CRITICAL" lblStatus.ForeColor = CriticalColor lblStatus.Text = "A critical error occurred while accessing the server!" Case "SUCCESS" lblStatus.ForeColor = SuccessColor lblStatus.Text = "The data was successfully updated on the server" Case "READING" lblStatus.ForeColor = StatusColor lblStatus.Text = "Data is being read from the server..." Case "WRITING" lblStatus.ForeColor = StatusColor lblStatus.Text = "Data is being written to the server..." Case "LOGON" lblStatus.ForeColor = StatusColor lblStatus.Text = "Logging onto the server..." Case "GRIDFILLED" lblStatus.ForeColor = AdvisoryColor lblStatus.Text = "If you modify a cell you must click on another cell for the change to register" Case "PROGRAMLIMIT" lblStatus.ForeColor = AdvisoryColor lblStatus.Text = "A program design limitation was encountered" Case "CONCURRENCY" lblStatus.ForeColor = CriticalColor lblStatus.Text = "Update to server failed, the data was being modified" Case "CLEAR" lblStatus.ForeColor = AdvisoryColor lblStatus.Text = "" Case Else lblStatus.ForeColor = AdvisoryColor lblStatus.Text = "Unknown error, string passed to error routine = " _ & MsgType End Select End Sub End Class