system.Transcation versus ADO transaction

[Guest]

I code this to show to my boss that it is possible to rollback some parts of
an ADO transaction.

Dim strCon As String = "Data Source=Q09073\SQLEXPRESS;Initial
Catalog=Bank;Integrated Security=True;"
Dim oConn As New SqlConnection(strCon)
Dim IdClient As Integer
Dim IdBankAccount As Integer

oConn.Open()


Dim oTransaction As SqlTransaction = oConn.BeginTransaction()

Try

For i s Integer = 1 To 5 'Clients

oTransaction.Save("Client" + i.ToString)

Dim oCommClient As New SqlCommand("INSERT INTO Client
(NameClient) VALUES ('Name" + i.ToString + "');SELECT SCOPE_IDENTITY() AS
[SCOPE_IDENTITY];", oConn, oTransaction)


IdClient = CInt(oCommClient.ExecuteScalar)


Dim oCommBankAccount As New SqlCommand("INSERT INTO
BankAccount (IdClient, Amount) VALUES (" + IdClient.ToString + ", 100);SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY];", oConn, oTransaction)
IdBankAccount = CInt(oCommBankAccount .ExecuteScalar)


oTransaction.Save("transaction" + i.ToString)


Dim oCommOutput As New SqlCommand("INSERT INTO Transactions
( IdBankAccount , Type, Amount, DateTransaction) VALUES (" + IdBankAccount
..ToString + ", 'O', 20, '" + Now.ToString + "');", oConn, oTransaction)

oCommOutput.ExecuteNonQuery()

oCommCompte.CommandText = "UPDATE BankAccount SET Amount =
Amount - 20 where IdBankAccount = " + IdBankAccount .ToString

oCommCompte.ExecuteNonQuery()


Dim oCommCredit As New SqlCommand("INSERT INTO Transactions
( IdBankAccount, Type, Account, DateTransaction) VALUES (" +
IdBankAccount.ToString + ", 'C', 10, '" + Now.ToString + "')", oConn,
oTransaction)

oCommCredit.ExecuteNonQuery()

oCommCompte.CommandText = "UPDATE BankAccount SET Amount =
Amount + 10 where IdBankAccount = " + IdBankAccount .ToString

oCommCompte.ExecuteNonQuery()

'I rollback client 3 with is transactions in is bank account
If i= 3 Then
oTransaction.Rollback("Client" + i.ToString)

End If

'I rollback the output and credit of client 5
If i= 5 Then
oTransaction.Rollback("transaction" + i.ToString)
End If
Next

oTransaction.Commit()

Catch ex As Exception

oTransaction.Rollback()

End Try

At the end I have this
Name1 with 90$
Name2 with 90$
Name4 with 90$
Name5 with 100$

It work perfectly but my boss want me to use System.Transactions
Is it possible to do this with the new System.Transactions in .NET 2.0? If
yes, where I can fond some good exemple?

 

[David Browne]

I code this to show to my boss that it is possible to rollback some parts
of
an ADO transaction.
....

It work perfectly but my boss want me to use System.Transactions
Is it possible to do this with the new System.Transactions in .NET 2.0? If
yes, where I can fond some good exemple?

Yes it's possible. But since you typically don't see the SqlTransaction
object when using System.Transactions, the easiest thing is to manage your
savepoints using TSQL. Instead of Tran.Save and Tran.Rollback, just send
"save transaction foo" and "rollback transaction foo" to the SQL Server.

EG

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Schema;
using System.Transactions;

namespace csTest
{
class Program
{

static void Main(string[] args)
{

string constr = "Data Source=(local);Integrated Security=true;Initial
Catalog=AdventureWorks";

using (TransactionScope scope = new TransactionScope())
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();


new SqlCommand("create table #t(id int primary key)",
con).ExecuteNonQuery();
new SqlCommand("insert into #t(id) values (1)",
con).ExecuteNonQuery();
new SqlCommand("save transaction foo", con).ExecuteNonQuery();

new SqlCommand("insert into #t(id) values (2)",
con).ExecuteNonQuery();
new SqlCommand("rollback transaction foo", con).ExecuteNonQuery();

using (SqlDataReader r = new SqlCommand("select * from #t",
con).ExecuteReader())
{
while (r.Read())
{
Console.WriteLine(r["id"]);
}
}

scope.Complete();
}

Console.WriteLine("Hit any key to exit.");
Console.ReadKey();
}
}
}

David