//从本地数据库获取股票上市退市信息 public List <StockIPOInfo> GetStockListInfoFromSql() { List <StockIPOInfo> stockInfos = new List <StockIPOInfo>(); var sqlStr = "select [Code],[SecName],[IPODate],[DelistDate],[UpdateDateTime] from [Common].[dbo].[StockInfo]"; DataTable dt = new DataTable(); try { dt = sqlReader.GetDataTable(sqlStr); } catch { logger.Warn(string.Format("There is no IPO information from specialized sqlserver!!!")); } foreach (DataRow dr in dt.Rows) { StockIPOInfo info = new StockIPOInfo(); info.code = Convert.ToString(dr[0]); info.name = Convert.ToString(dr[1]); info.IPODate = Convert.ToDateTime(dr[2]); if (dr[3] != DBNull.Value) { info.DelistDate = Convert.ToDateTime(dr[3]); } else { info.DelistDate = new DateTime(2099, 12, 31); } info.updateTime = Convert.ToDateTime(dr[4]); stockInfos.Add(info); } return(stockInfos); }
public int GetDuration(DateTime start, DateTime end) { if (allTradeDays.Count == 0) { var sqlStr = string.Format("select DateTime from [Common].[dbo].[TransactionDate]"); var res = sqlReader.GetDataTable(sqlStr); allTradeDays = res.ToList <DateTime>(); } return(allTradeDays.IndexOf(end) - allTradeDays.IndexOf(start) + 1); }
private void LoadStockTransactionToRedisFromSql(string code, List <DateTime> tradingDates) { var existedDateInRedis = GetExistedDateInRedis(code, tradingDates.First(), tradingDates.Last()); var nonExistedDateIntervalInRedis = Computor.GetNoExistedInterval <DateTime>(tradingDates, existedDateInRedis); if (nonExistedDateIntervalInRedis != null && nonExistedDateIntervalInRedis.Count > 0) { string sqlStr = GenerateSqlString(code, nonExistedDateIntervalInRedis); var dt = sqlReader.GetDataTable(sqlStr); WriteToRedis(code, dt); } }
public DataTable Get(string code, DateTime begin, DateTime end) { if (begin.Date != end.Date) { throw new ArgumentException("开始时间和结束时间必须是同一天"); } var sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) as tdatetime ,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit] FROM [WindFullMarket{0}].[dbo].[MarketData_{1}] where ((ttime>=91500000 and ttime<=113000000) or (ttime>=130000000 and ttime<=150100000)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) >='{2}' and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) <='{3}'", begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end); if (begin.Date <= new DateTime(2011, 07, 31)) //公司数据库在2011年8月1日开始改变了ttime字段的格式 { sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(ttime),3,0,':'),6,0,':')) as tdatetime ,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt] FROM [WindFullMarket{0}].[dbo].[MarketData_{1}] where ((ttime>=91500 and ttime<=113000) or (ttime>=130000 and ttime<=150100)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(ttime),3,0,':'),6,0,':')) >='{2}' and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(ttime),3,0,':'),6,0,':')) <='{3}'", begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end); } if (begin.Date >= new DateTime(2012, 03, 01) && begin.Date <= new DateTime(2012, 05, 31)) { sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(substring(ttime,0,7)),3,0,':'),6,0,':')) as tdatetime ,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit] FROM [WindFullMarket{0}].[dbo].[MarketData_{1}] where ((substring(ttime,0,7)>=91500 and substring(ttime,0,7)<=113000) or (substring(ttime,0,7)>=130000 and substring(ttime,0,7)<=150100)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(substring(ttime,0,7)),3,0,':'),6,0,':')) >='{2}' and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(rtrim(substring(ttime,0,7)),3,0,':'),6,0,':')) <='{3}'", begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end); } return(sqlReader.GetDataTable(sqlStr)); }
private List <StockBasicInfo> loadInfoDailyFromSql(string code, DateTime start, DateTime end) { List <StockBasicInfo> infoList = new List <StockBasicInfo>(); var timeList = SplitTimeYearly(start, end); foreach (var day in timeList) { DateTime dayStart = day; DateTime dayEnd = DateTimeExtension.DateUtils.PreviousOrCurrentTradeDay(new DateTime(day.Year, 12, 31)); if (dayEnd > end) { dayEnd = end; } var sqlStr = string.Format(@"select [Code],[DateTime] from [StockInfo].[dbo].[BasicInfoDaily{0}] where Code='{1}' and DateTime>='{2}' and DateTime<='{3}'", dayStart.Year, code, dayStart, dayEnd); var dt = sqlReader.GetDataTable(sqlStr); var tradedays = dateRepo.GetStockTransactionDate(dayStart, dayEnd); if (dt.Rows.Count < tradedays.Count()) //数据不足,去万德拉取 { var dtAll = windReader.GetDailyDataTable(code, "", dayStart, dayEnd); var dtLack = getLackData(dt, dtAll); if (dtLack.Rows.Count > 0) { WriteToSql(dtLack); } infoList.AddRange(dataTableToList(dtAll)); } else { infoList.AddRange(dataTableToList(dt)); } } return(infoList); }
public DataTable GetFromSpecializedSQLServer(string code, DateTime date, ConnectionType type) { var sqlReader = new SqlServerReader(type); var sqlStr = string.Format(@"SELECT [stkcd],[tdatetime],[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit] FROM [StockTickTransaction{0}].[dbo].[{1}]", date.ToString("yyyy"), date.ToString("yyyy-MM-dd")); return(sqlReader.GetDataTable(sqlStr)); }
private void LoadDataToRedisFromSqlServerIfNecessary(string code, DateTime date) { if (!ExistInRedis(code, date)) { var sqlStr = string.Format(@"SELECT convert(varchar(30),[tdatetime],121) as tdatetime ,[cp] ,[S1] ,[S2] ,[S3] ,[S4] ,[S5] ,[B1] ,[B2] ,[B3],[B4] ,[B5] ,[SV1] ,[SV2] ,[SV3] ,[SV4] ,[SV5] ,[BV1] ,[BV2] ,[BV3],[BV4],[BV5],[ts],[tt],[HighLimit],[LowLimit] FROM [StockTickTransaction{0}].[dbo].[{1}] where rtrim(stkcd)='{2}'", date.Year, date.ToString("yyyy-MM-dd"), code); var dt = sqlReader.GetDataTable(sqlStr); var key = string.Format(RedisKeyFormat, code.ToUpper(), date.ToString("yyyy-MM-dd")); BulkWriteToRedis(key, dt); } }
private void LoadDataToRedisFromSqlServerIfNecessary(string code, DateTime date) { if (!ExistInRedis(code, date)) { DateTime startTime = date.Date; DateTime endTime = date.AddHours(15); var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransaction].[dbo].[{0}_{1}] where DateTime>='{2}' and DateTime<='{3}'", code.ToUpper().Split('.')[0], code.ToUpper().Split('.')[1], startTime, endTime); try { var dt = sqlReader.GetDataTable(sqlStr); var key = string.Format(RedisKeyFormat, code.ToUpper(), "Minute", date.ToString("yyyy-MM-dd")); BulkWriteToRedis(key, dt); } catch (Exception e) { } } }
public DataTable Get(string code, DateTime begin, DateTime end) { if (begin.Date != end.Date) { throw new ArgumentException("开始时间和结束时间必须是同一天"); } var sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) as tdatetime ,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt] FROM [WindFullMarket{0}].[dbo].[MarketData_{1}] where ((ttime>=93000000 and ttime<=113000000) or (ttime>=130000000 and ttime<=150000000)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) >='{2}' and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) <='{3}'", begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end); return(sqlReader.GetDataTable(sqlStr)); }
private DataTable GetStockDailyTransactionFromSqlServer170(string code, DateTime begin, DateTime end) { var sqlStr = string.Format(@"SELECT [stkcd] as [Code] ,convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')) as [DateTime] ,[Open] as [open] ,[High] as [high] ,[Low] as [low] ,[Close] as [close] ,[Volume] as [volume] ,[Amount] as [amount] FROM [DayLine].[dbo].[DailyData] where tdate>={2} and tdate<={3} order by [DateTime]", code.Split('.')[0], code.Split('.')[1], begin.ToString("yyyyMMdd"), end.ToString("yyyyMMdd")); var res = sqlReader170.GetDataTable(sqlStr); return(res); }
/// <summary> /// 从sql源中读取数据 /// </summary> /// <param name="code">股票代码</param> /// <param name="startDate">开始时间</param> /// <param name="endDate">结束时间</param> private DataTable getDataFromSql(string code, DateTime startDate, DateTime endDate) { DataTable dt = new DataTable(); var sqlStr = string.Format(@"SELECT [stkcd] as [Code] ,DATEADD(mi,-1,convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(rtrim(ttime),3,0,':'))) as [DateTime] ,[Open] as [open] ,[High] as [high] ,[Low] as [low] ,[Close] as [close] ,[Volume] as [volume] ,[Amount] as [amount] FROM [MinuteLine].[dbo].[Min1_{0}_{1}] where tdate>={2} and tdate<={3} order by [DateTime]", code.Split('.')[0], code.Split('.')[1], startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd")); dt = sqlReaderSource.GetDataTable(sqlStr); return(dt); }
private List <StockMinuteTransaction> LoadStockMinuteFromSql(string code, DateTime currentTime) { List <StockMinuteTransaction> data = new List <StockMinuteTransaction>(); var exist = ExistInSqlServer(code, currentTime.Date); if (exist != false) { DateTime dayStart = currentTime.Date + new TimeSpan(9, 25, 00); DateTime dayEnd = currentTime.Date + new TimeSpan(15, 00, 00); var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransactionByTick{0}].[dbo].[Transaction{1}] where Code='{2}' and DateTime>='{3}' and DateTime<='{4}'", currentTime.Year, currentTime.ToString("yyyy-MM"), code, dayStart, dayEnd); var dt = sqlReader.GetDataTable(sqlStr); //dt转化为list data = dataTableToTransactionList(dt); } return(data); }
public List <StockTransaction> GetStockTransactionFrom170SqlByCode(string code, DateTime start, DateTime end) { var stocks = new List <StockTransaction>(); DateTime startTime = start.Date; DateTime endTime = end.Date.AddHours(15); try { var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransaction].[dbo].[{0}_{1}] where DateTime>='{2}' and DateTime<='{3}'", code.ToUpper().Split('.')[0], code.ToUpper().Split('.')[1], startTime, endTime); var dt = sqlReader170.GetDataTable(sqlStr); var list = datatableToList(dt); stocks.AddRange(list); } catch (Exception e) { Console.WriteLine(e.Message); } return(stocks); }
public DataTable Get(string code, DateTime begin, DateTime end) { if (begin.Date != end.Date) { throw new ArgumentException("开始时间和结束时间必须是同一天"); } var sqlStr = string.Format(@"SELECT [stkcd],convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) as tdatetime ,[cp],[S1],[S2],[S3],[S4],[S5],[B1],[B2],[B3],[B4],[B5],[SV1],[SV2],[SV3],[SV4],[SV5],[BV1],[BV2],[BV3],[BV4],[BV5],[ts],[tt],[OpenInterest] FROM [WindFullMarket{0}].[dbo].[MarketData_{1}] where ((ttime>=93000000 and ttime<=113000000) or (ttime>=130000000 and ttime<=150000000)) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) >='{2}' and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+stuff(stuff(stuff(rtrim(ttime),3,0,':'),6,0,':'),9,0,'.')) <='{3}'", begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end); // var sqlStr = string.Format(@"SELECT rtrim([code]) as [stkcd],stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+reverse(stuff(stuff(stuff(reverse(rtrim(ttime)),4,0,'.'),7,0,':'),10,0,':')) as tdatetime //,[lastprice] as [cp],[ask1] as [S1],[ask2] as [S2],[ask3] as [S3],[ask4] as [S4],[ask5] as [S5],[bid1] as [B1],[bid2] as [B2],[bid3] as [B3],[bid4] as [B4],[bid5] as [B5],[askv1] as [SV1],[askv2] as [SV2],[askv3] as [SV3],[askv4] as [SV4],[askv5] as [SV5],[bidv1] as [BV1],[bidv2] as [BV2],[bidv3] as [BV3],[bidv4] as [BV4],[bidv5] as [BV5],[volume] as [ts],[amount] as [tt],[OpenInterest] // FROM [TickData_50ETFOption].[dbo].[MarketData_{1}] // where ((ttime>=93000000 and ttime<=113000000 and ttime%10000000<=5959999 and ttime%100000<=59999 ) or (ttime>=130000000 and ttime<=150000000 and ttime%10000000<=5959999 and ttime%100000<=59999 )) and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+reverse(stuff(stuff(stuff(reverse(rtrim(ttime)),4,0,'.'),7,0,':'),10,0,':'))) >='{2}' // and convert(datetime,stuff(stuff(rtrim(tdate),5,0,'-'),8,0,'-')+' '+reverse(stuff(stuff(stuff(reverse(rtrim(ttime)),4,0,'.'),7,0,':'),10,0,':'))) <='{3}'", // begin.ToString("yyyyMM"), code.Replace('.', '_'), begin, end); return(sqlReader.GetDataTable(sqlStr)); }
private void BulkLoadStockMinuteToRedisFromSql(string code, DateTime currentTime) { DateTime latestTime = GetLatestTimeFromRedis(code, currentTime); var start = latestTime == default(DateTime) ? new DateTime(currentTime.Year, 1, 1) : latestTime.AddMinutes(1); var end = GetEndTime(currentTime); if (start < end) { Dictionary <DateTime, DateTime> dateSpan = SplitDateTimeMonthly(start, end); foreach (var item in dateSpan) { var exist = ExistInSqlServer(code, item.Value); if (exist != false) { var sqlStr = string.Format(@"select [Code],[DateTime] ,[open],[HIGH],[LOW],[CLOSE],[VOLUME],[Amount] from [StockMinuteTransaction{0}].[dbo].[Transaction{1}] where Code='{2}' and DateTime>='{3}' and DateTime<='{4}'", item.Key.Year, item.Key.ToString("yyyy-MM"), code, item.Key, item.Value); var dt = sqlReader.GetDataTable(sqlStr); WriteToRedis(dt); } } } }
private DataTable ReadFromSqlServer(string underlyingCode, DateTime start, DateTime end) { var sqlStr = string.Format("select * from [Common].dbo.[OptionInfo] where option_mark_code='{0}' and listed_date<='{2}' and expire_date>'{1}';", underlyingCode, start, end); return(sqlReader.GetDataTable(sqlStr)); }