Connecting to a Database

' --- Module-level: shared by the whole form ---
Private _bs As New BindingSource()
Private _dt As New DataTable()

' --- Form_Load: load data, wire everything up ---
Private Sub Form_Load(...) Handles MyBase.Load
    LoadData()
    WireUpControls()
End Sub

Private Sub LoadData()
    _dt.Clear()
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Using da As New SqliteDataAdapter("SELECT * FROM Students ORDER BY Name", conn)
            da.Fill(_dt)
        End Using
    End Using
    _bs.DataSource = _dt      ' attach DataTable to BindingSource
End Sub

Private Sub WireUpControls()
    ' DataGridView shows the whole table
    dgvStudents.DataSource = _bs

    ' Individual TextBoxes track the current row automatically
    txtName.DataBindings.Add("Text", _bs, "Name",  True)
    txtGrade.DataBindings.Add("Text", _bs, "Grade", True)

    ' A Label can show any field from the current row
    lblClass.DataBindings.Add("Text", _bs, "Class", True)

    ' BindingNavigator: wire to the same BindingSource
    BindingNavigator1.BindingSource = _bs

    ' Record counter label (e.g., "3 / 15")
    _bs.PositionChanged += Sub()
        lblPos.Text = $"{_bs.Position + 1} / {_bs.Count}"
    End Sub
End Sub

' --- Navigate programmatically ---
_bs.MoveFirst()     ' go to row 0
_bs.MovePrevious()  ' row - 1 (clamped at 0)
_bs.MoveNext()      ' row + 1 (clamped at Count-1)
_bs.MoveLast()      ' go to last row
_bs.Position = 4    ' jump to index 4

' --- Read the current row ---
Dim row   = CType(_bs.Current, DataRowView).Row
Dim name  = row("Name").ToString()
Dim grade = CDbl(row("Grade"))

' --- Live filter: same syntax as DataTable.Select() ---
Private Sub ApplyFilter()
    Dim parts As New List(Of String)

    ' Text search on Name
    If txtSearch.Text.Length > 0 Then
        parts.Add($"Name LIKE '%{txtSearch.Text}%'")
    End If

    ' Minimum grade
    If nudMinGrade.Value > 0 Then
        parts.Add($"Grade >= {nudMinGrade.Value}")
    End If

    ' Class filter
    If cboClass.SelectedIndex > 0 Then
        parts.Add($"Class = '{cboClass.Text}'")
    End If

    _bs.Filter = If(parts.Count > 0, String.Join(" AND ", parts), "")
    lblCount.Text = $"{_bs.Count} of {_dt.Rows.Count} records"
End Sub

' --- Sort by column ---
Private Sub SortByColumn(colName As String)
    ' Toggle ASC / DESC
    If _bs.Sort = colName & " ASC" Then
        _bs.Sort = colName & " DESC"
    Else
        _bs.Sort = colName & " ASC"
    End If
End Sub

' --- Clear all filters ---
Private Sub ClearFilter()
    _bs.Filter = ""
    _bs.Sort   = ""
    txtSearch.Clear()
    nudMinGrade.Value = 0
    cboClass.SelectedIndex = 0
End Sub

' --- Live search as user types ---
Private Sub txtSearch_TextChanged(...) Handles txtSearch.TextChanged
    ApplyFilter()    ' filter updates instantly — no database round-trip
End Sub

' --- After dgv.DataSource is set, customise columns ---
Private Sub CustomiseGrid()
    With dgvStudents
        .AutoGenerateColumns     = True    ' let ADO.NET create columns first
        .AutoSizeColumnsMode     = DataGridViewAutoSizeColumnsMode.Fill
        .SelectionMode           = DataGridViewSelectionMode.FullRowSelect
        .MultiSelect             = False
        .ReadOnly                = True    ' grid itself read-only; edit via form fields
        .RowHeadersVisible       = False   ' hide left row-number header
        .AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue

        ' Rename column headers
        .Columns("StudentID").HeaderText = "ID"
        .Columns("Name").HeaderText      = "Full Name"
        .Columns("Grade").HeaderText     = "Grade (%)"

        ' Set column widths
        .Columns("StudentID").Width = 40
        .Columns("Name").Width      = 180
        .Columns("Grade").Width     = 80
        .Columns("Class").Width     = 60

        ' Hide a column (e.g., keep ID for lookups but don't show it)
        .Columns("StudentID").Visible = False

        ' Format a numeric column (2 decimal places)
        .Columns("Grade").DefaultCellStyle.Format      = "N1"
        .Columns("Grade").DefaultCellStyle.Alignment    = DataGridViewContentAlignment.MiddleCenter

        ' Right-align numbers
        .Columns("Grade").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        ' Bold header font
        .ColumnHeadersDefaultCellStyle.Font = New Font("Segoe UI", 9, FontStyle.Bold)
    End With
End Sub

' --- Colour-code rows: red if grade < 60, green if >= 90 ---
Private Sub dgvStudents_RowPrePaint(...) Handles dgvStudents.RowPrePaint
    Dim grade = CDbl(dgvStudents.Rows(e.RowIndex).Cells("Grade").Value)
    If      grade < 60 Then dgvStudents.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.MistyRose
    ElseIf  grade >= 90 Then dgvStudents.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.Honeydew
    Else                      dgvStudents.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.White
    End If
End Sub

