Back to Subject

Unit 6: SQL Server and ASP.NET Core Web API

CSE253 — .Net Programming 12 min read

I. Orientation: Data and Service Communication

This unit connects relational data management in Microsoft SQL Server with HTTP-based services built using ASP.NET Core Web API. SQL Server stores structured records reliably, while a Web API exposes application data and operations to clients such as browsers, mobile applications, and Postman (Microsoft's API testing tool).

  • Defining principle: A database persists normalized data; an API provides controlled access to that data through request and response messages.
  • Core convention: SQL works with tables, rows, columns, and relationships; REST works with resources, URLs, representations, and HTTP methods.
  • Common data format: JSON is widely used for API bodies, for example { "id": 7, "name": "Asha" }.
  • Separation of concerns: Controllers handle HTTP concerns, services contain application logic, and data-access code communicates with SQL Server.
  • Important assumption: Client input is untrusted and must be validated before it is stored or used.

II. Introduction to SQL Server: Relational Storage

SQL Server is a relational database management system (RDBMS) that stores related data in tables and processes requests through Transact-SQL (T-SQL). Its engine supports transactions, security, indexing, backup, and concurrent access.

A. Introduction to SQL Server

This topic establishes SQL Server as the persistent data layer for applications.

  • Database structure: A database contains schemas, tables, views, stored procedures, functions, and other objects; dbo.Students identifies the Students table in the dbo schema.
  • Relational model: Data is represented as rows and columns, with relationships established through keys rather than duplicated text.
  • Transactions: A transaction groups operations so they satisfy atomicity, consistency, isolation, and durability (ACID); an account transfer either completes both updates or neither.
  • Access mechanism: Applications commonly connect through a connection string containing the server, database, authentication mode, and encryption settings.

III. SQL Commands: Working with Data

SQL commands define database objects, retrieve records, change data, and control permissions. Their result is determined by the selected database, schema, predicates, and transaction context.

A. SQL Commands

SQL commands provide the executable language for creating and manipulating relational data.

  • DDL, or Data Definition Language: CREATE TABLE, ALTER TABLE, and DROP TABLE define or change structures.
  • DML, or Data Manipulation Language: INSERT, UPDATE, and DELETE modify rows; SELECT reads them.
  • DCL, or Data Control Language: GRANT, DENY, and REVOKE manage permissions for users and roles.
  • Transaction control: BEGIN TRANSACTION, COMMIT, and ROLLBACK control a unit of work.
  • Worked example:
    SQL
      SELECT Id, Name
  FROM dbo.Students
  WHERE Department = 'CSE'
  ORDER BY Name;

    WHERE filters rows, and ORDER BY sorts the result without changing stored data.

IV. SQL Server Tables: Organized Records

A table is a named relational object whose columns describe attributes and whose rows represent individual records. Good table design makes valid data easy to store and retrieve.

A. SQL Server Tables

Tables define the physical and logical shape of application data.

  • Column definition: Each column has a name and data type, such as INT, NVARCHAR(100), DATE, DECIMAL(10,2), or BIT.
  • Nullability: NULL means missing or unknown, not an empty string or zero; NOT NULL requires a value.
  • Identity values: IDENTITY(1,1) can generate integer keys beginning at 1 and increasing by 1.
  • Normalization: Separating Departments from Students avoids repeating department details in every student row.
  • Performance: An index can accelerate searches such as WHERE Email = ..., but indexes consume storage and slow writes.

V. Constraints: Enforcing Validity

Constraints are database rules that reject invalid states at the point of storage. They complement, but do not replace, validation in the API.

A. Constraints

Constraints express integrity requirements directly in SQL Server.

  • Primary key: PRIMARY KEY (Id) uniquely identifies each row and does not allow NULL.
  • Foreign key: FOREIGN KEY (DepartmentId) REFERENCES Departments(Id) ensures that a referenced department exists.
  • Unique constraint: UNIQUE (Email) prevents two rows from using the same email address.
  • Check constraint: CHECK (Age >= 18) rejects values outside the stated condition.
  • Default constraint: DEFAULT GETDATE() supplies a value when an insert omits the column.
  • Integrity effect: A foreign-key violation fails an insert or update instead of silently creating an orphaned record.

VI. Joins: Combining Related Data

A join combines rows from tables using a related condition, usually a primary-key and foreign-key pair. The join type determines which unmatched rows remain in the result.

A. Joins

Joins retrieve related information without physically merging tables.

  • Inner join: Returns only matching rows:
    SQL
      SELECT s.Name, d.Name AS DepartmentName
  FROM Students AS s
  INNER JOIN Departments AS d ON s.DepartmentId = d.Id;
  • Left join: Returns every row from the left table and NULL for missing right-side data; it can list all departments including those with no students.
  • Right and full joins: A right join preserves the right table; a full join preserves unmatched rows from both sides and is less common in routine API queries.
  • Join condition: ON s.DepartmentId = d.Id defines matching; omitting it can produce a Cartesian product with unexpectedly many rows.
  • Efficiency: Indexing join columns and selecting only required columns reduces database work and response size.

VII. Introduction to REST Architecture: Resource-Oriented Services

REST (Representational State Transfer) is an architectural style in which clients manipulate representations of resources through a uniform interface. A student resource might be identified by /api/students/7.

A. Introduction to REST Architecture

REST organizes an API around resources rather than procedure names.

  • Client-server separation: The client handles presentation, while the server manages data and rules.
  • Statelessness: Each request contains the information needed to process it; the server does not depend on hidden client session state between requests.
  • Uniform interface: Standard methods and resource URLs provide predictable operations.
  • Representations: A stored student object may be represented as JSON, without exposing the database table directly.
  • Cacheability and layering: Responses may be cached when permitted, and intermediaries such as gateways can sit between client and server.

VIII. HTTP Protocol and HTTP Methods: Request and Response Rules

HTTP (Hypertext Transfer Protocol) carries a request from a client to a server and a response back. A request includes a method, target URL, headers, and sometimes a body.

A. HTTP Protocol and HTTP Methods

HTTP methods communicate the intended operation on a resource.

  • GET: Reads data and should not change server state; GET /api/students/7 requests one student.
  • POST: Creates a subordinate resource, commonly receiving JSON in the body and returning 201 Created.
  • PUT: Replaces a resource at a known URL and is generally idempotent, meaning repeating the same request has the same intended result.
  • PATCH: Applies a partial modification, such as changing only email.
  • DELETE: Removes a resource and commonly returns 204 No Content on success.
  • Headers and body: Content-Type: application/json describes a JSON request body; Accept: application/json states a preferred response format.

IX. HTTP Status Codes: Communicating Outcomes

An HTTP status code gives the client a standardized result category. Correct codes allow clients to distinguish success, invalid input, missing resources, and server failures.

A. HTTP Status Codes

Status codes are grouped by their first digit and should match the actual outcome.

  • 2xx success: 200 OK returns a representation, 201 Created confirms creation, and 204 No Content confirms success without a body.
  • 3xx redirection: 304 Not Modified can support cache validation; it does not mean that an operation succeeded or failed in the usual API sense.
  • 4xx client error: 400 Bad Request indicates malformed input, 401 Unauthorized indicates missing or invalid authentication, 403 Forbidden indicates insufficient permission, and 404 Not Found indicates no matching resource.
  • Conflict and validation: 409 Conflict suits a duplicate or state conflict; 422 Unprocessable Content may describe semantically invalid data where that convention is adopted.
  • 5xx server error: 500 Internal Server Error indicates an unexpected server failure; detailed exception information should not be exposed in production.

X. Introduction to ASP.NET Core Web API: The Application Framework

ASP.NET Core Web API is a cross-platform framework for building HTTP services on .NET. It uses middleware, dependency injection, model binding, routing, filters, and serializers to turn requests into application responses.

A. Introduction to ASP.NET Core Web API

The framework supplies the pipeline in which API endpoints execute.

  • Pipeline: A request passes through middleware such as exception handling, HTTPS redirection, routing, authentication, and authorization.
  • Model binding: Route, query-string, and body values are converted into C# parameters or objects.
  • Serialization: ASP.NET Core commonly serializes C# objects to JSON using System.Text.Json.
  • Dependency injection: Services are registered and injected into controllers, reducing direct construction and improving testability.

XI. Building and Configuring Web APIs: From Project to Endpoint

Building a Web API means defining the project, registering framework services, configuring middleware, and mapping controllers. Configuration is normally centralized in Program.cs.

A. Building and Configuring Web APIs

Configuration determines which services and request-processing stages are available.

  • Service registration:
    CSHARP
      builder.Services.AddControllers();
  builder.Services.AddEndpointsApiExplorer();
  builder.Services.AddSwaggerGen();
  • Middleware order:
    CSHARP
      app.UseHttpsRedirection();
  app.UseAuthorization();
  app.MapControllers();

    MapControllers exposes attribute-routed controller actions.
  • Configuration sources: appsettings.json, environment variables, and user secrets can hold settings; passwords should not be committed to source control.
  • Environment behavior: Swagger and detailed diagnostics are commonly enabled in development, while production should use controlled logging and error responses.

XII. API Controllers: HTTP Entry Points

An API controller is a C# class that groups related endpoints and converts HTTP requests into application operations. It should remain focused on transport concerns.

A. API Controllers

Controllers receive bound input, invoke dependencies, and return action results.

  • Base type: Inheriting from ControllerBase supplies API-oriented helpers such as Ok, NotFound, and CreatedAtAction.
  • Controller marker: [ApiController] enables automatic model validation responses and more consistent binding behavior.
  • Dependency injection: A constructor can receive StudentService or a database context rather than creating it internally.
  • Action result: ActionResult<T> permits either a typed success body or an error result, such as ActionResult<StudentDto>.
  • Boundary responsibility: Controllers should translate an absent database record into 404, not return an unexplained null with 200.

XIII. Routing Using Convention and Attribute Routing: Selecting Actions

Routing maps an HTTP method and URL to a controller action. It must produce stable, unambiguous resource addresses.

A. Routing using Convention and Attribute Routing

The two routing approaches differ in where URL patterns are declared.

  • Convention routing: A conventional pattern such as {controller}/{action}/{id?} derives routes from controller and action names; it is concise but less explicit for REST APIs.
  • Attribute routing: Attributes state the contract directly:
    CSHARP
      [Route("api/[controller]")]
  [HttpGet("{id:int}")]
  public ActionResult<StudentDto> Get(int id) { ... }
  • Constraints: {id:int} prevents a nonnumeric segment from selecting this action.
  • Route values versus query values: /api/students/7 uses a route value; /api/students?department=CSE uses a query value for filtering.

XIV. Data Transfer Objects (DTOs): Controlled API Shapes

A DTO is a purpose-specific object used to carry data across the API boundary. It prevents database entities from becoming an accidental public contract.

A. Data Transfer Objects (DTOs)

DTOs control exposed fields and separate input models from output models.

  • Response DTO: StudentDto might expose Id, Name, and DepartmentName while hiding an internal audit field.
  • Create DTO: CreateStudentDto can require Name and DepartmentId without accepting a client-supplied Id.
  • Update DTO: UpdateStudentDto can define exactly which fields a PUT operation replaces.
  • Validation: [Required], [EmailAddress], and [StringLength(100)] provide declarative checks before service logic runs.
  • Mapping: Explicit mapping, such as new StudentDto { Id = entity.Id, Name = entity.Name }, makes exposed data intentional.

XV. CRUD Operations Using Web API: The Resource Lifecycle

CRUD means Create, Read, Update, and Delete. A conventional student API maps each operation to an HTTP method and returns an appropriate status code.

A. CRUD Operations using Web API

CRUD endpoints implement the ordinary lifecycle of a resource.

  • Create: POST /api/students validates a CreateStudentDto, inserts a row, and returns 201 Created with a Location header.
  • Read: GET /api/students returns a collection; GET /api/students/7 returns one DTO or 404.
  • Update: PUT /api/students/7 verifies existence, validates the replacement representation, and returns 204 or the updated representation.
  • Delete: DELETE /api/students/7 removes the row and returns 204; deleting an absent resource commonly returns 404.
  • Database safety: Parameterized queries or Entity Framework Core prevent SQL injection; transactions protect multi-step changes.

XVI. Swagger/OpenAPI Documentation: A Machine-Readable Contract

OpenAPI is a standard description of an HTTP API, and Swagger tools generate interactive documentation from that description. The document records paths, methods, parameters, schemas, and responses.

A. Swagger/OpenAPI Documentation

Swagger makes endpoint contracts visible and directly testable in a browser.

  • Generated description: AddSwaggerGen discovers controller actions and produces an OpenAPI document.
  • Interactive UI: Swagger UI displays routes such as GET /api/students/{id} and provides an “Execute” operation.
  • Schema value: DTO properties, required fields, and response types help consumers construct valid requests.
  • Accuracy requirement: Documentation must reflect actual status codes, authentication requirements, and request bodies; a misleading schema is a maintenance defect.

XVII. Testing Web APIs using Postman: Verifying Behavior

Postman sends HTTP requests and displays status codes, headers, timing, and response bodies. It verifies the running API from the perspective of an external client.

A. Testing Web APIs using Postman

Postman testing checks both the happy path and defined failure behavior.

  • Request setup: Select POST, enter https://localhost:5001/api/students, choose raw JSON, and send Content-Type: application/json.
  • Body example:
    JSON
      { "name": "Asha", "departmentId": 2 }
  • Assertions: Confirm 201 for valid creation, a Location header, correct JSON fields, and persistence through a following GET.
  • Negative cases: Send a missing name, an unknown department ID, an invalid route such as /api/students/abc, and a nonexistent ID to verify 400, 404, or validation behavior.
  • Environment variables: Variables such as {{baseUrl}} and {{studentId}} keep requests reusable across development and testing environments.
  • Authentication check: When security is enabled, verify that missing, invalid, and authorized credentials produce the intended 401 or 403 responses.
Previous
Unit 5