Executing multiple statements / sqlcomands within a single ado.net transaction

Question

I'm not including any code here because I've re-written it so many times over the past few days its really not worth bothering with any more, so I'll explain my problem in generic terms.

I have a winforms application written in C# and connected to a sql server database. In certain parts of my application I can have as many as 10 SQL command operating on numerous tables within the database, and which I need to contain within a single transaction, because it is an 'all or nothing' situation. If any one of the 10 commands fails, I want non of the commands to be committed.

Complicating factors include:

Each of the 10 sqlcommands have multiple parameters
Some of the sqlcommands use INSERT and I need to use the server generated identity in some of the subsequent sqlcommands.
Some of the commands operate on the same data i.e. an early command might INSERT a new item in the database and one of the later sqlcommands will attempt to update that same entry.

Any help would be most appreciated as I am at my wits end with this issue having spent most of the week on it and feeling like relatively little progress has been made.

EDIT:

The sqlcommands were originally in different classes etc, however I though that might be the problem so I did a big re-write and now ALL of the sqlcommands are within the same method, albeit some of the sqlcommands are written elsewhere and passed back to the main method.

The problem at the moment is that, the first command (INSERT) is executed, but the transaction fails at the second command because that one attempts to refer to the database entry created in the first command.

Also I cannot use TransactionScope because MSDTC on the network causes other issues and so I need to handle this with my application and ADO.NET.

EDIT (SOME CODE)

Here is just the first two sqlcommands within the transaction:

string connectionString = aSystem.ConnectionString;

    using (SqlConnection linkToDB = new SqlConnection(connectionString))
    {
        linkToDB.Open();
        using (SqlTransaction transaction = linkToDB.BeginTransaction())
        {
            try
            {                            
                //...Case...//

                cCase c = new cCase();
                c.CaseType = cboCaseType.Text;
                c.Occupation = txtOccupation.Text;
                c.DateInstructed = txtDateInst.Text;
                c.Status = "Live";
                SqlCommand sqlSaveCase = c.SaveSqlCom();                               
                sqlSaveCase.Connection = linkToDB;
                sqlSaveCase.Transaction = transaction;
                c.SetCaseNo = sqlSaveCase.ExecuteNonQuery().ToString();

                //...IP Link...//

                string strIPID = cboIPAddress.SelectedValue.ToString();
                SqlCommand sqlNewIPLink = cIPID.NewIPLinkSqlCom(strIPID,c.CaseNo,txtIPRef.Text);
                sqlNewIPLink.Connection = linkToDB;
                sqlNewIPLink.Transaction = transaction;
                sqlNewIPLink.ExecuteNonQuery();                //...fails here...//


                //...an additional 8 sql commands follow...//

ISSUE

The sqlSaveCase command INSERTS a new case on tblCases, but when the second command sqlNewIPLink attempts to use the Identity created by the first, it generates a Foreign Key error as if it does not see the newly created Case from the first command.

Are the 10 commands in diferent places in the code. i.e. diferent classes, instances, etc? — Marcelo De Zen, Jul 28 '12 at 09:03
It's really not clear what *exactly* your problems are. Why you cannot use `SqlTransaction`? Perhaps try splitting this into more questions with each one treating a *specific* problem that can be easily demonstrated by a *simple* code sample. — Nikola Anusev, Jul 28 '12 at 09:18
Your sample code actually works (although seems you have a design problem). What exactly is not working? — Marcelo De Zen, Jul 28 '12 at 09:47
The sqlSaveCase command INSERTS a new case on tblCases, but when the second command sqlNewIPLink attempts to use the Identity created by the first, it generates a Foreign Key error as if it does not see the newly created Case from the first command. — PJW, Jul 28 '12 at 09:48

score 3 · Accepted Answer · answered Jul 28 '12 at 10:29

3

The error was this line

c.SetCaseNo = sqlSaveCase.ExecuteNonQuery().ToString();

which should be using ExecutreScalar() in order to retrieve the ID from the INSERT command. The clue came when I eventually realised it was always returning 1, which I assume is probably just a boolean 'true' to say the command was executed. Once I used the ExecuteScalar on the command instead, it started to returning more meaningful IDs which subsequent SqlCommands did recognise and hence no Foregin Key issues.

answered Jul 28 '12 at 10:29

PJW

5,197
19
60
74

"probably just a boolean 'true' to say the command was executed" Actually it is the number of rows affected by the last command. 1 row inserted. Less useful for `INSERT... VALUES(...)` where you know how many values you sent, more useful for `UPDATE... WHERE ...` – Ben Voigt Nov 02 '20 at 22:05

score 1 · Answer 2 · answered Jul 28 '12 at 10:28

No sure if this is the problem, but I don't see where you're you using c.SetCaseNo on the second SqlCommand.

  //
  // here you are setting c.SetCaseNo
  //
  c.SetCaseNo = sqlSaveCase.ExecuteNonQuery().ToString();

  string strIPID = cboIPAddress.SelectedValue.ToString();

  //
  // but here you're using c.CaseNo
  //
  SqlCommand sqlNewIPLink = cIPID.NewIPLinkSqlCom(strIPID,c.CaseNo,txtIPRef.Text);


  sqlNewIPLink.Connection = linkToDB;
  sqlNewIPLink.Transaction = transaction;
  sqlNewIPLink.ExecuteNonQuery();

Thanks for that - just posted the answer which was the ExecuteNonQuery instead of ExecuteScalar. The SetNewCase was just a second access modifier for CaseNo which bypasses the validation built into my class. — PJW, Jul 28 '12 at 10:30

score 0 · Answer 3 · answered Jul 28 '12 at 09:10

0

You can use TransactionScope Class for it.

here is the good explanation of it.

hope this helps.

answered Jul 28 '12 at 09:10

Manish Parakhiya

3,732
3
22
25

score 0 · Answer 4 · answered Jul 28 '12 at 09:47

in your case, it is a good practice to use command pattern. In fact, it allows you undo your operations. Transaction is good for few operations.But with 10 operations, it better to execute each command on a loop and if one command fail, undo command. It implies that you implement rollback operation (here 10). here a link with pattern sample :http://www.dofactory.com/Patterns/PatternCommand.aspx

Executing multiple statements / sqlcomands within a single ado.net transaction

4 Answers4