Building a Simple CRUD App in VB.NET

Analogy categoryAnalogies

Anatomy of a CRUD App in VB.NET

CRUD, Create, Read, Update, Delete, describes the four basic data operations nearly every business application needs, and building one in VB.NET is a good way to see ADO.NET, layered architecture, and UI binding work together. A typical VB.NET CRUD app has three pieces: a data-access layer that talks to SQL Server, or another database, via ADO.NET's SqlConnection and SqlCommand classes, a thin model layer of plain classes representing rows, a Customer or Product class, and a UI layer, commonly Windows Forms with a DataGridView, or a console app for something simpler, that calls into the data layer and displays results.

🏏

Cricket analogy: It's like a cricket scoring app needing four core actions, add a new player to the squad, look up a batting average, correct a scorecard entry, and remove a retired player, CRUD operations map onto exactly those four everyday database needs.

Setting Up the Data Layer

The data layer starts with a connection string, typically stored in App.config rather than hard-coded in source, so it can change between development and production without recompiling. Every SqlCommand that includes user-supplied values must use parameters, @CustomerName, @OrderId, rather than string-concatenated SQL, both to prevent SQL injection and to let SQL Server cache and reuse query execution plans. For reading data, a SqlDataReader gives fast, forward-only, low-memory access when you're just streaming rows into a list, while a DataTable filled via a SqlDataAdapter is convenient when you want an in-memory, updatable snapshot to bind directly to a DataGridView.

🏏

Cricket analogy: It's like a ground curator storing pitch specifications in a maintenance log rather than memorizing them, so the pitch prep matches the actual conditions each match day rather than whatever was assumed months earlier, connection strings belong in config, not hard-coded.

Implementing Create and Read

The Create operation is an INSERT statement executed with SqlCommand.ExecuteNonQuery, using parameters for every column value and, if you need the new row's identity, either an OUTPUT clause or a follow-up SELECT SCOPE_IDENTITY() to retrieve the auto-generated primary key. Read operations typically execute a parameterized SELECT and either loop a SqlDataReader with .Read() to map each row into a strongly typed object, a common hand-rolled pattern before Entity Framework became standard, or bind a DataTable straight to a DataGridView's DataSource property for a quick, editable grid without manual mapping code. For anything beyond trivial filtering, always push WHERE-clause logic to the SQL query itself rather than pulling every row into memory and filtering in VB.NET code.

🏏

Cricket analogy: It's like registering a new player with the scorer's table before they can bat, you can't read their stats until the Create step, entering them into the system, has happened, just as an INSERT must complete before a SELECT can find that row.

Implementing Update and Delete

UPDATE and DELETE statements both need a precise WHERE clause built from parameters, typically the primary key, because an UPDATE or DELETE without a WHERE clause, or with a mistakenly broad one, will silently modify or remove every row in the table. After executing an UPDATE or DELETE, check the integer returned by ExecuteNonQuery, the row count affected; if it's zero, the record you expected to change didn't exist, which usually means stale data in the UI, someone else deleted it first, rather than a bug in your SQL. When Update and Delete need to happen together with other statements as a single unit, for example, deleting an order and inserting an audit-log row, wrap them in a SqlTransaction so either all statements commit or all roll back together.

🏏

Cricket analogy: It's like a scorer correcting a specific delivery in the ball-by-ball log by over and ball number rather than editing 'the last entry' vaguely, an imprecise WHERE clause is like correcting the wrong over entirely and wiping out a correct score by mistake.

vbnet

Public Class CustomerRepository
    Private ReadOnly _connectionString As String =
        ConfigurationManager.ConnectionStrings("AppDb").ConnectionString

    Public Function Create(customer As Customer) As Integer
        Using conn As New SqlClient.SqlConnection(_connectionString)
            Using cmd As New SqlClient.SqlCommand(
                "INSERT INTO Customers (Name, Email) OUTPUT INSERTED.Id VALUES (@Name, @Email)", conn)
                cmd.Parameters.AddWithValue("@Name", customer.Name)
                cmd.Parameters.AddWithValue("@Email", customer.Email)
                conn.Open()
                Return CInt(cmd.ExecuteScalar())
            End Using
        End Using
    End Function

    Public Function GetAll() As List(Of Customer)
        Dim results As New List(Of Customer)
        Using conn As New SqlClient.SqlConnection(_connectionString)
            Using cmd As New SqlClient.SqlCommand("SELECT Id, Name, Email FROM Customers", conn)
                conn.Open()
                Using reader = cmd.ExecuteReader()
                    While reader.Read()
                        results.Add(New Customer With {
                            .Id = reader.GetInt32(0),
                            .Name = reader.GetString(1),
                            .Email = reader.GetString(2)
                        })
                    End While
                End Using
            End Using
        End Using
        Return results
    End Function

    Public Function Update(customer As Customer) As Boolean
        Using conn As New SqlClient.SqlConnection(_connectionString)
            Using cmd As New SqlClient.SqlCommand(
                "UPDATE Customers SET Name = @Name, Email = @Email WHERE Id = @Id", conn)
                cmd.Parameters.AddWithValue("@Name", customer.Name)
                cmd.Parameters.AddWithValue("@Email", customer.Email)
                cmd.Parameters.AddWithValue("@Id", customer.Id)
                conn.Open()
                Return cmd.ExecuteNonQuery() > 0
            End Using
        End Using
    End Function

    Public Function Delete(id As Integer) As Boolean
        Using conn As New SqlClient.SqlConnection(_connectionString)
            Using cmd As New SqlClient.SqlCommand("DELETE FROM Customers WHERE Id = @Id", conn)
                cmd.Parameters.AddWithValue("@Id", id)
                conn.Open()
                Return cmd.ExecuteNonQuery() > 0
            End Using
        End Using
    End Function
End Class

Always use SqlCommand.Parameters, or AddWithValue, instead of concatenating user input into SQL strings. Parameterized queries prevent SQL injection and let SQL Server reuse cached execution plans, improving performance under load.

Never store raw connection strings with plaintext passwords in source control. Use App.config with encrypted configuration sections (aspnet_regiis -pef), environment variables, or a secrets manager, especially for production database credentials.

CRUD apps map Create/Read/Update/Delete onto INSERT/SELECT/UPDATE/DELETE statements executed through ADO.NET's SqlConnection and SqlCommand.
Store connection strings in App.config, not hard-coded in source, and never commit plaintext production credentials.
Always use parameterized queries to prevent SQL injection and enable SQL Server execution-plan caching.
Use SqlDataReader for fast forward-only reads, or a DataTable/SqlDataAdapter for an in-memory, bindable snapshot.
Retrieve auto-generated identity values with an OUTPUT clause or SCOPE_IDENTITY() after an INSERT.
Check ExecuteNonQuery's returned row count after UPDATE/DELETE to detect stale or missing records.
Wrap multi-statement operations in a SqlTransaction so they commit or roll back as a single unit.