/// <summary>
/// 获取商品期货列表的信息。
/// </summary>
/// <param name="market">市场</param>
/// <returns>商品期货信息列表</returns>
public List <bondFormat> GetbondList(string market)
{
List <bondFormat> myList = new List <bondFormat>();
TDBCode[] codeArr;
tdbSource.GetCodeTable(market, out codeArr);
foreach (var item in codeArr)
{
bondFormat mybond = new bondFormat();
string code = item.m_strCode.ToUpper();
string date = "";
mybond.contractName = code;
if (market == "CZC")
{
date = MyApplication.RemoveNotNumber(code);
date = "1" + date;
}
else
{
date = MyApplication.RemoveNotNumber(code);
}
if (date.Length != 4)
{
continue;
}
if (date.Length == 4)
{
int num = Convert.ToInt32(date);
if (num >= 1304)
{
mybond.contractName = code + "." + market;
mybond.code = code;
mybond.startDate = (num - 100 + 200000) * 100 + 01;
mybond.endDate = (num + 200000) * 100 + 31;
mybond.market = market;
myList.Add(mybond);
}
}
}
return(myList);
}
/// <summary>
/// 核心存储函数。根据市场信息等存储商品期货数据。
/// </summary>
public void StoreData()
{
foreach (var bond in bondList)
{
string tableName = "MarketData_" + bond.code + "_" + bond.market + "E";
int maxRecordDate = 0;
foreach (int today in myTradeDays.myTradeDays)
{
if (today > bond.endDate)
//如果在期货交割之后，不再进行记录
{
break;
}
int yesterday = TradeDays.GetPreviousTradeDay(today);
string todayDataBase = "TradeMarket" + (today / 100).ToString();
//string todayConnectString = "server=(local);database=" + todayDataBase + ";Integrated Security=true;";
//string todayConnectString = "server=192.168.38.217;database=" + todayDataBase + ";uid =sa;pwd=maoheng0;";
string todayConnectString = "server=145.146.30.152;database=" + todayDataBase + ";uid =spqhhqld;pwd=spqhhqld;";
if (SqlApplication.CheckDataBaseExist(todayDataBase, orignalConnectString) == false)
{
maxRecordDate = 0;
}
else if (yesterday / 100 != today / 100 || today == startDate || maxRecordDate == 0)
{
if (SqlApplication.CheckExist(todayDataBase, tableName, todayConnectString) == true)
{
maxRecordDate = MaxRecordDate(tableName, todayConnectString);
}
}
if (maxRecordDate < today)
//若没有记录数据，需要重新记录
//若数据存在，存储数据,否则需要跳过
{
TDBReqFuture reqFuture = new TDBReqFuture(bond.contractName, today, today);
TDBFutureAB[] futureABArr;
TDBErrNo nErrInner = tdbSource.GetFutureAB(reqFuture, out futureABArr);
if (futureABArr.Length == 0)
{
continue;
}
//string yesterdayDataBase = "TradeMarket" + (yesterday / 100).ToString();
//string yesterdayConnectString = "server=(local);database=" + yesterdayDataBase + ";Integrated Security=true;";
//string yesterdayConnectString = "server=192.168.38.217;database=" + yesterdayDataBase + ";uid =sa;pwd=maoheng0;";Security=true;";
if (SqlApplication.CheckDataBaseExist(todayDataBase, orignalConnectString) == false)
//检测当日对应的数据库是否存在
{
CreateDataBase(todayDataBase, orignalConnectString);
}
if (SqlApplication.CheckExist(todayDataBase, tableName, orignalConnectString) == false)
{
CreateTable(tableName, todayConnectString);
}
//if (yesterdayDataBase != todayDataBase)
////如果前一交易日所在月份和当日不同，检测上一个月的数据库是否存在
//{
// if (SqlApplication.CheckDataBaseExist(yesterdayDataBase, orignalConnectString) == false)
// {
// CreateDataBase(yesterdayDataBase, orignalConnectString);
// }
// if (SqlApplication.CheckExist(yesterdayDataBase, tableName, orignalConnectString) == false)
// {
// CreateTable(tableName, yesterdayConnectString);
// }
//}
//判断数据是否已经存储，若数据存在，默认已经记录，仅记录数据条数，写入日志文档，靠人工来校对
// int alreadyRecord = CountRecordNumber(tableName, todayConnectString, today);
bondShot[] dataList;
dataList = ModifyData(futureABArr, bond.contractName, today);
StoreDataDaily(tableName, todayConnectString, dataList);
Console.WriteLine("Date:{0}, table:{1}, MaxRecordDate:{2}, Wind:{3}", today, tableName, maxRecordDate, futureABArr.Length);
string log = "Date:" + today.ToString() + ", table:" + tableName + ", MaxRecordDate:" + maxRecordDate.ToString() + ", Wind:" + futureABArr.Length.ToString();
MyApplication.TxtWrite(logName, log);
maxRecordDate = today;
}
else
{
Console.WriteLine("Date:{0}, table:{1}, MaxRecordDate:{2}", today, tableName, maxRecordDate);
string log = "Date:" + today.ToString() + ", table:" + tableName + ", MaxRecordDate:" + maxRecordDate.ToString();
MyApplication.TxtWrite(logName, log);
}
}
}
}
/// <summary>
/// 逐日逐品种存储数据。
/// </summary>
public void StoreDataDaily(string tableName, string connectString, bondShot[] data)
{
using (SqlConnection conn = new SqlConnection(connectString))
{
conn.Open();
DataTable todayData = new DataTable();
#region DataTable的列名的建立
todayData.Columns.Add("id", typeof(int));
todayData.Columns.Add("stkcd", typeof(string));
todayData.Columns.Add("tdate", typeof(string));
todayData.Columns.Add("ndate", typeof(string));
todayData.Columns.Add("ttime", typeof(string));
todayData.Columns.Add("cp", typeof(double));
todayData.Columns.Add("S1", typeof(double));
todayData.Columns.Add("S2", typeof(double));
todayData.Columns.Add("S3", typeof(double));
todayData.Columns.Add("S4", typeof(double));
todayData.Columns.Add("S5", typeof(double));
todayData.Columns.Add("S6", typeof(double));
todayData.Columns.Add("S7", typeof(double));
todayData.Columns.Add("S8", typeof(double));
todayData.Columns.Add("S9", typeof(double));
todayData.Columns.Add("S10", typeof(double));
todayData.Columns.Add("B1", typeof(double));
todayData.Columns.Add("B2", typeof(double));
todayData.Columns.Add("B3", typeof(double));
todayData.Columns.Add("B4", typeof(double));
todayData.Columns.Add("B5", typeof(double));
todayData.Columns.Add("B6", typeof(double));
todayData.Columns.Add("B7", typeof(double));
todayData.Columns.Add("B8", typeof(double));
todayData.Columns.Add("B9", typeof(double));
todayData.Columns.Add("B10", typeof(double));
todayData.Columns.Add("SV1", typeof(double));
todayData.Columns.Add("SV2", typeof(double));
todayData.Columns.Add("SV3", typeof(double));
todayData.Columns.Add("SV4", typeof(double));
todayData.Columns.Add("SV5", typeof(double));
todayData.Columns.Add("SV6", typeof(double));
todayData.Columns.Add("SV7", typeof(double));
todayData.Columns.Add("SV8", typeof(double));
todayData.Columns.Add("SV9", typeof(double));
todayData.Columns.Add("SV10", typeof(double));
todayData.Columns.Add("BV1", typeof(double));
todayData.Columns.Add("BV2", typeof(double));
todayData.Columns.Add("BV3", typeof(double));
todayData.Columns.Add("BV4", typeof(double));
todayData.Columns.Add("BV5", typeof(double));
todayData.Columns.Add("BV6", typeof(double));
todayData.Columns.Add("BV7", typeof(double));
todayData.Columns.Add("BV8", typeof(double));
todayData.Columns.Add("BV9", typeof(double));
todayData.Columns.Add("BV10", typeof(double));
todayData.Columns.Add("hp", typeof(double));
todayData.Columns.Add("lp", typeof(double));
todayData.Columns.Add("HighLimit", typeof(double));
todayData.Columns.Add("LowLimit", typeof(double));
todayData.Columns.Add("ts", typeof(double));
todayData.Columns.Add("tt", typeof(double));
todayData.Columns.Add("OPNPRC", typeof(double));
todayData.Columns.Add("PRECLOSE", typeof(double));
todayData.Columns.Add("Settle", typeof(double));
todayData.Columns.Add("PrevSettle", typeof(double));
todayData.Columns.Add("CurrDelta", typeof(int));
todayData.Columns.Add("PreDelta", typeof(int));
todayData.Columns.Add("OpenInterest", typeof(int));
todayData.Columns.Add("PreOpenInterest", typeof(int));
todayData.Columns.Add("LocalRecTime", typeof(string));
todayData.Columns.Add("TradeStatus", typeof(string));
#endregion
foreach (bondShot f in data)
{
#region 将数据写入每一行中。
DataRow r = todayData.NewRow();
r["id"] = f.id;
r["stkcd"] = f.stkcd;
r["tdate"] = f.tdate;
r["ndate"] = f.ndate;
r["ttime"] = f.ttime;
r["cp"] = f.cp;
r["S1"] = f.S1;
r["S2"] = f.S2;
r["S3"] = f.S3;
r["S4"] = f.S4;
r["S5"] = f.S5;
r["SV1"] = f.SV1;
r["SV2"] = f.SV2;
r["SV3"] = f.SV3;
r["SV4"] = f.SV4;
r["SV5"] = f.SV5;
r["B1"] = f.B1;
r["B2"] = f.B2;
r["B3"] = f.B3;
r["B4"] = f.B4;
r["B5"] = f.B5;
r["BV1"] = f.BV1;
r["BV2"] = f.BV2;
r["BV3"] = f.BV3;
r["BV4"] = f.BV4;
r["BV5"] = f.BV5;
r["ts"] = f.ts;
r["tt"] = f.tt;
r["OPNPRC"] = f.OPNPRC;
r["PRECLOSE"] = f.PRECLOSE;
r["settle"] = (f.Settle < 0)?0:f.Settle;
r["PrevSettle"] = f.PrevSettle;
r["CurrDelta"] = f.CurrDelta;
r["hp"] = f.hp;
r["lp"] = f.lp;
r["OpenInterest"] = f.OpenInterest;
r["PreOpenInterest"] = f.PreOpenInterest;
r["TradeStatus"] = f.TradeStatus;
todayData.Rows.Add(r);
#endregion
}
using (SqlBulkCopy bulk = new SqlBulkCopy(connectString))
{
try
{
bulk.BatchSize = 100000;
bulk.DestinationTableName = tableName;
#region 依次建立数据的映射。
bulk.ColumnMappings.Add("id", "id");
bulk.ColumnMappings.Add("stkcd", "stkcd");
bulk.ColumnMappings.Add("tdate", "tdate");
bulk.ColumnMappings.Add("ndate", "ndate");
bulk.ColumnMappings.Add("ttime", "ttime");
bulk.ColumnMappings.Add("cp", "cp");
bulk.ColumnMappings.Add("S1", "S1");
bulk.ColumnMappings.Add("S2", "S2");
bulk.ColumnMappings.Add("S3", "S3");
bulk.ColumnMappings.Add("S4", "S4");
bulk.ColumnMappings.Add("S5", "S5");
bulk.ColumnMappings.Add("S6", "S6");
bulk.ColumnMappings.Add("S7", "S7");
bulk.ColumnMappings.Add("S8", "S8");
bulk.ColumnMappings.Add("S9", "S9");
bulk.ColumnMappings.Add("S10", "S10");
bulk.ColumnMappings.Add("B1", "B1");
bulk.ColumnMappings.Add("B2", "B2");
bulk.ColumnMappings.Add("B3", "B3");
bulk.ColumnMappings.Add("B4", "B4");
bulk.ColumnMappings.Add("B5", "B5");
bulk.ColumnMappings.Add("B6", "B6");
bulk.ColumnMappings.Add("B7", "B7");
bulk.ColumnMappings.Add("B8", "B8");
bulk.ColumnMappings.Add("B9", "B9");
bulk.ColumnMappings.Add("B10", "B10");
bulk.ColumnMappings.Add("SV1", "SV1");
bulk.ColumnMappings.Add("SV2", "SV2");
bulk.ColumnMappings.Add("SV3", "SV3");
bulk.ColumnMappings.Add("SV4", "SV4");
bulk.ColumnMappings.Add("SV5", "SV5");
bulk.ColumnMappings.Add("SV6", "SV6");
bulk.ColumnMappings.Add("SV7", "SV7");
bulk.ColumnMappings.Add("SV8", "SV8");
bulk.ColumnMappings.Add("SV9", "SV9");
bulk.ColumnMappings.Add("SV10", "SV10");
bulk.ColumnMappings.Add("BV1", "BV1");
bulk.ColumnMappings.Add("BV2", "BV2");
bulk.ColumnMappings.Add("BV3", "BV3");
bulk.ColumnMappings.Add("BV4", "BV4");
bulk.ColumnMappings.Add("BV5", "BV5");
bulk.ColumnMappings.Add("BV6", "BV6");
bulk.ColumnMappings.Add("BV7", "BV7");
bulk.ColumnMappings.Add("BV8", "BV8");
bulk.ColumnMappings.Add("BV9", "BV9");
bulk.ColumnMappings.Add("BV10", "BV10");
bulk.ColumnMappings.Add("hp", "hp");
bulk.ColumnMappings.Add("lp", "lp");
bulk.ColumnMappings.Add("HighLimit", "HighLimit");
bulk.ColumnMappings.Add("LowLimit", "LowLimit");
bulk.ColumnMappings.Add("ts", "ts");
bulk.ColumnMappings.Add("tt", "tt");
bulk.ColumnMappings.Add("OPNPRC", "OPNPRC");
bulk.ColumnMappings.Add("PRECLOSE", "PRECLOSE");
bulk.ColumnMappings.Add("Settle", "Settle");
bulk.ColumnMappings.Add("PrevSettle", "PrevSettle");
bulk.ColumnMappings.Add("CurrDelta", "CurrDelta");
bulk.ColumnMappings.Add("PreDelta", "PreDelta");
bulk.ColumnMappings.Add("OpenInterest", "OpenInterest");
bulk.ColumnMappings.Add("PreOpenInterest", "PreOpenInterest");
bulk.ColumnMappings.Add("LocalRecTime", "LocalRecTime");
bulk.ColumnMappings.Add("TradeStatus", "TradeStatus");
#endregion
bulk.WriteToServer(todayData);
}
catch (Exception myerror)
{
//记录错误信息
System.Console.WriteLine(myerror.Message);
MyApplication.TxtWrite(logName, myerror.Message);
}
}
conn.Close();
}
}
/// <summary>
/// 核心存储函数。根据市场信息等存储股票数据。
/// </summary>
public void StoreData()
{
foreach (var stock in stockList)
{
string tableName = "MarketData_" + stock.code + "_" + stock.market;
int maxRecordDate = 0;
foreach (int today in myTradeDays.myTradeDays)
{
int yesterday = TradeDays.GetPreviousTradeDay(today);
string todayDataBase = "TradeMarket" + (today / 100).ToString();
//string todayConnectString = "server=192.168.38.217;database=" + todayDataBase + ";uid =sa;pwd=maoheng0;";
//string todayConnectString = "server=(local);database=" + todayDataBase + ";Integrated Security=true;";
string todayConnectString = "server=192.168.38.209;database=" + todayDataBase + ";uid =sa;pwd=280514;";
if (SqlApplication.CheckDataBaseExist(todayDataBase, orignalConnectString) == false)
{
maxRecordDate = 0;
}
if (yesterday / 100 != today / 100 || today == startDate || maxRecordDate == 0)
{
if (SqlApplication.CheckDataBaseExist(todayDataBase, orignalConnectString) == true && SqlApplication.CheckExist(todayDataBase, tableName, orignalConnectString) == true && SqlApplication.CheckExist(todayDataBase, tableName, todayConnectString) == true)
{
maxRecordDate = MaxRecordDate(tableName, todayConnectString);
}
}
if (maxRecordDate < today)
//若没有记录数据，需要重新记录
//若数据存在，存储数据,否则需要跳过
{
TDBReq reqTick = new TDBReq(stock.code + "." + stock.market, today, today);
TDBTickAB[] tickArr;
TDBErrNo nErrInner = tdbSource.GetTickAB(reqTick, out tickArr);
if (tickArr.Length == 0)
{
continue;
}
if (SqlApplication.CheckDataBaseExist(todayDataBase, orignalConnectString) == false)
//检测当日对应的数据库是否存在
{
CreateDataBase(todayDataBase, orignalConnectString);
}
if (SqlApplication.CheckExist(todayDataBase, tableName, orignalConnectString) == false)
{
CreateTable(tableName, todayConnectString);
}
//判断数据是否已经存储，若数据存在，默认已经记录，仅记录数据条数，写入日志文档，靠人工来校对
// int alreadyRecord = CountRecordNumber(tableName, todayConnectString, today);
stockShot[] dataList = ModifyData(tickArr, stock.code, today);
StoreDataDaily(tableName, todayConnectString, dataList);
Console.WriteLine("Date:{0}, table:{1}, MaxRecordDate:{2}, Wind:{3}", today, tableName, maxRecordDate, tickArr.Length);
string log = "Date:" + today.ToString() + ", table:" + tableName + ", MaxRecordDate:" + maxRecordDate.ToString() + ", Wind:" + tickArr.Length.ToString();
MyApplication.TxtWrite(logName, log);
maxRecordDate = today;
}
else
{
Console.WriteLine("Date:{0}, table:{1}, MaxRecordDate:{2}", today, tableName, maxRecordDate);
string log = "Date:" + today.ToString() + ", table:" + tableName + ", MaxRecordDate:" + maxRecordDate.ToString();
MyApplication.TxtWrite(logName, log);
}
}
}
}