ADO.NET DbConnection Class

The DbConnection class is the abstract base class for all ADO.NET connection classes. It provides a common interface for database connectivity, allowing you to establish a connection to a data source, issue commands, and retrieve results.

Overview

In ADO.NET, a Connection object represents a unique session with a data source. The Connection object is used to communicate with the data source and retrieve data. The specific implementation of the Connection class depends on the data provider you are using (e.g., SqlConnection for SQL Server, OracleConnection for Oracle, NpgsqlConnection for PostgreSQL).

Key Properties and Methods

• ConnectionString: Specifies the connection string used to open the connection to the data source.
• ConnectionTimeout: Gets the time to wait for the connection to be established before terminating the attempt.
• Database: Gets the name of the specific database that the connection is accessing.
• DataSource: Gets the server name or file name of the data source.
• State: Gets a value indicating whether the connection is open, closed, or attempting to connect.
• Open(): Opens a database connection with the properties and behavior specified by the ConnectionString.
• Close(): Closes the connection to the data source.
• BeginTransaction(): Starts a database transaction.
• CreateCommand(): Creates and returns a DbCommand object associated with the connection.

Connection String

The connection string is a crucial part of establishing a database connection. It contains information such as the data source location, authentication credentials, and other parameters required to connect to the database. The format of the connection string varies depending on the data provider.

Example Connection Strings:

// SQL Server Connection String
"Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"

// PostgreSQL Connection String
"Host=myHost;Database=myDatabase;Username=myUsername;Password=myPassword;Port=5432;"
            

Establishing a Connection

You can establish a connection to a data source using the Open() method of the specific connection object. It's good practice to wrap connection opening and closing in a try...catch...finally block to ensure proper resource management.

C# Example:

using System.Data.SqlClient;

public class DatabaseConnector
{
    private SqlConnection connection;

    public void ConnectToDatabase(string connectionString)
    {
        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
            Console.WriteLine("Connection opened successfully.");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Error connecting to database: {ex.Message}");
        }
    }

    public void CloseConnection()
    {
        if (connection != null && connection.State == System.Data.ConnectionState.Open)
        {
            connection.Close();
            Console.WriteLine("Connection closed.");
        }
    }

    public bool IsConnected()
    {
        return connection != null && connection.State == System.Data.ConnectionState.Open;
    }
}
            

VB.NET Example:

Imports System.Data.SqlClient

Public Class DatabaseConnector
    Private connection As SqlConnection

    Public Sub ConnectToDatabase(ByVal connectionString As String)
        Try
            connection = New SqlConnection(connectionString)
            connection.Open()
            Console.WriteLine("Connection opened successfully.")
        Catch ex As SqlException
            Console.WriteLine($"Error connecting to database: {ex.Message}")
        End Try
    End Sub

    Public Sub CloseConnection()
        If connection IsNot Nothing AndAlso connection.State = System.Data.ConnectionState.Open Then
            connection.Close()
            Console.WriteLine("Connection closed.")
        End If
    End Sub

    Public Function IsConnected() As Boolean
        Return connection IsNot Nothing AndAlso connection.State = System.Data.ConnectionState.Open
    End Function
End Class
            

Connection Pooling

ADO.NET utilizes connection pooling to improve performance. Instead of establishing a new connection every time one is requested, the data provider maintains a pool of open connections. When a connection is requested, it's taken from the pool. When it's closed, it's returned to the pool for reuse. This significantly reduces the overhead of establishing new connections.

See Also