ADO.NET Programming

📖 Theory

The night the bills survive

Tonight is the graduation: the cashier clicks Save, ShopKeeper writes the bill into ShopDB, the power can flicker all it likes, and tomorrow the day report reads everything back.

You hold every part already: the tested connection string, the 5-object model, BCA205's SQL, Unit 3's Try/Catch, and a DataGrid waiting for its DataSource. This lesson only ASSEMBLES: three code shapes (read connected, write, fill disconnected) that between them cover every ADO.NET exam program this paper sets.

⌨️ Practical

Shape 1 and 2: connected read, then a write

Imports System.Data.SqlClient

Module BillStore
    Dim cs As String = "Data Source=.;Initial Catalog=ShopDB;Integrated Security=True"

    Sub ReprintBill(ByVal billNo As Integer)
        Dim cn As New SqlConnection(cs)
        Try
            cn.Open()                                   ' dial
            Dim cmd As New SqlCommand( _
                "SELECT Item, Amount FROM Bills WHERE BillNo = " & billNo, cn)
            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()                             ' one row per pass
                MsgBox(rd("Item") & "  Rs " & rd("Amount"))
            End While
            rd.Close()
        Catch ex As Exception
            MsgBox("Database error: " & ex.Message)
        Finally
            cn.Close()                                  ' always hang up
        End Try
    End Sub

    Sub SaveLine(ByVal item As String, ByVal amount As Decimal)
        Dim cn As New SqlConnection(cs)
        cn.Open()
        Dim ins As New SqlCommand( _
            "INSERT INTO Bills(Item, Amount) VALUES('" & item & "', " & amount & ")", cn)
        Dim rows As Integer = ins.ExecuteNonQuery()     ' 1 row affected
        cn.Close()
    End Sub
End Module

📖 Theory

Reading the read

Walk ReprintBill's middle:

• ExecuteReader() sends the SELECT and returns a live SqlDataReader
• rd.Read() advances to the next row, answering True while rows remain: the natural While loop; each column is reachable by name, rd("Item")
• forward-only means no second pass: to re-walk the rows, re-execute

And note where Close() sits: the connection closes in Finally, so even a mid-read exception hangs up the line. Unit 3's grammar, guarding Unit 5's resource: this pairing is what the examiners call good practice and mark accordingly.

⌨️ Practical

Shape 3: the disconnected fill

Sub LoadDayReport()
    Dim cn As New SqlConnection(cs)
    Dim da As New SqlDataAdapter("SELECT * FROM Bills", cn)
    Dim ds As New DataSet()

    da.Fill(ds, "Bills")            ' opens AND closes cn by itself
    dgvBill.DataSource = ds.Tables("Bills")   ' Unit 3's glass, filled

    ' owner edits rows in the grid... later:
    ' da.Update(ds, "Bills")        ' pushes changes back to ShopDB
End Sub

Sub ShowDayTotal()
    Dim cn As New SqlConnection(cs)
    cn.Open()
    Dim cmd As New SqlCommand("SELECT SUM(Amount) FROM Bills", cn)
    Dim total As Decimal = CDec(cmd.ExecuteScalar())   ' one single value
    cn.Close()
    MsgBox("Today: Rs " & total)
End Sub

🧩 Quiz

Dim rows As Integer = ins.ExecuteNonQuery() runs a single-row INSERT successfully. What is in rows, and what was ExecuteNonQuery built for?

1. 1: it returns the number of rows affected, and exists for INSERT/UPDATE/DELETE
2. 0: it returns a success flag where 0 means no error
3. The new row's data: it returns whatever the statement produced
4. It cannot run an INSERT: only SELECT statements execute in ADO.NET

💭 Think first

Who opened the connection for Fill?

LoadDayReport never calls cn.Open() or cn.Close(), yet it works and leaks nothing. ReprintBill had to do both. Explain the difference before tapping.

⚠️ Watch out

Habits that separate pass from distinction

Unclosed connections: each one holds a database slot; a busy till leaks them until the server refuses. Close in Finally, every path.

Gluing user text into SQL: our teaching listings concatenate for readability, but an item named O'Brien breaks the string, and worse is possible: mention parameterised commands (cmd.Parameters) as the professional fix and examiners nod.

Wrong execute: ExecuteReader on an INSERT, ExecuteNonQuery on a SELECT: both run, both give useless results. Match the verb to the SQL.

📖 Theory

ShopKeeper, complete

Step back: a Windows Forms face (Unit 3), an OOP heart of Items, Bills and GST-polymorphic Products (Unit 4), and now a memory that survives the night (Unit 5), all running on the CLR machinery you can narrate end to end (Units 1 and 2). That is the whole BCA404 syllabus living in one till. When BCA503-01 hands you server-side data access later, this exact object model returns with new names, and you will greet it like an old supplier.

✅ Summary

Key takeaways

• Connected read: Open, Command + ExecuteReader, While rd.Read() with rd("Column"), Close in Finally.
• Writes: ExecuteNonQuery for INSERT/UPDATE/DELETE, returning the affected-row count (1 for a single insert; 0 often means WHERE matched nothing).
• Single values: ExecuteScalar (SUM, COUNT): one call, one result.
• Disconnected: DataAdapter.Fill(ds, "Bills") opens AND closes for you; bind ds.Tables("Bills"); Update(ds) syncs edits.
• Wrap database work in Try/Catch/Finally; never leave a connection open on an error path.
• Prefer parameterised commands over concatenated SQL in real code.
• Memory hook: readers hold the line, adapters make the trip, counts confess what happened.