ADO.NET SqlConnection Class

The SqlConnection class in ADO.NET is used to establish a connection between a .NET application and a SQL Server database. It manages the database connection and provides methods to open and close it.

Namespace:

using System.Data.SqlClient;

1. Creating a Connection

To use SqlConnection, you need a connection string, which contains details like the database server, database name, authentication type, etc.

Example: Creating a Connection

string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    Console.WriteLine("Connection opened successfully!");
}

using ensures the connection is automatically closed after use.
conn.Open() opens the database connection.

2. Connection String Components

A connection string is required to define how to connect to the database.

SQL Server Authentication Example:

string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;";

Windows Authentication Example (Trusted Connection):

string connectionString = "Server=myServer;Database=myDB;Integrated Security=True;";

Local SQL Server (e.g., SQL Express):

string connectionString = "Server=localhost\\SQLEXPRESS;Database=myDB;Trusted_Connection=True;";

3. Opening and Closing a Connection

Manually Opening and Closing:

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
Console.WriteLine("Database Connected!");

// Perform database operations

conn.Close();
Console.WriteLine("Connection Closed!");

Always close the connection when not in use to free resources.
Instead of Close(), use Dispose() or using for automatic cleanup.

4. Checking Connection State

Use the ConnectionState property to check if a connection is open or closed.

if (conn.State == System.Data.ConnectionState.Open)
{
    Console.WriteLine("Connection is Open");
}
else
{
    Console.WriteLine("Connection is Closed");
}

5. Executing Queries with `SqlConnection`

A SqlConnection is typically used with SqlCommand to execute SQL queries.

Example: Using SqlCommand to Fetch Data

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    string query = "SELECT * FROM Users";
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(reader["Username"]);
        }
    }
}

6. Handling Connection Errors

Use try-catch to handle database connection errors.

try
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        Console.WriteLine("Connected Successfully!");
    }
}
catch (SqlException ex)
{
    Console.WriteLine("SQL Error: " + ex.Message);
}
catch (Exception ex)
{
    Console.WriteLine("General Error: " + ex.Message);
}

SqlException handles database-related errors.
Exception handles general errors.

7. Using Connection Pooling

ADO.NET automatically uses connection pooling to improve performance. You can control pooling via the connection string.

Enabling Connection Pooling (Default Behavior)

string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;Pooling=True;";

Disabling Connection Pooling

string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;Pooling=False;";

8. Best Practices for Using `SqlConnection`

Always use using statements to ensure the connection is closed automatically.
Check connection state before opening or closing.
Use try-catch blocks to handle connection errors gracefully.
Use connection pooling to optimize database performance.
Keep connections open only when necessary to reduce server load.

interview

ADO.NET SqlConnection Class

ICSM Computer

Can you answer this question?

1 Answers

1. Creating a Connection

2. Connection String Components

3. Opening and Closing a Connection

4. Checking Connection State

5. Executing Queries with `SqlConnection`

6. Handling Connection Errors

7. Using Connection Pooling

8. Best Practices for Using `SqlConnection`

Liked By

interview

ADO.NET SqlConnection Class

ICSM Computer

Can you answer this question?

1 Answers

1. Creating a Connection

2. Connection String Components

3. Opening and Closing a Connection

4. Checking Connection State

5. Executing Queries with SqlConnection

6. Handling Connection Errors

7. Using Connection Pooling

8. Best Practices for Using SqlConnection

Liked By

5. Executing Queries with `SqlConnection`

8. Best Practices for Using `SqlConnection`