private void SaveResultToMssql(DataTable dt, string indexCode, string parameters) { var sql = string.Format(@"delete from [Basis{0}].[dbo].[{1}]", indexCode, parameters); sqlWriter.WriteChanges(sql); sqlWriter.InsertBulk(dt, string.Format("[Basis{0}].[dbo].[{1}]", indexCode, parameters)); }
////先删除再添加 //private void ModifyStockBonusDataToSql(DataTable dt) //{ // try // { // CreateStockBonusInfoInDB(); // } // catch // { // logger.Warn("[Common].[dbo].[StockBonusInfo] create failed!"); // } // foreach (DataRow dr in dt.Rows) // { // for (int i = 3; i <= 9; i++) // { // if (dr[i] == DBNull.Value) // { // dr[i] = 0; // } // } // } // sqlWriter.InsertBulk(dt, "[Common].[dbo].[StockBonusInfo]"); //} private void InsertStockBonusDataToSql(DataTable dt) { try { CreateStockBonusInfoInDB(); } catch { logger.Warn("[Common].[dbo].[StockBonusInfo] create failed!"); } foreach (DataRow dr in dt.Rows) { for (int i = 3; i <= 9; i++) { if (dr[i] == DBNull.Value) { dr[i] = 0; } } string code = Convert.ToString(dr[1]); DateTime time = Convert.ToDateTime(dr[0]).Date; var sql = string.Format("delete from [Common].[dbo].[StockBonusInfo] where code='{0}' and ExDividendDate='{1}'", code, time); sqlWriter.WriteChanges(sql); } sqlWriter.InsertBulk(dt, "[Common].[dbo].[StockBonusInfo]"); }
//保存逐笔交易数据 private void SaveResultToMssql2(string databaseName, string tableName, DataTable dt, DateTime startTime, DateTime endTime, int delaynum, int startnum, int calculatornum, string code) { var sql = string.Format(@"delete from [{0}].[dbo].[{1}] where opentime>='{2}' and closetime<='{3}' and delaynum='{4}' and startnum='{5}' and calculatornum='{6}' and code='{7}'", databaseName, tableName, startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"), delaynum, startnum, calculatornum, code); sqlWriter.WriteChanges(sql); sqlWriter.InsertBulk(dt, string.Format("[{0}].[dbo].[{1}]", databaseName, tableName)); }
private void SaveResultToMssql(DateTime date, DataTable dt, double strike, int expiredate) { var sql = string.Format(@"delete from [PutCallParity{0}].[dbo].[{1}] where tdatetime>'{2}' and tdatetime<'{3}' and strike='{4}' and expiredate='{5}'", date.Year, date.ToString("yyyy"), date.ToString("yyyy-MM-dd"), date.AddDays(1).ToString("yyyy-MM-dd"), strike, expiredate); sqlWriter.WriteChanges(sql); sqlWriter.InsertBulk(dt, string.Format("[PutCallParity{0}].[dbo].[{1}]", date.Year, date.ToString("yyyy"))); }
private void SaveResultToMssql(DateTime date, DataTable dt) { var sql = string.Format(@"delete from [ivix{0}].[dbo].[{1}] where tdatetime>'{2}' and tdatetime<'{3}'", date.Year, date.ToString("yyyy"), date.ToString("yyyy-MM-dd"), date.AddDays(1).ToString("yyyy-MM-dd")); sqlWriter.WriteChanges(sql); sqlWriter.InsertBulk(dt, string.Format("[ivix{0}].[dbo].[{1}]", date.Year, date.ToString("yyyy"))); }
/// <summary> /// 根据股票代码逐月删除数据,慎用!!! /// </summary> /// <param name="code">股票代码</param> /// <param name="startDate">开始时间</param> /// /// <param name="endDate">结束时间</param> private void DeleteDataByCode(string code, DateTime startDate, DateTime endDate) { string firstDate = startDate.ToString("yyyy-MM-01"); int firstYear = startDate.Year; int firstMonth = startDate.Month; int endYear = endDate.Year; int endMonth = endDate.Month; string sqlStr = string.Format(@"declare @date date,@tb_name nvarchar(60),@yearIndex int,@index int,@latest_date datetime,@sqlStr nvarchar(300),@tem_date datetime set @date ='{0}' set @yearIndex={1} set @index={2} while @yearIndex<={3} or (@yearIndex={3} and @index<={4}) begin if @yearIndex={1} begin set @index={2} end set @index =1 while @index <=12 begin set @tb_name='[StockMinuteTransaction'+datename(year,@date)+'].dbo.[Transaction'+ datename(year,@date)+'-'+datename(month,@date)+']' set @sqlStr ='delete from '+@tb_name+' where code=''{5}''' if object_id(@tb_name) is not null begin execute(@sqlStr) end set @date = dateadd(month,1,@date) set @index=@index+1 end set @yearIndex=@yearIndex+1 end", firstDate, firstYear, firstMonth, endYear, endMonth, code.ToUpper()); sqlWriter.WriteChanges(sqlStr); }
private void SaveResultToMssql(DateTime date, DataTable dt, double strike, int expiredate, string type, string code) { //var sql = string.Format(@"delete from [Impv{0}].[dbo].[{1}] where tdatetime>'{2}' and tdatetime<'{3}' and strike='{4}' and expiredate='{5}' and call_or_put='{6}'", date.Year, date.ToString("yyyy-MM-dd"), date.ToString("yyyy-MM-dd"), date.AddDays(1).ToString("yyyy-MM-dd"), strike, expiredate,type); var sql = string.Format(@"delete from [Impv{0}].[dbo].[{1}] where tdatetime>'{2}' and tdatetime<'{3}' and code='{4}'", date.Year, date.ToString("yyyy-MM-dd"), date.ToString("yyyy-MM-dd"), date.AddDays(1).ToString("yyyy-MM-dd"), code); sqlWriter.WriteChanges(sql); sqlWriter.InsertBulk(dt, string.Format("[Impv{0}].[dbo].[{1}]", date.Year, date.ToString("yyyy-MM-dd"))); }
private void ClearStockInfoInDB() { var sql = "delete from [Common].[dbo].[StockInfo]"; sqlWriter.WriteChanges(sql); }
private void ClearExistedOptionInfo(string underlyingCode) { var sql = string.Format(@"delete [Common].dbo.[OptionInfo] where option_mark_code='{0}'", underlyingCode); sqlWriter.WriteChanges(sql); }