Hjälp med SQLserver och C# i .NET

namespace AdoNetSample
{
    using System;
    using System.Data;
    using Microsoft.Data.SqlClient;

    // using only nuget package Microsoft.Data.SqlClient
    internal class Program
    {
        private static void Main()
        {
            Console.WriteLine("ADO.NET shopping sample.");

            const string host = "(LocalDB)\\MSSQLLocalDB";
            const string database = "shopping_sample";
            var connectionString = $"Data Source={host};Initial Catalog={database};Integrated Security=True;";

            using var connection = new SqlConnection(connectionString);

            connection.Open();

            var userId = 79; // Session["USERID"]
            var productId = 1001; // Session["CartPID"]
            var quantity = 1;

            var query = "select * from tblCart where UID = @UserId";

            // look up the shopping cart for a specific user

            // warning 1: there could be many carts for the same user. so this will not work

            // warning 2: if the value is from the web ui, the user can modify it
            // and modify another users cart. so this is not ok

            var command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("@UserId", userId);

            var reader = command.ExecuteReader();

            // does the shopping cart exists?
            if (reader.Read())
            {
                decimal.TryParse(reader[8].ToString(), out var existingQuantity);

                // release the connection s it can be re-used further down
                reader.Dispose();

                // if so, update quantity and sell price

                var updateSql = "UPDATE tblCart SET Qty = @Quantity WHERE PID = @ProductId";

                // warning 1: since the cart already exists the original product will be overwritten.
                // i.e, adding an apple then a banana.. now the cart contains 2 bananas. so this will not work

                using var updateCommand = new SqlCommand
                {
                    Connection = connection,
                    CommandType = CommandType.Text,
                    CommandText = updateSql
                };

                updateCommand.Parameters.AddWithValue("@Quantity", existingQuantity + quantity);
                updateCommand.Parameters.AddWithValue("@ProductId", productId);

                updateCommand.ExecuteNonQuery();
            }
            else
            {
                var cartId = 1; // Session["CartPID"]
                var sellPrice = 100.50M; // Session["myPSelPrice"]
                var productName = "Apple"; // Session["myPName"]
                var price = 9.30M; // Session["myPPrice"]

                var insertSql = "INSERT INTO [dbo].[tblCart]([UID],[PID],[PName],[PPrice],[PSelPrice],[Qty]) VALUES (@UserId, @ProductId, @ProductName, @ProductPrice, @ProductSellPrice, @Quantity)";

                // warning 1: if the value is from the web ui, the user can modify it
                // and select his/hers own price etc. so this is not ok

                // release the connection s it can be re-used further down
                reader.Dispose();

                using var insertCommand = new SqlCommand
                {
                    Connection = connection,
                    CommandType = CommandType.Text,
                    CommandText = insertSql
                };

                insertCommand.Parameters.AddWithValue("@UserId", userId);
                insertCommand.Parameters.AddWithValue("@ProductId", productId);
                insertCommand.Parameters.AddWithValue("@ProductName", productName);
                insertCommand.Parameters.AddWithValue("@ProductPrice", price);
                insertCommand.Parameters.AddWithValue("@ProductSellPrice", sellPrice);
                insertCommand.Parameters.AddWithValue("@Quantity", quantity);

                insertCommand.ExecuteNonQuery();
            }
        }
    }
}

namespace DapperSample
{
    using System;
    using Dapper;
    using Microsoft.Data.SqlClient;

    // using nuget package Dapper
    // using nuget package Microsoft.Data.SqlClient

    internal class Program
    {
        private static void Main()
        {
            Console.WriteLine("Dapper shopping sample.");

            const string host = "(LocalDB)\\MSSQLLocalDB";
            const string database = "shopping_sample";
            var connectionString = $"Data Source={host};Initial Catalog={database};Integrated Security=True;";

            using var connection = new SqlConnection(connectionString);

            var userId = 78; // Session["USERID"]
            var productId = 1001; // Session["CartPID"]
            var quantity = 1;

            var query = "select * from tblCart where UID = @UserId";
            
            // look up the shopping cart for a specific user

            // warning 1: there could be many carts for the same user. so this will not work

            // warning 2: if the value is from the web ui, the user can modify it
            // and modify another users cart. so this is not ok

            var cart = connection.QueryFirstOrDefault(
                query,
                new { UserId = userId });

            // does the shopping cart exists?
            if (cart != null)
            {
                var updateSql = "UPDATE tblCart SET Qty = @Quantity WHERE PID = @ProductId";

                // if so, update quantity and sell price

                // warning 1: since the cart already exists the original product will be overwritten.
                // i.e, adding an apple then a banana.. now the cart contains 2 bananas. so this will not work
                
                connection.Execute(
                    updateSql, 
                    new { Quantity = cart.Qty + quantity, ProductId = productId });
            }
            else
            {
                var cartId = 1; // Session["CartPID"]
                var sellPrice = 100.50M; // Session["myPSelPrice"]
                var productName = "Apple"; // Session["myPName"]
                var price = 9.30M; // Session["myPPrice"]

                var insertSql = "INSERT INTO [dbo].[tblCart]([UID],[PID],[PName],[PPrice],[PSelPrice],[Qty]) VALUES (@UserId, @ProductId, @ProductName, @ProductPrice, @ProductSellPrice, @Quantity)";

                // warning 1: if the value is from the web ui, the user can modify it
                // and select his/hers own price etc. so this is not ok

                connection.Execute(insertSql, new
                {
                    UserId = userId,
                    ProductId = productId,
                    ProductName = productName,
                    ProductPrice = price,
                    ProductSellPrice = sellPrice,
                    Quantity = quantity
                });
            }

        }
    }
}

