What TransactionScope is
Yesterday I was stuck with some logic to maintain a single transaction in a multi DML operation in SQL Server and Oracle Database. I mean to say that I had to set a transaction on a single click where an insert or update was to be done in a SQL database and an insert or update was to be done in a Oracle Database. After extensive research I finally got the solution from the MSDN. I want to explain my experience with a small example.
Namespace Name: System.Transactions.TransactionScope
Definition: TransactionalScope makes your code block Transactional.
You can easily maintain one transaction for multiple databases or a single database with multiple connectionstrings, using TransactionScope.
When you use TransactionScope there is no need to close any Database connections in the middle.
Just make an object of the TransactionScope class with using. Write all your code in this block and after completion of all your operations call "objTransactionscope.complete()". Ensure one thing; you should write a try/catch block in the TransactionScope block.
Syntax to use TransactionScope
using (TransactionScope transactionScope = new TransactionScope())
{
try
{
method1() // Oracle DML operation
method2() // Sql DML operation
method3() // Oracle DML operation
// transactionScope.complete()
// transactionScope.Dispose();
}
catch (TransactionException ex)
{
transactionScope.Dispose();
MessageBox.Show("Transaction Exception Occured");
}
}
Now if an exception occurrs in method1(), method2() or method3() then it will catch the exception in the catch block and all the operations will be rolled back automatically. There is no option to call a rollback manually in TransactionScope.
Take care of one thing, that there is no connection.close() or dispose() in any methods (1, 2 or 3) otherwise it will give you the error:
"the transaction has aborted"
when you call transactionScope.Dispose() after completion of all your objects that are used in this TransactionScope dispose.
Now I will explain a simple scenario for a better understanding.
Suppose I have a table in SQL Server named TestSql that contains the 3 columns Name, Salary and Status.
CREATE TABLE [dbo].[TESTSql](
[Name] [varchar](200) NULL,
[Salary] [int] NULL,
[Status] [varchar](50) NULL
)
Name contains emp name, salary contains emp salary and status contains nothing initially, in other words NULL.
I have another table in an Oracle database named TestOracle. It also contains the 3 columns Name, Salary, Srlno.
CREATE TABLE TESTORACLE
(
NAME VARCHAR2(200 BYTE),
SALARY INTEGER,
SRLNO INTEGER
)
Name and salary contains the same info as the Sqlserver table. Here Srlno is an autogenerated number that shows how many rows I currently have.
In this example, I have some emp records in SQL Server table. I need to upload this data into the Oracle Server. If the data insertion in the Oracle Server is successful then I update the status in the SQL server table with "UPD".
I have created a form where two TextBoxes are placed. There you need to enter your Oracle connection string and Sqlserver in the proper manner. When you press the "GO" button if everything occurs correctly then you will get a success msg.
If you get any error like Unable to load DLL (oramts.dll)
Then you need to go to the Oracle folder (where Oracle is installed) and find the DLL oramts.dll and copy and paste this file into the system32 folder in the C drive.
Code on Button_Click
using (TransactionScope transactionScope = new TransactionScope())
{
try
{
string SqlString = txtSqlConn.Text.Trim();
string OraString = txtOracleConn.Text.Trim();
//-------- Get data from Sql server --------
DataSet TotData = new DataSet();
TotData = DbSettings.GetDataSetSQL(SqlString, "Select * from TestSql");
for (int i = 0; i < TotData.Tables[0].Rows.Count; i++)
{
DataSet Oracleds = new DataSet();
Oracleds = DbSettings.GetDataSetOracle(OraString, "Select NVL(MAX(SRLNO),0)+1 from TestOracle");
int k = DbSettings.ExecuteNonQueryOracle(OraString, "Insert into TestOracle(NAME,SALARY,SRLNO) values('" + TotData.Tables[0].Rows[i][0].ToString() + "'," + Convert.ToInt32(TotData.Tables[0].Rows[i][1]) + "," + Convert.ToInt32(Oracleds.Tables[0].Rows[0][0]) + ")");
if (k == 1)
{
int j = DbSettings.ExecuteNonQuerySQL(SqlString, "Update TestSql Set status='UPD' where Name='" + TotData.Tables[0].Rows[i][0].ToString() + "'");
}
}
transactionScope.Complete();
transactionScope.Dispose();
MessageBox.Show("Successful");
}
catch (TransactionException ex)
{
transactionScope.Dispose();
MessageBox.Show("Transaction Exception Occured");
}
catch (Exception ex1)
{
MessageBox.Show(ex1.Message);
}
}
I think you will find this article useful. Thanks for reading.