how to Insert data into header and line table with PK/FK relation at a time using .NET core API, Oracle21c (XE), postman

Project Structure:

Project Depedency:

Database Table: 

CREATE TABLE ItemHeader (
    HeaderId NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    HeaderName VARCHAR2(100),
    CreatedDate DATE DEFAULT SYSDATE
);

CREATE TABLE ItemLine (
    LineId NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Auto-increment
    HeaderId NUMBER,
    LineDescription VARCHAR2(200),
    Quantity NUMBER,
    FOREIGN KEY (HeaderId) REFERENCES ItemHeader(HeaderId)
);

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace itemdetails.Models
{
    public class ItemHeader
    {
        [Key]
        public int HEADERID { get; set; } // Primary Key
        public string HEADERNAME { get; set; }
        public DateTime CREATEDDATE { get; set; }
        public ICollection<ItemLine> ItemLines { get; set; } // Navigation property
    }
}

using Microsoft.AspNetCore.Mvc.ModelBinding.Validation;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json.Serialization;

namespace itemdetails.Models
{
    public class ItemLine
    {
        [Key]
        public int LINEID { get; set; } // Primary Key
        public int HEADERID { get; set; } // Foreign Key

        public string LINEDESCRIPTION { get; set; }
        public int QUANTITY { get; set; }

        [JsonIgnore] // Ignore during JSON deserialization
        public ItemHeader ItemHeader { get; set; } // Navigation property
    }
}

namespace itemdetails.Models
{
    public class ItemHeaderDto
    {
        public string HeaderName { get; set; }
        public DateTime CreatedDate { get; set; }
        public List<ItemLineDto> ItemLines { get; set; }
    }
}

namespace itemdetails.Models
{
    public class ItemLineDto
    {
        public string LineDescription { get; set; }
        public int Quantity { get; set; }
    }
}

using itemdetails.Models;

namespace itemdetails.Service
{
    public interface IItemService
    {
        Task<ItemHeader> CreateItemHeaderAsync(ItemHeader itemHeader);
        Task<ItemLine> CreateItemLineAsync(ItemLine itemLine);
        Task<ItemHeader> CreateItemHeaderWithLinesAsync (ItemHeader itemHeader);
    }
}

using itemdetails.Models;
using itemdetails.Repository;

namespace itemdetails.Service
{
    public class ItemService : IItemService
    {
        private readonly IItemRepository _itemRepository;
        public ItemService(IItemRepository itemRepository)
        {
            _itemRepository = itemRepository;
        }
        public Task<ItemHeader> CreateItemHeaderAsync(ItemHeader itemHeader)
        {
            throw new NotImplementedException();
        }      

        public Task<ItemLine> CreateItemLineAsync(ItemLine itemLine)
        {
            throw new NotImplementedException();
        }
        public Task<ItemHeader> CreateItemHeaderWithLinesAsync(ItemHeader itemHeader)
        {
            return _itemRepository.AddItemHeaderWithLinesAsync(itemHeader);
        }
    }
}

using itemdetails.Models;

namespace itemdetails.Repository
{
    public interface IItemRepository
    {
        Task<ItemHeader> AddItemHeaderAsync(ItemHeader itemHeader);    
        Task<ItemLine> AddItemLineAsync(ItemLine itemLine);
        Task<ItemHeader> AddItemHeaderWithLinesAsync(ItemHeader itemHeader);
    }
}

using itemdetails.Context;
using itemdetails.Models;
using Microsoft.EntityFrameworkCore;

namespace itemdetails.Repository
{
    public class ItemRepository : IItemRepository
    {
        private readonly ApplicationDbContext _dbContext;
        public ItemRepository(ApplicationDbContext dbContext) {
            _dbContext = dbContext;
        }
        public async Task<ItemHeader> AddItemHeaderAsync(ItemHeader itemHeader)
        {
            _dbContext.ItemHeaders.Add(itemHeader);
            await _dbContext.SaveChangesAsync();
            return itemHeader;
        }

        public async Task<ItemHeader> AddItemHeaderWithLinesAsync(ItemHeader itemHeader)
        {
            // Add the ItemHeader and line to the database
            _dbContext.ItemHeaders.Add(itemHeader);
            await _dbContext.SaveChangesAsync();
            return itemHeader;
        }

