Hi All,
I had already find a solution. See below code:
public string UpdateUploadedXls()
{
OleDbConnection oConn;
SqlConnection sConn;
SqlTransaction trx;
string strConn1 = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString.ToString();
if (!strConn1.ToUpper().Contains("TIMEOUT"))
{
strConn1 = strConn1 + ";Connection Timeout=900; pooling='true';Max Pool Size=900";
}
string strConn2 = ConfigurationManager.ConnectionStrings["ORAConnection"].ConnectionString.ToString();
if (!strConn2.ToUpper().Contains("TIMEOUT"))
{
strConn2 = strConn2 + ";Connection Timeout=900;pooling='true';Max Pool Size=900";
}
try
{
sConn = new SqlConnection(strConn1);
sConn.Open();
trx = sConn.BeginTransaction();
oConn = new OleDbConnection(strConn2);
oConn.Open();
otrx = oConn.BeginTransaction();
}
catch (Exception)
{
return "Connection to database failed.";
}
try
{
#region 'For SQL'
using (cmd = new SqlCommand())
{
cmd.CommandTimeout = 900;
cmd.Connection = sConn;
cmd.Transaction = trx;
StringBuilder sb1 = new StringBuilder();
cmd.CommandText = sb1.ToString();
cmd.ExecuteNonQuery();
}
#endregion
#region 'For Oracle'
using (ocmd = new OleDbCommand())
{
ocmd.CommandTimeout = 900;
ocmd.Connection = oConn;
ocmd.Transaction = otrx;
StringBuilder sb2 = new StringBuilder();
ocmd.CommandText = sb2.ToString();
ocmd.ExecuteNonQuery();
}
#endregion
trx.Commit();
otrx.Commit();
}
catch (Exception)
{
trx.Rollback();
otrx.Rollback();
return "Uploading process aborted, Transaction were rolled back";
}
finally
{
cmd.Dispose();
ocmd.Dispose();
sConn.Close();
oConn.Close();
otrx.Dispose();
trx.Dispose();
}
return string.Empty;
}