Data Management and Persistence

Introduction

Most useful software must preserve information beyond the lifetime of a single program execution. A student-information system stores enrolments, a banking application records transactions, and an enterprise application maintains customers, products, and audit events. Data management concerns the organization, protection, retrieval, transformation, and governance of data. Persistence is the ability to retain data so that it remains available after an application terminates or a machine restarts.

In Visual Basic, persistence may be implemented through text files, XML or JSON documents, binary serialization, relational databases, or cloud data services. At postgraduate level, the important issue is not merely how to write a file or execute a query, but how to select an appropriate data model, preserve consistency, control access, handle failure, and design code that remains maintainable under changing requirements.

This chapter uses Visual Basic .NET and ADO.NET examples. The principles also apply when Visual Basic applications access SQL Server, SQLite, PostgreSQL, or another relational database through an appropriate provider.

Learning Objectives

After studying this chapter, the learner should be able to:

1. Distinguish volatile memory from persistent storage and compare common persistence mechanisms.
2. Explain relational data modelling, keys, constraints, normalization, and transactions.
3. Use ADO.NET connections, commands, parameters, readers, and transactions safely.
4. Design persistence layers that separate user-interface logic from data-access logic.
5. Analyse reliability, performance, security, privacy, and governance issues in data management.
6. Evaluate a practical persistence design and justify trade-offs.

1. The Persistence Problem

Variables stored in RAM normally disappear when a process ends. Persistence transfers selected program state to a medium whose lifetime exceeds that of the process. A persistence design must answer five questions:

• What data must be retained?
• In what structure should it be represented?
• Where should it be stored?
• When should changes be committed?
• How will correctness, security, and recovery be ensured?

A useful distinction is:

Persistence is not equivalent to simply saving an object. An object graph may contain temporary values, references, or implementation details that should not become part of a durable business record. A durable representation should be designed around business meaning and long-term compatibility.

2. Persistence Alternatives

2.1 Files

Text files are simple and portable. CSV is useful for tabular exchange, but it has weak support for nested structures, types, constraints, and concurrent updates. XML and JSON represent hierarchical data more naturally. They are suitable for configuration, interchange, exports, and small local datasets.

A file-based approach becomes difficult when multiple users update the same data, when partial writes must be recovered, or when queries require filtering and joining large collections. File locking, atomic replacement, versioning, and backup policies must then be designed explicitly.

2.2 Object serialization

Serialization maps an object into bytes or text. It can be convenient for caching or temporary state, but direct serialization of application classes creates coupling between the stored format and the class implementation. Changes to property names, types, or inheritance may make old data unreadable. For security reasons, untrusted serialized input must never be deserialized using unsafe mechanisms.

2.3 Relational databases

A relational database stores data in relations commonly presented as tables. Rows represent occurrences of an entity and columns represent attributes. SQL provides declarative selection, joining, grouping, insertion, modification, and deletion. Constraints and transactions allow the database—not only the application—to enforce important rules.

Relational databases are appropriate when data is structured, shared, queried in multiple ways, and subject to integrity requirements. They also provide indexes, concurrency control, backup facilities, and recovery mechanisms.

2.4 Other models

Document databases store JSON-like documents and can be effective for variable or aggregate-shaped data. Key-value stores optimize direct lookup. Graph databases represent relationships explicitly. These models are not automatically superior to relational databases; selection depends on access patterns, consistency requirements, operational expertise, and data shape.

3. Relational Modelling

Consider a university registration system. A possible conceptual model contains Student, Course, and Enrollment. A student may enrol in many courses, and a course may contain many students. This many-to-many relationship is represented using the associative table Enrollment.

A relational design might be:

Student(StudentId, UniversityNumber, Name, Email)
Course(CourseId, Code, Title, Credits)
Enrollment(StudentId, CourseId, EnrolledOn, Grade)

StudentId and CourseId are primary keys. In Enrollment, the pair (StudentId, CourseId) may be a composite primary key if a student can enrol in a course only once. StudentId and CourseId are also foreign keys referencing their parent tables.

Keys and constraints

