ADO.NET | Microsoft

ADO.NET

ADO.NET, which stands for Active Data Objects for .NET, is a data access technology provided by Microsoft as part of the .NET framework. It allows developers to interact with data from various data sources, such as databases, XML files, and more, in a .NET application.

Key components of ADO.NET include:

1. Data Providers: ADO.NET provides data providers specific to different databases like SQL Server, Oracle, MySQL, etc. These data providers offer classes and methods to connect to and manipulate data in these databases.

2. Connection: ADO.NET includes classes like SqlConnection and OracleConnection to establish connections to databases.

3. Command: You can use classes like SqlCommand or OracleCommand to execute SQL or database commands against the connected data source.

4. DataReader: The DataReader provides a forward-only, read-only stream of data from a database query. It's efficient for reading large sets of data.

5. DataSet and DataTable: These classes allow you to work with disconnected data, meaning data is fetched from the database, and the connection is closed. You can manipulate data in memory and later reconcile changes with the database.

6. DataAdapter: The DataAdapter acts as a bridge between a DataSet and a data source, allowing you to fill a DataSet with data from a database and update the database with changes made in the DataSet.

7. Entity Framework: While not strictly part of ADO.NET, Entity Framework is a higher-level ORM (Object-Relational Mapping) framework built on top of ADO.NET. It simplifies data access by allowing you to work with database entities as .NET objects.

Use ADO.NET to create data-driven applications, perform CRUD (Create, Read, Update, Delete) operations on databases, and manage data efficiently. It's a fundamental technology for building database-driven applications in the .NET ecosystem.

Simple C# code example that demonstrates ADO.NET connections and basic CRUD (Create, Read, Update, Delete) operations using SQL Server as the database. 

using System;

using System.Data;

using System.Data.SqlClient;

public class AdoNetDemo

{

    private const string ConnectionString = "Your_Connection_String_here"; // Replace with your SQL Server connection string

    public static void Main(string[] args)

    {

        // CREATE operation

        InsertData("John", "Doe");

        // READ operation

        ReadData();

        // UPDATE operation

        UpdateData(1, "UpdatedFirstName", "UpdatedLastName");

        // READ operation after update

        ReadData();

        // DELETE operation

        DeleteData(1);

        // READ operation after delete

        ReadData();

    }

    public static void InsertData(string firstName, string lastName)

    {

        using (SqlConnection connection = new SqlConnection(ConnectionString))

        {

            connection.Open();

            string insertQuery = "INSERT INTO YourTable (FirstName, LastName) VALUES (@FirstName, @LastName)";

            SqlCommand cmd = new SqlCommand(insertQuery, connection);

            cmd.Parameters.AddWithValue("@FirstName", firstName);

            cmd.Parameters.AddWithValue("@LastName", lastName);

            int rowsAffected = cmd.ExecuteNonQuery();

            Console.WriteLine($"{rowsAffected} row(s) inserted.");

        }

    }

    public static void ReadData()

    {

        using (SqlConnection connection = new SqlConnection(ConnectionString))

        {

            connection.Open();

            string selectQuery = "SELECT * FROM YourTable";

            SqlCommand cmd = new SqlCommand(selectQuery, connection);

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())

            {

                Console.WriteLine($"ID: {reader["ID"]}, FirstName: {reader["FirstName"]}, LastName: {reader["LastName"]}");

            }

        }

    }

    public static void UpdateData(int id, string firstName, string lastName)

    {

        using (SqlConnection connection = new SqlConnection(ConnectionString))

        {

            connection.Open();

            string updateQuery = "UPDATE YourTable SET FirstName = @FirstName, LastName = @LastName WHERE ID = @ID";

            SqlCommand cmd = new SqlCommand(updateQuery, connection);

            cmd.Parameters.AddWithValue("@ID", id);

            cmd.Parameters.AddWithValue("@FirstName", firstName);

            cmd.Parameters.AddWithValue("@LastName", lastName);

            int rowsAffected = cmd.ExecuteNonQuery();

            Console.WriteLine($"{rowsAffected} row(s) updated.");

        }

    }

    public static void DeleteData(int id)

    {

        using (SqlConnection connection = new SqlConnection(ConnectionString))

        {

            connection.Open();

            string deleteQuery = "DELETE FROM YourTable WHERE ID = @ID";

            SqlCommand cmd = new SqlCommand(deleteQuery, connection);

            cmd.Parameters.AddWithValue("@ID", id);

            int rowsAffected = cmd.ExecuteNonQuery();

            Console.WriteLine($"{rowsAffected} row(s) deleted.");

        }

    }

}

Remember to replace `"Your_Connection_String_here"` with your actual SQL Server connection string and `"YourTable"` with the name of the table you want to perform CRUD operations on. This code demonstrates how to insert, read, update, and delete data in a SQL Server database using ADO.NET.