How to Kill Oracle Session when network gets disconnected in Windows Form C# Application (Working with ODP.NET)

[Muslim Abbas]

I am working on Transaction Management of multiple inline queries if Connection Loss between any query all other transaction should be rollback which forks fine but exception throws ORA-12571 encountered.




public void SaveRole()
{
OracleTransaction Trans;
OracleConnection Constring = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings["ConString"].ToString());
using (Constring)
{
if (Constring.State == System.Data.ConnectionState.Closed) Constring.Open();

Trans = Constring.BeginTransaction();

//LogId = AL.GenerateLog();
try
{

string query = "";
if (isUpdate)
{
query = @"update user_role ur set ur.description = '" + RoleDescription + "', ur.post = '" + Post + "' , ur.log_id = '" + LogId + "' where ur.role_id = '" + RoleId + "'";
db.ExecuteQuery(query, Constring, Trans);
}
else
{
query = @"insert into user_role values('" + RoleId + "', '" + RoleDescription + "', '" + Post + "', '" + LogId + "') ";
db.ExecuteQuery(query, Constring, Trans);
}
SaveRoleDetail(Constring, Trans);
Trans.Commit();
Constring.Close();
}
catch (Exception ex) { Trans.Rollback(); OracleConnection.ClearPool(Constring); Constring.Close(); throw new Exception(ex.Message); }
}
}
public void SaveRoleDetail(OracleConnection conString, OracleTransaction Trans)
{
try
{
string Query = @"delete from Role_Detail rd where rd.role_id = '" + RoleId + "'";
db.ExecuteQuery(Query, conString, Trans);

foreach (DataRow row in dtAssignedAuthority.Rows)
{
Query = @"insert into Role_Detail values('" + RoleId + "', '" + row["Trn_Code"] + "' , 'VIEW', '0')";
db.ExecuteQuery(Query, conString, Trans);
}
}
catch (Exception ex) { throw new Exception(ex.Message); }
}

Continue reading...