namespace EFCoreSample
{
    using System;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;
    using Microsoft.EntityFrameworkCore;

    // using nuget package Microsoft.EntityFrameworkCore.SqlServer
    // using nuget package Microsoft.Data.SqlClient

    internal class Program
    {
        private static void Main()
        {
            Console.WriteLine("Entity Framework Core shopping sample.");

            const string host = "(LocalDB)\\MSSQLLocalDB";
            const string database = "shopping_sample";
            var connectionString = $"Data Source={host};Initial Catalog={database};Integrated Security=True;";

            var dbContextBuilder = new DbContextOptionsBuilder<ShoppingContext>();

            dbContextBuilder.UseSqlServer(connectionString);

            using var db = new ShoppingContext(dbContextBuilder.Options);

            var userId = 77; // Session["USERID"]
            var productId = 1001; // Session["CartPID"]
            var quantity = 1;

            // look up the shopping cart for a specific user

            // warning 1: there could be many carts for the same user. so this will not work

            // warning 2: if the value is from the web ui, the user can modify it
            // and modify another users cart. so this is not ok

            var cart = db.Carts.FirstOrDefault(x => x.UserId == userId);

            // does the shopping cart exists?
            if (cart != null)
            {
                // if so, update quantity and sell price

                // warning 1: since the cart already exists the original product will be overwritten.
                // i.e, adding an apple then a banana.. now the cart contains 2 bananas. so this will not work

                cart.Quantity += quantity;
                cart.ProductId = productId;
            }
            else
            {
                var cartId = 1; // Session["CartPID"]
                var sellPrice = 100.50M; // Session["myPSelPrice"]
                var productName = "Apple"; // Session["myPName"]
                var price = 9.30M; // Session["myPPrice"]

                // warning 1: if the value is from the web ui, the user can modify it
                // and select his/hers own price etc. so this is not ok

                cart = new Cart
                {
                    ProductId = productId,
                    ProductName = productName,
                    Price = price,
                    SellPrice = sellPrice,
                    Quantity = quantity,
                    UserId = userId
                };

                db.Add(cart);
            }

            db.SaveChanges();
        }
    }

    public class ShoppingContext : DbContext
    {
        public ShoppingContext(DbContextOptions<ShoppingContext> options)
            : base(options)
        {
            // empty..
        }

        public virtual DbSet<Cart> Carts { get; set; }
    }

    [Table("tblCart")]
    public class Cart
    {
        [Key]
        [Column("CartID")]
        public int Id { get; set; }

        [Column("UID")]
        public int UserId { get; set; }

        [Column("PID")]
        public int ProductId { get; set; }

        [Column("Qty")]
        public int Quantity { get; set; }

        [Column("PSelPrice")]
        public decimal SellPrice { get; set; }

        [Column("SubSAmount")]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public decimal TotalSellPrice { get; set; }

        // note: product related data should be in its own product table

        [Column("PName")]
        public string ProductName { get; set; }

        [Column("PPrice")]
        public decimal Price { get; set; }

        [Column("SubPAmount")]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public decimal TotalPrice { get; set; }
    }
}

