none
how insert masrter/detailtabels(order and orderDetail )in a tarnsaction by linq,C# and 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


    linq msdn
    Tuesday, July 24, 2012 5:18 AM

Answers

  • hi tony

    thanks for your attention but my question is insert all of my record  together such as:

                dcbankDataContext db = new dcbankDataContext();
                tblBank bank = new tblBank();
                bank.Name = txtbankName.Text;
                foreach (tblBankAccount bankAcc in tblbankAccLIST)
                {
                   bank.tblBankAccounts.Add(bankAcc);
                }
                db.tblBanks.InsertOnSubmit(bank);
                db.SubmitChanges();

    I use taransaction like this one:

     using (var transaction = new TransactionScope())
                {
                    try
                    {
                        clsPayment cls = new clsPayment(this.Name);
                        clsPaymentDetail clsdetail = new clsPaymentDetail(this.Name);
                        payment.Date = txtDate.Text;
                        payment.Description = txtDescription.Text;
                        payment.PaymentType = 1;
                        if (paymentID == 0)//new pay
                        {
                            paymentID = cls.insert(payment);
                            if (paymentID == 0)
                            {
                                Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                return;
                            }
                        }
                        else if (paymentID != 0)//edit pay
                        {
                            if (cls.update(payment) == 0)
                            {
                                Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                return;
                            }
                        }
                        foreach (sptblPaymentDetailSelectResult item in PaymentDetailList)
                        {
                            if (item.PeymantDetailID == 0)//new detail
                            {
                                item.peymentID = paymentID;
                                if (clsdetail.insert(item) == 0)
                                {
                                    Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                    return;
                                }
                            }
                            else if (item.PeymantDetailID != 0)//edit detail
                                if (item.xMode == "-1")
                                {
                                    if (!clsdetail.delete(item.PeymantDetailID))
                                    {
                                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                        return;
                                    }
                                }
                                else
                                    if (clsdetail.update(item) == 0)
                                    {
                                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                        return;
                                    }
                        }
                        this.DialogResult = DialogResult.OK;
                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertsucssed)));
                        transaction.Complete();
                    }
                    catch
                    { 
                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                        return;
                    }


    linq msdn

    • Marked as answer by atefe naeini Wednesday, July 25, 2012 9:53 AM
    Wednesday, July 25, 2012 9:46 AM

All replies

  • Hi atefe,

    Please refer to the following article about how to call the stored procedure from linq to sql:

    Hope this could help you.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 25, 2012 8:30 AM
    Moderator
  • hi tony

    thanks for your attention but my question is insert all of my record  together such as:

                dcbankDataContext db = new dcbankDataContext();
                tblBank bank = new tblBank();
                bank.Name = txtbankName.Text;
                foreach (tblBankAccount bankAcc in tblbankAccLIST)
                {
                   bank.tblBankAccounts.Add(bankAcc);
                }
                db.tblBanks.InsertOnSubmit(bank);
                db.SubmitChanges();

    I use taransaction like this one:

     using (var transaction = new TransactionScope())
                {
                    try
                    {
                        clsPayment cls = new clsPayment(this.Name);
                        clsPaymentDetail clsdetail = new clsPaymentDetail(this.Name);
                        payment.Date = txtDate.Text;
                        payment.Description = txtDescription.Text;
                        payment.PaymentType = 1;
                        if (paymentID == 0)//new pay
                        {
                            paymentID = cls.insert(payment);
                            if (paymentID == 0)
                            {
                                Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                return;
                            }
                        }
                        else if (paymentID != 0)//edit pay
                        {
                            if (cls.update(payment) == 0)
                            {
                                Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                return;
                            }
                        }
                        foreach (sptblPaymentDetailSelectResult item in PaymentDetailList)
                        {
                            if (item.PeymantDetailID == 0)//new detail
                            {
                                item.peymentID = paymentID;
                                if (clsdetail.insert(item) == 0)
                                {
                                    Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                    return;
                                }
                            }
                            else if (item.PeymantDetailID != 0)//edit detail
                                if (item.xMode == "-1")
                                {
                                    if (!clsdetail.delete(item.PeymantDetailID))
                                    {
                                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                        return;
                                    }
                                }
                                else
                                    if (clsdetail.update(item) == 0)
                                    {
                                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                                        return;
                                    }
                        }
                        this.DialogResult = DialogResult.OK;
                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertsucssed)));
                        transaction.Complete();
                    }
                    catch
                    { 
                        Msg.Show(clsGlobal.messagebox((int)(clsGlobal.messagetype.insertfailed)));
                        return;
                    }


    linq msdn

    • Marked as answer by atefe naeini Wednesday, July 25, 2012 9:53 AM
    Wednesday, July 25, 2012 9:46 AM
  • Hi atefe

    It’s glad to see this issue has been resolved.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, July 26, 2012 3:06 AM
    Moderator