C# with Databases - Textnotes

C# with Databases

C# provides multiple ways to interact with databases, from low-level ADO.NET to high-level Entity Framework (EF) Core.

1. ADO.NET Overview

  1. ADO.NET is a set of classes for interacting with SQL Server and other databases.
  2. Key components:
  3. SqlConnection → Connects to database
  4. SqlCommand → Executes queries or stored procedures
  5. SqlDataReader → Reads data in forward-only mode
  6. SqlDataAdapter → Fills DataSet/DataTable

2. SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter

2.1 SqlConnection


using System.Data.SqlClient;

string connectionString = @"Server=.;Database=TestDB;Trusted_Connection=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
Console.WriteLine("Database connected!");
}

2.2 SqlCommand


string query = "SELECT * FROM Students";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["Name"] + " - " + reader["Age"]);
}
}
}

2.3 SqlDataAdapter


using System.Data;

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Students", conn);
DataTable dt = new DataTable();
adapter.Fill(dt);

foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["Name"] + " - " + row["Age"]);
}

3. Parameters and Stored Procedures

Using parameters prevents SQL Injection.


string query = "INSERT INTO Students (Name, Age) VALUES (@Name, @Age)";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@Name", "John");
cmd.Parameters.AddWithValue("@Age", 25);
cmd.ExecuteNonQuery();
}

Stored Procedure


CREATE PROCEDURE GetStudentById
@Id INT
AS
BEGIN
SELECT * FROM Students WHERE Id = @Id
END

using (SqlCommand cmd = new SqlCommand("GetStudentById", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", 1);

using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
Console.WriteLine(reader["Name"] + " - " + reader["Age"]);
}
}

4. Transactions

Ensure all-or-nothing execution of multiple commands.


using (SqlTransaction transaction = conn.BeginTransaction())
{
try
{
SqlCommand cmd1 = new SqlCommand("INSERT INTO Students(Name, Age) VALUES('Alice', 22)", conn, transaction);
SqlCommand cmd2 = new SqlCommand("INSERT INTO Students(Name, Age) VALUES('Bob', 23)", conn, transaction);

cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();

transaction.Commit();
Console.WriteLine("Transaction committed!");
}
catch
{
transaction.Rollback();
Console.WriteLine("Transaction rolled back!");
}
}

5. Entity Framework Core

  1. EF Core is an ORM that maps C# classes to database tables.
  2. Install via NuGet:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

5.1 DbContext & Models


using Microsoft.EntityFrameworkCore;

public class Student
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}

public class SchoolContext : DbContext
{
public DbSet<Student> Students { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlServer(@"Server=.;Database=TestDB;Trusted_Connection=True;");
}

6. CRUD Operations with EF Core

6.1 Create


using (var context = new SchoolContext())
{
var student = new Student { Name = "John", Age = 25 };
context.Students.Add(student);
context.SaveChanges();
}

6.2 Read


using (var context = new SchoolContext())
{
var students = context.Students.ToList();
foreach (var s in students)
Console.WriteLine($"{s.Name} - {s.Age}");
}

6.3 Update


using (var context = new SchoolContext())
{
var student = context.Students.First();
student.Age = 26;
context.SaveChanges();
}

6.4 Delete


using (var context = new SchoolContext())
{
var student = context.Students.First();
context.Students.Remove(student);
context.SaveChanges();
}

7. LINQ with EF Core

EF Core supports LINQ queries to retrieve data.


using (var context = new SchoolContext())
{
var teenStudents = context.Students
.Where(s => s.Age >= 13 && s.Age <= 19)
.OrderBy(s => s.Name)
.ToList();

foreach (var s in teenStudents)
Console.WriteLine($"{s.Name} - {s.Age}");
}

Summary of Chapter 13:

  1. ADO.NET: Low-level database access using SqlConnection, SqlCommand, SqlDataReader.
  2. Parameters & Stored Procedures: Prevent SQL injection and reuse logic.
  3. Transactions: Ensure atomic operations.
  4. EF Core: High-level ORM for CRUD operations and LINQ queries.
  5. LINQ with EF Core: Write readable and type-safe database queries.