Q:
I face the following problem two weeks ago , and i don't know how to handle it taking the performance issues , the data integrity in consideration.
What I do is:
I wanna to migrate the data from the XML file to its similar tables in my database.
for example :
i have two nodes (XML file):
courses , teachers
Two tables (database)
courses , teachers.
I allow the user to upload the XML file to a folder on my server and i begin to read the XML file and insert the data into my database .
The problem is:
if some failure happens during the insertion operation , i wanna to delete all the inserted records in all tables.(or roll back).
I begin to think about transaction , the insertion of each entity will be performed through transaction but i face two problems:
Should i put all insertions of all entities in one transaction or one entity by one in transaction?(all entities data must be all inserted or no insertion at all )for each uploader.
When i have huge number of records say(1500 record).the following exception appear:
This IfxTransaction has completed; it is no longer usable,no one fixes it.
My team leader told me not to use the transaction , because it will lock the tables and many users use those tables. he wanna some other mechanism .
please i wanna a solution to my problem(detailed explanation), How to handle this case and maintain the performance issues and the data integrity and consistency .
I would suggest using SqlBulkCopy. You can google, or read these 2 articles: