I have a base class which implements my database connection. I have a second class that inherits this base database class. The second class has some recursion inside of it, where when evaluating it's value, it may instantiate another instance of the second class. The recursion is only a few levels deep. I am running everything single-threaded.
My code will run correctly for about 1 or 2 minutes, then I begin getting consistent errors: "Timeout Expired. The timeout period elapsed prior to obtaining a connection from the pool".
My base class has a destructor which calls the .Dispose() method on the database objects. My second class has a destructor which closes the connection object in the base class.
My connection string to the database specifies a connection timeout = 0.
Any ideas as to why the code will work correctly for a few minutes and then begin timing out trying to connect to the database? I'm baffled.
namespace BaseLib2
{
public class TSBase
{
protected StreamWriter logFile;
protected OleDbCommand queryCmd;
protected OleDbCommand exeCmd;
protected OleDbConnection connection;
protected OleDbDataReader reader;
public SqlConnection sqlconn;//used for BCP
public TSBase()
{
}
~TSBase()
{
try
{
queryCmd.Dispose();
exeCmd.Dispose();
reader.Dispose();
connection.Dispose();
sqlconn.Dispose();
}
catch (Exception ex)
{
Console.WriteLine("BaseLib2 destrutor:" + ex.Message);
}
}
public void ConnectToDB()
{
string connString = "Provider=SQLNCLI11;Server=myserver;Database=mydb;Uid=myid;pwd=password;connection timeout=0";
queryCmd = new OleDbCommand();
exeCmd = new OleDbCommand();
connection = new OleDbConnection(connString);
queryCmd.CommandTimeout = 60000;
exeCmd.CommandTimeout = 60000;
connection.Open();
queryCmd.Connection = connection;
exeCmd.Connection = connection;
string sqlConnString = "server=dc2k8housql;database=mydb;Uid=myid;pwd=password;connection timeout=0";
sqlconn = new SqlConnection(sqlConnString);
sqlconn.Open();
}
public class Expression : BaseLib2.TSBase
{
private string ExpName;
private string ExpressionTxt;
private string sql;
private DateTime Contract_dt;
private DateTime Quote_dt;
private bool SaveToDB;
private string BaseSymbol;
public Expression(string expNameIn, DateTime contract_dtIn, DateTime quote_dtIn)
{
ExpName = expNameIn;
Contract_dt = contract_dtIn;
Quote_dt = quote_dtIn;
try
{
try
{
ConnectToDB();
}
catch (Exception ex)
{
Console.WriteLine("Error in EXP constructor connecting to database." + ex.Message );
throw new Exception("Error in EXP constructor connecting to database.");
}
//get expression text from database
sql = "select expression, save_to_db, coalesce(base_symbol, '') as base_symbol from expressions where exp_name = " + DBI(ExpName);
reader = ReadData(sql);
if (reader.Read())//should only return 1 row
{
ExpressionTxt = reader[0].ToString();
SaveToDB = bool.Parse(reader[1].ToString());
BaseSymbol = reader[2].ToString();
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Exception in Expression constructor:" + ex.Message);
}
}
~Expression()
{
try
{
connection.Close();
sqlconn.Close();
connection.Dispose();
sqlconn.Dispose();
}
catch (Exception ex)
{
Console.WriteLine("Error in destructor:" + ex.Message);
}
}
public double Eval()
{
try
{
//check to see if there are any $RV in the expression
if (ExpressionTxt.Contains("$RV("))
{
//parse and evaluate the $RV's
String[] split = ExpressionTxt.Split(("$".ToCharArray()));
foreach (string s in split){
Console.WriteLine("s=" + s);
if (s.Length > 3)//make sure we have a string with a symbol in it
{
//for each rv we find, create a new expression and evaluate it
if (s.Substring(0, 3).Contains("RV"))
{
int pStart = s.IndexOf("(");
int pEnd = s.IndexOf(")");
string rvSymb = s.Substring(pStart + 1, pEnd - pStart - 1);
System.Console.WriteLine(rvSymb);
Expression oExp = new Expression(rvSymb, Contract_dt, Quote_dt);
double rVal = oExp.Eval();//recursive call
oExp = null;
ExpressionTxt = ExpressionTxt.Replace("$RV(" + rvSymb + ")", rVal.ToString());
}
}
}
}
//replace SV values in formula
if (ExpressionTxt.Contains("$SV("))
{
//find symbols in $SV brackets and collect contract dates
String[] split = ExpressionTxt.Split (("$".ToCharArray()));
foreach (string s in split)
{
if (s.Length > 3)
{//make sure we have a symbol
if (s.Substring(0, 3).Contains("SV"))
{
int pStart = s.IndexOf("(");
int pEnd = s.IndexOf(")");
string svSymb = s.Substring(pStart + 1, pEnd - pStart - 1);
System.Console.WriteLine("sv=" + svSymb);
//replace $SV with numerical values
double sVal = GetQuoteValue(svSymb);
ExpressionTxt = ExpressionTxt.Replace("$SV(" + svSymb + ")", sVal.ToString());
}
}
}
}
//evaluate
double ret = Evaluate(ExpressionTxt);
Console.WriteLine(ExpName + "=" + ret.ToString());
if (SaveToDB)
{
Console.WriteLine(ExpName + " cd:" + Contract_dt.ToShortDateString() + " qd:" + Quote_dt.ToShortDateString() + ": saving to db...");
sql = "delete from exp_quotes where exp_name = " + DBI(ExpName ) ;
sql = sql + " and contract_dt = " + DBI(Contract_dt.ToShortDateString());
sql = sql + " and quote_dt = " + DBI(Quote_dt.ToShortDateString());
WriteData(sql);
sql = "insert into exp_quotes(exp_name, contract_dt, quote_dt, calculated_dt, price) values(";
sql = sql + DBI(ExpName ) + "," + DBI(Contract_dt.ToShortDateString()) + "," + DBI(Quote_dt.ToShortDateString());
sql = sql + ", getdate(), " + ret + ")";
WriteData(sql);
}
connection.Close();//after we evaluate, close down the connection
connection.Dispose();
return ret;
//return value
}
catch (Exception ex)
{
Console.WriteLine("exp:" + ExpName + " cd:" + Contract_dt.ToShortDateString() + " qd:" + Quote_dt.ToShortDateString() + " = " + ex.Message);
}
return 0;
}
private double GetQuoteValue(string symbIn)
{
double ret = 0;
sql = "select close_val from prices_union_all_vw where symbol = " + DBI(symbIn) + " and contract_dt = " + DBI(Contract_dt.ToShortDateString()) + " and quote_dt = " + DBI(Quote_dt.ToShortDateString());
reader = ReadData(sql);
if (reader.Read())
{
ret = Double.Parse(reader[0].ToString());
reader.Close();
}
else
{//we didn't get a record for the specific quote date, try again using the mostrecent view
sql = "select close_val from prices_union_all_mostrecent_vw where symbol = " + DBI(symbIn) + " and contract_dt = " + DBI(Contract_dt.ToShortDateString());
reader = ReadData(sql);
if (reader.Read())
{
ret = Double.Parse(reader[0].ToString());
}
reader.Close();
}
return ret;
}
private static double Evaluate(string expression)
{
var loDataTable = new DataTable();
var loDataColumn = new DataColumn("Eval", typeof(double), expression);
loDataTable.Columns.Add(loDataColumn);
loDataTable.Rows.Add(0);
return (double)(loDataTable.Rows[0]["Eval"]);
}
