SQL Connection, Commands, DataReader, DataAdapter, DataSet, Transactions, and Parameterized Queries in C#

SQL Connection, Commands, DataReader, DataAdapter, DataSet, Transactions, and Parameterized Queries in C# are the core building blocks of ADO.NET. Every enterprise-level .NET application interacts with databases using these components. Whether you are building an ASP.NET Core Application, Banking System, E-Commerce Platform, ERP Solution, CRM Software, Hospital Management System, or School Management System, understanding SQL Connection, Commands, DataReader, DataAdapter, DataSet, Transactions, and Parameterized Queries in C# is essential.

These components help applications connect to SQL Server, execute queries, retrieve records, update data, prevent security vulnerabilities, and ensure data consistency.

Understanding the ADO.NET Workflow

The typical workflow in ADO.NET is:

Application

↓

SqlConnection

↓

SqlCommand

↓

SQL Server

↓

Data Returned

↓

Application

This architecture enables communication between the application and database.

What is SqlConnection?

SqlConnection establishes communication with SQL Server.

Namespace:

using Microsoft.Data.SqlClient;

Example:

string connectionString =
"Server=localhost;Database=StudentDB;Trusted_Connection=True;";

SqlConnection connection =
new SqlConnection(
connectionString);

The connection object links the application to the database.

Opening a SQL Connection

Example:

connection.Open();

Output:

Connection Opened

The application can now execute SQL commands.

Closing a SQL Connection

Example:

connection.Close();

Output:

Connection Closed

Always close connections when work is complete.

Using Statement for Connections

Recommended approach:

using(
SqlConnection connection =
new SqlConnection(
connectionString))
{
    connection.Open();
}

Benefits:

• Automatic cleanup
• Better memory management
• Reduced resource leaks

This approach is used in production applications.

What is SqlCommand?

SqlCommand executes SQL statements against SQL Server.

Example:

SqlCommand command =
new SqlCommand(
query,
connection);

SqlCommand is one of the most frequently used ADO.NET objects.

Executing INSERT Commands

Example:

INSERT INTO Students
(
Name,
Course
)
VALUES
(
'Rahul',
'.NET'
)

C#:

string query =
@"INSERT INTO Students
(Name, Course)
VALUES
('Rahul','.NET')";

Execute:

command.ExecuteNonQuery();

Output:

Record Inserted

What is ExecuteNonQuery()?

ExecuteNonQuery is used when no data is returned.

Examples:

INSERT

UPDATE

DELETE

CREATE TABLE

Return value:

Affected Rows

Example:

int rows =
command.ExecuteNonQuery();

Output:

1

One record was affected.

Executing SELECT Queries

Example:

SELECT *
FROM Students

Command:

SqlCommand command =
new SqlCommand(
query,
connection);

Data retrieval requires a different execution method.

What is SqlDataReader?

SqlDataReader retrieves records from SQL Server.

Characteristics:

Fast

Forward Only

Read Only

Connected Architecture

SqlDataReader is commonly used for high-performance data retrieval.

Reading Data with SqlDataReader

Example:

SqlDataReader reader =
command.ExecuteReader();

Read Records:

while(reader.Read())
{
    Console.WriteLine(
    reader["Name"]);
}

Output:

Rahul

Amit

Priya

Records are retrieved sequentially.

Understanding reader.Read()

Example:

reader.Read();

Purpose:

Move to Next Record

Returns:

True

False

depending on whether records exist.

ExecuteScalar()

ExecuteScalar retrieves a single value.

Example:

SELECT COUNT(*)
FROM Students

C#:

object result =
command.ExecuteScalar();

Output:

100

Useful for aggregates and statistics.

When to Use ExecuteScalar()

Examples:

COUNT

SUM

AVG

MAX

MIN

Only the first column of the first row is returned.

What is SqlDataAdapter?

SqlDataAdapter acts as a bridge between the database and DataSet.

Characteristics:

Disconnected Architecture

Automatic Data Transfer

Supports Updates

Useful for large applications.

Creating a DataAdapter

Example:

SqlDataAdapter adapter =
new SqlDataAdapter(
query,
connection);

The adapter retrieves data from SQL Server.

What is DataSet?

A DataSet is an in-memory representation of data.

Characteristics:

Disconnected

Multiple Tables

Relations

XML Support

DataSet is useful for complex applications.

Filling a DataSet

Example:

DataSet dataSet =
new DataSet();

adapter.Fill(
dataSet);

Output:

Data Loaded

The database connection can then be closed.

Accessing DataSet Records

Example:

foreach(DataRow row
in dataSet.Tables[0].Rows)
{
    Console.WriteLine(
    row["Name"]);
}

Output:

