Database Operations in C#

Database operations in C# are typically performed using ADO.NET, which provides a set of classes to interact with various databases like SQL Server, MySQL, Oracle, and SQLite. The most common approach involves using connection strings, command objects, and data readers to execute SQL operations.

Connection String Syntax

A connection string contains the information needed to connect to a database −

// SQL Server connection string
"Data Source=serverName;Initial Catalog=databaseName;Integrated Security=true;"

// SQL Server with username/password
"Data Source=serverName;Initial Catalog=databaseName;User ID=username;Password=password;"

Establishing Database Connection

The SqlConnection class is used to establish a connection to SQL Server databases −

using System;
using System.Data.SqlClient;

class DatabaseConnection {
    private static string connectionString = "Data Source=.;Initial Catalog=TestDB;Integrated Security=true;";
    
    public static void Main() {
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            try {
                connection.Open();
                Console.WriteLine("Database connection established successfully!");
                Console.WriteLine("Database: " + connection.Database);
                Console.WriteLine("Server: " + connection.DataSource);
            }
            catch (Exception ex) {
                Console.WriteLine("Connection failed: " + ex.Message);
            }
        }
    }
}

The output of the above code is −

Database connection established successfully!
Database: TestDB
Server: .

Select Operations

Use SqlCommand and SqlDataReader to fetch data from the database −

using System;
using System.Data.SqlClient;

class SelectExample {
    private static string connectionString = "Data Source=.;Initial Catalog=TestDB;Integrated Security=true;";
    
    public static void Main() {
        string query = "SELECT EmployeeID, FirstName, LastName FROM Employees";
        
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlCommand command = new SqlCommand(query, connection);
            
            try {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                
                Console.WriteLine("Employee Records:");
                Console.WriteLine("ID\tFirst Name\tLast Name");
                Console.WriteLine("---\t----------\t---------");
                
                while (reader.Read()) {
                    Console.WriteLine($"{reader["EmployeeID"]}\t{reader["FirstName"]}\t{reader["LastName"]}");
                }
                reader.Close();
            }
            catch (Exception ex) {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

Insert Operations

The ExecuteNonQuery() method is used for INSERT, UPDATE, and DELETE operations −

using System;
using System.Data.SqlClient;

class InsertExample {
    private static string connectionString = "Data Source=.;Initial Catalog=TestDB;Integrated Security=true;";
    
    public static void Main() {
        string insertQuery = "INSERT INTO Employees (FirstName, LastName, Department) VALUES (@FirstName, @LastName, @Department)";
        
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlCommand command = new SqlCommand(insertQuery, connection);
            
            // Using parameters to prevent SQL injection
            command.Parameters.AddWithValue("@FirstName", "John");
            command.Parameters.AddWithValue("@LastName", "Smith");
            command.Parameters.AddWithValue("@Department", "IT");
            
            try {
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"Insert successful! {rowsAffected} row(s) affected.");
            }
            catch (Exception ex) {
                Console.WriteLine("Insert failed: " + ex.Message);
            }
        }
    }
}

The output of the above code is −

Insert successful! 1 row(s) affected.

Update Operations

using System;
using System.Data.SqlClient;

class UpdateExample {
    private static string connectionString = "Data Source=.;Initial Catalog=TestDB;Integrated Security=true;";
    
    public static void Main() {
        string updateQuery = "UPDATE Employees SET Department = @Department WHERE EmployeeID = @EmployeeID";
        
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlCommand command = new SqlCommand(updateQuery, connection);
            command.Parameters.AddWithValue("@Department", "Marketing");
            command.Parameters.AddWithValue("@EmployeeID", 1);
            
            try {
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"Update successful! {rowsAffected} row(s) updated.");
            }
            catch (Exception ex) {
                Console.WriteLine("Update failed: " + ex.Message);
            }
        }
    }
}

The output of the above code is −

Update successful! 1 row(s) updated.

Delete Operations

using System;
using System.Data.SqlClient;

class DeleteExample {
    private static string connectionString = "Data Source=.;Initial Catalog=TestDB;Integrated Security=true;";
    
    public static void Main() {
        string deleteQuery = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
        
        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlCommand command = new SqlCommand(deleteQuery, connection);
            command.Parameters.AddWithValue("@EmployeeID", 1);
            
            try {
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"Delete successful! {rowsAffected} row(s) deleted.");
            }
            catch (Exception ex) {
                Console.WriteLine("Delete failed: " + ex.Message);
            }
        }
    }
}

The output of the above code is −

Delete successful! 1 row(s) deleted.

Common Database Operations Comparison

Conclusion

Database operations in C# using ADO.NET involve establishing connections, executing SQL commands, and handling results. Always use parameterized queries to prevent SQL injection attacks and dispose of database resources properly using using statements to ensure connections are closed automatically.