none
tarnsaction in linq or how pass a tabel data type in query with linq to insert in order detail?or how insert master/detail tabels(order,order detail) by linq C# sql2008 RRS feed

  • Question

  • Hello 

     my skill in English is low , please pardon me because of mistake in English writing.

    I am beginner in C# and LINQ and SQL.

    About my problem we handle with 2 table :

    1-tblorder 2- tblorderDetail

    they relation with a foreign key “Order ID”. When a customer order some goods,his name,date,... as order and list of goods,name,count,price,.. that he/she buy as details of his order insert to data Base :

    In 2 table first general information(date,customer name,…) insert in tblOrder and give orderID as a foreign key for tblOrderDetail to insert detail of this order (goods name,count,…)each order has several order detail.

    Problem:

    I want insert object of tblOrderDetail immediate after Inserting in tblOrder in a unique store procedure (pass an object of tblOrderDetail  as a table type contain records that must insert in tblOrderDetail)

    This procedure couldn’t add to my LINQ 2 SQL class (dbml class)-

    Please help me or offer me a way!!!

    What is your Opinion about entity framework or transaction with LINQ or transaction in LINQ.

    my query:

     create proc [dbo].[sptblPaymentDetailInsertTabel]

    @MyTableParam tblorderDetailType readonly,

    @Date varchar(10),

    @Description nvarchar(250),

    @orderType tinyint,

    as

    begin

    declare @ordertype int

    set @ordertype=ISNULL((SELECT MAX(orderNo)  FROM tblorder WHERE  orderType=@ordertType), 0) + 1

    INSERT INTO tblorder

                          (Date, Description, orderType, orderNo)

    VALUES     (@Date

               ,@Description

               ,@orderType

               ,@paymnettype)

                   select @@identity as id

          insert into tblOrderDetail(orderID,custumerId,date,sellman)

          select (select max(orderID)from tblOrderDetail),@custumerId,@date,@sellman   

    from @MyTableParam

          end

    Kind regards

    atefe.naeini 


    linq msdn


    • Edited by atefe naeini Tuesday, July 24, 2012 5:54 AM titel mistake
    Tuesday, July 24, 2012 5:20 AM

Answers

  • Hi,

    In that case you can simply put all of your code in transaction as

    BeginTran

    End

    So that either all of the data will be saved or none.

    Tuesday, July 24, 2012 9:21 AM

All replies

  • Hi,

    You want to perform the following action using LINQ-

    -> Insert a record into tblOrder

    -> Immediately after inserting a record in tblOrder a new record in tblOrderDetail with the same orderID as in tblOrder

    If this is what you want to perform, you can do it as follows:

    1. Create a object of tblOrder Class and after assigning all the values using entity framework save the object as

    tblOrder obj = new tblOrder();

    obj.Date = date;

    obj.Description = description;

    and so on...

    entity.AddtotblOrder(obj);

    entity.SaveChanges();

    2. After adding the above object create a new object for tblOrderDetail and add it as follows

    tblOrderDetail objDetail = new tblOrderDetail();

    objDetail.OrderID = obj.OrderID;

    objDetail.Qunatity = quantity;

    and so on...

    entity.AddtotblOrderDetail(objDetail);

    entity.SaveChanges();

    This will add the record in the tblOderDetail with the same ID that was generated while entering the value in tblOrder.

    Please tell me if you require any other help..

    Tuesday, July 24, 2012 7:40 AM
  • thank you for your answer

    but my basic problem is insert  multiple record of  tblorderdetail it means pass an object of tblorderdetail as a field to my store procedure to sure that all of my detail save OR non of them not save. I want do tarnsaction : if a record of  tblorderdetail  could not save in database rollback saving order and other order details of this order (whit same orderID)


    linq msdn

    Tuesday, July 24, 2012 8:14 AM
  • Hi,

    In that case you can simply put all of your code in transaction as

    BeginTran

    End

    So that either all of the data will be saved or none.

    Tuesday, July 24, 2012 9:21 AM