PVFC API - Backend Services

Program.cs

using Microsoft.Data.SqlClient;

var builder = WebApplication.CreateBuilder(args);

// services
builder.Services.AddOpenApi();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
}


app.MapGet("/api/recommendations/{productId}", (int productId, IConfiguration config) =>
{
    List results = new List();

    string connStr = config.GetConnectionString("PVFC");

    using (SqlConnection con = new SqlConnection(connStr))
    {
        con.Open();

        string query = @"
        SELECT DISTINCT p2.Product_Description
        FROM Order_line_t ol1
        JOIN Order_line_t ol2 
            ON ol1.Order_Id = ol2.Order_Id
        JOIN PRODUCT_t p1 ON ol1.Product_Id = p1.Product_Id
        JOIN PRODUCT_t p2 ON ol2.Product_Id = p2.Product_Id
        WHERE ol1.Product_Id = @pid
          AND ol2.Product_Id <> @pid";

        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.AddWithValue("@pid", productId);

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            results.Add(reader["Product_Description"].ToString());
        }
    }

    return results;
});

app.MapGet("/api/reorder/{customerId}", (int customerId, IConfiguration config) =>
{
    List result = new List();

    string connStr = config.GetConnectionString("PVFC");

    using (SqlConnection con = new SqlConnection(connStr))
    {
        con.Open();

        string query = @"
        SELECT DISTINCT P.Product_Description
        FROM ORDER_t O
        JOIN Order_line_t OL ON O.Order_Id = OL.Order_Id
        JOIN PRODUCT_t P ON P.Product_Id = OL.Product_Id
        WHERE O.Customer_Id = @cid";

        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.AddWithValue("@cid", customerId);

        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            result.Add(dr["Product_Description"].ToString());
        }
    }

    return result;
});

app.MapGet("/api/forecast/{productId}", (int productId, IConfiguration config) =>
{
    List