Visual Basic .NET - ADO .Net - SqlConnection

The SqlConnection class in VB.NET is part of the ADO.NET framework and is used for establishing a connection to a SQL Server database. It provides methods and properties to manage the connection and interact with the database.

Connection String: To establish a connection, you need to provide a connection string that contains information such as the server name, database name, authentication credentials, and other connection-specific options. The connection string is passed as a parameter when creating an instance of the SqlConnection class.

Opening and Closing the Connection: The SqlConnection class provides the Open() and Close() methods to open and close the connection, respectively. You should always explicitly open the connection before using it and close it when you're done to release the resources.

Connection State: The ConnectionState property of the SqlConnection class indicates the current state of the connection, such as Open, Closed, Connecting, Executing, Fetching, etc. You can check the connection state using this property.

Connection Events: The SqlConnection class exposes events such as StateChange and InfoMessage that you can subscribe to for handling connection state changes and receiving informational messages from the database server.

Connection Pooling: ADO.NET automatically manages connection pooling for SqlConnection objects. Connection pooling allows reusing connections from a pool instead of creating a new connection for each request, which improves performance. By default, connection pooling is enabled.

Imports System.Data.SqlClient
Module Program
    Sub Main()
        ' Connection string
        Dim connectionString As String = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password"
        ' Create a new SqlConnection object
        Dim connection As New SqlConnection(connectionString)
        Try
            ' Open the connection
            connection.Open()
            ' Perform database operations here
            ' Close the connection
            connection.Close()
        Catch ex As Exception
            ' Handle any exceptions
            Console.WriteLine("Error: " & ex.Message)
        Finally
            ' Ensure the connection is always closed
            If connection.State = ConnectionState.Open Then
                connection.Close()
            End If
        End Try
    End Sub
End Module

In the example, replace "ServerName", "DatabaseName", "Username", and "Password" with the appropriate values for your SQL Server database. The SqlConnection object is created with the provided connection string, and the connection is opened using the Open() method. You can perform database operations within the Try block. Finally, the connection is closed in the Finally block to ensure proper cleanup.

The SqlConnection class is an essential component of ADO.NET for establishing and managing connections to SQL Server databases in VB.NET applications. It provides the foundation for interacting with the database using other ADO.NET classes like SqlCommand, SqlDataReader, and SqlDataAdapter.