        public async Task<ItemLine> AddItemLineAsync(ItemLine itemLine)
        {
            _dbContext.ItemLines.Add(itemLine);
            await _dbContext.SaveChangesAsync();
            return itemLine;
        }
    }
}

using itemdetails.Models;
using itemdetails.Service;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace itemdetails.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ItemController : ControllerBase
    {
        private readonly IItemService _itemService;
        public ItemController(IItemService itemService) {
            _itemService = itemService;
        }

        [HttpPost("Create")]
        public async Task<IActionResult> CreateItemHeaderWithLines([FromBody] ItemHeaderDto itemHeaderDto)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            // Map DTO to domain models
            var itemHeader = new ItemHeader
            {
                HEADERNAME = itemHeaderDto.HeaderName,
                CREATEDDATE = itemHeaderDto.CreatedDate,
                ItemLines = itemHeaderDto.ItemLines.Select(line => new ItemLine
                {
                    LINEDESCRIPTION = line.LineDescription,
                    QUANTITY = line.Quantity
                }).ToList()
            };

            var createdHeader = await _itemService.CreateItemHeaderWithLinesAsync(itemHeader);
            return CreatedAtAction(nameof(CreateItemHeaderWithLines), new { id = createdHeader.HEADERID }, createdHeader);
        }

    }
}

using itemdetails.Models;
using Microsoft.EntityFrameworkCore;

namespace itemdetails.Context
{
    public class ApplicationDbContext: DbContext
    {
        public DbSet<ItemHeader> ItemHeaders { get; set; }
        public DbSet<ItemLine> ItemLines { get; set; }
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // ItemHeader configuration
            modelBuilder.Entity<ItemHeader>(entity =>
            {
                entity.ToTable("ITEMHEADER"); // Matches table name in database
                entity.HasKey(e => e.HEADERID);
            });

            // ItemLine configuration
            modelBuilder.Entity<ItemLine>(entity =>
            {
                entity.ToTable("ITEMLINE"); // Matches table name in database
                entity.HasKey(e => e.LINEID);

                // Foreign Key Relationship
                entity.HasOne(e => e.ItemHeader)
                      .WithMany(h => h.ItemLines)
                      .HasForeignKey(e => e.HEADERID)
                      .OnDelete(DeleteBehavior.Cascade);
            });
        }
    }
}

Program.cs:

using itemdetails.Context;
using Microsoft.Extensions.Options;
using System.Configuration;
using Oracle.ManagedDataAccess.Client;
using itemdetails.Service;
using itemdetails.Repository;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
builder.Services.AddDbContext<ApplicationDbContext>(options =>
        options.UseOracle(builder.Configuration.GetConnectionString("OracleConnection")));
//builder.Services.AddDbContext<ApplicationDbContext>(options=> options.oracle(Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<IItemService, ItemService>();
builder.Services.AddScoped<IItemRepository, ItemRepository>();

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseAuthorization();

app.MapControllers();

app.Run();

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "OracleConnection": "User Id=c##ekycdev;Password=ekycdev;Data Source=DESKTOP-8OFULSU:1521/XE"
  }
}

Postman:

{

  "HeaderName": "Header 1",

  "CreatedDate": "2024-12-27T00:00:00",

  "ItemLines": [

    {

      "LineDescription": "Item Line 1",

      "Quantity": 10

    },

    {

      "LineDescription": "Item Line 2",

      "Quantity": 5

    }

  ]

}

Output:

{

    "headerid": 6,

    "headername": "Header 1",

    "createddate": "2024-12-27T00:00:00",

    "itemLines": [

        {

            "lineid": 13,

            "headerid": 6,

            "linedescription": "Item Line 1",

            "quantity": 10

        },

        {

            "lineid": 14,

            "headerid": 6,

            "linedescription": "Item Line 2",

            "quantity": 5

        }

    ]

}

SPRING BOOT WITH JPA, HIBERNATE AND KAFKA

package com.abg.uniapi.entity;

import jakarta.persistence.Entity;

import jakarta.persistence.GeneratedValue;

import jakarta.persistence.GenerationType;

import jakarta.persistence.Id;

