Using ADO.NET - Comprehensive Tutorial

Introduction

ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components. It is an integral part of .NET and provides a rich set of classes for working with databases, XML files, and other data sources.

Setting Up Your Environment

To get started with ADO.NET, you need to have Visual Studio installed. You can download the Community Edition for free from the official website. Once installed, create a new C# Console Application project.

Make sure to add the necessary references to your project. By default, the System.Data namespace is included in a new Console Application.

Connecting to a Database

To connect to a database using ADO.NET, you need to use the SqlConnection class. Below is an example of how to establish a connection to a SQL Server database:

using System;
using System.Data.SqlClient;

namespace AdoNetExample
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=your_server;Initial Catalog=your_database;Integrated Security=True";
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    Console.WriteLine("Connection successful.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
            }
        }
    }
}

Executing SQL Commands

Once a connection is established, you can execute SQL commands using the SqlCommand class. Below is an example of how to execute an SQL command to insert data into a database:

using System;
using System.Data.SqlClient;

namespace AdoNetExample
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=your_server;Initial Catalog=your_database;Integrated Security=True";
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
                
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Name", "John Doe");
                    command.Parameters.AddWithValue("@Email", "john.doe@example.com");

                    try
                    {
                        connection.Open();
                        int rowsAffected = command.ExecuteNonQuery();
                        Console.WriteLine("Rows affected: " + rowsAffected);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error: " + ex.Message);
                    }
                }
            }
        }
    }
}

Reading Data

To read data from a database, you can use the SqlDataReader class. Below is an example of how to read data from a database:

using System;
using System.Data.SqlClient;

namespace AdoNetExample
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=your_server;Initial Catalog=your_database;Integrated Security=True";
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = "SELECT * FROM Users";
                
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine($"ID: {reader["ID"]}, Name: {reader["Name"]}, Email: {reader["Email"]}");
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Error: " + ex.Message);
                    }
                }
            }
        }
    }
}

Using DataSets and DataAdapters

DataSets and DataAdapters provide a more flexible way of working with data. A DataSet can hold multiple tables and relationships between them. A DataAdapter acts as a bridge between a DataSet and a data source for retrieving and saving data.

Below is an example of using a DataSet and DataAdapter:

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

namespace AdoNetExample
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=your_server;Initial Catalog=your_database;Integrated Security=True";
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Users", connection);
                DataSet dataSet = new DataSet();
                
                try
                {
                    connection.Open();
                    adapter.Fill(dataSet, "Users");

                    foreach (DataRow row in dataSet.Tables["Users"].Rows)
                    {
                        Console.WriteLine($"ID: {row["ID"]}, Name: {row["Name"]}, Email: {row["Email"]}");
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
            }
        }
    }
}

Handling Transactions

Transactions allow you to execute a series of operations as a single unit of work. If any operation fails, the transaction can be rolled back, undoing all the changes made during the transaction.

Below is an example of handling transactions using ADO.NET:

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

namespace AdoNetExample
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=your_server;Initial Catalog=your_database;Integrated Security=True";
            
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlTransaction transaction = null;

                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();

                    SqlCommand command = new SqlCommand("INSERT INTO Users (Name, Email) VALUES (@Name, @Email)", connection, transaction);
                    command.Parameters.AddWithValue("@Name", "Jane Doe");
                    command.Parameters.AddWithValue("@Email", "jane.doe@example.com");
                    command.ExecuteNonQuery();

                    command = new SqlCommand("UPDATE Users SET Email=@Email WHERE Name=@Name", connection, transaction);
                    command.Parameters.AddWithValue("@Name", "John Doe");
                    command.Parameters.AddWithValue("@Email", "john.doe@newemail.com");
                    command.ExecuteNonQuery();

                    transaction.Commit();
                    Console.WriteLine("Transaction committed.");
                }
                catch (Exception ex)
                {
                    if (transaction != null)
                    {
                        transaction.Rollback();
                    }
                    Console.WriteLine("Error: " + ex.Message);
                }
            }
        }
    }
}

Conclusion

In this tutorial, we covered the basics of using ADO.NET to interact with databases. We learned how to establish a connection, execute SQL commands, read data, use DataSets and DataAdapters, and handle transactions. ADO.NET is a powerful tool for data access in .NET applications, and mastering it will allow you to create robust and scalable data-driven applications.