public static List <Candle> Get(string instrumentID, DateTime fromTradingDay) { var table = new DataTable(); var command = new SQLiteCommand("SELECT * FROM candle WHERE instrumentid=@InstrumentID AND tradingday>=@TradingDay", DALUtil.Connection); command.Parameters.AddWithValue("@InstrumentID", instrumentID); command.Parameters.AddWithValue("@TradingDay", fromTradingDay); var adapter = new SQLiteDataAdapter(command); adapter.Fill(table); var candles = new List <Candle>(); foreach (var item in table.Rows) { var row = item as DataRow; var candle = new Candle(); candle.InstrumentID = row.Field <string>(0); candle.TradingDay = row.Field <DateTime>(1); candle.CandleTime = row.Field <DateTime>(2); candle.Open = DALUtil.GetDouble(row[3]); candle.Close = DALUtil.GetDouble(row[4]); candle.High = DALUtil.GetDouble(row[5]); candle.Low = DALUtil.GetDouble(row[6]); candle.Volume = row.Field <int>(7); candle.OpenInterest = DALUtil.GetDouble(row[8]); candles.Add(candle); } return(candles); }
public static Instrument[] Get() { var table = new DataTable(); var adapter = new SQLiteDataAdapter("SELECT * FROM instrument", DALUtil.Connection); adapter.Fill(table); var instruments = new List <Instrument>(); foreach (var item in table.Rows) { var row = item as DataRow; var instrument = new Instrument(); instrument.InstrumentID = row.Field <string>(0); instrument.ExchangeID = row.Field <string>(1); instrument.InstrumentName = row.Field <string>(2); instrument.ExchangeInstID = row.Field <string>(3); instrument.ProductID = row.Field <string>(4); instrument.ProductClass = (EnumProductClassType)row.Field <byte>(5); instrument.DeliveryYear = row.Field <int>(6); instrument.DeliveryMonth = row.Field <int>(7); instrument.MaxMarketOrderVolume = row.Field <int>(8); instrument.MinMarketOrderVolume = row.Field <int>(9); instrument.MaxLimitOrderVolume = row.Field <int>(10); instrument.MinLimitOrderVolume = row.Field <int>(11); instrument.VolumeMultiple = row.Field <int>(12); instrument.PriceTick = row.Field <double>(13); instrument.CreateDate = row.Field <string>(14); instrument.OpenDate = row.Field <string>(15); instrument.ExpireDate = row.Field <string>(16); instrument.StartDelivDate = row.Field <string>(17); instrument.EndDelivDate = row.Field <string>(18); instrument.InstLifePhase = (EnumInstLifePhaseType)row.Field <byte>(19); instrument.IsTrading = row.Field <int>(20); instrument.PositionType = (EnumPositionTypeType)row.Field <byte>(21); instrument.PositionDateType = (EnumPositionDateTypeType)row.Field <byte>(22); instrument.LongMarginRatio = row.Field <double>(23); instrument.ShortMarginRatio = row.Field <double>(24); instrument.MaxMarginSideAlgorithm = (EnumMaxMarginSideAlgorithmType)row.Field <byte>(25); instrument.UnderlyingInstrID = row.Field <string>(26); instrument.StrikePrice = DALUtil.GetDouble(row[27]); instrument.OptionsType = (EnumOptionsTypeType)row.Field <byte>(28); instrument.UnderlyingMultiple = DALUtil.GetDouble(row[29]); instrument.CombinationType = (EnumCombinationTypeType)row.Field <byte>(30); instruments.Add(instrument); } return(instruments.OrderBy(p => p.InstrumentID).ToArray()); }
public static void Save(Instrument[] instrument) { new SQLiteCommand("DELETE FROM `instrument`", DALUtil.Connection).ExecuteNonQuery(); var builder = new StringBuilder("REPLACE INTO `instrument` VALUES"); foreach (var item in instrument) { builder.Append('('); DALUtil.AddField(builder, item.InstrumentID); DALUtil.AddField(builder, item.ExchangeID); DALUtil.AddField(builder, item.InstrumentName); DALUtil.AddField(builder, item.ExchangeInstID); DALUtil.AddField(builder, item.ProductID); DALUtil.AddField(builder, (int)item.ProductClass); DALUtil.AddField(builder, item.DeliveryYear); DALUtil.AddField(builder, item.DeliveryMonth); DALUtil.AddField(builder, item.MaxMarketOrderVolume); DALUtil.AddField(builder, item.MinMarketOrderVolume); DALUtil.AddField(builder, item.MaxLimitOrderVolume); DALUtil.AddField(builder, item.MinLimitOrderVolume); DALUtil.AddField(builder, item.VolumeMultiple); DALUtil.AddField(builder, item.PriceTick); DALUtil.AddField(builder, item.CreateDate); DALUtil.AddField(builder, item.OpenDate); DALUtil.AddField(builder, item.ExpireDate); DALUtil.AddField(builder, item.StartDelivDate); DALUtil.AddField(builder, item.EndDelivDate); DALUtil.AddField(builder, (int)item.InstLifePhase); DALUtil.AddField(builder, item.IsTrading); DALUtil.AddField(builder, (int)item.PositionType); DALUtil.AddField(builder, (int)item.PositionDateType); DALUtil.AddField(builder, item.LongMarginRatio); DALUtil.AddField(builder, item.ShortMarginRatio); DALUtil.AddField(builder, (int)item.MaxMarginSideAlgorithm); DALUtil.AddField(builder, item.UnderlyingInstrID); DALUtil.AddField(builder, item.StrikePrice); DALUtil.AddField(builder, (int)item.OptionsType); DALUtil.AddField(builder, item.UnderlyingMultiple); builder.Append((int)item.CombinationType); builder.Append("),"); } builder.Remove(builder.Length - 1, 1); new SQLiteCommand(builder.ToString(), DALUtil.Connection).ExecuteNonQuery(); }
public static List <Quotation> Get() { var table = new DataTable(); var adapter = new SQLiteDataAdapter("SELECT * FROM quotation", DALUtil.Connection); adapter.Fill(table); var quotations = new List <Quotation>(); foreach (var item in table.Rows) { var row = item as DataRow; var quotation = new Quotation(); quotation.TradingDay = row.Field <DateTime>(0); quotation.InstrumentID = row.Field <string>(1); quotation.ExchangeID = row.Field <string>(2); quotation.ExchangeInstID = row.Field <string>(3); quotation.LastPrice = DALUtil.GetDouble(row[4]); quotation.PreSettlementPrice = DALUtil.GetDouble(row[5]); quotation.PreClosePrice = DALUtil.GetDouble(row[6]); quotation.PreOpenInterest = DALUtil.GetDouble(row[7]); quotation.OpenPrice = DALUtil.GetDouble(row[8]); quotation.HighestPrice = DALUtil.GetDouble(row[9]); quotation.LowestPrice = DALUtil.GetDouble(row[10]); quotation.Volume = row.Field <int>(11); quotation.Turnover = DALUtil.GetDouble(row[12]); quotation.OpenInterest = DALUtil.GetDouble(row[13]); quotation.ClosePrice = DALUtil.GetDouble(row[14]); quotation.SettlementPrice = DALUtil.GetDouble(row[15]); quotation.UpperLimitPrice = DALUtil.GetDouble(row[16]); quotation.LowerLimitPrice = DALUtil.GetDouble(row[17]); quotation.PreDelta = DALUtil.GetDouble(row[18]); quotation.CurrDelta = DALUtil.GetDouble(row[19]); quotation.Time = row.Field <DateTime>(20); quotation.BidPrice1 = DALUtil.GetDouble(row[21]); quotation.BidVolume1 = row.Field <int>(22); quotation.AskPrice1 = DALUtil.GetDouble(row[23]); quotation.AskVolume1 = row.Field <int>(24); quotation.AveragePrice = row.Field <double>(25); quotations.Add(quotation); } return(quotations); }
public static void Save(List <Candle> candles) { var builder = new StringBuilder("REPLACE INTO candle VALUES"); foreach (var item in candles) { builder.Append('('); DALUtil.AddField(builder, item.InstrumentID); DALUtil.AddField(builder, item.TradingDay.ToString("yyyy-MM-dd")); DALUtil.AddField(builder, item.CandleTime.ToString("yyyy-MM-dd HH:mm:ss.fff")); DALUtil.AddField(builder, item.Open); DALUtil.AddField(builder, item.Close); DALUtil.AddField(builder, item.High); DALUtil.AddField(builder, item.Low); DALUtil.AddField(builder, item.Volume); DALUtil.AddLastField(builder, item.OpenInterest); builder.Append("),"); } builder.Remove(builder.Length - 1, 1); new SQLiteCommand(builder.ToString(), DALUtil.Connection).ExecuteNonQuery(); }
public static void Save(List <Quotation> quotations) { var builder = new StringBuilder("REPLACE INTO quotation VALUES"); foreach (var item in quotations) { builder.Append('('); DALUtil.AddField(builder, item.TradingDay.ToString("yyyy-MM-dd")); DALUtil.AddField(builder, item.InstrumentID); DALUtil.AddField(builder, item.ExchangeID); DALUtil.AddField(builder, item.ExchangeInstID); DALUtil.AddField(builder, item.LastPrice); DALUtil.AddField(builder, item.PreSettlementPrice); DALUtil.AddField(builder, item.PreClosePrice); DALUtil.AddField(builder, item.PreOpenInterest); DALUtil.AddField(builder, item.OpenPrice); DALUtil.AddField(builder, item.HighestPrice); DALUtil.AddField(builder, item.LowestPrice); DALUtil.AddField(builder, item.Volume); DALUtil.AddField(builder, item.Turnover); DALUtil.AddField(builder, item.OpenInterest); DALUtil.AddField(builder, item.ClosePrice); DALUtil.AddField(builder, item.SettlementPrice); DALUtil.AddField(builder, item.UpperLimitPrice); DALUtil.AddField(builder, item.LowerLimitPrice); DALUtil.AddField(builder, item.PreDelta); DALUtil.AddField(builder, item.CurrDelta); DALUtil.AddField(builder, item.Time.ToString("yyyy-MM-dd HH:mm:ss.fff")); DALUtil.AddField(builder, item.BidPrice1); DALUtil.AddField(builder, item.BidVolume1); DALUtil.AddField(builder, item.AskPrice1); DALUtil.AddField(builder, item.AskVolume1); builder.Append(item.AveragePrice); builder.Append("),"); } builder.Remove(builder.Length - 1, 1); new SQLiteCommand(builder.ToString(), DALUtil.Connection).ExecuteNonQuery(); }
public static Candle GetLast(string instrumentID) { var command = new SQLiteCommand("SELECT * FROM candle WHERE instrumentid=@InstrumentID order by candletime desc limit 1", DALUtil.Connection); command.Parameters.AddWithValue("@InstrumentID", instrumentID); SQLiteDataReader reader = command.ExecuteReader(); if (!reader.Read()) { return(null); } var candle = new Candle(); candle.InstrumentID = reader.GetString(0); candle.TradingDay = reader.GetDateTime(1); candle.CandleTime = reader.GetDateTime(2); candle.Open = DALUtil.GetDouble(reader[3]); candle.Close = DALUtil.GetDouble(reader[4]); candle.High = DALUtil.GetDouble(reader[5]); candle.Low = DALUtil.GetDouble(reader[6]); candle.Volume = reader.GetInt32(7); candle.OpenInterest = DALUtil.GetDouble(reader[8]); return(candle); }