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

SQL Server is a relational database management system developed by Microsoft. It stores, manages, and retrieves structured data using tables and SQL.

Main features:

• Stores data in databases, tables, rows, and columns.
• Uses SQL for data definition, manipulation, and querying.
• Supports transactions and maintains data consistency.
• Provides security through authentication, authorization, roles, and permissions.
• Supports backup, recovery, indexing, views, stored procedures, and triggers.
• Provides tools such as SQL Server Management Studio for database administration.

In application development, SQL Server acts as the persistent data layer. Applications such as ASP.NET Core Web APIs communicate with it to create, read, update, and delete data.

SQL commands are commonly divided into the following categories:

• DDL (Data Definition Language): Defines database structures. Examples include CREATE, ALTER, and DROP.
  • CREATE TABLE Students (Id INT, Name VARCHAR(50));
• DML (Data Manipulation Language): Modifies data. Examples include INSERT, UPDATE, and DELETE.
  • INSERT INTO Students VALUES (1, 'Anita');
• DQL (Data Query Language): Retrieves data using SELECT.
  • SELECT * FROM Students;
• DCL (Data Control Language): Controls permissions using GRANT and REVOKE.
• TCL (Transaction Control Language): Manages transactions using COMMIT, ROLLBACK, and SAVE TRANSACTION.

Together, these command categories allow developers to define structures, manipulate records, retrieve information, manage security, and control transactions.

A SQL Server table is a collection of related data organized into rows and columns.

• Columns: Represent attributes of an entity, such as StudentId, Name, or Email.
• Rows: Represent individual records in the table.
• Data types: Specify the kind of value a column can store, such as INT, DECIMAL, DATE, VARCHAR, or BIT.
• Primary key: Uniquely identifies each row.
• Indexes: Improve the speed of searches and sorting operations. A primary key normally creates a clustered index by default, depending on the table definition.

For example, a Students table may contain an integer identifier, a text name, and a date of birth. A well-designed table uses suitable data types and indexes while avoiding unnecessary duplication of data.

Constraints are rules applied to table columns to maintain data integrity and consistency.

• Primary key: Uniquely identifies every row and does not allow NULL values.
• Foreign key: Establishes a relationship between a column in one table and a primary or unique key in another table.
• UNIQUE: Ensures that all values in a column or group of columns are different.
• NOT NULL: Requires a column to contain a value.
• CHECK: Restricts values according to a condition, such as Age >= 18.
• DEFAULT: Supplies a value automatically when no value is provided.

For example, an order table can use a foreign key to ensure that every order belongs to an existing customer. Constraints prevent invalid, duplicate, or inconsistent data from entering the database.

A join combines rows from two or more tables using a related column.

• INNER JOIN: Returns only rows with matching values in both tables.
• LEFT JOIN: Returns every row from the left table and matching rows from the right table. Missing matches produce NULL values.
• RIGHT JOIN: Returns every row from the right table and matching rows from the left table.
• FULL OUTER JOIN: Returns all rows from both tables, matching them where possible.

Example:
SELECT Customers.Name, Orders.OrderDate FROM Customers INNER JOIN Orders ON Customers.Id = Orders.CustomerId;

The ON clause specifies the relationship between the tables. Joins are essential for retrieving normalized data stored across related tables.

REST, or Representational State Transfer, is an architectural style for designing networked applications.

Important principles include:

• Resource-based design: Data and functionality are represented as resources identified by URLs.
• Client-server separation: The client handles the user interface and the server manages data and business logic.
• Statelessness: Each request contains all information required to process it. The server does not depend on previous requests.
• Uniform interface: Resources are accessed using consistent URLs and HTTP methods.
• Cacheability: Responses may indicate whether they can be cached.
• Layered system: A client may communicate through intermediaries such as proxies or gateways.

A RESTful service commonly represents resources using JSON and uses HTTP methods such as GET, POST, PUT, and DELETE.

HTTP is an application-layer protocol used for communication between clients and servers on the web. It follows a request-response model.

An HTTP request contains:

• A method, such as GET or POST.
• A target URL or resource path.
• HTTP version.
• Headers containing metadata such as authorization and content type.
• An optional body containing submitted data.

An HTTP response contains:

