UNIT- 3

What is [Link]?
[Link] is a part of the .NET Framework used to connect applications with databases. It
is mostly used with Microsoft SQL Server, but can also work with other databases.

It helps your program to:

• Get data (retrieve)

• Add new data (insert)

• Modify existing data (update)

• Delete data

Features -
1) Easy-to-Use Classes
[Link] provides ready-made classes (like tools) so developers don’t have to write
everything from scratch.

Example:

• SqlConnection

• SqlCommand

2) Disconnected Data (Very Important Concept)

You don’t need to stay connected to the database all the time.
How it works:
1. Connect to database
2. Copy data into memory
3. Close connection
4. Work on data offline
5. Reconnect only to save changes
Saves time
Improves performance
 3) Works with Many Data Sources

[Link] can connect to different types of data sources like:

• SQL Server

• Oracle
• MySQL

• XML files

So, it is flexible and not limited to one database.

4) Data Providers (Bridge Concept)

Data Providers act like a bridge between your application and the database.

They include:

• Connection

• Command

• DataReader

• DataAdapter

Main Components of [Link]

Component What it Does (Simple)

SqlConnection Connects your app to database

SqlCommand Executes SQL queries

SqlDataReader Reads data quickly (forward only)

SqlDataAdapter Transfers data between DB and memory

DataSet Stores data temporarily in memory

Advantages of [Link]
1) Fast Performance

[Link] is designed to work quickly with data. Uses optimized classes like
SqlDataReader for quick reading of data.

2) Secure Data Handling

[Link] provides safe ways to work with data.

How it is secure:
• Uses authentication methods (like Windows Authentication)
• Controls access to database

3) Works with Many Databases

[Link] is not limited to just one database.

It can connect to:
• Microsoft SQL Server
• Oracle
• MySQL
• XML files
This is possible because of data providers (bridge between app and database).

4) Easy to Use with .NET Applications

[Link] is part of the .NET Framework, so it works smoothly with C# and other .NET
languages.
Why it is easy:
• Ready-made classes (SqlConnection, SqlCommand)
• Simple syntax
• Well integrated with Visual Studio

5) Supports Disconnected Architecture

This is one of the most important features. You can work without staying connected to the
database
Steps:
1. Connect to database
2. Load data into memory (DataSet)
3. Disconnect
4. Work on data
5. Reconnect to save changes
 Evolution of [Link]
NET Versions (Easy Summary)
• .NET 1.0 → Basic database connection tools ([Link] started)
• .NET 2.0 → Work with objects (Entity Framework)
• .NET 3.5 → Write queries in C# (LINQ)
• .NET 4.0+ → Faster + multitasking + better performance

.NET 1.0
.NET 1.0 → Start (basic tools to connect database)

• First version of [Link]

• Introduced basic tools like:
o DataSet
o DataReader
o Connection

.NET 2.0
• Introduced Entity Framework (EF)
• EF helps developers work with objects instead of writing SQL queries
.NET 2.0 → Work with objects (Entity Framework)
Easy meaning:
Instead of writing SQL like:
SELECT * FROM Students
You can use C# objects like:
studentsList

. NET 3.5
• Introduced LINQ (Language Integrated Query)
• LINQ lets you write queries using C# syntax
• .NET 3.5 → Write queries in C# (LINQ)

Easy meaning:
You can write queries directly in C# like:

var data = from s in students

where [Link] > 18
select s;

No need to switch between SQL and C#

Cleaner and easier code
.NET 4.0 and Later

• Faster and better performance

• Added async (non-blocking) operations
• Better support for large applications

.NET 4.0+ → Faster + multitasking + better performance

Connecting to Database in [Link]

*What is Database Connection?

Connecting means linking your application to a database.
After connecting, you can:
• Insert data
• Update data
• Delete data
• Retrieve data

*SqlConnection Class
SqlConnection is used to connect your application to Microsoft SQL Server.
Found in:
[Link]
It helps to:
• Open connection
• Close connection
• Communicate with database
*Steps to Connect to Database
Step 1: Import Namespace

using [Link];
Allows use of database classes.

Step 2: Create Connection String

string con = "Data Source=.;Initial Catalog=StudentDB;Integrated Security=True";

Meaning:
• Data Source → Server name
• Initial Catalog → Database name
• Integrated Security → Uses Windows login

Step 3: Create Connection Object

SqlConnection conn = new SqlConnection(con);

Creates connection using the string.

Step 4: Open Connection

[Link]();
Starts connection with database.

Step 5: Close Connection

[Link]();
Always close connection to:
• Save resources
• Improve performance
 Performing Insert, Update and Delete
Operations

*Introduction to SQL
SQL (Structured Query Language) is a standard language used to store, retrieve, update,
and delete data in a database.
SQL works with Relational Database Management Systems (RDBMS) such as:
• Microsoft SQL Server
• MySQL
• Oracle

*Basic SQL Operations

Types of SQL Commands

Data Definition Language- create(Create table or database), drop(Delete

DDL
table), alter(Modify table structure), truncate(Remove all records)

DML Data Manipulation Language- insert, delete, update

DQL Data Query Language- select(Fetch data)

DCL Data Control Language-drant, revoke

Transaction Control Language-rollback(Undo changes),

TCL
savepoint(Temporary save point), commit(Save changes)

We mainly use:

• CREATE
• INSERT
• SELECT
• UPDATE
• DELETE
1. CREATE Statement
The CREATE statement is used to create database objects such as tables, databases, views,
etc.

Syntax-

CREATE TABLE tablename

column1 datatype,

column2 datatype,

column3 datatype
);

Example

CREATE TABLE Student

Id INT PRIMARY KEY,

Name VARCHAR(50),

Age INT,
City VARCHAR(50)

);

2. INSERT Statement
• The Insert operation is used to add new records to a database table.
• It uses the INSERT INTO SQL statement.
• Values are passed using parameters.
• It does not return any data.
Syntax

INSERT INTO tablename

VALUES (value1, value2, value3);

Example-
INSERT INTO Student
VALUES (1,'Anjali',20,'Pune');
*Insert Multiple Records-

INSERT INTO Student VALUES (2,'Rahul',21,'Nagpur');

INSERT INTO Student VALUES (3,'Priya',19,'Mumbai');

3. SELECT Statement
The SELECT statement is used to retrieve data from a database table.

Syntax

SELECT * FROM tablename;

Example
SELECT * FROM Student;

4. UPDATE Statement
• The Update operation is used to modify existing records in a table.
• It uses the UPDATE SQL statement
• Requires a WHERE condition to update specific records.
• The Update operation is used to change the existing data in a database table using an
SQL UPDATE command.

Syntax
UPDATE tablename

SET column_name = value

WHERE condition;

Example

UPDATE Student

SET Age = 22
WHERE Id = 1;
5. DELETE Statement
• The Delete operation is used to remove records from a table.
• It uses the DELETE SQL statement.
• WHERE condition specifies which record to delete.
• The Delete operation removes unwanted data from a database table using an SQL
DELETE command.

Syntax

DELETE FROM tablename

WHERE condition;

Example

DELETE FROM Student

WHERE Id = 3;