MS .
NET Notes Day-9 (22 December, 2025)
Adding Service and DAO layer to today's project
[Link]
What is [Link]?
[Link] is the official .NET data provider for SQL Server used in [Link].
Purpose
Connect to SQL Server
Execute SQL commands
Retrieve and manipulate data
Namespace
using [Link];
Connection Object (SqlConnection)
Purpose
Establishes a connection with SQL Server
Example
SqlConnection con = new
SqlConnection("Server=.;Database=StudentDB;Trusted_Connection=True;");
[Link]();
// Database operations
[Link]();
Key Points
Represents a physical connection
Must be opened before executing commands
Should always be closed or disposed
Command Object (SqlCommand)
Purpose
Executes SQL statements or stored procedures
Example
SqlCommand cmd = new SqlCommand("SELECT * FROM Students", con);
Types of Commands
Text (SQL query)
Stored Procedure
TableDirect (rare)
DataReader (SqlDataReader)
Purpose
Reads data forward-only, read-only
Fastest data retrieval mechanism
Example
SqlDataReader dr = [Link]();
while ([Link]())
{
[Link](dr["Name"]);
}
Characteristics
Connected architecture
Cannot scroll backward
One row at a time
DataAdapter (SqlDataAdapter)
Purpose
Acts as a bridge between database and DataSet
Example
SqlDataAdapter da = new SqlDataAdapter( "SELECT * FROM Students", con);
Responsibilities
Fill DataSet
Update database changes
DataSet
Purpose
In-memory representation of data
Can hold multiple tables
Example
DataSet ds = new DataSet();
[Link](ds);
Characteristics
Disconnected architecture
Can contain relationships
Works offline
DataTable
Purpose
Represents a single table in memory
Example
DataTable dt = [Link][0];
Comparisaon
Feature DataReader DataTable
Speed Fast Moderate
Connection Connected Disconnected
Editing No Yes
Implementing StudentDAO in Day-8 Project
Create Database (LocalDB)
Using SQL Server Object Explorer
(localdb)\MSSQLLocalDB
Create Database: StudentDB
Table
CREATE TABLE Students
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Age INT,
Fees FLOAT
)
Add Connection String ([Link])
"ConnectionStrings": {
"DefaultConnection": "Server=
(localdb)\\MSSQLLocalDB;Database=YOURDB;Trusted_Connection=True;TrustServerCertifi
cate=True"
}
Register Dependencies ([Link])
[Link]<IStudentDao, StudentDao>();
[Link]<IStudentService, StudentService>();
IStudentDAO (Interface)
public interface IStudentDAO
{
public List<Student> GetAll();
public void Add(Student student);
public void Delete(int id);
public Student GetById(int id);
public void Update(Student student);
}
StudentDAO (Class)
public class StudentDAO : IStudentDAO
{
private readonly string _connStr;
public StudentDAO(IConfiguration configuration)
{
_connStr = [Link]("DefaultConnection");
}
public List<Student> GetAll()
{
List<Student> students = new();
SqlConnection con = new(_connStr);
SqlCommand cmd = new("SELECT * FROM Students", con);
[Link]();
SqlDataReader dr = [Link]();
while ([Link]())
{
[Link](new Student
{
Id = Convert.ToInt32(dr["Id"]),
Name = dr["Name"].ToString(),
Age = Convert.ToInt32(dr["Age"]),
Fees = [Link](dr["Fees"])
});
}
return students;
}
public void Add(Student student)
{
SqlConnection con = new(_connStr);
SqlCommand cmd = new("INSERT INTO Students VALUES (@id,@name,@age,@fees)",
con);
[Link]("@id", [Link]);
[Link]("@name", [Link]);
[Link]("@age", [Link]);
[Link]("@fees", [Link]);
[Link]();
[Link]();
}
public void Delete(int id)
{
using SqlConnection con = new(_connStr);
using SqlCommand cmd = new( "DELETE FROM Students WHERE Id = @id", con);
[Link]("@id", id);
[Link]();
[Link]();
}
public Student GetById(int id)
{
Student student = null;
using SqlConnection con = new(_connStr);
using SqlCommand cmd = new("SELECT * FROM Students WHERE Id=@id", con);
[Link]("@id", id);
[Link]();
SqlDataReader dr = [Link]();
if ([Link]())
{
student = new Student
{
Id = Convert.ToInt32(dr["Id"]),
Name = dr["Name"].ToString(),
Age = Convert.ToInt32(dr["Age"]),
Fees = [Link](dr["Fees"])
};
}
return student;
}
public void Update(Student student)
{
SqlConnection con = new(_connStr);
SqlCommand cmd = new(@"UPDATE Students SET Name = @name, Age = @age, Fees
= @fees WHERE Id = @id", con);
[Link]("@id", [Link]);
[Link]("@name", [Link]);
[Link]("@age", [Link]);
[Link]("@fees", [Link]);
[Link]();
[Link]();
}
}