VB.NET Data Access Documentation

Introduction to Data Access in VB.NET

Accessing data is a fundamental aspect of most applications. VB.NET, as part of the .NET Framework, provides a rich set of tools and libraries for interacting with various data sources, including relational databases, XML files, and more. This section will guide you through the primary mechanisms for data access in VB.NET.

ADO.NET Overview

ADO.NET is the cornerstone of data access in the .NET Framework. It provides a set of classes that expose data access services to the .NET Framework application. ADO.NET allows you to build components that efficiently manage data from multiple data sources, such as relational databases (SQL Server, Oracle, MySQL, etc.) and XML.

Key ADO.NET Objects:

Connection: Establishes a connection to a data source.
Command: Represents a Transact-SQL statement or stored procedure to execute against a data source.
DataReader: Provides a way to read a forward-only stream of rows from a data source. It's highly efficient for read-only scenarios.
DataAdapter: Bridges the gap between a DataSet and a data source for retrieving and saving data.
DataSet: An in-memory representation of data. It can contain multiple tables, relationships, and constraints.

For more detailed information on ADO.NET, refer to the official ADO.NET Documentation.

Working with SQL Server

Connecting to and querying SQL Server is a common task. Here's a basic example of how to establish a connection and retrieve data:


Imports System.Data.SqlClient

Public Module SqlExample
    Public Sub GetCustomerData(connectionString As String)
        Using connection As New SqlConnection(connectionString)
            Dim query As String = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City"
            Using command As New SqlCommand(query, connection)
                command.Parameters.AddWithValue("@City", "London")

                Try
                    connection.Open()
                    Using reader As SqlDataReader = command.ExecuteReader()
                        If reader.HasRows Then
                            While reader.Read()
                                Console.WriteLine($"ID: {reader("CustomerID")}, Name: {reader("CompanyName")}")
                            End While
                        Else
                            Console.WriteLine("No customers found in London.")
                        End If
                    End Using
                Catch ex As SqlException
                    Console.WriteLine($"Error: {ex.Message}")
                End Try
            End Using
        End Using
    End Sub

    ' Example Usage:
    ' Dim connStr As String = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"
    ' GetCustomerData(connStr)
End Module

Using DataSets and DataTables

DataSet and DataTable objects are powerful for working with data in memory, especially when you need to perform operations like filtering, sorting, or performing calculations without constantly querying the database.


Imports System.Data
Imports System.Data.SqlClient

Public Module DataSetExample
    Public Function GetOrdersAsDataSet(connectionString As String, customerID As String) As DataSet
        Dim ds As New DataSet("CustomerOrders")
        Dim conn As New SqlConnection(connectionString)
        Dim adapter As New SqlDataAdapter()

        Try
            conn.Open()
            Dim query As String = "SELECT OrderID, OrderDate, ShipCity FROM Orders WHERE CustomerID = @CustomerID"
            adapter.SelectCommand = New SqlCommand(query, conn)
            adapter.Fill(ds, "Orders") ' Fills the DataSet with a DataTable named "Orders"

            Return ds
        Catch ex As SqlException
            Console.WriteLine($"Error: {ex.Message}")
            Return Nothing
        Finally
            conn.Close()
        End Try
    End Function

    ' Example Usage:
    ' Dim connStr As String = "..."
    ' Dim customerId As String = "ALFKI"
    ' Dim ordersDs As DataSet = GetOrdersAsDataSet(connStr, customerId)
    ' If ordersDs IsNot Nothing AndAlso ordersDs.Tables.Contains("Orders") Then
    '     For Each row As DataRow In ordersDs.Tables("Orders").Rows
    '         Console.WriteLine($"Order ID: {row("OrderID")}, Date: {row("OrderDate")}")
    '     Next
    ' End If
End Module

Entity Framework

For modern .NET development, Entity Framework (EF) is the recommended Object-Relational Mapper (ORM). EF simplifies data access by allowing you to work with data as objects, abstracting away much of the underlying SQL and ADO.NET code.

Key benefits of Entity Framework:

Abstraction: Work with data using .NET objects.
Productivity: Reduced boilerplate code.
Maintainability: Easier to manage database schema changes.
LINQ to Entities: Powerful querying capabilities using Language Integrated Query (LINQ).

Explore the Entity Framework Documentation to learn more about using EF Core or EF6 with VB.NET.

MSDN Documentation