@Entity

public class Product {

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

private Long id;

private String name;

private double price;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public double getPrice() {

return price;

}

public void setPrice(double price) {

this.price = price;

}

}

package com.abg.uniapi.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.abg.uniapi.entity.Product;

public interface ProductRepository extends JpaRepository<Product, Long> {

}

package com.abg.uniapi.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.abg.uniapi.entity.Product;

import com.abg.uniapi.repository.ProductRepository;

@Service

public class ProductService {

@Autowired

private ProductRepository productRepository;

public List<Product> getAllProducts(){

return productRepository.findAll();

}

public Product getProductById(Long id) {

return productRepository.findById(id).orElse(null);

}

public Product saveProudct(Product product) {

return productRepository.save(product);

}

public void deleteProduct(Long id) {

productRepository.deleteById(id);

}

}

package com.abg.uniapi.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.DeleteMapping;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.PutMapping;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.abg.uniapi.entity.Product;

import com.abg.uniapi.kafka.KafkaProducerService;

import com.abg.uniapi.service.ProductService;

@RestController

@RequestMapping("/api/product")

public class ProductController {

@Autowired

ProductService productService;

@Autowired

private KafkaProducerService kafkaProducerService; 

@GetMapping

public List<Product> getAllProduct(){

return productService.getAllProducts();

}

@GetMapping("/{id}")

public Product getProductById(@PathVariable Long id) {

return productService.getProductById(id);

}

@PostMapping

public Product createProduct(@RequestBody Product product) {

Product saveProduct = productService.saveProudct(product);

kafkaProducerService.sendMessage("Created Product with name: "+saveProduct.getName()+", price: "+saveProduct.getPrice());

return saveProduct;

}

@PutMapping("/{id}")

public Product updateProduct(@PathVariable Long id, @RequestBody Product product) {

Product existingProduct = productService.getProductById(id);

if(existingProduct !=null) {

product.setId(id);

return productService.saveProudct(product);

}

return null;

}

@DeleteMapping("/{id}")

public void deleteProduct(@PathVariable Long id) {

productService.deleteProduct(id);

}

}

package com.abg.uniapi.kafka;

import org.springframework.kafka.annotation.KafkaListener;

import org.springframework.stereotype.Service;

@Service

public class KafkaConsumerListener {

@KafkaListener(topics = "products", groupId = "group_id")

    public void consume(String message) {

        System.out.println("Consumed message: " + message);

    }

}

package com.abg.uniapi.kafka;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.kafka.core.KafkaTemplate;

import org.springframework.stereotype.Service;

@Service

public class KafkaProducerService {

private static final String TOPIC = "products";

    @Autowired

    private KafkaTemplate<String, Object> kafkaTemplate;

    public void sendMessage(String message) {

        kafkaTemplate.send(TOPIC, message);

    }

}

Application.properties

spring.application.name=uniapi

# MySQL Database Configuration

spring.datasource.url=jdbc:mysql://localhost:3306/uniapi

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# Hibernate specific properties

spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

spring.jpa.hibernate.ddl-auto=update

# Kafka Configuration

spring.kafka.bootstrap-servers=localhost:9092

spring.kafka.consumer.group-id=group_id

spring.kafka.consumer.auto-offset-reset=earliest

server.port=8084

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

<modelVersion>4.0.0</modelVersion>

<parent>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>3.3.2-SNAPSHOT</version>

<relativePath/> <!-- lookup parent from repository -->

</parent>

<groupId>com.abg</groupId>

<artifactId>uniapi</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>uniapi</name>

<description>Role Management project for Spring Boot</description>

<url/>

<licenses>

<license/>

</licenses>

<developers>

<developer/>

</developers>

<scm>

<connection/>

<developerConnection/>

<tag/>

<url/>

</scm>

<properties>

<java.version>17</java.version>

</properties>

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-thymeleaf</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<dependency>

<groupId>org.apache.kafka</groupId>

<artifactId>kafka-streams</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.kafka</groupId>

<artifactId>spring-kafka</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.session</groupId>

<artifactId>spring-session-core</artifactId>

</dependency>

<dependency>

<groupId>com.mysql</groupId>

<artifactId>mysql-connector-j</artifactId>

