
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.ComponentModel
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms

Public Class frmMain
  Inherits System.Windows.Forms.Form

  Private mFieldsSelected As Integer
  Private mFieldNames() As String

  Private strConnect As String
  Private MyDS As DataSet
  Private MyTable As DataTable

#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 Label1 As System.Windows.Forms.Label
  Friend WithEvents Label2 As System.Windows.Forms.Label
  Friend WithEvents btnSubmit As System.Windows.Forms.Button
  Friend WithEvents btnExit As System.Windows.Forms.Button
  Friend WithEvents txtQuery As System.Windows.Forms.TextBox
  Friend WithEvents Label3 As System.Windows.Forms.Label
  Friend WithEvents dtgGrid As System.Windows.Forms.DataGrid
  Friend WithEvents OleDbConnection1 As System.Data.OleDb.OleDbConnection
  Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbUpdateCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbDeleteCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter
  Friend WithEvents OleDbCommand1 As System.Data.OleDb.OleDbCommand
  Friend WithEvents lstTables As System.Windows.Forms.ListBox
  Friend WithEvents lstFields As System.Windows.Forms.ListBox
  Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
  Friend WithEvents btnClear As System.Windows.Forms.Button
  Friend WithEvents btnWhere As System.Windows.Forms.Button
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader()
    Me.Label1 = New System.Windows.Forms.Label()
    Me.Label2 = New System.Windows.Forms.Label()
    Me.btnSubmit = New System.Windows.Forms.Button()
    Me.btnExit = New System.Windows.Forms.Button()
    Me.txtQuery = New System.Windows.Forms.TextBox()
    Me.Label3 = New System.Windows.Forms.Label()
    Me.dtgGrid = New System.Windows.Forms.DataGrid()
    Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection()
    Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand()
    Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand()
    Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand()
    Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand()
    Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter()
    Me.OleDbCommand1 = New System.Data.OleDb.OleDbCommand()
    Me.lstTables = New System.Windows.Forms.ListBox()
    Me.lstFields = New System.Windows.Forms.ListBox()
    Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog()
    Me.btnClear = New System.Windows.Forms.Button()
    Me.btnWhere = New System.Windows.Forms.Button()
    CType(Me.dtgGrid, System.ComponentModel.ISupportInitialize).BeginInit()
    Me.SuspendLayout()
    '
    'Label1
    '
    Me.Label1.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
    Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.Label1.Location = New System.Drawing.Point(8, 8)
    Me.Label1.Name = "Label1"
    Me.Label1.Size = New System.Drawing.Size(176, 16)
    Me.Label1.TabIndex = 1
    Me.Label1.Text = "Table Names"
    Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
    '
    'Label2
    '
    Me.Label2.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
    Me.Label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.Label2.Location = New System.Drawing.Point(216, 8)
    Me.Label2.Name = "Label2"
    Me.Label2.Size = New System.Drawing.Size(176, 16)
    Me.Label2.TabIndex = 3
    Me.Label2.Text = "Field Names"
    Me.Label2.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
    '
    'btnSubmit
    '
    Me.btnSubmit.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.btnSubmit.Location = New System.Drawing.Point(424, 40)
    Me.btnSubmit.Name = "btnSubmit"
    Me.btnSubmit.Size = New System.Drawing.Size(88, 24)
    Me.btnSubmit.TabIndex = 4
    Me.btnSubmit.Text = "&Submit"
    '
    'btnExit
    '
    Me.btnExit.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.btnExit.Location = New System.Drawing.Point(424, 152)
    Me.btnExit.Name = "btnExit"
    Me.btnExit.Size = New System.Drawing.Size(88, 24)
    Me.btnExit.TabIndex = 5
    Me.btnExit.Text = "E&xit"
    '
    'txtQuery
    '
    Me.txtQuery.Location = New System.Drawing.Point(56, 184)
    Me.txtQuery.Multiline = True
    Me.txtQuery.Name = "txtQuery"
    Me.txtQuery.Size = New System.Drawing.Size(456, 40)
    Me.txtQuery.TabIndex = 6
    Me.txtQuery.Text = ""
    '
    'Label3
    '
    Me.Label3.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
    Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.Label3.Location = New System.Drawing.Point(8, 184)
    Me.Label3.Name = "Label3"
    Me.Label3.Size = New System.Drawing.Size(48, 40)
    Me.Label3.TabIndex = 7
    Me.Label3.Text = "Query:"
    Me.Label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight
    '
    'dtgGrid
    '
    Me.dtgGrid.AlternatingBackColor = System.Drawing.Color.WhiteSmoke
    Me.dtgGrid.ColumnHeadersVisible = CType(configurationAppSettings.GetValue("dtgGrid.ColumnHeadersVisible", GetType(System.Boolean)), Boolean)
    Me.dtgGrid.DataMember = ""
    Me.dtgGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText
    Me.dtgGrid.Location = New System.Drawing.Point(8, 232)
    Me.dtgGrid.Name = "dtgGrid"
    Me.dtgGrid.Size = New System.Drawing.Size(504, 168)
    Me.dtgGrid.TabIndex = 8
    '
    'OleDbDataAdapter1
    '
    Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1
    Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
    Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
    Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
    '
    'lstTables
    '
    Me.lstTables.Location = New System.Drawing.Point(8, 24)
    Me.lstTables.Name = "lstTables"
    Me.lstTables.ScrollAlwaysVisible = True
    Me.lstTables.Size = New System.Drawing.Size(176, 147)
    Me.lstTables.TabIndex = 9
    '
    'lstFields
    '
    Me.lstFields.Location = New System.Drawing.Point(216, 24)
    Me.lstFields.Name = "lstFields"
    Me.lstFields.ScrollAlwaysVisible = True
    Me.lstFields.Size = New System.Drawing.Size(176, 147)
    Me.lstFields.TabIndex = 10
    '
    'OpenFileDialog1
    '
    Me.OpenFileDialog1.CheckFileExists = False
    Me.OpenFileDialog1.CheckPathExists = False
    '
    'btnClear
    '
    Me.btnClear.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.btnClear.Location = New System.Drawing.Point(424, 104)
    Me.btnClear.Name = "btnClear"
    Me.btnClear.Size = New System.Drawing.Size(88, 24)
    Me.btnClear.TabIndex = 11
    Me.btnClear.Text = "&Clear Query"
    '
    'btnWhere
    '
    Me.btnWhere.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
    Me.btnWhere.Location = New System.Drawing.Point(424, 8)
    Me.btnWhere.Name = "btnWhere"
    Me.btnWhere.Size = New System.Drawing.Size(88, 24)
    Me.btnWhere.TabIndex = 12
    Me.btnWhere.Text = "&Add Where"
    '
    'frmMain
    '
    Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    Me.ClientSize = New System.Drawing.Size(520, 405)
    Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnWhere, Me.btnClear, Me.lstFields, Me.lstTables, Me.dtgGrid, Me.Label3, Me.txtQuery, Me.btnExit, Me.btnSubmit, Me.Label2, Me.Label1})
    Me.Name = "frmMain"
    Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
    Me.Text = "SQL Tester"
    CType(Me.dtgGrid, System.ComponentModel.ISupportInitialize).EndInit()
    Me.ResumeLayout(False)

  End Sub

