MSDN Documentation

Connecting to Data with ADO.NET

Connecting to Data with ADO.NET

ADO.NET is a set of .NET Framework classes that expose data access services to .NET Framework application developers. ADO.NET provides a comprehensive framework for working with data sources, both relational and non-relational. It enables applications to retrieve data from a data source, process that data, and then update the data source, all while maintaining data consistency.

Overview of ADO.NET

ADO.NET components allow you to connect to a data source, execute commands, and retrieve data. The core objects for data access include:

Establishing a Connection

The first step in accessing data is to establish a connection to the data source. This is done using a Connection object. You typically provide a connection string that specifies details like the server name, database name, authentication credentials, and the provider to use.

Here's an example using SqlConnection for SQL Server:

using System;
using System.Data.SqlClient;

public class ConnectionExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection successful!");
                // You can now execute commands
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Error connecting to database: {ex.Message}");
            }
        }
    }
}
Note: It's crucial to handle connection strings securely, especially in production environments. Avoid hardcoding sensitive information directly in your code. Consider using configuration files or environment variables.

Executing Commands and Retrieving Data

Once a connection is established, you can use Command objects to execute SQL statements or stored procedures. The results can be retrieved using a DataReader or by populating a DataSet.

Using DataReader

The DataReader is ideal for reading data sequentially. It's lightweight and efficient for scenarios where you just need to iterate through results.

using System;
using System.Data.SqlClient;

public class DataReaderExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT CustomerID, CompanyName FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                    }
                }
            }
        }
    }
}

Using DataAdapter and DataSet

The DataAdapter and DataSet provide a more flexible way to work with data, especially when dealing with disconnected scenarios or when you need to manipulate data in memory.

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

public class DataSetExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT OrderID, OrderDate, ShipCountry FROM Orders";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();

            try
            {
                connection.Open();
                adapter.Fill(dataSet, "Orders"); // Fill the DataSet with data into a table named "Orders"

                // Access data from the DataSet
                DataTable ordersTable = dataSet.Tables["Orders"];
                foreach (DataRow row in ordersTable.Rows)
                {
                    Console.WriteLine($"Order ID: {row["OrderID"]}, Ship Country: {row["ShipCountry"]}");
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Error retrieving data: {ex.Message}");
            }
        }
    }
}
Tip: When using DataAdapter, ensure you have appropriate commands set for InsertCommand, UpdateCommand, and DeleteCommand if you plan to update the data source.

Data Providers

ADO.NET supports a variety of data providers, each with its own set of classes derived from the common ADO.NET types. Common providers include:

You can also find third-party providers for other databases like MySQL, PostgreSQL, etc.

Best Practices