Help needed for Transaction Support in SQL server 2005

Hi,

I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.

my data layer methods somewhat looks like

public void Save(order value)
{
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required))
{
int orderId = SaveOrderMaster(value);
value.OrderId = orderid;
int childId = SaveOrderDetails(value);
//complete the transaction
transactionScope.Complete();
}
}

here
1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.

2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".

My Problem:
Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.

The problem may occur if your code calls the second procedure before the first procedure is done (or before the row has been added to the parent table). This can happen, because in your code you have not ensured the second procedure to call after the first procedure is completed. So either you can implement this logic in your code, or you can make use of trigger functionality in SQL. For example you can use an INSERT trigger on the parent table to automatically do the same thing as your second procedure did:

CREATE TRIGGER trg_InsOrdDetails ON Orders FOR INSERT
AS
--do your insert here, you can also call the second procedure

go

For more information about SQL trigger, you can refer to this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||Jay

But those calls are from Businss Layer Dll which written in .NET. So when SaveOrderMaster(value) call returns it should update the record in database. because SaveOrderMaster(value) calls one stored procedure that returns the id.

so call is flowing in the following order:

UI calls BLL.Save(value) -->
BLL calls DAL.SaveOrderMaster(value) -->
DAL calls Stored Procedure and set the newly created id in value object.
BLL Calls DAL.SaveOrderDetails(values) -->
DAL calls stored procedure with the details and Id (created in SaveOrderMaster call).
and I am getting the foreignKey error while making call to SaveOrderDetails().

But I am not able to find the source of error. as this is not repeatative behaviour, some time it ouccurs after 5000 BLL.Save() calls.

I also cant use triggers here, as order detail data is huge. and i dont want to call my pass so many data to stored procedure in one go. (aka. business and design requirement -:) )

any help in this regard is highly appriciated?|||

When you have the error, what's the orderId in the parent table, and does it look like what you'd expect? Also, are you using identity(), or Scope_identity to get the key? If you're not using scope_identity(), then that could be your problem.

|||I am using the Scope_identity to retrive the value for the Order Table identity column.
and if i remove the Transaction, My SaveOrder method save the Master table entry while some time I am getting error while saving order details.|||

The TransactionScope class performs none atomic transactions which is not legal so pass your code to T-SQL transaction block and your problem will go away. Hope this helps.

http://www.codeproject.com/database/sqlservertransactions.asp

http://msdn2.microsoft.com/en-us/library/ms190295.aspx