Hello i developed BackgroundService which is to monitor Oracle Database and when new records inserted into source table, copy records into SQL Server Database. But after sometime (sometimes 1 days, sometimes 2 days). My service has stopped working. Error says: "A Task was cancelled". I don't understand why my code has stopped working suddenly.
Note: my application hosting on IIS (Windows Server 2012)
Here's my code:
public class MainTransactionService : BackgroundService
{
private readonly ILogger Logger;
private readonly TransactionSettings TSettings;
public MainTransactionService(ILogger logger, TransactionSettings tSettings)
{
TSettings = tSettings;
Logger = logger;
}
protected async override Task ExecuteAsync(CancellationToken stoppingToken)
{
//Create a connection to Oracle
string conString = "";
//How to connect to an Oracle DB without SQL*Net configuration file
// also known as tnsnames.ora.
//"Data Source=orclpdb";
"Data Source=eoffice";
OracleConfiguration.OracleDataSources.Add("eoffice", "");
//How to connect to an Oracle DB with a DB alias.
//Uncomment below and comment above.
//"Data Source=<service name alias>;";
OracleConnection con = new OracleConnection(conString);
Logger.Information("Connected to Oracle");
OracleCommand cmd = con.CreateCommand();
try
{
Logger.Information("Run part executed");
if (con.State != ConnectionState.Open)
{
con.Open();
}
while (!stoppingToken.IsCancellationRequested)
{
await Task.Delay(TSettings.Frequency, stoppingToken);
//Logger.Information(stoppingToken.IsCancellationRequested.ToString());
try
{
var cancelTask = Task.Delay(TSettings.Timeout, stoppingToken);
//Logger.Information("CODE EXECUTED");
//await context.Response.WriteAsync("Server is Listening");
//OracleConfiguration.OracleDataSources.Add("orclpdb", "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = miatdw1.miat.com.mn)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = orclpdb.miat.com.mn)))");
//Check Oracle
Logger.Information("Establishing Oracle Connection (While)");
cmd.CommandText = "select MAX(id) from PB_TRANSACTION";
int SourceTableMaxId = Convert.ToInt32(cmd.ExecuteScalar());
//Check SQL
SqlConnection conn = new SqlConnection("");
SqlCommand check_id = new SqlCommand("select MAX(id) from PB_TRANSACTION", conn);
Logger.Information("Establishing MS-SQL Connection");
conn.Open();
int TargetTableMaxId = Convert.ToInt32(check_id.ExecuteScalar());
if (SourceTableMaxId > TargetTableMaxId)
{
int TotalNewRecords = SourceTableMaxId - TargetTableMaxId;
Logger.Information(TotalNewRecords.ToString() + " New Records Found");
OracleCommand command = new OracleCommand();
command.CommandText = "select * from (select * from pb_transaction order by id desc) x where ROWNUM <= :lastrecord";
command.Parameters.Add(":username", OracleDbType.Int32).Value = TotalNewRecords;
command.Connection = con;
Logger.Information("Retrieved " + TotalNewRecords.ToString() + " from PB_TRANSACTION table.");
Transaction LastTransaction = new Transaction();
using (OracleDataReader oReader = command.ExecuteReader())
{
while (oReader.Read())
{
LastTransaction.Id = Convert.ToInt32(oReader["ID"]);
LastTransaction.type = oReader["TYPE"].ToString();
LastTransaction.account = oReader["ACCOUNT"].ToString();
LastTransaction.journal_id = oReader["JOURNAL_ID"].ToString();
LastTransaction.amount = Convert.ToDouble(oReader["AMOUNT"]);
LastTransaction.currency = oReader["CURRENCY"].ToString();
LastTransaction.posted_date = oReader["POSTED_DATE"].ToString();
LastTransaction.statement_date = oReader["STATEMENT_DATE"].ToString();
LastTransaction.description = oReader["DESCRIPTION"].ToString();
LastTransaction.pnr = oReader["PNR"].ToString();
LastTransaction.pnr_approved = Convert.ToInt32(oReader["PNR_APPROVED"]);
LastTransaction.jid = oReader["JID"].ToString();
LastTransaction.user_id = oReader["USER_ID"].ToString();
LastTransaction.inserted_date = oReader["INSERTED_DATE"].ToString();
SqlCommand scmd = new SqlCommand("insert into PB_TRANSACTION (ID,TYPE, ACCOUNT, JOURNAL_ID, AMOUNT, CURRENCY, POSTED_DATE, STATEMENT_DATE, DESCRIPTION, PNR, PNR_APPROVED, JID, USER_ID, INSERTED_DATE) values(@id,@type,@account,@journalid, @amount,@currency,@posteddate,@statementdate,@description,@pnr,@pnrapproved,@jid,@userid,@inserteddate);", conn);
scmd.Parameters.AddWithValue("@id", LastTransaction.Id);
scmd.Parameters.AddWithValue("@type", LastTransaction.type);
scmd.Parameters.AddWithValue("@account", LastTransaction.account);
scmd.Parameters.AddWithValue("@journalid", LastTransaction.journal_id);
scmd.Parameters.AddWithValue("@amount", LastTransaction.amount);
scmd.Parameters.AddWithValue("@currency", LastTransaction.currency);
scmd.Parameters.AddWithValue("@posteddate", LastTransaction.posted_date);
scmd.Parameters.AddWithValue("@statementdate", LastTransaction.statement_date);
scmd.Parameters.AddWithValue("@description", LastTransaction.description);
scmd.Parameters.AddWithValue("@pnr", LastTransaction.pnr);
scmd.Parameters.AddWithValue("@pnrapproved", LastTransaction.pnr_approved);
scmd.Parameters.AddWithValue("@jid", LastTransaction.jid);
scmd.Parameters.AddWithValue("@userid", LastTransaction.user_id);
scmd.Parameters.AddWithValue("@inserteddate", LastTransaction.inserted_date);
scmd.ExecuteNonQuery();
}
Logger.Information(TotalNewRecords.ToString() + " records inserted");
}
}
else
{
Logger.Information("No New Records found");
}
}
catch (Exception ex)
{
LogError("INNER TRY: " + ex.Message);
}
}
Logger.Information("Oracle Database connection has successfully Disconnected!!!");
}
catch (TaskCanceledException tex)
{
LogError("TASK ERROR: " + tex.Message);
}
catch (Exception ex)
{
LogError("MAIN ERROR: " + ex.Message);
}
finally
{
con.Close();
con.Dispose();
}
}
public async override Task StopAsync(CancellationToken cancellationToken)
{
await base.StopAsync(cancellationToken);
}
private void LogError(string error)
{
Logger.Error(error);
}
}
my application hosting on IIS (Windows Server 2012)
I want to run my application endlessly.
A BackgroundService hosted in ASP.NET in IIS will run as long as your ASP.NET application runs. That's it. Since ASP.NET/IIS will restart periodically, your BackgroundService will be periodically shut down (and restarted). Neither ASP.NET nor IIS were designed for long-lived background processes; they were designed for HTTP services. In the general case (i.e., cloud providers), your app may actually be shut down until the next HTTP request comes in.
If this is not acceptable, then ASP.NET/IIS is not the correct host for your needs. You can use a Win32 Service host instead of ASP.NET/IIS by creating a new project with the Worker Service template, adding a Win32 Service lifetime, and hosting the same BackgroundService in that application instead of in your ASP.NET app.