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.
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 ClassAlways 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.
Practice what you learned
1. Why should SQL parameters be used instead of string concatenation when building queries?
2. What does a SqlDataReader provide that a DataTable does not?
3. What should you check after calling ExecuteNonQuery on an UPDATE statement?
4. Why wrap multiple related INSERT/UPDATE statements in a SqlTransaction?
5. What is the risk of an UPDATE statement without a WHERE clause?
Was this page helpful?
You May Also Like
VB.NET Best Practices
Practical conventions and patterns for writing clean, maintainable, and performant VB.NET code in real-world applications.
VB.NET Quick Reference
A condensed reference for VB.NET syntax covering data types, control flow, collections, LINQ, and object-oriented constructs.
VB.NET vs C#
A practical comparison of Visual Basic .NET and C# covering syntax, features, and when to choose each language on the .NET platform.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics