Establishing a Database Connection using ADO.NET in C#

This guide provides a step-by-step approach to establishing a database connection using ADO.NET in C#. The code is presented in simple English for easy understanding.

Step 1: Include Necessary Namespaces

First, include the necessary namespaces in your C# file. These namespaces provide the classes required for database operations.

using System;
using System.Data;
using System.Data.SqlClient;

Step 2: Define the Connection String

A connection string contains information about how to connect to the database. Replace the placeholders with your actual database details.

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        
    

Step 3: Create a SqlConnection Object

To create a connection object SqlConnection class is used. Pass the connection string as a parameter to the constructor.

SqlConnection connection = new SqlConnection(connectionString);
        
    

Step 4: Open the Connection

Before executing any commands, you need to open the connection.

try
{
    connection.Open();
    Console.WriteLine("Connection opened successfully");
}
catch (Exception ex)
{
    Console.WriteLine("Error in opening connection: " + ex.Message);
}
        
    

Step 5: Perform Database Operations

Once the connection is open, you can perform database operations such as executing SQL queries.

SqlCommand command = new SqlCommand("SELECT * FROM YourTable", connection);
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine(reader[0].ToString() + ", " + reader[1].ToString());
}
reader.Close();
        
    

Step 6: Close the Connection

Always close the connection after your operations are complete.

connection.Close();
Console.WriteLine("Connection closed successfully");
        
    

Step 7: Exception Handling

Handle exceptions that might have occurred during database operations.

catch (Exception ex)
{
    Console.WriteLine("Error: " + ex.Message);
}
finally
{
    if (connection.State == ConnectionState.Open)
    {
        connection.Close();
    }
}
        
    

Here's how the complete C# code would look:

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        SqlConnection connection = new SqlConnection(connectionString);

        try
        {
            // Open the connection
            connection.Open();
            Console.WriteLine("Connection opened successfully");

            // Perform database operations
            SqlCommand command = new SqlCommand("SELECT * FROM YourTable", connection);
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine(reader[0].ToString() + ", " + reader[1].ToString());
            }

            // Close the reader
            reader.Close();
        }
        catch (Exception ex)
        {
            // Handle any exceptions
            Console.WriteLine("Error: " + ex.Message);
        }
        finally
        {
            // Ensure the connection is closed
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
                Console.WriteLine("Connection closed successfully");
            }
        }
    }
}

Sample Output

Example output of a successful database operation:

Connection opened successfully
Row1Column1, Row1Column2
Row2Column1, Row2Column2
...
Connection closed successfully
        
    

In this code:

1. The necessary namespaces are included at the beginning.
2. A connection string is defined with placeholders for the server, database, username, and password.
3. A SqlConnection object is instantiated with the connection string.
4. The connection is opened, and a SQL command is executed to read data from the specified table.
5. Data is read from the database and printed to the console.
6. Proper exception handling is implemented to catch any errors.
7. Finally, the connection is closed inside the finally block to ensure it's always closed, even if an error occurs.

This code provides a basic structure for connecting to a database using ADO.NET in C#. You'll need to replace the placeholders in the connection string with your actual database details and modify the SQL command to suit your specific needs.