Multiple Values In One Field (foreign Keys?)

I am trying to do an Ordering System where you can put many Products in one order. I have very little knowledge about this and this is where i am now There are 3 tables, Product t

Solution 1:

How Linq 2 SQL dataclasses look for me:

The code that goes with it:

//I have 2 columns in my dataGridView, Id 1st amount 2nd//I added 3 items for testing
List<Tuple<int, int>> cart = new List<Tuple<int,int>>();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
    if (row.Cells[0].Value != null && row.Cells[1].Value != null)
    { 
        cart.Add(new Tuple<int, int>(Convert.ToInt32(row.Cells[0].Value.ToString()),Convert.ToInt32(row.Cells[1].Value.ToString())));
                //Now each list item will have .Item1 (productId) and .Item2 (amount)
    }
}
using (DataClasses1DataContext dataContext = new DataClasses1DataContext())
{
    //The tables you add in the dataContext are accessible by name
    Order order = new Order();
    dataContext.Orders.InsertOnSubmit(order);
    dataContext.SubmitChanges(); // Submit once so we get an orderIdforeach (Tuple<int, int> product in cart)
    {
        OrderProduct orderProduct = new OrderProduct();
        orderProduct.OrderId = order.OrderID;
        orderProduct.ProductId = product.Item1;
        orderProduct.Amount = product.Item2;
        dataContext.OrderProducts.InsertOnSubmit(orderProduct);
    }
    dataContext.SubmitChanges();
}

Solution 2:

Create foreign key relationship between Order - OrderProduct over OrderID and Product - OrderProduct over Product ID.

For each Product in order insert a row into OrderProduct with OrderId

Solution 3:

This may not answer your question completely but consider an object orientated approach to this. It's always better in my opinion to have a strongly typed method of accessing values returned from a database, although others may disagree. Here is some pseudo code to get you started and is by no means the entire solution but should encourage you to think how you can make your code more object orientated and strongly typed. Use the same methodology to save and update tables in your database.

example

//Business layerpublicclassProduct
{
    publicstring ProductName {get;set;}
    publicint Quantity {get;set;}
    publicstring Unit {get;set;}
    publicdecimal Price {get;set;}
    publiclong Total {get;set;}

    publicProduct(){}

    publicProduct(string productName, int quantity, string unit, decimal price, long total)
    {
        ProductName = productName;
        Quantity = quantity;
        Unit = unit;
        Price = price;
        Total = total;
    }

    public List<Product> GetProductList()
    {
        //get the list of products from the data access layer
        ProductDal dal = new ProductDal();
        return dal.GetProductList();
    }
}

//Data layerpublicclassProductDal
{
    public List<Product> GetProductList()
    {
        List<Product> lstProducts = new List<Product>();
        //connect to your database code here//fill your list with records from your Sql query//inside your DataReader while loop you can add a new Product object to your list for each record//assuming your database field names match the Product class's proeprties you would do this
        lstProducts.Add(new Product((string)reader["ProductName"],
                                    (int)reader["Quantity"],
                                    (string)reader["Unit"], 
                                    decimal)reader["Price"],
                                    (long)reader["Total"]));

        return lstProducts;
    }
}

//front end code behind pageprivatevoidbutton2_Click(object sender, EventArgs e)
    {
        Product product = new Product();
        dgvCart.DataScource = product.GetProductList();
        dgvCart.DataBind();
    }