• A primary key uniquely identifies each row and must be non-null.
• A candidate key is any minimal attribute set capable of uniquely identifying a row.
• A foreign key requires a value to correspond to a referenced key, subject to the configured referential action.
• A unique constraint prevents duplicate values where uniqueness is a business rule.
• A check constraint restricts values, such as credits being positive.
• A not-null constraint requires a value to be present.

Constraints are executable statements of business integrity. A Visual Basic validation message improves usability, but it cannot replace a database constraint because other applications, scripts, or concurrent users may bypass the user interface.

Normalization

Normalization reduces unnecessary duplication and update anomalies. In informal terms:

• First normal form requires atomic, indivisible values and no repeating groups.
• Second normal form requires first normal form and removal of partial dependencies on part of a composite key.
• Third normal form requires second normal form and removal of dependencies on non-key attributes through another non-key attribute.

For example, storing (StudentId, StudentName, CourseId, CourseTitle, Grade) in one table repeats student and course information. Separating student, course, and enrollment data reduces update anomalies. Denormalization may later be justified for reporting performance, but it should be deliberate and supported by mechanisms that maintain consistency.

4. ADO.NET Architecture

ADO.NET provides .NET abstractions for data access. Provider-specific classes implement the connection to a particular database engine. Common elements include:

• DbConnection: opens and manages a database connection.
• DbCommand: represents SQL or a stored procedure invocation.
• DbParameter: supplies typed values to a command.
• DbDataReader: provides efficient, forward-only reading.
• DbTransaction: groups operations into an atomic unit.
• DataTable and DataSet: support disconnected, in-memory representations.

A modern application commonly uses a provider such as Microsoft.Data.SqlClient for SQL Server or Microsoft.Data.Sqlite for SQLite. The provider should be selected according to the target database and deployment requirements.

Connection lifetime

Connections should be opened as late as practical, used briefly, and disposed deterministically. Connection pooling normally reuses physical connections behind the scenes, so repeatedly opening and closing logical connections is not necessarily inefficient. A connection string should not be hard-coded with a production password in source code; secure configuration or a secret-management service is preferable.

Parameterized commands

SQL must not be constructed by concatenating untrusted input. Parameters provide value separation and reduce SQL injection risk. They also communicate type information and avoid many quoting and culture problems.

Imports Microsoft.Data.SqlClient

Public Function FindStudent(connectionString As String,
                            universityNumber As String) As Student?
    Const sql As String =
        "SELECT StudentId, UniversityNumber, Name, Email " &
        "FROM Student WHERE UniversityNumber = @number;"

    Using connection As New SqlConnection(connectionString),
          command As New SqlCommand(sql, connection)

        command.Parameters.Add("@number", SqlDbType.VarChar, 20).Value = universityNumber
        connection.Open()

        Using reader As SqlDataReader = command.ExecuteReader()
            If Not reader.Read() Then Return Nothing

            Return New Student With {
                .StudentId = reader.GetInt32(reader.GetOrdinal("StudentId")),
                .UniversityNumber = reader.GetString(reader.GetOrdinal("UniversityNumber")),
                .Name = reader.GetString(reader.GetOrdinal("Name")),
                .Email = reader.GetString(reader.GetOrdinal("Email"))
            }
        End Using
    End Using
End Function

Using ensures that connections, commands, and readers are disposed even when an exception occurs. In production code, asynchronous methods such as OpenAsync, ExecuteReaderAsync, and ReadAsync can improve scalability when the application is I/O-bound. Cancellation tokens should be used where operations may outlive a request.

5. Transactions and Consistency

A transaction groups operations so that the database moves between valid states rather than exposing partially completed work. The classical ACID properties are:

• Atomicity: all operations commit or none commit.
• Consistency: declared constraints and application invariants remain satisfied.
• Isolation: concurrent transactions do not produce unacceptable interference.
• Durability: committed results survive normal failures according to the database’s recovery guarantees.

Isolation is a design choice. Stronger isolation can reduce anomalies but may increase locking, waiting, or version-store use. Common anomalies include dirty reads, non-repeatable reads, and phantom reads. The appropriate level depends on the application’s correctness requirements.

A registration operation illustrates a transaction:

