6. Connecting a React app to SQL Server (through an API)

React (browser)  ──fetch JSON──▶  ASP.NET Core Web API / Node Express  ──ADO.NET/EF/Dapper or mssql──▶  SQL Server

// Program.cs
builder.Services.AddCors(o => o.AddPolicy("spa", p => p.WithOrigins("http://localhost:5173").AllowAnyHeader().AllowAnyMethod()));
var cs = builder.Configuration.GetConnectionString("Shop");   // appsettings.json → "Server=.;Database=Shop;Trusted_Connection=True;TrustServerCertificate=True"
var app = builder.Build();
app.UseCors("spa");
app.MapGet("/api/products", async () =>
{
    await using var conn = new SqlConnection(cs);
    return await conn.QueryAsync<ProductDto>("SELECT Id, Name, Price FROM dbo.Products ORDER BY Name");
});

// src/lib/apiClient.ts
const BASE = import.meta.env.VITE_API_URL ?? "";          // "" in dev when using the Vite proxy
export async function api<T>(path: string, init?: RequestInit): Promise<T> {
  const res = await fetch(`${BASE}${path}`, { ...init, headers: { "Content-Type": "application/json", ...init?.headers } });
  if (!res.ok) throw new Error(`API ${res.status}`);
  return res.json() as Promise<T>;
}

// src/features/products/api.ts
import { api } from "../../lib/apiClient";
export type Product = { id: number; name: string; price: number };
export const getProducts = () => api<Product[]>("/api/products");

// src/features/products/useProducts.ts
import { useQuery } from "@tanstack/react-query";
import { getProducts } from "./api";
export const useProducts = () => useQuery({ queryKey: ["products"], queryFn: getProducts });

// src/features/products/ProductList.tsx
export default function ProductList() {
  const { data, isLoading, error } = useProducts();
  if (isLoading) return <p>Loading…</p>;
  if (error) return <p>Could not load products.</p>;
  return <ul>{data!.map((p) => <li key={p.id}>{p.name} — ₹{p.price}</li>)}</ul>;
}

// vite.config.ts — dev proxy avoids CORS during development
export default defineConfig({
  plugins: [react()],
  server: { proxy: { "/api": { target: "https://localhost:7001", changeOrigin: true, secure: false } } },
});