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