C# Using ADO.NET for Database Operations • ITER Academy

Hello, C# developers! In this post, we are going to explore ADO.NET, a core library in the .NET Framework that provides access to data sources such as databases, XML files, and more. ADO.NET is a powerful tool for performing database operations in C# applications. Let’s look at how to connect to a database, execute SQL commands, and manipulate data efficiently with ADO.NET.

What is ADO.NET?

ADO.NET is a set of classes that expose data access services for .NET Framework programmers. It provides a bridge between the front-end applications and back-end data sources, allowing developers to retrieve and manipulate data. ADO.NET supports a disconnected architecture where data can be managed offline, making it a versatile choice for many applications.

Setting Up ADO.NET in Your Project

To get started with ADO.NET, you need to create a new console application:

dotnet new console -n AdoNetDemo
cd AdoNetDemo

You also need to add the necessary database provider package (e.g., for SQL Server):

dotnet add package Microsoft.Data.SqlClient

Connecting to a Database

To connect to a database, you’ll typically create a connection string. Here’s an example of how to establish a connection with SQL Server:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open(); // Open the database connection
            Console.WriteLine("Connected to the database!");
        }
    }
}

Make sure to replace your_server, your_db, your_user, and your_password with your actual database credentials.

Executing SQL Commands

Once the connection is established, you can execute SQL commands using the SqlCommand class:

using (SqlCommand command = new SqlCommand("SELECT * FROM Products", connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Product ID: {reader[0]}, Name: {reader[1]}");
        }
    }
}

This code snippet executes a SELECT SQL command to retrieve all products from the database and prints their IDs and names to the console.

Inserting Data into the Database

You can also insert data into the database using ADO.NET. Here’s how:

string insertQuery = "INSERT INTO Products (Name, Price) VALUES (@name, @price)";
using (SqlCommand cmd = new SqlCommand(insertQuery, connection))
{
    cmd.Parameters.AddWithValue("@name", "New Product");
    cmd.Parameters.AddWithValue("@price", 29.99);
    int rowsAffected = cmd.ExecuteNonQuery();
    Console.WriteLine($"Rows inserted: {rowsAffected}");
}

This snippet illustrates how to insert a new product into the Products table, using parameterized queries to prevent SQL injection.

Best Practices for Using ADO.NET

Use Parameterized Queries: Always use parameters to prevent SQL injection attacks and ensure data integrity.
Handle Connections Properly: Use using statements to ensure that database connections are closed properly, even in the event of an error.
Implement Error Handling: Use try-catch blocks to handle exceptions gracefully, providing meaningful error messages when needed.

Conclusion

Working with ADO.NET in C# allows for efficient data access and manipulation within your applications. By following the steps outlined in this post, you can seamlessly connect to databases, execute commands, and manage data effectively. Start integrating ADO.NET into your projects today to enhance data handling capabilities!

To learn more about ITER Academy, visit our website. Visit Here