<scope>runtime</scope>

</dependency>

<dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

<optional>true</optional>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

<scope>test</scope>

</dependency>

<dependency>

<groupId>org.springframework.kafka</groupId>

<artifactId>spring-kafka-test</artifactId>

<scope>test</scope>

</dependency>

</dependencies>

<build>

<plugins>

<plugin>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

<configuration>

<excludes>

<exclude>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

</exclude>

</excludes>

</configuration>

</plugin>

</plugins>

</build>

<repositories>

<repository>

<id>spring-snapshots</id>

<name>Spring Snapshots</name>

<url>https://repo.spring.io/snapshot</url>

<releases>

<enabled>false</enabled>

</releases>

</repository>

</repositories>

<pluginRepositories>

<pluginRepository>

<id>spring-snapshots</id>

<name>Spring Snapshots</name>

<url>https://repo.spring.io/snapshot</url>

<releases>

<enabled>false</enabled>

</releases>

</pluginRepository>

</pluginRepositories>

</project>

SETUP KAFKA

SETUP KAFKA

Step-1:

Download Kafka software from https://kafka.apache.org/downloads

Extracting directory:  D:/kafka_2.12-3.7.0

Step-2:

Configure zookeeper.properties

# the directory where the snapshot is stored.

dataDir=D:/kafka_2.12-3.7.0/zookeeper //D:/kafka_2.12-3.7.0/ location where kafka has extracted.

Start Zookeeper:

.\bin\windows\zookeeper-server-start.bat .\config\zookeeper.properties

Start Kafka:

.\bin\windows\kafka-server-start.bat .\config\server.properties

Configure server.properties

# A comma separated list of directories under which to store log files

log.dirs=D:/kafka_2.12-3.7.0/kafka-logs

Run: jsp

Create Topics:

D:\kafka_2.12-3.7.0\bin\windows>kafka-topics.bat --create --bootstrap-server localhost:9092 --topic test

Created topic test.

Step-3:

Start Kafka Producer:

D:\kafka_2.12-3.7.0\bin\windows>kafka-console-producer.bat --broker-list localhost:9092 --topic test

>debo

>hello debo

>thank

>have u seen me

>well done

>then how can i see u

>cls

>clear

>where do u leave

Kafka Consumer Receive Request:

D:\kafka_2.12-3.7.0\bin\windows>kafka-console-consumer.bat --topic test --bootstrap-server localhost:9092 --from-beginning

debo

hello debo

thank

have u seen me

well done

then how can i see u

cls

clear

where do u leave

Lets learn angular 17 with .NET core API 7

• Pre-requisite: install node and angular cli
• Create new project with this command
• ng new projectName --no-standalone   //there are two types of project standalone and no-standalone
• npm install bootstrap@5.3
• Create model class:
• ng g class models/product
• Create a component class
• ng g c components/product
• Create a service class
• ng g s services/product

Project Structure:

app.module.ts

import { NgModule } from '@angular/core';

import { BrowserModule, provideClientHydration } from '@angular/platform-browser';

import { AppRoutingModule } from './app-routing.module';

import { AppComponent } from './app.component';

import { ProductComponent } from './components/product/product.component';

import { FormsModule, ReactiveFormsModule } from '@angular/forms';

import { HttpClient, HttpClientModule, provideHttpClient, withFetch } from '@angular/common/http';

@NgModule({

  declarations: [

    AppComponent,

    ProductComponent

  ],

  imports: [

    BrowserModule,

    AppRoutingModule,

    FormsModule,

    HttpClientModule,

    ReactiveFormsModule

  ],

  providers: [

    provideClientHydration(),

    provideHttpClient(withFetch())

  ],

  bootstrap: [AppComponent]

})

export class AppModule { }

app.component.ts

import { Component } from '@angular/core';

@Component({

  selector: 'app-root',

  templateUrl: './app.component.html',

  styleUrl: './app.component.css'

})

export class AppComponent {

  title = 'mytask4';

}

app.component.html

<div class="container">

  <nav class="navbar navbar-expand navbar-dark bg-dark">

    <a href="#" class="navbar-brand">MyTask</a>

    <div class="navbar-nav mr-auto">

      <li class="nav-item">

        <a routerLink="product" class="nav-link">Product</a>

      </li>

    </div>

  </nav>

  <div class="container mt-3">

    <router-outlet />

  </div>

