Lesson: C# Database Connectivity

Introduction to C# Database Connectivity

In C#, you can connect to a database using ADO.NET, which provides a set of classes to interact with data sources. This lesson will teach you how to connect to a SQL Server database, execute SQL commands, and handle the results in your application.

Setting Up Database Connectivity

To connect to a SQL Server database in C#, you need to import the `System.Data.SqlClient` namespace. You also need the connection string, which contains the details about the database, such as the server name, database name, user credentials, etc.

Example: Basic Database Connection

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServer;Database=myDatabase;User Id=myUsername;Password=myPassword;";
        
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                Console.WriteLine("Connection successful.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

In this code, the `SqlConnection` object is used to open a connection to the database. If the connection is successful, a message will be displayed; otherwise, an error will be shown.

Executing SQL Commands

Once the connection is open, you can execute SQL commands like `SELECT`, `INSERT`, `UPDATE`, and `DELETE`. Here's an example of executing a `SELECT` command and reading the results.

Example: Executing a SELECT Query

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServer;Database=myDatabase;User Id=myUsername;Password=myPassword;";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "SELECT Name, Age FROM Employees";
            
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand(query, conn);
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine("Name: " + reader["Name"] + ", Age: " + reader["Age"]);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

In the code above, we execute a `SELECT` statement using `SqlCommand`. The results are retrieved using `SqlDataReader`, and we loop through the records to print the data to the console.

Executing INSERT, UPDATE, DELETE Commands

You can also use ADO.NET to execute `INSERT`, `UPDATE`, and `DELETE` statements. Here's how you can execute an `INSERT` statement to add a new record to a table.

Example: Executing an INSERT Query

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServer;Database=myDatabase;User Id=myUsername;Password=myPassword;";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Employees (Name, Age) VALUES ('John Doe', 30)";
            
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand(query, conn);
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine(rowsAffected + " row(s) inserted.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

In the code above, we execute an `INSERT` statement to add a new employee to the `Employees` table. The `ExecuteNonQuery` method is used because this query does not return any data, but it returns the number of rows affected.

Interactive Example: Execute a SELECT Query

Let's simulate executing a `SELECT` query and displaying the results:

Enter a query to execute:

Execute Query

Query Result:

Database Connectivity Quiz

Test your knowledge on C# database connectivity!