dimanche 28 juin 2015

Entity Framework 6 Insert data with foreign keys into azure sql database

I have a sql database on azure and can load data like articles. If I try to save data in table orders, billingDetails and positions it is running through the saving process without any exceptions but if I let the data of the tables be shown in the visual studio IDE afterwards there are no new entries displayed.

My fluent API of the tables orders, positions and billingDetails looks like this:

//------Relationship Orders <--> Billing Details-------
//Configure the primary key for orders (Primary key BillingDeatailID is foreign key in orders)
modelBuilder.Entity<Orders>()
.HasKey(b => b.BillingDetailID);

//one-to-one relationship 
modelBuilder.Entity<BillingDetails>()
.HasRequired(b => b.Order)
.WithRequiredPrincipal(b => b.BillingDetails)
.WillCascadeOnDelete(false);

//------Relationship Products <--> Positions-------
//one-to-many relationship (a position can have one product but a product can have many positions (optional relationship); the table positions contains ProductID as a required foreign key if the relation exists)
modelBuilder.Entity<Positions>()
.HasOptional<Products>(p => p.Product)
.WithMany(p => p.Positions)
.HasForeignKey(p => p.ProductID);


//------Relationship Orders <--> Positions-------
//one-to-many relationship (a position can have one order but an order can have many positions (optional relationship); the table positions contains OrderID as a required foreign key if the relation exists)
modelBuilder.Entity<Positions>()
.HasOptional<Orders>(o => o.Order)
.WithMany(o => o.Positions)
.HasForeignKey(o => o.OrderID);

my Action for saving the data:

public ActionResult CompleteOrder() 
        {
            //save data to database
            using (var context = new OnlineShopContext())
            {
                BillingDetails billDetails = new BillingDetails();
                Orders order = new Orders();

                try
                {
                    //save billing details
                    try
                    {
                        billDetails.Owner = Session["PaymentOwner"].ToString();
                        billDetails.CardType = (int)Session["PaymentType"];
                        if(Session["PaymentType"].ToString() == "0"){
                            billDetails.Number = Session["PaymentCreditcardNumber"].ToString();
                        }else{
                            billDetails.Number = Session["PaymentAccountNumber"].ToString();
                        }

                        billDetails.ExpiryMonth = (int)Session["PaymentExpireMonth"];
                        billDetails.ExpiryYear = (int)Session["PaymentExpireYear"];
                        billDetails.Swift = Session["PaymentSwift"].ToString();
                        billDetails.Blz = Session["PaymentBlz"].ToString();
                        billDetails.IBAN = Session["PaymentIBAN"].ToString();

                        context.BillingDetails.AddOrUpdate(billDetails);
                        context.Entry(billDetails).State = EntityState.Added;

                        if (context.SaveChanges() > 0)
                        {
                            //saved
                        }
                        else
                        {
                            string Msg = "Error while saving!";

                            return View((object)Msg);
                        }
                    }
                    catch (OptimisticConcurrencyException ocEx)
                    {
                        log.Fatal("OptimisticConcurrencyException while saving billing details: " + ocEx.Message);
                        string Msg = "Error while saving!";

                        return View((object)Msg);
                    }

                    //get the id of added billing details item and complete the order
                    var billingDetailsId = billDetails.BillingDetailID;

                    order.BillingDetailID = billingDetailsId;

                    order.DeliveryName = Session["DeliveryName"].ToString();
                    order.DeliveryStreet = Session["DeliveryStreet"].ToString();
                    order.DeliveryCity = Session["DeliveryCity"].ToString();
                    order.DeliveryZipCode = Session["DeliveryZipCode"].ToString();
                    order.DeliveryCountry = Session["DeliveryCountry"].ToString();

                    order.BillName = Session["BillName"].ToString();
                    order.BillStreet = Session["BillStreet"].ToString();
                    order.BillCity = Session["BillCity"].ToString();
                    order.BillZipCode = Session["BillZipCode"].ToString();
                    order.BillCountry = Session["BillCountry"].ToString();

                    order.OrderDate = DateTime.Now;

                    //save the order
                    try
                    {
                        context.Orders.AddOrUpdate(order);
                        context.Entry(order).State = EntityState.Added;
                        if(context.SaveChanges() > 0){
                            //saved
                        }
                        else{
                            string Msg = "Error while saving!";

                            return View((object)Msg);
                        }
                    }
                    catch (OptimisticConcurrencyException ocEx)
                    {
                        log.Fatal("OptimisticConcurrencyException while saving order: " + ocEx.Message);
                        string Msg = "Error while saving!";

                        return View((object)Msg);
                    }

                    //get id of added order
                    var orderId = order.OrderID;

                    //save all positions of this order
                    foreach (var item in CartItems)
                    {
                        Positions position = new Positions();
                        position.OrderID = orderId;
                        position.ProductID = item.product.ProductID;
                        position.Amount = item.amount;

                        try
                        {
                            context.Positions.AddOrUpdate(position);
                            context.Entry(position).State = EntityState.Added;

                            if(context.SaveChanges() > 0){
                               //saved
                            }
                            else{
                               string Msg = "Error while saving!";

                                return View((object)Msg);
                            }
                        }
                        catch (OptimisticConcurrencyException ocEx)
                        {
                            log.Fatal("OptimisticConcurrencyException while saving position: " + ocEx.Message);
                            string Msg = "Error while saving!";

                            return View((object)Msg);
                        }
                    }

                }
                catch (Exception ex)
                {
                    log.Fatal("Error while saving order data. Exception: " + ex.Message);

                    string Msg = "Error while saving!";

                    return View((object)Msg);
                }

                //empty the shopping cart
                RemoveAllCartItems();

                //redirect to the catalog
                return RedirectToAction("Index", "Products");
            }
        }

The IDs are incremented correctly when I check them while debugging (e.g. ID 9) and if I reproduce it one more time the IDs are incremented again (e.g. ID 10).

There are already some dummy data in the database and they do not change so it is not updating them by accident as well.

Why are my new added data not displayed if I try to display them in the IDE?

Aucun commentaire:

Enregistrer un commentaire