Rahul

Amit

Priya

Data can be processed locally.

What is DataTable?

DataTable represents a single table in memory.

Example:

DataTable table =
new DataTable();

Benefits:

Lightweight

Easy to Use

In-Memory Processing

Frequently used with DataAdapters.

Filling a DataTable

Example:

adapter.Fill(
table);

Output:

Records Loaded

The table now contains database records.

Connected vs Disconnected Architecture

Connected Architecture

Objects:

SqlConnection

SqlCommand

SqlDataReader

Connection remains open.

Disconnected Architecture

Objects:

SqlDataAdapter

DataSet

DataTable

Connection closes after data retrieval.

What are Transactions?

A Transaction is a group of database operations that execute as a single unit.

Example:

Withdraw Money

Deposit Money

Both operations must succeed together.

Why Use Transactions?

Transactions ensure:

• Data consistency
• Reliability
• Atomicity
• Integrity

Critical business systems rely on transactions.

Creating a Transaction

Example:

SqlTransaction transaction =
connection.BeginTransaction();

The transaction starts.

Transaction Example

Example:

try
{
    command1.ExecuteNonQuery();

    command2.ExecuteNonQuery();

    transaction.Commit();
}
catch
{
    transaction.Rollback();
}

Output:

Success

or

Rollback

This ensures safe data processing.

Commit Transaction

Example:

transaction.Commit();

Changes are permanently saved.

Rollback Transaction

Example:

transaction.Rollback();

All changes are reverted.

Essential for financial applications.

What are Parameterized Queries?

Parameterized Queries prevent SQL Injection attacks.

Bad Example:

string query =
"SELECT * FROM Users
WHERE Name='"
+ userInput + "'";

Danger:

SQL Injection

This approach should never be used.

SQL Injection Example

Malicious Input:

' OR 1=1 --

Result:

SELECT *
FROM Users
WHERE Name=''
OR 1=1

All records may be exposed.

Creating Parameterized Queries

Example:

string query =
@"SELECT *
FROM Students
WHERE Name=@Name";

Parameter:

command.Parameters.AddWithValue(
"@Name",
"Rahul");

Execute:

SqlDataReader reader =
command.ExecuteReader();

This approach is secure.

Benefits of Parameterized Queries

Benefits:

• Prevent SQL Injection
• Improve security
• Improve performance
• Simplify query handling

Parameterized queries should always be used.

Real-World Applications

Banking Application

Transactions

Account Management

Statements

E-Commerce Application

Orders

Payments

Inventory

Hospital Management System

Patients

Appointments

Medical Records

School Management System

Students

Attendance

Results

ADO.NET powers all these systems.

Common Mistakes to Avoid

Not Closing Connections

Causes resource leaks.

Using Dynamic SQL

Creates security vulnerabilities.

Ignoring Transactions

Can cause inconsistent data.

Leaving Readers Open

May block resources.

Hardcoding Connection Strings

Store configuration externally.

Best Practices for SQL Connection, Commands, DataReader, DataAdapter, DataSet, Transactions, and Parameterized Queries in C#

• Always use parameterized queries.
• Close connections promptly.
• Use using statements.
• Handle exceptions properly.
• Use transactions for critical operations.
• Choose DataReader for performance.
• Use DataSet for disconnected scenarios.
• Protect sensitive database information.

Interview Questions

What is SqlConnection?

SqlConnection establishes communication with SQL Server.

What is SqlCommand?

SqlCommand executes SQL statements.

What is SqlDataReader?

SqlDataReader retrieves records using connected architecture.

What is DataSet?

DataSet stores multiple tables in memory.

What is a Transaction?

A Transaction ensures multiple operations execute as a single unit.

Why use Parameterized Queries?

Parameterized Queries prevent SQL Injection attacks.

Key Takeaways

• SqlConnection connects applications to SQL Server.
• SqlCommand executes database commands.
• SqlDataReader provides fast data retrieval.
• DataAdapter transfers data between databases and memory.
• DataSet supports disconnected architecture.
• Transactions ensure data consistency.
• Parameterized Queries improve security.
• These components are fundamental to enterprise .NET development.

Frequently Asked Questions (FAQs)

What is SqlConnection in C#?

SqlConnection establishes a connection with SQL Server.

What is SqlCommand?

SqlCommand executes SQL queries and database commands.

What is SqlDataReader?

SqlDataReader retrieves records in a fast, forward-only, read-only manner.

What is DataSet?

DataSet is an in-memory collection of tables and relationships.

Why are Transactions important?

Transactions ensure data consistency and integrity.

Why are Parameterized Queries important?

They protect applications from SQL Injection attacks and improve security.

Internal Link

Click here for more free courses