0 Let me give you the sample code for the above example:
string connectionString = "YOUR_CONNECTION_STRING";
using (SqlConnection myConnection = new SqlConnection("connectionString")
{
connection.Open();
using (SqlTransaction myTransaction = connection.BeginTransaction())
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.Transaction = myTransaction;
try
{
// Subtract Rs.1000 from the Nethra's account balance.
myCommand.CommandText = "UPDATE Accounts SET Balance = Balance - 1000 WHERE Customer = 'Nethra'";
myCommand.ExecuteNonQuery();
// Increment the Satish's account balance by Rs.1000.
myCommand.CommandText = "UPDATE Accounts SET Balance = Balance + 1000 WHERE Customer = 'Satish'";
myCommand.ExecuteNonQuery();
// If everythign is OK, then commit transaction
myTransaction.Commit();
}
catch (Exception ep)
{
// If anything goes wrong, Rollback to previous state.
myTransaction.Rollback();
}
}
}
If you are targetting your application for .NET Framework 2.0 or above, you can use TransactionScope to simplify the things.
0 Hi Satish,
Thanks for the explanation.. Very well explained. May i request you to please give an example where you have used sqltransaction class members. It will be helpful to understand even better
0 0 Hi Datta,
All these are definitions and explanations.. In some cases we do not use this sqltransactionclass members.. But in some cases we use it,i just want to know the scenario where i can use it... With a suitable example
0 Hi
Commit:-
* Purpose
Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.
Until you commit a transaction:
You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.
You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK.
Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.
You can also use this statement to
Commit an in-doubt distributed transaction manually
Terminate a read-only transaction begun by a SET TRANSACTION statement
Oracle recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle Database. If you do not explicitly commit the transaction and the program terminates abnormally, then the last uncommitted transaction is automatically rolled back.
Example:-
private OracleCommand cmd;
private OracleConnection con;
cmd.Connection = con;
if (con.State != ConnectionState.Open)
{
con.Open();
}
moTransaction = con.BeginTransaction();
moTransaction.Commit();