Using connection As New SqlConnection(cs)
    connection.Open()
    Using transaction As SqlTransaction = connection.BeginTransaction()
        Try
            Using command As New SqlCommand(
                "INSERT INTO Enrollment(StudentId, CourseId, EnrolledOn) " &
                "VALUES (@student, @course, @date);", connection, transaction)
                command.Parameters.Add("@student", SqlDbType.Int).Value = studentId
                command.Parameters.Add("@course", SqlDbType.Int).Value = courseId
                command.Parameters.Add("@date", SqlDbType.Date).Value = Date.Today
                command.ExecuteNonQuery()
            End Using

            transaction.Commit()
        Catch
            transaction.Rollback()
            Throw
        End Try
    End Using
End Using

The database should also enforce duplicate prevention and referential integrity. Application checks alone are subject to race conditions: two concurrent requests may both observe that a row does not exist and then both attempt insertion.

6. Layered Design and the Repository Boundary

A maintainable Visual Basic application separates responsibilities:

1. Presentation layer: forms, web endpoints, and user interaction.
2. Application layer: use cases, authorization decisions, and orchestration.
3. Domain layer: business concepts and invariants.
4. Persistence layer: SQL, mapping, transactions, and database-specific concerns.

A repository can expose operations such as FindById, Save, and ListByCourse, while hiding SQL details from the application layer. However, an abstraction is useful only when it clarifies a real boundary. A generic repository that conceals important query semantics may make performance and correctness harder to understand.

Data-transfer objects should be chosen deliberately. A database row, domain object, and API response often have different purposes. Mapping between them prevents accidental exposure of internal fields and makes schema evolution more manageable.

7. Reliability, Validation, and Error Handling

Validation operates at several levels:

• Syntactic validation: Is an email address shaped plausibly?
• Semantic validation: Is the enrolment date within an allowed period?
• Relational validation: Does the referenced student exist?
• Concurrency validation: Has another user changed the record since it was read?

Errors should be classified rather than hidden. A duplicate-key error may be a user-correctable conflict; a network timeout may justify bounded retry; a corrupted database may require operational intervention. Retrying a non-idempotent command blindly can create duplicate effects. Retry policies should use time limits, backoff, and idempotency keys where appropriate.

Optimistic concurrency can use a version column or database timestamp. An update may require the original version:

UPDATE Student
SET Name = @newName, Version = Version + 1
WHERE StudentId = @id AND Version = @originalVersion;

If the affected-row count is zero, the application should report that the record changed concurrently rather than silently overwriting another user’s work.

8. Security, Privacy, and Governance

Data persistence creates obligations beyond programming correctness. Important practices include:

• Use least-privilege database accounts.
• Encrypt data in transit and protect sensitive data at rest.
• Store secrets outside source code and rotate them.
• Use parameterized commands and validate authorization on the server side.
• Avoid logging passwords, tokens, health information, or unnecessary personal data.
• Define retention and deletion rules.
• Maintain audit trails for sensitive operations without recording excessive personal information.
• Back up data and regularly test restoration.

Privacy principles such as purpose limitation, data minimization, accuracy, retention control, and accountability are relevant under laws such as the GDPR and comparable national frameworks. Legal duties vary by jurisdiction; technical teams should work with institutional data-protection and compliance specialists.

A backup that has never been restored is an assumption, not evidence of recoverability. Recovery planning should specify recovery-point objective (acceptable data loss measured in time) and recovery-time objective (acceptable restoration duration), then test whether infrastructure meets those objectives.

9. Performance and Scalability

Performance problems frequently arise from transferring too many rows, selecting unused columns, missing indexes, or executing one query per item (the N+1 query problem). Practical principles include:

• Select only required columns.
• Filter and aggregate at the database where appropriate.
• Index columns used frequently in selective predicates, joins, and ordering.
• Examine execution plans rather than guessing.
• Avoid holding connections or transactions longer than necessary.
• Use pagination for large result sets.
• Measure under realistic data volume and concurrency.

An index is not free: it consumes storage and increases write cost. A useful index design balances read benefit against insertion, update, and maintenance overhead. Caching can reduce repeated reads, but stale data, invalidation, memory pressure, and privacy must be addressed.

