public bool DeleteTicks(Symbol symbol)
{
OleDbConnection dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
OleDbCommand deleteCommand = new OleDbCommand("DELETE FROM TickData WHERE SymbolId = '" + symbol.ToUniqueId() + "'", dbConnection);
deleteCommand.ExecuteNonQuery();
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
return true;
}
public int SaveTicks(Symbol symbol, List<TickData> ticks)
{
OleDbConnection dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
OleDbCommand deleteCommand = new OleDbCommand("DELETE FROM TickData WHERE SymbolId = '" + symbol.ToUniqueId() + "'", dbConnection);
deleteCommand.ExecuteNonQuery();
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
UpdateTicks(symbol, ticks);
return ticks.Count;
}
public int UpdateTicks(Symbol symbol, List<TickData> newTicks)
{
if (newTicks.Count == 0)
{
return 0;
}
SystemUtils.StableSort(newTicks, new TickDateComparer().Compare);
DateTime firstDate = newTicks[0].time;
DateTime lastDate = newTicks[newTicks.Count - 1].time;
OleDbConnection dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
OleDbTransaction transaction;
transaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
int ret = 0;
OleDbCommand command = new OleDbCommand("DELETE FROM TickData WHERE Time >= #" + firstDate.ToString() +
"# AND Time <= #" + lastDate.ToString() + "#");
ret -= Convert.ToInt32(command.ExecuteScalar());
foreach (TickData tick in newTicks)
{
InsertTick(dbConnection, transaction, tick, symbol.ToUniqueId());
ret++;
}
transaction.Commit();
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
return ret;
}
public void SaveTick(Symbol symbol, TickData tick)
{
OleDbConnection dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
InsertTick(dbConnection, null, tick, symbol.ToUniqueId());
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
}
public List<TickData> LoadTicks(Symbol symbol, DateTime startDate, DateTime endDate)
{
List<TickData> ret = new List<TickData>();
OleDbConnection dbConnection = new OleDbConnection(connectionString);
dbConnection.Open();
string query = "SELECT * FROM TickData WHERE SymbolId = '" + symbol.ToUniqueId() + "' ";
if (startDate != DateTime.MinValue)
{
query += "AND Time >= #" + startDate.ToShortDateString() + " " + startDate.ToShortTimeString() + "# ";
}
if (endDate != DateTime.MaxValue)
{
query += "AND Time <= #" + endDate.ToShortDateString() + " " + endDate.ToShortTimeString() + "# ";
}
query += "ORDER BY Time ASC, Seconds ASC";
OleDbCommand command = new OleDbCommand(query, dbConnection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TickData tick = new TickData();
tick.time = Convert.ToDateTime(reader["Time"]);
tick.time = tick.time.AddSeconds(Convert.ToDouble(reader["Seconds"]));
tick.tickType = (TickType)Convert.ToInt32(reader["TickType"]);
tick.price = Convert.ToDouble(reader["Price"]);
tick.size = Convert.ToUInt64(reader["Size"]);
if (tick.time >= startDate)
{
ret.Add(tick);
}
}
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
return ret;
}
public int UpdateTicks(Symbol symbol, List<TickData> newTicks)
{
if (newTicks.Count == 0)
{
return 0;
}
int ret = 0;
SystemUtils.StableSort(newTicks, new TickDateComparer().Compare);
DateTime firstDate = newTicks[0].time;
DateTime lastDate = newTicks[newTicks.Count - 1].time;
SqlConnection dbConnection = null;
try
{
dbConnection = new SqlConnection(ConnectionString);
dbConnection.Open();
SqlTransaction transaction;
transaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
SqlCommand command = new SqlCommand("DELETE FROM TickData WHERE Time >= @FirstDate " +
"AND Time <= @LastDate");
SqlParameter param = new SqlParameter("@FirstDate", SqlDbType.DateTime);
param.Value = firstDate;
command.Parameters.Add(param);
param = new SqlParameter("@LastDate", SqlDbType.DateTime);
param.Value = lastDate;
ret -= Convert.ToInt32(command.ExecuteScalar());
foreach (TickData tick in newTicks)
{
InsertTick(dbConnection, transaction, tick, symbol.ToUniqueId());
ret++;
}
transaction.Commit();
}
finally
{
if (dbConnection != null)
{
dbConnection.Close();
}
}
return ret;
}
public void SaveTick(Symbol symbol, TickData tick)
{
SqlConnection dbConnection = null;
try
{
dbConnection = new SqlConnection(ConnectionString);
dbConnection.Open();
InsertTick(dbConnection, null, tick, symbol.ToUniqueId());
}
finally
{
if (dbConnection != null)
{
dbConnection.Close();
}
}
}
public List<TickData> LoadTicks(Symbol symbol, DateTime startDate, DateTime endDate)
{
List<TickData> ret = new List<TickData>();
SqlConnection dbConnection = null;
try
{
dbConnection = new SqlConnection(ConnectionString);
dbConnection.Open();
string query = "SELECT * FROM TickData WHERE SymbolId = @SymbolId";
// Can't check for DateTime.MinValue because they may ask for DateTime.MinValue + their frequency
// in which case, the previous check would fail. See bug ID 1135.
if (startDate > new DateTime(1, 1, 1753))
{
query += " AND [Time] >= @StartDate ";
}
if (endDate != DateTime.MaxValue)
{
query += " AND [Time] <= @EndDate ";
}
query += " ORDER BY Time ASC, Seconds ASC";
SqlCommand command = new SqlCommand(query, dbConnection);
SqlParameter symbolIdParam = new SqlParameter("@SymbolID", SqlDbType.NVarChar, 50);
symbolIdParam.Value = symbol.ToUniqueId();
command.Parameters.Add(symbolIdParam);
if (startDate.Year > 1)
{
SqlParameter param = new SqlParameter("@StartDate", SqlDbType.DateTime);
param.Value = startDate;
command.Parameters.Add(param);
}
if (endDate != DateTime.MaxValue)
{
SqlParameter param = new SqlParameter("@EndDate", SqlDbType.DateTime);
param.Value = endDate;
command.Parameters.Add(param);
}
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
TickData tick = new TickData();
tick.time = Convert.ToDateTime(reader["Time"]);
tick.time = tick.time.AddSeconds(Convert.ToDouble(reader["Seconds"]));
tick.tickType = (TickType)Convert.ToInt32(reader["TickType"]);
tick.price = Convert.ToDouble(reader["Price"]);
tick.size = Convert.ToUInt64(reader["Size"]);
if (tick.time >= startDate)
{
ret.Add(tick);
}
}
}
finally
{
if (dbConnection != null)
{
dbConnection.Close();
}
}
return ret;
}
