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; }