none
question about the multiple insert RRS feed

  • Question

  • Hi, i have 2 questions about hte multiple insert.

    1) Can i get the id back after insert InventoryQtyIO table and assign the Id to SOInv table as this this.soInv.InventoryQtyIOId = this.inventoryQtyIO.Id;

    2) How do i assign the SaleOrderItemId back to SOInv after db.SaleOrderItems.InsertOnSubmit(this.saleOrderItem[i]);  Is the way i did below correct? It is because the  SaleOrderItem is an array.

    3) should i declare this so many times?  using (LenDataContext db = new LenDataContext ())

     

      using (TransactionScope ts = new TransactionScope())
          {
            try
            {
              using (LenDataContext db = new LenDataContext ())
              {
                db.SaleOrders.InsertOnSubmit(this);
              }
              using (LenDataContext db = new LenDataContext ())
              {
                //foreach (SaleOrderItem saleItem in this.saleOrderItem)
                for (int i = 0; i < this.saleOrderItem.Count(); i++) 
                {
                  //Insert into SaleOrderItems
                  db.SaleOrderItems.InsertOnSubmit(this.saleOrderItem[i]);
    
                  if (this.saleOrderItem[i].InventoryTypeId == "product")
                  {
                    //Insert into InventoryQtyIO table to get the InveotyQtyIO id 
                    db.InventoryQtyIOs.InsertOnSubmit(this.inventoryQtyIO);
    
                    //Insert into SOInv table
                    this.soInv.SaleOrderItemId = <strong>this.saleOrderItem[i].Id;</strong>
                    this.soInv.InventoryQtyIOId =<strong> this.inventoryQtyIO.Id;</strong>           
                    db.SOInvs.InsertOnSubmit(this.soInv);
                  }
                }
              }
              using (LenDataContext db = new LenDataContext ())
              {
                db.Payments.InsertOnSubmit(this.payment);
              }
    
              ts.Complete(); 
            }
            catch (Exception ex)
            {
              throw;
            }
    

     


    Change
    Tuesday, December 21, 2010 2:22 AM

Answers

  • 2) How do i assign the SaleOrderItemId back to SOInv after db.SaleOrderItems.InsertOnSubmit(this.saleOrderItem[i]);  Is the way i did below correct? It is because the  SaleOrderItem is an array.

          for (int i = 0; i < this.saleOrderItem.Count(); i++) 
    
          {
    
           //Insert into SaleOrderItems
    
           db.SaleOrderItems.InsertOnSubmit(this.saleOrderItem[i]);
    
    
          }

    Yes, it looks fine, but remember to do a db.SubmitChanges() some time after the loop and before the ts.Complete();

    Tuesday, December 21, 2010 12:25 PM

All replies

  • Hi,

    Welcome to LINQ to SQL forum!

    Could you please make the scenario be more clear?  Besides, I don't think we need to intialize multiple instance DataContext here. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, December 21, 2010 5:52 AM
    Moderator
  • Ok sure

    1) Is this the correct way to get the id back after insert InventoryQtyIO table and assign the Id to SOInv table as this?

     //Insert into InventoryQtyIO table to get the InveotyQtyIO id 
            db.InventoryQtyIOs.InsertOnSubmit(this.inventoryQtyIO);
    
            //Insert into SOInv table
            this.soInv.InventoryQtyIOId = this.inventoryQtyIO.Id;     
            db.SOInvs.InsertOnSubmit(this.soInv);
    

     

    2)  How do i assign the SaleOrderItemId back to SOInv? Is the way i did below correct? It is because the  SaleOrderItem is an array.

      for (int i = 0; i < this.saleOrderItem.Count(); i++) 
          {  
            db.SaleOrderItems.InsertOnSubmit(this.saleOrderItem[i]);  
    
            if (this.saleOrderItem[i].InventoryTypeId == "product")
            {        
             //Insert into SOInv table
             this.soInv.SaleOrderItemId = this.saleOrderItem[i].Id;       
             db.SOInvs.InsertOnSubmit(this.soInv);
            }       
          }
    
    3) can i just use 1  using (LenDataContext db = new LenDataContext ())  from top to bottom to handle the multiple insert above?


    Change
    Tuesday, December 21, 2010 6:20 AM
  • 3) should i declare this so many times?  using (LenDataContext db = new LenDataContext ())

    No, just declare it once in this method. It's the thing which holds the database objects which you've added (by InsertOnSubmit and any you've fetched). So as soon as the using is complete, db can be disposed of & can be garbage collected & it's contents are no longer available.

    The usual recommendation is to keep the context fairly briefly but it can be kept for longer periods.

    Tuesday, December 21, 2010 11:59 AM
  • 1) Can i get the id back after insert InventoryQtyIO table and assign the Id to SOInv table as this this.soInv.InventoryQtyIOId = this.inventoryQtyIO.Id;

    So InventoryQtyIO.Id is an auto IDENTITY(x,y) field? In that case the id is available only after you submit your changes.

        db.InventoryQtyIOs.InsertOnSubmit(this.inventoryQtyIO);

        db.SubmitChanges();

        // Now this.inventoryQtyIO.Id is available.

    But if you are trying to assign the Id to a related record before SubmitChanges there is also the way of using the Association property. You should be able to do something like:

        db.InventoryQtyIOs.InsertOnSubmit(this.inventoryQtyIO);

        this.soInv.InventoryQtyIO = this.inventoryQtyIO;

        db.SOInvs.InsertOnSubmit(this.soInv);
        db.SubmitChanges();

     

     

    Tuesday, December 21, 2010 12:16 PM
  • 2) How do i assign the SaleOrderItemId back to SOInv after db.SaleOrderItems.InsertOnSubmit(this.saleOrderItem[i]);  Is the way i did below correct? It is because the  SaleOrderItem is an array.

          for (int i = 0; i < this.saleOrderItem.Count(); i++) 
    
          {
    
           //Insert into SaleOrderItems
    
           db.SaleOrderItems.InsertOnSubmit(this.saleOrderItem[i]);
    
    
          }

    Yes, it looks fine, but remember to do a db.SubmitChanges() some time after the loop and before the ts.Complete();

    Tuesday, December 21, 2010 12:25 PM
  • Hi,

    Could you please tell us how is the problem now?  If you need any further assistance, please feel free to let me know.

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 24, 2010 1:23 AM
    Moderator