Tutorials – C#.Net

Chapter 11: Working with Databases (ADO.NET)

Chapter 11 of our C# tutorial focuses on working with databases using ADO.NET, a set of libraries provided by Microsoft to interact with relational databases. ADO.NET allows you to perform various database operations, such as connecting to a database, executing queries, and retrieving or updating data. In this chapter, we’ll explore the fundamentals of ADO.NET, including connecting to a database, executing SQL commands, and handling data.

11.1 Introduction to ADO.NET

ADO.NET, which stands for ActiveX Data Objects for .NET, is a data access technology provided by Microsoft as part of the .NET Framework. It is used to interact with databases, such as Microsoft SQL Server, Oracle, MySQL, and more, in C# applications. ADO.NET provides a rich set of classes and libraries for performing database operations, making it a versatile and essential tool for developers working with databases in C#.

The primary components of ADO.NET include:

1. Data Providers: Data providers are responsible for connecting to specific database management systems (DBMS) and providing access to their data. Some popular data providers in ADO.NET include System.Data.SqlClient for SQL Server, System.Data.OracleClient for Oracle, and MySql.Data for MySQL.

2. Connection: The connection component is used to establish a connection to a database. It requires connection strings that contain information about the server, database, and authentication details.

3. Command: The command component allows you to execute SQL commands against the database. It includes classes for executing queries, stored procedures, and updating data.

4. DataReader: The DataReader component is used to retrieve data from the database, allowing for efficient streaming of large result sets.

5. DataSet and DataTable: These components are used for working with disconnected data, allowing you to store, manipulate, and display data in a tabular format.

11.2 Connecting to a Database

Before interacting with a database, you need to establish a connection. ADO.NET provides the SqlConnection class for connecting to SQL Server databases and other data providers for other databases. The connection string specifies the server, database, and authentication information.

Here’s an example of connecting to a SQL Server database:

using System;
using System.Data.SqlClient;
class Program {
 static void Main() {
 string connectionString = "Server=myServer;Database=myDatabase;User=myUser;Password=myPassword;";
 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open();
 // Database operations go here.
            connection.Close();
        }
    }
}

In this example, we create a SqlConnection object and open a connection using a connection string. The using statement ensures that the connection is properly closed when done.

11.3 Executing SQL Commands

Once you have a connection to the database, you can execute SQL commands using the SqlCommand class. ADO.NET supports various types of SQL commands, including SELECT queries, INSERT, UPDATE, and DELETE statements, and stored procedures.

Here’s an example of executing a SELECT query and retrieving data:

using System;
using System.Data;
using System.Data.SqlClient;
class Program {
 static void Main() {
 string connectionString = "Server=myServer;Database=myDatabase;User=myUser;Password=myPassword;";
 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open();
 string selectQuery = "SELECT Id, Name FROM Employees";
 using (SqlCommand command = new SqlCommand(selectQuery, connection)) {
 using (SqlDataReader reader = command.ExecuteReader()) {
 while (reader.Read()) {
 int id = (int)reader["Id"];
 string name = reader["Name"].ToString();
Console.WriteLine($"Employee Id: {id}, Name: {name}"); } } }
             connection.Close();
        }
    }
}

In this example, we execute a SELECT query to retrieve data from the “Employees” table and use a SqlDataReader to iterate through the results.

11.4 Inserting, Updating, and Deleting Data

You can use SqlCommand to execute INSERT, UPDATE, and DELETE statements as well. Here’s an example of inserting data into a table:

using System;
using System.Data.SqlClient;
class Program {
 static void Main() {
 string connectionString = "Server=myServer;Database=myDatabase;User=myUser;Password=myPassword;";
 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open();
 string insertQuery = "INSERT INTO Employees (Name, Department) VALUES (@Name, @Department)";
 using (SqlCommand command = new SqlCommand(insertQuery, connection)) { command.Parameters.AddWithValue("@Name", "John Doe"); command.Parameters.AddWithValue("@Department", "HR");
 int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"{rowsAffected} row(s) inserted."); }
            connection.Close();
        }
    }
}

