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