• An HTTP version.
• A status code, such as 200 or 404.
• Response headers.
• An optional response body, commonly formatted as JSON.

For example, a client may send GET /api/products/4, and the server may return status 200 with a JSON representation of product 4.

HTTP methods express the intended operation on a resource.

• GET: Retrieves a resource or collection. It corresponds to the Read operation and should not modify server data.
• POST: Creates a new resource. It corresponds to the Create operation.
• PUT: Replaces an existing resource completely. It corresponds to the Update operation and is generally idempotent.
• PATCH: Applies a partial update to an existing resource.
• DELETE: Removes a resource. It corresponds to the Delete operation.

For a product resource, typical endpoints are:

• GET /api/products
• POST /api/products
• PUT /api/products/5
• PATCH /api/products/5
• DELETE /api/products/5

The method and URL together communicate the intended action clearly.

HTTP status codes communicate the result of a request.

• 1xx informational: The request is being processed.
• 2xx success: The request was successfully completed.
• 3xx redirection: Additional action is required, often related to caching or resource movement.
• 4xx client errors: The request is invalid or unauthorized.
• 5xx server errors: The server failed to fulfill a valid request.

Common API responses include:

• 200 OK: Successful retrieval or update.
• 201 Created: A resource was successfully created.
• 204 No Content: The operation succeeded without a response body.
• 400 Bad Request: Invalid input or malformed request.
• 401 Unauthorized: Authentication is required or invalid.
• 403 Forbidden: The client is authenticated but lacks permission.
• 404 Not Found: The requested resource does not exist.
• 500 Internal Server Error: An unexpected server-side failure occurred.

ASP.NET Core Web API is a framework for building HTTP-based services that can be consumed by web, mobile, desktop, and other client applications.

Advantages include:

• Cross-platform execution on Windows, Linux, and macOS.
• High performance and support for asynchronous programming.
• Built-in dependency injection.
• Middleware-based request processing.
• Attribute routing and controller support.
• Integration with authentication, authorization, logging, configuration, and Entity Framework Core.

Middleware consists of components arranged in a request pipeline. Each component can inspect or modify an HTTP request and response, perform an action, and optionally call the next component. Typical middleware handles exception processing, HTTPS redirection, authentication, authorization, routing, CORS, and static files.

The general steps are:

1. Create a Web API project using the .NET CLI, Visual Studio, or another development tool.
2. Configure services in Program.cs, including controllers, database contexts, authentication, and OpenAPI services.
3. Register application services and repositories using dependency injection.
4. Configure the HTTP request pipeline with HTTPS, routing, authentication, authorization, and controller mapping.
5. Create models, DTOs, controllers, and service classes.
6. Configure the database connection string and apply migrations when Entity Framework Core is used.
7. Run the application and test endpoints using Swagger UI, Postman, or another HTTP client.

Configuration should be separated from business logic, and sensitive values such as connection strings should be managed securely rather than hard-coded.

An API controller is a class that receives HTTP requests, validates input, invokes application logic, and returns HTTP responses.

Typical characteristics:

• It is declared with [ApiController].
• It usually inherits from ControllerBase.
• It contains action methods for resource operations.
• It receives dependencies through constructor injection.
• It uses routing attributes to map URLs to actions.
• It returns appropriate action results and status codes.

A controller should remain focused on HTTP concerns. Database access and complex business rules should generally be placed in services or repositories. This separation improves testability, maintainability, and reuse.

Convention-based routing uses a route pattern configured centrally, such as api/{controller}/{id?}. The framework derives route values from controller and action names.

Attribute routing defines routes directly on controllers and actions using attributes such as [Route], [HttpGet], [HttpPost], and [HttpDelete].

Example:
[Route("api/products")]
[HttpGet("{id:int}")]

Comparison:

• Convention routing provides centralized and consistent route conventions.
• Attribute routing gives precise control over individual endpoint URLs.
• Attribute routing makes HTTP verbs and route constraints visible beside the action.
• Modern REST APIs commonly use attribute routing because it clearly expresses resource-oriented endpoints.

Route constraints, such as {id:int}, help ensure that only valid route values match an action.

A Data Transfer Object is a class designed specifically for transferring data between an API and its clients.

Reasons to use DTOs:

