CODEWITHSUNDEEP
c#sql-servertransactionscope
CSharpGood_VBSucks
CSharpGood_VBSucks

Reputation: 57

TransactionScope Primary Key Insert then Foreign Key Insert -> Inserts 2 Primary Key Rows

I am trying to create a large transactionScope that is all or nothing. I am inserting about 2000 records. I want to insert the primary key, read it back and then insert the foreign key in a transaction and then roll everything back if an error occurs.

PSS_InvoiceTotal Table has PK -> InvoiceTotalID

PSS_Invoices Table has a FK -> InvoiceTotalID

The problem is in the database each time 2 Primary Key rows are made. I ran the code without the Foreign Key insert and it worked fine. Is there a way to prevent it from making 2 Primary Key rows?

        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
        {
            try
            {
                conn.Open();

                foreach (var uniqueSupply in supplyList.GroupBy(a=>a.ShipTo))
                {
                    //reset total 
                    total = 0;

                    var supplyListByShipTo = supplyList.Where(a => a.ShipTo == uniqueSupply.Key);

                    foreach (var addSupply in supplyListByShipTo)
                    {
                        total = total + addSupply.Amount;
                    }

                    StringBuilder insert_PSS_InvoiceTotal = new StringBuilder();

                    //save total to DB
                    insert_PSS_InvoiceTotal = new StringBuilder();
                    insert_PSS_InvoiceTotal.Append("INSERT INTO [PSS_InvoiceTotal] ");
                    insert_PSS_InvoiceTotal.Append("([InvoiceDate],[Amount]) ");
                    insert_PSS_InvoiceTotal.Append("VALUES(@DateTime, @Amount) ");
                    insert_PSS_InvoiceTotal.Append("SELECT SCOPE_IDENTITY() AS [InvoiceTotalID];");

                    cmd = new SqlCommand(insert_PSS_InvoiceTotal.ToString(), conn);

                    cmd.Parameters.AddWithValue("@DateTime", DateTime.Now);
                    cmd.Parameters.AddWithValue("@Amount", total);

                    //ToDo: Add back in later
                    cmd.ExecuteNonQuery();

                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        InvoiceTotalID = Convert.ToInt32(dr["InvoiceTotalID"].ToString());
                    }

                    dr.Close();

                    foreach (var supply in supplyListByShipTo)
                    {
                        StringBuilder insert_PSS_Invoice = new StringBuilder();

                        //Create the SQL command
                        insert_PSS_Invoice.Append("INSERT INTO [PSS_Invoices] ");
                        insert_PSS_Invoice.Append("([ClientDetailId],Amount],InvoiceTotalId)");
                        insert_PSS_Invoice.Append("VALUES(");
                        insert_PSS_Invoice.Append("@Amount,@InvoiceTotalId)");

                        cmd = new SqlCommand(insert_PSS_Invoice.ToString(), conn);

                        cmd.Parameters.AddWithValue("@Amount", 4.44);
                        cmd.Parameters.AddWithValue("@InvoiceTotalId", InvoiceTotalID);

                        //ToDo: put back in later
                        cmd.ExecuteNonQuery();
                    }
                }

                //Close connection
                conn.Close();

                //Commit and Dispose Transaction
                scope.Complete();
                scope.Dispose();
            }

            catch (Exception ex)
            {
                //Rollback Transaction
                scope.Dispose();
                return Json(new { success = false, message = ex.ToString() }, JsonRequestBehavior.AllowGet);
            }
        }

Upvotes: 1

Views: 677

Answers (2)

ErnestoDeLucia
ErnestoDeLucia

Reputation: 49

May be you can look into using the OUTPUT option of the INSERT command to get the Primary Key on the insert execution. That way you don't have to read it back. Check out this link: https://msdn.microsoft.com/en-us/library/ms177564.aspx

In addition, you may need to add an output parameter to your command object to get that information.

Upvotes: -1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

try to comment 

 //ToDo: Add back in later
 //cmd.ExecuteNonQuery();

Upvotes: 0

Related Questions