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")));
}
/// <summary>
/// 将数据逐月存入数据库
/// </summary>
/// <param name="dataTable"></param>
private void WriteToSqlMonthByMonth(DataTable dataTable)
{
Dictionary <DateTime, DataTable> monthData = SplitDataTableMonthly(dataTable);
foreach (var item in monthData)
{
IdentifyOrCreateDBAndTable(item.Key);
sqlWriter.InsertBulk(item.Value, string.Format("[StockMinuteTransaction{0}].dbo.[Transaction{1}]", item.Key.Year, item.Key.ToString("yyyy-MM")));
}
}
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")));
}
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 LoadDataToSqlServerFromSourceIfNecessary(string code, DateTime date)
{
if (!ExistInSqlServer(code, date))
{
CreateDBOrTableIfNecessary(date);
var dt = dataSource.Get(code, date, date.AddHours(23));
sqlWriter.InsertBulk(dt, string.Format("[StockOptionTickTransaction{0}].[dbo].[{1}]", date.Year, date.ToString("yyyy-MM-dd")));
}
}
//将股票数据存到sql
private void WriteToSql(DataTable dataTable)
{
Dictionary <DateTime, DataTable> yearData = SplitDataTableYearly(dataTable);
foreach (var item in yearData)
{
IdentifyOrCreateDBAndTable(item.Key);
sqlWriter.InsertBulk(item.Value, string.Format("[StockInfo].[dbo].[BasicInfoDaily{0}]", item.Key.Year));
}
}
private void LoadStockTransactionToSqlFromSource(string code, List <DateTime> tradingDates)
{
var existedDateInSql = GetExistedDateInSql(code, tradingDates.First(), tradingDates.Last());
var nonExistedDateIntervalInSql = Computor.GetNoExistedInterval <DateTime>(tradingDates, existedDateInSql);
foreach (var item in nonExistedDateIntervalInSql)
{
var dt = dataSource.Get(code, item.Key, item.Value);
sqlWriter.InsertBulk(dt, "[DailyTransaction].[dbo].[Stock]");
}
}
public List <DateTime> GetStockTransactionDate(DateTime start, DateTime end)
{
for (int year = start.Year; year <= end.Year; year++)
{
var existed = sqlReader.ExecuteScalar <int>(string.Format("select 1 from [Common].[dbo].[TransactionDate] where datetime >= '{0}-01-01' and datetime<='{0}-12-31'", year)) > 0;
if (!existed)
{
var res = windReader.GetTransactionDate(new DateTime(year, 1, 1), new DateTime(year, 12, 31)).ToDataTableWithSingleColum("DateTime");
sqlWriter.InsertBulk(res, "[Common].[dbo].[TransactionDate]");
}
}
return(FetchTransactionDateFromSql(start, end));
}
private void LoadDataToSqlServerFromSourceIfNecessary(string code, DateTime date, bool record, Infrastructure.ConnectionType type = Infrastructure.ConnectionType.Local)
{
if (!ExistInSqlServer(code, date))
{
CreateDBOrTableIfNecessary(date);
var dt = dataSource.Get(code, new DateTime(date.Year, date.Month, date.Day, 9, 15, 0, 0), new DateTime(date.Year, date.Month, date.Day, 15, 1, 0, 0));
if (dt.Rows.Count > 0 && record == true)
{
sqlWriter.InsertBulk(dt, string.Format("[StockTickTransaction{0}].[dbo].[{1}]", date.Year, date.ToString("yyyy-MM-dd")));
}
}
else
{
var dt = dataSource.GetFromSpecializedSQLServer(code, date, type);
}
}
private void LoadStockTransactionToSqlFromSource(string code, List <DateTime> tradingDates)
{
IdentifyOrCreateDBandDataTable();
var existedDateInSql = GetExistedDateInSql(code, tradingDates.First(), tradingDates.Last());
var nonExistedDateIntervalInSql = Computor.GetNoExistedInterval <DateTime>(tradingDates, existedDateInSql);
foreach (var item in nonExistedDateIntervalInSql)
{
var dt = dataSource.Get(code, item.Key, item.Value);
//数据需要清洗一遍，为NaN的数据变成0
foreach (DataRow dr in dt.Rows)
{
for (int i = 2; i < dt.Columns.Count; i++)
{
if (double.IsNaN(Convert.ToDouble(dr[i])) == true)
{
//dr[i] = 0;
dr[i] = DBNull.Value;
}
}
}
sqlWriter.InsertBulk(dt, "[DailyTransaction].[dbo].[StockOption]");
}
}
private void InsertToSql(DataTable dt)
{
ClearStockInfoInDB();
sqlWriter.InsertBulk(dt, "[Common].[dbo].[StockInfo]");
}
private void WriteToSqlServer(string underlyingCode, DataTable dt)
{
ClearExistedOptionInfo(underlyingCode);
sqlWriter.InsertBulk(dt, "[Common].dbo.[OptionInfo]");
}