C# Connect to Database

Understanding Database Connections

Database connectivity is crucial for data-driven applications. C# supports multiple database systems through ADO.NET, which provides classes for connecting, querying, and managing databases. You can perform CRUD operations (Create, Read, Update, Delete), execute stored procedures, and handle transactions efficiently with proper connection management and error handling.

SqlConnection conn = 
  new SqlConnection(connString);
conn.Open();

SqlCommand cmd = 
  new SqlCommand(query, conn);
cmd.ExecuteNonQuery();

SqlDataReader reader = 
  cmd.ExecuteReader();
while (reader.Read()) { }

string connStr = 
  "Server=localhost;" +
  "Database=myDB;";

🔹 Basic Database Connection

Establishing a basic database connection in C# using SQL Server involves configuring a connection string and utilizing SqlConnection. Create a connection string specifying the server, database, and authentication details. Instantiate SqlConnection with this string, then call Open() to establish the link. Always close connections with Close() or employ using statements for automatic disposal. This foundational step is critical for any data-driven application.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Connection string
        string connectionString = 
            "Server=localhost;" +
            "Database=TestDB;" +
            "Integrated Security=true;";
        
        SqlConnection connection = null;
        
        try
        {
            // Create connection
            connection = new SqlConnection(connectionString);
            
            // Open connection
            connection.Open();
            
            Console.WriteLine("✓ Database connected successfully!");
            Console.WriteLine($"Database: {connection.Database}");
            Console.WriteLine($"Server: {connection.DataSource}");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Connection failed: {ex.Message}");
        }
        finally
        {
            // Always close connection
            if (connection != null && connection.State == 
                System.Data.ConnectionState.Open)
            {
                connection.Close();
                Console.WriteLine("Connection closed.");
            }
        }
    }
}

✓ Database connected successfully!
Database: TestDB
Server: localhost
Connection closed.

🔹 Execute SQL Query

Executing an SQL query in C# involves sending commands to a database and retrieving results. Use SqlCommand to define your query (e.g., SELECT) and associate it with an open SqlConnection. Execute the command with ExecuteReader() for result sets, then iterate through SqlDataReader to access data. Parameterized queries prevent SQL injection. Always handle exceptions to manage connection issues or syntax errors gracefully.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = 
            "Server=localhost;Database=TestDB;Integrated Security=true;";
        
        string query = "SELECT Id, Name, Email FROM Users";
        
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                
                // Create command
                SqlCommand cmd = new SqlCommand(query, conn);
                
                // Execute and read data
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    Console.WriteLine("Users in Database:\n");
                    
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        string email = reader.GetString(2);
                        
                        Console.WriteLine($"ID: {id}");
                        Console.WriteLine($"Name: {name}");
                        Console.WriteLine($"Email: {email}");
                        Console.WriteLine("---");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

Users in Database:

ID: 1
Name: John Doe
Email: [email protected]
---
ID: 2
Name: Jane Smith
Email: [email protected]
---

🔹 Insert Data into Database

Inserting data into a database table in C# is accomplished using the INSERT INTO SQL command via SqlCommand. Construct the command with the table name and column values, using parameters (e.g., @ParamName) to avoid injection. Execute with ExecuteNonQuery(), which returns rows affected. Validate input data before insertion and use transactions for multiple inserts to ensure data integrity and consistency.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = 
            "Server=localhost;Database=TestDB;Integrated Security=true;";
        
        Console.Write("Enter name: ");
        string name = Console.ReadLine();
        
        Console.Write("Enter email: ");
        string email = Console.ReadLine();
        
        string query = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
        
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                
                // Create command with parameters
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Email", email);
                
                // Execute insert
                int rowsAffected = cmd.ExecuteNonQuery();
                
                Console.WriteLine($"\n✓ {rowsAffected} record inserted successfully!");
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

Enter name: Alice Brown
Enter email: [email protected]

✓ 1 record inserted successfully!

🔹 Update Database Records

Updating existing records in a C# database application modifies data based on specified conditions. Formulate an UPDATE statement with SET and WHERE clauses, using parameters for values and criteria. Execute via ExecuteNonQuery() within a using block. It’s crucial to verify the WHERE clause to prevent unintended updates. Implement error handling and consider optimistic concurrency checks if multiple users might edit the same record.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = 
            "Server=localhost;Database=TestDB;Integrated Security=true;";
        
        Console.Write("Enter user ID to update: ");
        int userId = int.Parse(Console.ReadLine());
        
        Console.Write("Enter new email: ");
        string newEmail = Console.ReadLine();
        
        string query = "UPDATE Users SET Email = @Email WHERE Id = @Id";
        
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@Email", newEmail);
                cmd.Parameters.AddWithValue("@Id", userId);
                
                int rowsAffected = cmd.ExecuteNonQuery();
                
                if (rowsAffected > 0)
                {
                    Console.WriteLine($"\n✓ User {userId} updated successfully!");
                }
                else
                {
                    Console.WriteLine($"\n✗ User {userId} not found.");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

Enter user ID to update: 1
Enter new email: [email protected]

✓ User 1 updated successfully!

🔹 Delete Database Records

Deleting records from a database in C# removes data permanently using the DELETE SQL command. Create a parameterized command with a WHERE clause to target specific rows; omitting WHERE deletes all rows. Use ExecuteNonQuery() and check the returned count. Always confirm deletions with users in interactive applications and maintain backups. Employ transactions to group deletions with other operations if needed.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = 
            "Server=localhost;Database=TestDB;Integrated Security=true;";
        
        Console.Write("Enter user ID to delete: ");
        int userId = int.Parse(Console.ReadLine());
        
        string query = "DELETE FROM Users WHERE Id = @Id";
        
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@Id", userId);
                
                int rowsAffected = cmd.ExecuteNonQuery();
                
                if (rowsAffected > 0)
                {
                    Console.WriteLine($"\n✓ User {userId} deleted successfully!");
                }
                else
                {
                    Console.WriteLine($"\n✗ User {userId} not found.");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

Enter user ID to delete: 3

✓ User 3 deleted successfully!

🔹 Using Connection String from Config

Storing connection strings in configuration files enhances security and maintainability in C# applications. Place the connection string in app.config or web.config within a <connectionStrings> section. Access it using ConfigurationManager.ConnectionStrings["YourKey"].ConnectionString. This approach centralizes settings, eases deployment changes across environments, and avoids hardcoding sensitive data into source code.

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

class Program
{
    static void Main()
    {
        try
        {
            // Read from App.config or Web.config
            string connectionString = 
                ConfigurationManager.ConnectionStrings["MyDatabase"]
                .ConnectionString;
            
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                Console.WriteLine("✓ Connected using config file!");
                
                // Execute query
                string query = "SELECT COUNT(*) FROM Users";
                SqlCommand cmd = new SqlCommand(query, conn);
                
                int userCount = (int)cmd.ExecuteScalar();
                Console.WriteLine($"Total users: {userCount}");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

/* App.config file:


  
    
  

*/

✓ Connected using config file!
Total users: 5