Please tell me how to save data in two tables using 1 transaction.

Question

0.00/5 (No votes)

See more:

I want to save data into a Primary table and into a secondary table. They have foreign Key relationship. Primary table will have only one row entry at a time but Secondary table will have many rows entered for the primary key.
I want to use transactions for this purpose so either all the data is entered on none.
I know how to use transactions but for single query not for these complex one's

Primary Table Structure

TicketNo , HeadName, ValidFrom , ValidTo , UnitPrice , NoOfPerson , TotalPrice

Secondary Table Structure

TicketNo , Name

I have made procedures for entering data into these two tables.This is what i have done upto now

C#

trns = con.BeginTransaction();
               ans = obj.SaveTicketDetails(trns);

if (ans>0) // i.e if nonquery gives result as 1


<pre>for (int i = 1; i < Member.Count; i++)
                    {
                        objMem.Visitorname = Name[i];
                        

                        ans1 = objMem.SaveGroupMembers(trns);
                        

                       

                    }

                      if (ans1)> 1)
                        {
                            flag = false;
                            trns.Rollback();
                        }

Posted 2-Sep-12 22:28pm

Aakash Sharma

Updated 2-Sep-12 22:29pm

v2

Add a Solution

Comments

Karwa_Vivek 3-Sep-12 5:32am

Are you Using GridView to Save the Details ...?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**AmitGajjar** · Answer 1 · 2012-09-02T22:34:00

Solution 2

Hi,

Instead of creating C# Transaction i suggest you to create transaction in your StoredProcedure.

Please refer : SQL Server Transactions and Error Handling[^]

Thanks
-Amit Gajjar

Posted 2-Sep-12 22:34pm

AmitGajjar

Comments

Aakash Sharma 3-Sep-12 4:47am

Sir the link you provided was of help but it again tell only about how to save single row into two tables. I am still stuck at saving multiple rows in the second table. i will make a stored procedure to enter data in both the tables but inside the for each loop it will be called many time and data in primary table will be entred for each vlaue in secondary table.. :'(

AmitGajjar 3-Sep-12 4:50am

you can pass DataTable in stored procedure and use for loop in your SP.

Below link will guide you to send DataTable in SP. (Credit goes to _Amy)

Sending a DataTable to a Stored Procedure[^]

_Amy 3-Sep-12 5:19am

This much is enough for me Amit. Thanks. :)

AmitGajjar 3-Sep-12 5:37am

Your welcome sir.

tnsk · Answer 2 · 2012-09-02T22:33:00

Better to write trigger on the first table. When first table is update immediately second table will update..

Ex:

SQL

CREATE TRIGGER trig_Update_Employee
ON [EmployeeResult]
FOR INSERT
AS
Begin
    Insert into Employee (Name, Department) 
    Select Distinct i.Name, i.Department 
    from Inserted i
    Left Join Employee e
    on i.Name = e.Name and i.Department = e.Department
    where e.Name is null
End

Mohamed Mitwalli · Answer 3 · 2012-09-02T22:45:00

Solution 3

Hi ,
Check this
how can i insert data in multiple table in one store procedure?[^]
Best Regards
M.Mitwalli

Posted 2-Sep-12 22:45pm

Mohamed Mitwalli

Comments

Aakash Sharma 3-Sep-12 4:52am

sir i went through the link you provided but again will it not insert the same value into Primary and secondary table i.e for each primary entry there will be only one secondary entry. i what i need to do is for each primary entry enter varibale not of rows in secondary table.

Mohamed Mitwalli 3-Sep-12 5:01am

Check this Using Transactions in ADO.NET[^]

Mohamed Mitwalli 3-Sep-12 5:09am

Check this too SqlCommandBuilder
http://www.dotnetperls.com/sqlcommandbuilder[^]
http://www.c-sharpcorner.com/uploadfile/mahesh/commandbuilder-in-ado-net/[^]
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx[^]

Please tell me how to save data in two tables using 1 transaction.

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us