10. Illustrative Case: University Enrolment Service

Background

A university initially stores enrolment records in CSV files. The system is used by several departmental offices, and each office maintains a separate copy.

Problem

Copies diverge, duplicate enrolments occur, and a power failure during a file rewrite sometimes produces an incomplete file. Staff cannot reliably determine which version is authoritative.

Analysis

The data is shared, relational, and subject to integrity rules. The many-to-many relationship between students and courses requires an associative table. The system also needs concurrent access, auditability, and recoverable updates. CSV remains useful as an import/export format but is unsuitable as the authoritative store.

Approach

The redesigned service uses a relational database with Student, Course, and Enrollment tables. Primary and foreign keys enforce identity and relationships; a composite key prevents duplicate enrolments. A transaction inserts an enrolment and records an audit event together. Visual Basic application services call a repository using parameterized commands. A version column supports optimistic concurrency. Scheduled backups are accompanied by quarterly restoration tests.

The application validates input for usability, while the database enforces non-null, uniqueness, and referential rules. A role with read-only access supports reporting, while enrolment operations use a narrower write role. Exports are generated from controlled queries rather than copied database files.

Outcome

The authoritative database removes conflicting copies, failed partial writes are rolled back, and duplicate enrolments are rejected consistently. Reporting becomes reproducible because each report uses a defined query and a known data snapshot. Operational complexity increases, but it is explicit and manageable.

Learning points

1. The storage format should reflect sharing, query, and integrity requirements.
2. Database constraints are part of the application’s correctness boundary.
3. Transactions address failure across multiple related operations.
4. Security and recovery must be designed before deployment, not added after an incident.

11. Contemporary Developments

Cloud-hosted databases, managed backups, serverless functions, and distributed data platforms have changed deployment practices, but they do not eliminate modelling or governance decisions. Polyglot persistence may combine relational storage for transactions, a search engine for full-text queries, and object storage for large files. Such architectures introduce synchronization and consistency boundaries that must be documented.

AI-assisted coding can generate SQL and Visual Basic data-access code, but generated code requires review for injection vulnerabilities, incorrect joins, missing authorization, unsafe deserialization, and inappropriate data exposure. AI systems should not receive confidential production data merely to obtain coding assistance. Human accountability for schema design, privacy, and operational decisions remains essential.

Best-Practice Checklist

• Model business entities and relationships before writing persistence code.
• Make integrity rules executable through keys and constraints.
• Parameterize every value supplied to SQL.
• Dispose database resources deterministically.
• Use transactions for logically atomic work.
• Separate presentation, domain, application, and persistence concerns.
• Design for concurrency rather than assuming a single user.
• Store secrets securely and apply least privilege.
• Monitor, back up, and test restoration.
• Measure performance with realistic workloads.
• Document retention, audit, and deletion policies.

Chapter Summary

Data management and persistence convert temporary program state into reliable, governed information. Files and serialization are useful for limited or exchange-oriented scenarios, whereas relational databases provide structured querying, constraints, concurrency control, transactions, and recovery. Effective Visual Basic data access uses ADO.NET connections, commands, typed parameters, readers, and transactions, with deterministic resource disposal. Sound designs separate user interfaces from domain and persistence responsibilities. Correctness depends on both application validation and database constraints; reliability depends on transaction design, concurrency handling, backups, and tested recovery. Security, privacy, least privilege, retention, and auditability are integral technical requirements. Contemporary cloud and AI tools can improve productivity, but they do not remove the need for deliberate modelling, measurement, governance, and human review.

Key Terms

Persistence: Retention of data beyond a program’s execution.

Serialization: Conversion of an object or data structure into a storable representation.

Relational database: A database organized around related tables and declarative queries.

Primary key: Attribute or attribute set that uniquely identifies a row.

Foreign key: Attribute referencing a key in another table.

Constraint: Database rule that restricts invalid states.

Normalization: Structured decomposition intended to reduce redundancy and anomalies.

ADO.NET: .NET data-access framework supporting database providers.

Connection pooling: Reuse of physical database connections for logical requests.

