ADO.NET Complete Guide - Database Operations in C#

This code demonstrates the fundamental ADO.NET concepts for database operations in C#. ADO.NET (ActiveX Data Objects for .NET) is Microsoft's data access technology for .NET applications.

1. ADO.NET Architecture Overview

Core Components

ADO.NET consists of two main components:

1. Connected Architecture: Direct connection to database (SqlConnection, SqlCommand, SqlDataReader)
2. Disconnected Architecture: Works with data offline (DataSet, DataTable, DataAdapter)

Key Classes Used in This Code

• SqlConnection: Manages database connection
• SqlCommand: Executes SQL commands
• SqlDataReader: Forward-only, read-only data reader
• SqlParameter: Represents parameters for stored procedures

2. Connection String Deep Dive

Connection String Variations

// Option 1: Using LocalDB with server syntax
string connectionDetails = "server=(LocalDB)\\\\MSSQLLocalDB;database=PGDAC;Integrated Security=true";

// Option 2: Using Data Source syntax (used in code)
string connectionDetails = "Data Source=(LocalDB)\\\\MSSQLLocalDB;Initial Catalog=PGDAC;Integrated Security=True";

Connection String Components

• Data Source: Specifies the SQL Server instance
  • (LocalDB)\\\\MSSQLLocalDB: SQL Server LocalDB instance
  • Could be localhost, server_name, or IP address
• Initial Catalog: Database name (same as database=)
• Integrated Security: Uses Windows Authentication
  • True: Use current Windows user
  • False: Requires User ID and Password

Alternative Connection String Examples