</div>

app-routing.module.ts

import { NgModule } from '@angular/core';

import { RouterModule, Routes } from '@angular/router';

import { ProductComponent } from './components/product/product.component';

const routes: Routes = [

  {path:'product', component:ProductComponent}

];

@NgModule({

  imports: [RouterModule.forRoot(routes)],

  exports: [RouterModule]

})

export class AppRoutingModule { }

module/product.ts

export class Product {

    id?:any;

    name?:string;

    price?:any;

    discount?:any;

}

product.service.ts

import { HttpClient } from '@angular/common/http';

import { Injectable } from '@angular/core';

import { Observable } from 'rxjs';

import { Product } from '../models/product';

const baseUrl = 'http://localhost:7043/api/Product';

@Injectable({

  providedIn: 'root'

})

export class ProductService {

  constructor(private http: HttpClient) { }

  getAllProduct():Observable<Product[]>{

    return this.http.get<Product[]>(baseUrl);

  }

}

product.component.ts

import { Component, OnInit } from '@angular/core';

import { Product } from '../../models/product';

import { ProductService } from '../../services/product.service';

import { FormBuilder, FormGroup, NgForm, Validators } from '@angular/forms';

@Component({

  selector: 'app-product',

  templateUrl: './product.component.html',

  styleUrl: './product.component.css'

})

export class ProductComponent implements OnInit{

  product?: Product[];

  constructor(private productService: ProductService){

  }

  ngOnInit(): void {

    this.getAllProduct();

  }

  title = 'Create Product';

  list:Product[]=[];

  formData:Product=new Product();

  saveProduct(form:NgForm) {

    console.log("formDate.name", this.formData.name);    

    if(form.valid){

      console.log("formDate.price", this.formData.price);

      console.log("formDate.discount", this.formData.discount);

    }

   

  }

  getAllProduct():void{

    this.productService.getAllProduct().subscribe({

      next:(data)=>{

        this.product = data;

        //console.log(data);

      },

      error:(e)=>console.log(e)

    })

  }

}

product.component.html

<button type="button" class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#saveProduct">Create New

    Product</button>

<table class="table table-striped" style="text-align: center;">

    <thead>

        <th>Id</th>

        <th>Name</th>

        <th>Price</th>

        <th>Discount</th>

        <th>Action</th>

    </thead>

    <tbody>

        @for (item of product; track $index) {

        <tr>

            <td>{{item.id}}</td>

            <td>{{item.name}}</td>

            <td>{{item.price}}</td>

            <td>{{item.discount}}</td>

            <td>

                <button type="button" class="btn btn-info">Edit</button>&nbsp;

                <button type="button" class="btn btn-warning">Delete</button>

            </td>

        </tr>

        }

    </tbody>

</table>

<!-- Modal -->

<div class="modal fade" id="saveProduct" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">

    <div class="modal-dialog">

        <form #form="ngForm" (submit)="saveProduct(form)">

            <div class="modal-content">

                <div class="modal-header">

                    <h5 class="modal-title" id="exampleModalLabel">{{title}}</h5>

                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>

                </div>

                <div class="modal-body">

                    <div class="mb-3">

                        <label for="Name">Name</label>

                        <input class="form-control form-control-lg" placeholder="Product Name" type="text"

                            #name="ngModel" name="name" [(ngModel)]="formData.name" id="name" required />

                    </div>

                    <div class="mb-3">

                        <label for="Price">Price</label>

                        <input class="form-control form-control-lg" placeholder="Product Price" type="text"

                            #price="ngModel" name="price" [(ngModel)]="formData.price" id="price" required />

                    </div>

                    <div class="mb-3">

                        <label for="Discount">Discount</label>

                        <input class="form-control form-control-lg" placeholder="Product Name" type="text"

                            #discount="ngModel" name="discount" [(ngModel)]="formData.discount" id="discount" required />

                    </div>

                </div>

                <div class="modal-footer">

                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>

                    <button class="btn btn-success">Save</button>

                </div>

            </div>

        </form>

    </div>

</div>