#End Region

  Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim FileName As String
    Dim AdoConn As New ADODB.Connection()

    FileName = GetFileName()
    If FileName.Length = 0 Then
      Exit Sub
    End If
    Me.Text = "SQL Tester: " & FileName

    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source="
    strConnect &= FileName
    AdoConn.Open(strConnect)
    Dim MyCat As New ADOX.Catalog()
    MyCat.ActiveConnection = AdoConn
    Dim tbl As ADOX.Table, col As ADOX.Column

    For Each tbl In MyCat.Tables
      lstTables.Items.Add(tbl.Name.ToString)
      'For Each col In tbl.Columns
      '  clbFields.Items.Add(tbl.Name.ToString & ":" & col.Name, False)
      ' Next
    Next
    AdoConn.Close()

  End Sub


  Private Sub lstTables_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstTables.SelectedIndexChanged
    Dim AdoConn As New ADODB.Connection()
    Dim i As Integer, buff As String

    AdoConn.Open(strConnect)
    Dim MyCat As New ADOX.Catalog()
    MyCat.ActiveConnection = AdoConn
    Dim tbl As ADOX.Table, col As ADOX.Column
    lstFields.Items.Clear()
    lstFields.Sorted = False
    For Each tbl In MyCat.Tables
      If tbl.Name.ToString = lstTables.SelectedItem Then
        lstFields.Items.Add("*")
        For Each col In tbl.Columns
          lstFields.Items.Add(col.Name)
          i += 1
        Next
      End If
    Next
    ReDim mFieldNames(i)
    AdoConn.Close()
  End Sub

  Private Function GetFileName() As String
    ' Purpose:  This function attempts to open an Access database
    '
    ' Argument list:
    '   none
    '
    ' Return value
    '   string      the name of the file selected or an empty 
    '               string if cancelled.
    '
    Dim NewFileName As String
    Dim NewFile As OpenFileDialog = New OpenFileDialog()
    Dim MyChoice As DialogResult

    With NewFile
      .Filter = "Access Files (*.mdb)|*.mdb|All Files (*.*)|*.*"
      .FilterIndex = 1                      ' Assume text files
      .DefaultExt = "mdb"                   ' Ditto
      .InitialDirectory = "D:\VSNET\Crystal Reports\Samples\Database\"
      .CheckFileExists = False
      .readonlychecked = True
      .Title = "Open Access Data File"
    End With

    MyChoice = NewFile.ShowDialog           ' What did they do?

    If MyChoice = DialogResult.Cancel Then  ' Bail out?
      NewFileName = ""
    Else
      NewFileName = NewFile.FileName
    End If

    If NewFileName.Length > 0 Then
      Return NewFileName
    End If
  End Function

  Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
    Me.Dispose()
  End Sub

  Private Sub lstFields_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstFields.DoubleClick
    Dim SpaceIndicator As Integer, i As Integer

    Dim buff As String

    If lstFields.SelectedItem = "*" Then
      mFieldsSelected = 0
      ReDim mFieldNames(lstFields.Items.Count + 1)
      For i = 1 To lstFields.Items.Count - 1
        mFieldNames(mFieldsSelected) = lstFields.Items(i)
        mFieldsSelected += 1
      Next
      'mFieldsSelected = lstFields.Items.Count - 1
      txtQuery.Text = "SELECT *  "
      Exit Sub
    End If
    If txtQuery.TextLength = 0 Then
      mFieldsSelected = 0
      txtQuery.Text = "SELECT "
    End If
    SpaceIndicator = InStr(lstFields.SelectedItem, " ")
    If SpaceIndicator Then
      buff = "[" & lstFields.SelectedItem & "]"
    Else
      buff = lstFields.SelectedItem
    End If
    mFieldNames(mFieldsSelected) = buff
    mFieldsSelected += 1
    txtQuery.Text &= buff & ","
  End Sub

  Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
    Dim FromFound As Integer, i As Integer, row As Long
    Dim WhichField As Integer, j As Integer
    Dim temp As String, buff As String

    temp = txtQuery.Text
    FromFound = temp.LastIndexOf(" FROM ")
    If FromFound = -1 Then
      temp = temp.Remove(temp.Length - 1, 1)
      FromFound = InStr(lstFields.SelectedItem, " ")
      If FromFound Then
        buff = "[" & lstTables.SelectedItem & "]"
      Else
        buff = lstTables.SelectedItem
      End If
      temp &= " FROM " & buff
      txtQuery.Text = temp
    End If
    'temp = "SELECT DISTINCT CourseIndex,CourseName FROM Courses INNER JOIN ClassRoster ON ClassRoster.CourseID = Courses.CourseIndex"
    Dim ReConnect As New OleDbConnection(strConnect)
    ReConnect.Open()

    Dim MyGridStyle As New DataGridTableStyle()

    InitDataGrid()

    dtgGrid.AlternatingBackColor = Color.PaleGreen

    Dim MyQuery As New OleDbCommand(temp, ReConnect)
    Dim NewRecord As DataRow

    Try
      Dim MyData As OleDbDataReader = MyQuery.ExecuteReader()
      dtgGrid.CaptionText = "Records: " & CStr(MyData.RecordsAffected())
      For i = 0 To MyData.FieldCount - 1
        Dim txtCol As New DataGridTextBoxColumn()
        MyGridStyle.GridColumnStyles.Add(txtCol)
        txtCol.HeaderText = MyData.GetName(i)
        MyTable.Columns.Add(MyData.GetName(i))
      Next

      Do While MyData.Read()
        NewRecord = MyTable.NewRow()
        row += 1
        MyDS.Tables("SQLOutput").Rows.Add(NewRecord)
        For i = 0 To MyData.FieldCount - 1
          If MyData.IsDBNull(i) Then
            NewRecord.Item(i) = "NULL "
          Else
            NewRecord.Item(i) = MyData.GetValue(i).ToString
          End If
        Next i
        MyDS.AcceptChanges()
      Loop
    Catch
      MessageBox.Show("Probably an ill-formed SQL statement", "Error in Database Read")
    End Try
    dtgGrid.CaptionText = "Records: " & CStr(row)

  End Sub

  Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
    txtQuery.Text = ""
    lstFields.Items.Clear()
    lstTables.ClearSelected()

  End Sub

  Private Sub btnWhere_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWhere.Click
    Dim FromFound As Integer
    Dim temp As String

    temp = txtQuery.Text
    FromFound = temp.LastIndexOf(" FROM ")
    If FromFound = -1 Then
      temp = temp.Remove(temp.Length - 1, 1)
      temp &= " FROM " & lstTables.SelectedItem
      txtQuery.Text = temp
    End If
    txtQuery.Text &= " WHERE"
    txtQuery.Focus()
    txtQuery.AppendText(" ")
    ' For debug
    'txtQuery.Text = "SELECT [Contact Last Name],City FROM Customer WHERE City = 'London'"
  End Sub

  Private Sub InitDataGrid()
    Dim i As Integer

    MyDS = New DataSet("SQLTest")
    MyTable = New DataTable("SQLOutput")

    'For i = 0 To mFieldsSelected - 1
    '  MyTable.Columns.Add(mFieldNames(i))
    'Next i
    MyDS.Tables.Add(MyTable)
    dtgGrid.DataSource = MyTable

  End Sub

  Private Sub AddRecord()
    Dim NewRecord As DataRow

    NewRecord = MyTable.NewRow()
    MyDS.Tables("SQLOutput").Rows.Add(NewRecord)

  End Sub
End Class
