/// <summary>
/// Get a bar of data from the MSQL database
/// </summary>
/// <param name="cmdString"></param>
/// <returns></returns>
public static BarDataItem ReadBar(string cmdString)
{
MySqlConnection connection = new MySqlConnection(myConnPath);
connection.Open();
MySqlCommand cmd = new MySqlCommand(cmdString, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
dataReader.Read();
BarDataItem mBarData = new BarDataItem(
dataReader.GetString(1), //Time
dataReader.GetUInt32(2), // Volume
dataReader.GetDouble(3), // Open
dataReader.GetDouble(4), // High
dataReader.GetDouble(5), // Low
dataReader.GetUInt32(6), // Close
0, // Other
0); // Other
mBarData.EditTime = dataReader.GetDateTime(7);
Log.Data(3, string.Format("{0:MM/dd/yyyy} {1:0.000} {2:0.000} {3:0.000} {4:0.000} {5,-8} {6:MM/dd/yyyy HH:mm:ss}",
mBarData.Time, mBarData.Volume, mBarData.Open, mBarData.High, mBarData.Low, mBarData.Close, mBarData.EditTime));
dataReader.Close();
connection.Close();
return(mBarData);
}
/// <summary>
/// Callback function that writes the historical data into the database
/// </summary>
/// <param name="conId"></param>
/// <param name="bar"></param>
/// <param name="end"></param>
public static void WriteBarDB(int conId, BarDataItem bar, string symbol, bool end)
{
//string today = DateTime.Now.Date.ToString("yyyyMMdd");
if (symbol == null)
{
Log.Error(3, string.Format("WriteBarDB Null Symbol ConId={0}", conId));
return;
}
ReplaceIBbarDB(symbol, conId, bar, 0);
Log.Info(0, string.Format("End WriteBarDB {0,-8} {1,-10}", symbol, conId));
}
public static BarDataList ReadAllBars(string cmdString)
{
BarDataList mBarDataList = new BarDataList();
MySqlConnection connection = new MySqlConnection(myConnPath);
connection.Open();
MySqlCommand cmd = new MySqlCommand(cmdString, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
int numBars = 0;
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// SELECT ticker, tdate, volume, open, high, low, close, editdate FROM historical WHERE ticker = "FB";
while (dataReader.Read())
{
BarDataItem mBarData = new BarDataItem(
dataReader.GetString(1), // Time
dataReader.GetDouble(3), // Open
dataReader.GetDouble(4), // High
dataReader.GetDouble(5), // Low
dataReader.GetDouble(6), // Close
dataReader.GetUInt32(2), // Volume
0, // Other
0); // Other
mBarData.editTime = dataReader.GetDateTime(7); // EditTime
mBarDataList.Add(mBarData);
numBars++;
Log.Data(0, string.Format("{0,20} {1,8} {2:0.000} {3:0.000} {4:0.000} {5:0.000} {6:MM/dd/yyyy HH:mm:ss}",
mBarData.Time, mBarData.Volume, mBarData.Open, mBarData.High, mBarData.Low, mBarData.Close, mBarData.editTime));
}
stopwatch.Stop();
if (numBars != 0)
{
Log.Info(2, string.Format("End ReadAllBars Changed={0} Time/Rec= {1:F}", numBars, stopwatch.Elapsed.TotalMilliseconds / numBars));
}
dataReader.Close();
connection.Close();
return(mBarDataList);
}
private static void ReplaceIBbarDB(string symbol, int conId, BarDataItem bar, double change)
{
string editDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string cmdString = "REPLACE INTO " + DB.myHistTable + " VALUES ('" + symbol + "','" + conId + "','" + bar.Time.Date.ToString("yyyy-MM-dd") + "','" +
bar.Volume + "','" + bar.Open + "','" + bar.High + "','" + bar.Low + "','" + bar.Close + "','" + change + "','" + editDate + "')";
int returnCode = DB.ExecuteNonQuery(cmdString);
if (returnCode == 1)
{
Log.Data(3, string.Format("Insert {0,-8} {1,-10} {2,-6} {3,-8} {4,-6} {5,-6} {6,-6} {7,-6}", symbol, conId, bar.Time.Date.ToString("dd/MM/yyyy"), bar.Volume, bar.Open, bar.High, bar.Low, bar.Close, change));
}
else
{
Log.Data(3, string.Format("Replace {0,-8} {1,-10} {2,-6} {3,-8} {4,-6} {5,-6} {6,-6} {7,-6}", symbol, conId, bar.Time.Date.ToString("dd/MM/yyyy"), bar.Volume, bar.Open, bar.High, bar.Low, bar.Close, change));
}
}
private static BarDataItem convertToBarDataItem(Bar convertBar)
{
BarDataItem tempBDI = new BarDataItem
(
convertBar.Time,
convertBar.Open,
convertBar.High,
convertBar.Low,
convertBar.Close,
convertBar.Volume,
convertBar.Count,
convertBar.WAP
);
return(tempBDI);
}