' --- Tooltip on hover ---
Private Sub dgvStudents_CellMouseEnter(...) Handles dgvStudents.CellMouseEnter
    If e.RowIndex < 0 Then Return
    Dim grade = CDbl(dgvStudents.Rows(e.RowIndex).Cells("Grade").Value)
    Dim tip = If(grade < 60, "⚠ Below passing grade",
              If(grade >= 90, "⭐ Excellent!", "✓ Passing"))
    dgvStudents.Rows(e.RowIndex).Cells(e.ColumnIndex).ToolTipText = tip
End Sub

' --- Safe load: Try/Catch around all data access ---
Private Sub LoadData()
    Try
        _dt.Clear()
        Using conn As New SqliteConnection(CONN_STR)
            conn.Open()
            Using da As New SqliteDataAdapter("SELECT * FROM Students", conn)
                da.Fill(_dt)
            End Using
        End Using
        _bs.DataSource = _dt
        lblStatus.Text = $"Loaded {_dt.Rows.Count} records."
        lblStatus.ForeColor = Color.DarkGreen
    Catch ex As SqliteException
        ' SQLite-specific error (bad SQL, locked file, etc.)
        ShowError($"Database error: {ex.Message}")
    Catch ex As IO.FileNotFoundException
        ShowError($"Database file not found: {CONN_STR}")
    Catch ex As Exception
        ShowError($"Unexpected error: {ex.Message}")
    End Try
End Sub

Private Sub ShowError(msg As String)
    lblStatus.Text      = $"⚠ {msg}"
    lblStatus.ForeColor = Color.Red
    MessageBox.Show(msg, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Sub

' --- Connection test helper ---
Function TestConnection() As Boolean
    Try
        Using conn As New SqliteConnection(CONN_STR)
            conn.Open()
            Return True
        End Using
    Catch
        Return False
    End Try
End Function

' --- Form_Load: check before loading ---
Private Sub Form_Load(...) Handles MyBase.Load
    If Not TestConnection() Then
        MessageBox.Show("Cannot connect to database. Check file path.",
                        "Startup Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Return
    End If
    LoadData()
End Sub

' Complete student database form — ~60 lines of logic
Imports Microsoft.Data.Sqlite

Public Class frmStudents
    Private Const CONN_STR = "Data Source=students.db"
    Private _bs As New BindingSource()
    Private _dt As New DataTable()

    Private Sub Form_Load(...) Handles MyBase.Load
        EnsureTable() : LoadData() : SetupGrid() : WireBindings()
    End Sub

    Private Sub EnsureTable()
        Using conn As New SqliteConnection(CONN_STR)
            conn.Open()
            Using cmd As New SqliteCommand(
                "CREATE TABLE IF NOT EXISTS Students (" &
                "StudentID INTEGER PRIMARY KEY AUTOINCREMENT," &
                "Name TEXT, Grade REAL, Class TEXT)", conn)
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub

    Private Sub LoadData()
        Try
            _dt.Clear()
            Using conn As New SqliteConnection(CONN_STR)
                conn.Open()
                Using da As New SqliteDataAdapter("SELECT * FROM Students ORDER BY Name", conn)
                    da.Fill(_dt)
                End Using
            End Using
            _bs.DataSource = _dt
            lblCount.Text  = $"{_bs.Count} records"
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Load Error")
        End Try
    End Sub

    Private Sub SetupGrid()
        dgv.DataSource              = _bs
        dgv.SelectionMode           = DataGridViewSelectionMode.FullRowSelect
        dgv.ReadOnly                = True
        dgv.AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue
        dgv.Columns("StudentID").Visible    = False
        dgv.Columns("Name").HeaderText        = "Full Name"
        dgv.Columns("Grade").HeaderText       = "Grade (%)"
        dgv.Columns("Grade").DefaultCellStyle.Format = "N1"
    End Sub

    Private Sub WireBindings()
        BindingNavigator1.BindingSource = _bs
        txtName.DataBindings.Add("Text", _bs, "Name",  True)
        txtGrade.DataBindings.Add("Text", _bs, "Grade", True)
        cboClass.DataBindings.Add("Text", _bs, "Class", True)
    End Sub

    ' --- Live search ---
    Private Sub txtSearch_TextChanged(...) Handles txtSearch.TextChanged
        _bs.Filter = If(txtSearch.Text = "", "", $"Name LIKE '%{txtSearch.Text}%'")
        lblCount.Text = $"{_bs.Count} records"
    End Sub

    ' --- Save changes back to DB ---
    Private Sub btnSave_Click(...) Handles btnSave.Click
        Dim id    = CInt(CType(_bs.Current, DataRowView).Row("StudentID"))
        Dim name  = txtName.Text
        Dim grade = CDbl(txtGrade.Text)
        Dim cls   = cboClass.Text
        Using conn As New SqliteConnection(CONN_STR)
            conn.Open()
            Using cmd As New SqliteCommand(
                "UPDATE Students SET Name=@N, Grade=@G, Class=@C WHERE StudentID=@ID", conn)
                cmd.Parameters.AddWithValue("@N",  name)
                cmd.Parameters.AddWithValue("@G",  grade)
                cmd.Parameters.AddWithValue("@C",  cls)
                cmd.Parameters.AddWithValue("@ID", id)
                cmd.ExecuteNonQuery()
            End Using
        End Using
        LoadData()
    End Sub
End Class