public void ExecuteSqlTran(Hashtable SQLStringList) { using (AseConnection connection = new AseConnection(this.connectionString)) { connection.Open(); using (AseTransaction transaction = connection.BeginTransaction()) { AseCommand cmd = new AseCommand(); try { foreach (DictionaryEntry entry in SQLStringList) { string cmdText = entry.Key.ToString(); AseParameter[] cmdParms = (AseParameter[])entry.Value; this.PrepareCommand(cmd, connection, transaction, cmdText, cmdParms); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); transaction.Commit(); } } catch { transaction.Rollback(); throw; } } } }
/// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ /// <summary> </summary> /// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ public int AddAndEnrichClaims(ClaimsSubmission req, string tableName, string httpContextUser) { int claimsInserted = 0; TableName = tableName; req.SetClaims(); // Build claim list from string input SybaseConnString = HomeController.SybaseConnString; using (var conn = new AseConnection(SybaseConnString)) { AseTransaction trans = conn.BeginTransaction(); // START LOCKING TABLE HERE!!! try { conn.Open(); AseCommand cmd; //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ // INSERT NEW CLAIM DELETIONS TO STAGING TABLES //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ foreach (var claim in req.ClaimList) { try { cmd = new AseCommand("SP_XC_CLAIM_REJECTION_INSERT", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new AseParameter("@TABLE_NAME", TableName)); cmd.Parameters.Add(new AseParameter("@CLCL_ID", claim.ClaimId)); cmd.Parameters.Add(new AseParameter("@CREATED_BY_USER", httpContextUser)); if (cmd.ExecuteNonQuery() > 0) { claimsInserted++; } else { req.InvalidClaimList.Add(claim.ClaimId); } } catch (Exception e) { if (e.ToString().ToUpper().Contains("DUPLICATE KEY ROW")) { req.DupClaimList.Add(claim.ClaimId); } System.Console.WriteLine("**ERROR INSERTING CLAIMS** : {0}", e.ToString()); } } //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ // NOW ENRICH THE NEWLY INSERTED CLAIM DELETIONS //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ if (TableName.ToUpper().Contains("NPI")) { cmd = new AseCommand("SP_XC_CLAIM_REJECTION_ENRICH_NPI", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); } else if (TableName.ToUpper().Contains("NAME")) { cmd = new AseCommand("SP_XC_CLAIM_REJECTION_ENRICH_NAME", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); } else if (TableName.ToUpper().Contains("TIN")) { cmd = new AseCommand("SP_XC_CLAIM_REJECTION_ENRICH_TIN", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); } trans.Commit(); // COMMIT THE TRANSACTION } catch (Exception e) { Console.WriteLine("*****************************************"); Console.WriteLine("INSERT ERROR!! --ROLLBACK TRANSACTION--"); Console.WriteLine("*****************************************"); Console.WriteLine(e.ToString()); trans.Rollback(); // **** Abort transaction **** throw e; } } return(claimsInserted); }