• Prevent internal database fields from being exposed.
• Avoid returning sensitive properties such as passwords or internal audit data.
• Provide different shapes for input and output models.
• Reduce unnecessary data sent over the network.
• Prevent over-posting, where clients submit properties they should not control.
• Decouple the public API contract from the database schema.
• Support validation rules appropriate to a request.

For example, a CreateUserDto may contain a name and email, while a UserResponseDto may contain an identifier, name, and creation date but never a password hash.

CRUD operations can be represented using controller actions and HTTP methods:

• Create: POST /api/products accepts a creation DTO, validates it, saves a new product, and commonly returns 201 Created.
• Read: GET /api/products returns a collection, while GET /api/products/{id} returns one product or 404 Not Found.
• Update: PUT /api/products/{id} accepts an update DTO, changes the product, and returns 204 No Content or the updated representation.
• Delete: DELETE /api/products/{id} removes the product and commonly returns 204 No Content.

A typical implementation uses a controller, service layer, repository or Entity Framework Core context, DTO mapping, model validation, and appropriate error handling. The API should also validate resource identifiers and prevent unauthorized modifications.

Model binding maps values from route parameters, query strings, headers, and request bodies to action parameters or model objects. For JSON request bodies, ASP.NET Core uses input formatters to deserialize the data.

Validation checks whether the bound model satisfies declared rules. Common data annotation attributes include:

• [Required]
• [StringLength]
• [Range]
• [EmailAddress]

With [ApiController], invalid model state normally causes the framework to return 400 Bad Request automatically, together with validation details. An API should return a clear and consistent error format, avoid accepting unexpected fields, and validate both syntactic input and business rules before modifying database data.

Dependency injection is a design technique in which a class receives the objects it depends on from an external container instead of creating them directly.

In a Web API, a controller may receive an IProductService through its constructor. The service is registered in the dependency injection container with a lifetime such as:

• Transient: A new instance is created each time it is requested.
• Scoped: One instance is created per HTTP request.
• Singleton: One instance is shared for the application's lifetime.

Benefits:

• Reduces coupling between controllers and implementations.
• Makes unit testing easier through mock services.
• Centralizes object creation and configuration.
• Supports clean separation of responsibilities.
• Allows implementations to be changed without modifying controllers.

OpenAPI is a machine-readable specification for describing HTTP APIs. Swagger is a collection of tools commonly used to generate, display, and interact with OpenAPI documents.

Swagger/OpenAPI can describe:

• Available endpoints and routes.
• HTTP methods and parameters.
• Request and response models.
• Data types and validation requirements.
• Authentication schemes.
• Possible status codes.

Swagger UI presents this information in an interactive browser page. Developers can inspect endpoints and send test requests without writing a separate client. The generated specification can also be used to generate client libraries and server stubs. Documentation should accurately reflect the public API contract and should avoid exposing sensitive implementation details.

The following procedure can be used:

1. Start the Web API and note its base URL and port.
2. Create a Postman request with the required HTTP method and endpoint URL.
3. Add headers such as Content-Type: application/json and an authorization header when required.
4. For POST, PUT, or PATCH, select a raw JSON body and provide valid request data.
5. Send the request and inspect the status code, response headers, and response body.
6. Test successful cases and failure cases, including invalid identifiers, missing fields, unauthorized access, and duplicate values.
7. Verify that database changes made by create, update, and delete requests are correct.
8. Organize requests into collections and use environment variables for reusable values such as the server URL or token.

Postman testing checks both functional behavior and whether the API follows its documented contract.

Entity Framework Core is an object-relational mapper that allows .NET applications to work with relational databases using .NET classes and LINQ.

Typical process:

• Define entity classes representing database records.
• Create a class derived from DbContext.
• Add DbSet<TEntity> properties for the required tables.
• Store the SQL Server connection string in configuration.
• Register the context with SQL Server support in dependency injection.
• Create and apply migrations to synchronize the model and database.
• Inject the context into a service or repository.
• Use asynchronous operations such as ToListAsync, FindAsync, AddAsync, and SaveChangesAsync.

The API should manage database resources correctly, use parameterized queries through EF Core, validate input, and avoid exposing database entities directly when DTOs are more appropriate.