Parameterized query: SQL command whose values are supplied separately from SQL text.

Data reader: Forward-only, read-oriented database result interface.

Transaction: Atomic unit of database work.

ACID: Atomicity, consistency, isolation, and durability properties.

Concurrency: Simultaneous activity by multiple users or processes.

Optimistic concurrency: Conflict detection performed when an update is submitted.

Repository: Component that provides an application-oriented persistence boundary.

Idempotency: Property that repeated execution has the same intended effect as one execution.

Least privilege: Granting only the access required for a task.

Recovery-point objective: Maximum acceptable period of data loss.

Recovery-time objective: Maximum acceptable restoration duration.

Review Questions

Short Answer Questions

1. Why is persistence different from storing a value in a Visual Basic variable?
2. What roles do primary keys and foreign keys play in a relational design?
3. State the four ACID properties.
4. Why should SQL values be supplied through parameters?
5. What is optimistic concurrency?

Long Answer Questions

1. Compare text files, serialized objects, and relational databases as persistence mechanisms.
2. Explain normalization through the university enrolment example.
3. Describe the responsibilities of the main ADO.NET components.
4. Explain why database constraints and transactions are both required for reliable applications.
5. Discuss security, privacy, backup, and recovery considerations in a student-information system.

Analytical / Application Questions

1. Design a persistence model for an online examination system. Identify entities, keys, relationships, and at least three integrity constraints.
2. A Visual Basic application constructs a SQL statement by concatenating a user-entered student number. Analyse the risks and propose a secure redesign.
3. Two staff members edit the same student record. Compare last-write-wins, pessimistic locking, and optimistic concurrency, and recommend an approach with justification.

Multiple Choice Questions with Answers

Question 1

Which mechanism most directly prevents SQL injection when executing a query in ADO.NET?

A. String concatenation with escaped quotes

B. Parameterized commands

C. Opening a second connection

D. Serializing the query as JSON

Correct Answer: B. Parameterized commands

Question 2

Which table correctly represents a many-to-many relationship between Student and Course?

A. A single Student column containing course names

B. A Course table with one StudentId column

C. An Enrollment associative table containing StudentId and CourseId

D. A text file containing comma-separated student objects

Correct Answer: C. An Enrollment associative table containing StudentId and CourseId

Question 3

Which ACID property means that all operations in a transaction succeed or none take effect?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 4

What is the principal purpose of a foreign key?

A. To encrypt a column

B. To enforce a relationship with a referenced table

C. To sort every query automatically

D. To increase the size of a database

Correct Answer: B. To enforce a relationship with a referenced table

Question 5

Which ADO.NET object is most appropriate for efficient forward-only reading of query results?

A. DbTransaction

B. DbDataReader

C. DbParameter

D. DataSet only

Correct Answer: B. DbDataReader

Question 6

An update uses the original version value in its WHERE clause and affects zero rows. What does this most likely indicate?

A. The database has no tables

B. A concurrent modification occurred

C. The connection was automatically encrypted

D. The query returned too many rows

Correct Answer: B. A concurrent modification occurred

Question 7

Why should a database connection normally be disposed promptly after use?

A. To delete the database schema

B. To release resources and return pooled resources appropriately

C. To prevent all future connections

D. To convert rows into JSON

Correct Answer: B. To release resources and return pooled resources appropriately

Question 8

Which statement about database indexes is correct?

A. Indexes always improve every operation

B. Indexes have storage and write-maintenance costs

C. Indexes replace foreign keys

D. Indexes guarantee transaction atomicity

Correct Answer: B. Indexes have storage and write-maintenance costs

Question 9

Which practice best follows the principle of least privilege?

A. Give every application administrator rights

B. Use a database account with only required permissions

C. Store passwords in the source repository

D. Allow all users to modify audit records

Correct Answer: B. Use a database account with only required permissions

Question 10

What is the role of a transaction in inserting an enrolment and its audit record?

A. It ensures the related operations can commit or roll back as one unit

B. It automatically normalizes all tables

C. It replaces parameterized SQL

D. It guarantees unlimited performance

Correct Answer: A. It ensures the related operations can commit or roll back as one unit