VB.NET Database Access

This section provides comprehensive documentation on how to access and manipulate data from various databases using Visual Basic .NET. We will cover different approaches, from traditional ADO.NET to modern Entity Framework.

ADO.NET Fundamentals

ADO.NET is the foundational data access technology in the .NET Framework. It provides a set of classes for connecting to data sources, executing commands, and retrieving results.

Key ADO.NET Objects:

• SqlConnection / OleDbConnection: Establishes a connection to the database.
• SqlCommand / OleDbCommand: Executes SQL statements or stored procedures.
• SqlDataReader / OleDbDataReader: Provides a forward-only, read-only stream of data.
• DataSet and DataTable: In-memory representations of database tables.
• SqlDataAdapter / OleDbDataAdapter: Bridges DataSet and the data source for retrieving and saving data.

Connecting to a SQL Server Database

' Replace with your actual connection string
Dim connectionString As String = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"

Using connection As New SqlConnection(connectionString)
    Try
        connection.Open()
        MessageBox.Show("Connection successful!")
    Catch ex As SqlException
        MessageBox.Show("Unable to connect to the database. Error: " & ex.Message)
    End Try
End Using

Executing a Query with SqlDataReader

Dim connectionString As String = "YourConnectionStringHere"
Dim query As String = "SELECT CustomerID, CompanyName FROM Customers"

Using connection As New SqlConnection(connectionString)
    Using command As New SqlCommand(query, connection)
        Try
            connection.Open()
            Dim reader As SqlDataReader = command.ExecuteReader()

            If reader.HasRows Then
                While reader.Read()
                    Console.WriteLine($"ID: {reader.GetInt32(0)}, Name: {reader.GetString(1)}")
                End While
            Else
                Console.WriteLine("No rows found.")
            End If
            reader.Close()
        Catch ex As SqlException
            MessageBox.Show("Error executing query: " & ex.Message)
        End Try
    End Using
End Using

Entity Framework (EF) Core

Entity Framework Core is a modern, cross-platform Object-Relational Mapper (ORM) for .NET. It allows you to work with databases using .NET objects instead of raw SQL.

Key Concepts:

• DbContext: Represents a session with the database and allows you to query and save data.
• DbSet: Represents a collection of a given entity type in the context.
• Migrations: A feature to manage incremental changes to your database schema.

Getting Started with EF Core

First, install the necessary NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

Defining a Model and DbContext

Public Class Product
        Public Property ProductID As Integer
        Public Property ProductName As String
        Public Property UnitPrice As Decimal
    End Class

    Public Class NorthwindContext
        Inherits DbContext
        Public Property Products As DbSet(Of Product)

        Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder)
            ' Replace with your actual connection string
            optionsBuilder.UseSqlServer("Server=myServerAddress;Database=Northwind;Trusted_Connection=True;")
        End Sub
    End Class

Querying Data with EF Core

Using context As New NorthwindContext()
        Dim expensiveProducts = context.Products.Where(Function(p) p.UnitPrice > 50).ToList()

        For Each product In expensiveProducts
            Console.WriteLine($"{product.ProductName} - ${product.UnitPrice}")
        Next
    End Using

Choosing the Right Data Access Approach

The choice between ADO.NET and Entity Framework depends on your project's needs:

• ADO.NET: Offers fine-grained control, higher performance for specific scenarios, and is good for legacy applications or when direct SQL manipulation is required.
• Entity Framework: Simplifies development, reduces boilerplate code, improves maintainability, and is generally preferred for new applications, especially those with complex object models.

Best Practices

• Always use parameterized queries to prevent SQL injection vulnerabilities.
• Manage database connections efficiently (e.g., using Using blocks).
• Handle exceptions gracefully.
• Consider asynchronous operations for database calls in UI applications to keep the interface responsive.
• Keep your ORM (if used) updated to benefit from performance improvements and new features.