Database Introduction

-- Read all columns from Students table
SELECT * FROM Students;

-- Read specific columns, filtered and sorted
SELECT StudentID, Name, Grade
FROM   Students
WHERE  Grade >= 70
ORDER BY Name ASC;

-- Pattern search with LIKE (% = wildcard)
SELECT * FROM Students WHERE Name LIKE 'A%';

-- Aggregate: count, average, max
SELECT COUNT(*), AVG(Grade), MAX(Grade) FROM Students;

-- Insert a new row
INSERT INTO Students (Name, Grade, Class)
VALUES ('Alice Wong', 88, '4A');

-- Update an existing row
UPDATE Students SET Grade = 92 WHERE StudentID = 5;

-- Delete a row
DELETE FROM Students WHERE StudentID = 5;

-- Create a table
CREATE TABLE IF NOT EXISTS Students (
    StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name      TEXT    NOT NULL,
    Grade     REAL    DEFAULT 0,
    Class     TEXT
);

Imports Microsoft.Data.Sqlite   ' NuGet: Microsoft.Data.Sqlite

' Connection string — database file in same folder as the exe
Private Const CONN_STR = "Data Source=students.db"

' --- Create the table if it doesn't exist yet ---
Private Sub CreateTable()
    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 NOT NULL," &
            "  Grade REAL DEFAULT 0," &
            "  Class TEXT" &
            ")", conn)
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

' --- Load all students into DataGridView ---
Private Sub LoadStudents()
    Dim dt As New DataTable()
    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
    dgvStudents.DataSource = dt        ' auto-generates columns
    lblCount.Text = $"{dt.Rows.Count} records"
End Sub

' --- Search by name ---
Private Sub SearchStudents(nameFilter As String)
    Dim dt As New DataTable()
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Dim sql = "SELECT * FROM Students WHERE Name LIKE @Filter ORDER BY Name"
        Using da As New SqliteDataAdapter(sql, conn)
            da.SelectCommand.Parameters.AddWithValue("@Filter", $"%{nameFilter}%")
            da.Fill(dt)
        End Using
    End Using
    dgvStudents.DataSource = dt
End Sub

' --- Form_Load: create table, load data ---
Private Sub Form_Load(...) Handles MyBase.Load
    CreateTable()
    LoadStudents()
End Sub

Imports System.Data.OleDb

' Connection string for .accdb (Access 2007–2016 format)
Private connStr As String =
    "Provider=Microsoft.ACE.OLEDB.12.0;" &
    "Data Source=C:\School\students.accdb"

' --- Load all records into DataGridView ---
Private Sub LoadData()
    Dim dt As New DataTable()
    Using conn As New OleDbConnection(connStr)
        conn.Open()
        Using da As New OleDbDataAdapter("SELECT * FROM Students", conn)
            da.Fill(dt)
        End Using
    End Using
    DataGridView1.DataSource = dt
End Sub

' --- Parameterised INSERT (OleDb uses ? placeholders, not @name) ---
Private Sub InsertStudent(name As String, grade As Double, cls As String)
    Using conn As New OleDbConnection(connStr)
        conn.Open()
        Dim sql = "INSERT INTO Students (Name, Grade, Class) VALUES (?, ?, ?)"
        Using cmd As New OleDbCommand(sql, conn)
            cmd.Parameters.AddWithValue("@Name",  name)
            cmd.Parameters.AddWithValue("@Grade", grade)
            cmd.Parameters.AddWithValue("@Class", cls)
            cmd.ExecuteNonQuery()
        End Using
    End Using
    LoadData()   ' refresh the grid
End Sub

' --- Get a single scalar value ---
Private Function GetStudentCount() As Integer
    Using conn As New OleDbConnection(connStr)
        conn.Open()
        Using cmd As New OleDbCommand("SELECT COUNT(*) FROM Students", conn)
            Return CInt(cmd.ExecuteScalar())
        End Using
    End Using
End Function

' --- DataReader: fast forward-only read ---
Private Sub ListTopStudents()
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Dim sql = "SELECT Name, Grade FROM Students WHERE Grade >= @Min ORDER BY Grade DESC"
        Using cmd As New SqliteCommand(sql, conn)
            cmd.Parameters.AddWithValue("@Min", 80)
            Using reader = cmd.ExecuteReader()
                lstResults.Items.Clear()
                While reader.Read()
                    Dim name  = reader.GetString(0)     ' column 0 = Name
                    Dim grade = reader.GetDouble(1)    ' column 1 = Grade
                    lstResults.Items.Add($"{name}: {grade}")
                End While
            End Using
        End Using
    End Using
End Sub

' --- Column access alternatives ---
reader.GetString(0)           ' by index (fast)
reader("Name").ToString()     ' by column name (convenient)
reader.IsDBNull(2)            ' check for NULL before reading

' --- ExecuteScalar: single value ---
Dim avg = CDbl(cmd.ExecuteScalar())    ' returns first column of first row

' --- ExecuteNonQuery: INSERT / UPDATE / DELETE ---
Dim rowsAffected = cmd.ExecuteNonQuery()   ' returns count of rows changed

' --- INSERT ---
Private Sub AddStudent(name As String, grade As Double, cls As String)
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Dim sql = "INSERT INTO Students (Name, Grade, Class) VALUES (@Name, @Grade, @Class)"
        Using cmd As New SqliteCommand(sql, conn)
            cmd.Parameters.AddWithValue("@Name",  name)
            cmd.Parameters.AddWithValue("@Grade", grade)
            cmd.Parameters.AddWithValue("@Class", cls)
            cmd.ExecuteNonQuery()
        End Using
    End Using
    LoadStudents()   ' refresh DataGridView
End Sub

' --- UPDATE ---
Private Sub UpdateGrade(studentId As Integer, newGrade As Double)
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Dim sql = "UPDATE Students SET Grade = @Grade WHERE StudentID = @ID"
        Using cmd As New SqliteCommand(sql, conn)
            cmd.Parameters.AddWithValue("@Grade", newGrade)
            cmd.Parameters.AddWithValue("@ID",    studentId)
            Dim rows = cmd.ExecuteNonQuery()
            MessageBox.Show($"{rows} row(s) updated.")
        End Using
    End Using
    LoadStudents()
End Sub

' --- DELETE ---
Private Sub DeleteStudent(studentId As Integer)
    If MessageBox.Show("Delete this student?", "Confirm",
            MessageBoxButtons.YesNo) <> DialogResult.Yes Then Return
    Using conn As New SqliteConnection(CONN_STR)
        conn.Open()
        Using cmd As New SqliteCommand("DELETE FROM Students WHERE StudentID = @ID", conn)
            cmd.Parameters.AddWithValue("@ID", studentId)
            cmd.ExecuteNonQuery()
        End Using
    End Using
    LoadStudents()
End Sub

' --- Read selected row from DataGridView ---
Private Sub dgvStudents_CellClick(...) Handles dgvStudents.CellClick
    If dgvStudents.SelectedRows.Count = 0 Then Return
    Dim row   = dgvStudents.SelectedRows(0)
    Dim id    = CInt(row.Cells("StudentID").Value)
    Dim name  = row.Cells("Name").Value.ToString()
    Dim grade = CDbl(row.Cells("Grade").Value)
    txtName.Text  = name
    nudGrade.Value = grade
    _selectedId   = id   ' store for UPDATE/DELETE
End Sub