In this example, we execute an INSERT statement to add a new employee record to the “Employees” table.

11.5 Working with Transactions

Transactions ensure the consistency of your data by grouping multiple database operations into a single unit of work. ADO.NET supports transactions through the SqlTransaction class.

Here’s an example of using transactions in ADO.NET:

using System;
using System.Data.SqlClient;
class Program {
 static void Main() {
 string connectionString = "Server=myServer;Database=myDatabase;User=myUser;Password=myPassword;";
 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
 try {
 // Execute multiple SQL commands within the same transaction.
 // ...
 // If all operations are successful, commit the transaction. transaction.Commit(); }
 catch (Exception ex) {
 // If an error occurs, roll back the transaction to maintain data consistency. transaction.Rollback(); Console.WriteLine($"Transaction rolled back: {ex.Message}"); }
            connection.Close();
        }
    }
}

In this example, we create a transaction, execute multiple SQL commands within the transaction, and commit or roll back the transaction based on the outcome.

11.6 Working with DataSets and DataTables

ADO.NET provides the DataSet and DataTable classes for working with disconnected data. These classes allow you to store data retrieved from a database, manipulate it, and display it in a tabular format.

Here’s an example of using a DataSet to retrieve and manipulate data:

using System;
using System.Data;
using System.Data.SqlClient;
class Program {
 static void Main() {
 string connectionString = "Server=myServer;Database=myDatabase;User=myUser;Password=myPassword;";
 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open();
 string selectQuery = "SELECT Id, Name FROM Employees";
 using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection)) { DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Employees");
DataTable table = dataSet.Tables["Employees"];
 foreach (DataRow row in table.Rows) {
 int id = (int)row["Id"];
 string name = row["Name"].ToString();
Console.WriteLine($"Employee Id: {id}, Name: {name}"); } }
            connection.Close();
        }
    }
}

In this example, we use a DataSet and a DataTable to store and display data retrieved from the “Employees” table.

11.7 Handling Exceptions

Handling exceptions is crucial when working with databases. ADO.NET can throw exceptions related to database connections, query execution, or data retrieval. It’s essential to catch and handle these exceptions to ensure graceful error handling.

Here’s an example of exception handling in ADO.NET:

using System;
using System.Data.SqlClient;
class Program
{
 static void Main()
 {
 string connectionString = "Server=myServer;Database=myDatabase;User=myUser;Password=myPassword;";
 try
 {
 using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
 // Database operations go here.
                connection.Close();
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Database error: {ex.Message}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occurred: {ex.Message}");
        }
    }
}

In this example, we catch specific exceptions, such as SqlException, to handle database-related errors, and a general Exception catch block to handle other errors.

11.8 Best Practices for ADO.NET

To work effectively with ADO.NET and databases, consider the following best practices:

1. Use Parameterized Queries: Always use parameterized queries to prevent SQL injection attacks. Avoid building SQL queries by concatenating user inputs.

2. Connection Management: Open connections only when needed and close them as soon as possible to release resources. Use the using statement for automatic connection disposal.

3. Transactions: Use transactions to ensure data consistency when multiple database operations need to be atomic.

4. Error Handling: Implement robust error handling to catch and manage exceptions, providing informative error messages and log details.

5. Data Access Layer: Consider creating a data access layer (DAL) to encapsulate database access logic, making it reusable and maintainable.

6. Connection Strings: Store connection strings in a secure and centralized configuration, such as an app.config or web.config file.

7. Performance Considerations: Optimize database operations by using indexes, views, and stored procedures. Minimize round trips to the database.

8. Data Retrieval: When working with large datasets, use pagination or filtering to retrieve only the required data.

11.9 Conclusion of Chapter 11

In Chapter 11, you’ve learned the fundamentals of working with databases using ADO.NET in C#. ADO.NET provides a comprehensive set of tools for connecting to databases, executing SQL commands, and handling data, making it an essential technology for C# developers. Understanding how to interact with databases is crucial for building data-driven applications, and ADO.NET provides the necessary tools and best practices for doing so efficiently and securely.