namespace EFCoreSample2
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Diagnostics.CodeAnalysis;
    using System.Linq;

    using Microsoft.EntityFrameworkCore;

    // using nuget package Microsoft.EntityFrameworkCore.SqlServer
    // using nuget package Microsoft.Data.SqlClient

    internal class Program
    {
        private static void Main()
        {
            Console.WriteLine("Entity Framework Core shopping sample 2.");

            const string host = "(LocalDB)\\MSSQLLocalDB";
            const string database = "shopping_sample2";
            var connectionString = $"Data Source={host};Initial Catalog={database};Integrated Security=True;";

            var dbContextBuilder = new DbContextOptionsBuilder<ShoppingContext>();

            dbContextBuilder.UseSqlServer(connectionString);

            using var db = new ShoppingContext(dbContextBuilder.Options);

            // uncomment this to auto create the database if missing
            // warning: check your connection string first
            //db.Database.EnsureCreated();

            // add sample products
            var apple = AddProduct(db, "Apple", 5.50M, 3M);
            var banana = AddProduct(db, "Banana", 12.20M, 6.76M);

            var quantity = 1;

            // note: use hard-coded guids to test update of carts
            var userId = Guid.NewGuid(); // Session["USERID"]
            var cartId = Guid.NewGuid(); // Session["CartPID"]

            // purchase the apple
            var productId = apple.Id; // Session["CartPID"]

            // look up the shopping cart for a specific user
            // and include all items
            var cart = db.Carts
                .Include(x => x.Items)
                .FirstOrDefault(x => x.UserId == userId && x.Id == cartId);

            // does the shopping cart exists?
            if (cart != null)
            {
                var existingCartItem = cart.Items.FirstOrDefault(
                    x => x.ProductId == productId);

                // is the product type already ordered?
                if (existingCartItem != null)
                {
                    // if so, update only quantity

                    existingCartItem.Quantity += quantity;
                }
                else
                {
                    var item = CreateCartItem(
                        db,
                        productId,
                        quantity);

                    // add the new item to the cart
                    cart.Items.Add(item);
                }

                db.SaveChanges();
            }
            else
            {
                cart = new Cart
                {
                    Id = Guid.NewGuid(),
                    UserId = userId
                };

                var item = CreateCartItem(
                    db,
                    productId,
                    quantity);

                // add the new item to the cart
                cart.Items.Add(item);

                db.Add(cart);
            }

            db.SaveChanges();

            ShowCarts(db);
        }

        private static void ShowCarts(ShoppingContext db)
        {
            // get all carts with all items and its product
            var carts = db.Carts
                .Include(x => x.Items)
                .ThenInclude(x=> x.Product)
                .ToList();

            foreach (var cart in carts)
            {
                Console.WriteLine($"cart {cart.Id} contains:");

                foreach (var item in cart.Items)
                {
                    Console.WriteLine($"\t item: {item.Product.ProductName}, qty: {item.Quantity}, tot.price: {item.Price*item.Quantity}");
                }
            }
        }

        private static Product AddProduct(
            ShoppingContext db,
            string productName,
            decimal price,
            decimal supplierPrice)
        {
            var existingProduct = db.Products.FirstOrDefault(x => x.ProductName == productName);

            if (existingProduct != null)
            {
                return existingProduct;
            }

            var product = new Product
            {
                ProductName = productName,
                Price = price,
                SupplierPrice = supplierPrice
            };

            db.Products.Add(product);

            db.SaveChanges();

            return product;
        }

        private static CartItem CreateCartItem(
            ShoppingContext db,
            int productId,
            int quantity)
        {
            // look up the product price to be added to the cart
            var product = db.Products
                .Where(x => x.Id == productId)
                .Select(x => new { x.Price })
                .FirstOrDefault();

            if (product == null)
            {
                throw new Exception("Product does not exists.");
            }

            var item = new CartItem
            {
                Price = product.Price,
                ProductId = productId,
                Quantity = quantity
            };
            return item;
        }
    }

    public class ShoppingContext : DbContext
    {
        public ShoppingContext(DbContextOptions<ShoppingContext> options)
            : base(options)
        {
            // empty..
        }

        public virtual DbSet<Cart> Carts { get; set; }
        public virtual DbSet<Product> Products { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Cart>(entity =>
            {
                // make a composite key so we can have multiple shopping carts at the same time
                // for a specific user (multiple tabs opened?)
                entity.HasKey(e => new { e.Id, e.UserId });
            });
        }
    }


    public class Cart
    {
        // note: make both id and cart id guid
        // so they cannot be guessed

        public Guid Id { get; set; }
        public Guid UserId { get; set; }
        public ICollection<CartItem> Items { get; set; } = new List<CartItem>();
    }

    public class CartItem
    {
        public int Id { get; set; }
        public int ProductId { get; set; }
        public Product Product { get; set; }
        public decimal Price { get; set; }
        public int Quantity { get; set; }
    }

    public class Product
    {
        public int Id { get; set; }
        public string ProductName { get; set; }
        public decimal Price { get; set; }
        public decimal SupplierPrice { get; set; }
    }
}