ADO.NET and Database Connectivity

ADO.NET and Database Connectivity

ADO.NET is the data access technology in .NET for connecting to databases, executing queries, and managing data. It supports both connected (real-time) and disconnected (cached) data access models.

ADO.NET Architecture

ADO.NET consists of Data Providers (SqlClient for SQL Server, OleDb, ODBC, Oracle) and the DataSet. Data providers include Connection, Command, DataReader, and DataAdapter objects. The architecture separates data access from data manipulation.

Connection

The SqlConnection class connects to SQL Server using a connection string containing server, database, and authentication details. Always use using statements or try-finally to ensure connections are closed. Connection pooling improves performance by reusing connections.

Commands

SqlCommand executes SQL statements. ExecuteReader() returns a DataReader for SELECT queries. ExecuteNonQuery() returns rows affected for INSERT, UPDATE, DELETE. ExecuteScalar() returns a single value. Parameterised queries (@paramName) prevent SQL injection.

DataReader

SqlDataReader provides a fast, forward-only, read-only stream of data. It is used in connected mode — the connection must remain open. Use Read() to advance to the next row. Access columns by index or name. DataReader is ideal for read-heavy, sequential data processing.

DataSet and DataAdapter

DataSet is an in-memory cache of data that works in disconnected mode. SqlDataAdapter fills the DataSet using Fill() and updates the database using Update(). A DataSet contains DataTable, DataRow, DataColumn, and DataRelation objects, supporting complex data manipulation offline.

Transactions

SqlTransaction groups multiple operations into an atomic unit. Begin with connection.BeginTransaction(), assign to commands, then Commit() on success or Rollback() on failure. Transactions ensure data integrity in multi-step operations.

LINQ to SQL

LINQ (Language Integrated Query) provides query syntax directly in C#. LINQ to SQL maps database tables to classes and enables querying with C# syntax: from c in db.Customers where c.City == "London" select c. Entity Framework is the modern ORM successor.

Summary

ADO.NET provides comprehensive data access through connected (DataReader) and disconnected (DataSet) models. Parameterised queries, transactions, and LINQ enhance security, reliability, and developer productivity.