Working with Databases in C# using ADO.NET

ADO.NET (ActiveX Data Objects .NET) is a set of data access components provided by Microsoft to interact with databases in C#. In this guide, you'll learn how to connect to a database, perform CRUD (Create, Read, Update, Delete) operations, and handle database connections and data using ADO.NET.

Connecting to a Database

Before you can work with a database, you need to establish a connection to it. ADO.NET provides classes such as `SqlConnection` and `OleDbConnection` for connecting to SQL Server and other database systems.

Example of connecting to a SQL Server database:

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        // Define the connection string
        string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                // Open the database connection
                connection.Open();
                Console.WriteLine("Connected to the database.");
                // Perform database operations here
                // ...
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}
    

Executing SQL Queries

Once connected, you can execute SQL queries to interact with the database. ADO.NET provides classes like `SqlCommand` to execute queries.

Example of executing a SQL query:

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                // Execute a SELECT query
                string sqlQuery = "SELECT FirstName, LastName FROM Users";
                SqlCommand command = new SqlCommand(sqlQuery, connection);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine($"Name: {reader["FirstName"]} {reader["LastName"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}
    

Performing CRUD Operations

You can use ADO.NET to perform Create, Read, Update, and Delete (CRUD) operations on the database by executing SQL queries accordingly. These operations allow you to interact with and modify data in the database.

Working with DataSets and DataAdapters

ADO.NET provides the `DataSet` and `DataAdapter` classes to work with data from the database in a disconnected manner. DataSets are in-memory representations of data, and DataAdapters are used to fill and update data between the database and the DataSet.

Conclusion

Working with databases in C# using ADO.NET is a fundamental skill for data-driven applications. You've learned how to connect to a database, execute SQL queries, and perform CRUD operations. ADO.NET provides a versatile and powerful framework for working with various database systems.

Practice connecting to and interacting with databases in your C# applications to gain proficiency in database programming. As you continue your programming journey, you'll explore advanced topics like database transactions, stored procedures, and ORMs (Object-Relational Mapping).