public void SaveToDB()
{
SqlCommand cmd = null;
if (GD.SubAppDbTool == null)
{
return;
}
SqlConnection conn = GD.SubAppDbTool.GetDbConnection();
try
{
conn.Open();
string delstr = "delete from SubStockInfo where Symbol=@Symbol and StartDate=@StartDate";
string addstr = "insert into SubStockInfo (Symbol, SymbolName, Market, StartDate, StopDate, DebitDate, DrawDate, " +
"Share, Txun, TempRefer, Refer, Volumn, CouponDate, RefundDate, UpdateTime, LastTDPrice, PriceDiff, PriceDiffRatio, TAMarket) " +
"values (@Symbol, @SymbolName, @Market, @StartDate, @StopDate, @DebitDate, @DrawDate, @Share, @Txun, " +
"@TempRefer, @Refer, @Volumn, @CouponDate, @RefundDate, @UpdateTime, @LastTDPrice, @PriceDiff, @PriceDiffRatio, @TAMarket)";
//SqlCommand cmd = new SqlCommand(delstr, conn);
cmd = new SqlCommand();
cmd.Connection = conn;
foreach (KeyValuePair <string, SubStock> item in this)
{
SubStock stock = (SubStock)item.Value;
cmd.Parameters.Clear();
cmd.CommandText = delstr;
cmd.Parameters.AddWithValue("@Symbol", stock.Symbol);
cmd.Parameters.AddWithValue("@StartDate", PushUtils.RepublicToAD(stock.StartDate));
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = addstr;
cmd.Parameters.AddWithValue("@Symbol", stock.Symbol);
cmd.Parameters.AddWithValue("@SymbolName", stock.SymbolName);
cmd.Parameters.AddWithValue("@Market", stock.Market);
if (!string.IsNullOrEmpty(stock.StartDate))
{
cmd.Parameters.AddWithValue("@StartDate", PushUtils.RepublicToAD(stock.StartDate));
}
else
{
cmd.Parameters.AddWithValue("@StartDate", DBNull.Value);
}
if (!string.IsNullOrEmpty(stock.StopDate))
{
cmd.Parameters.AddWithValue("@StopDate", PushUtils.RepublicToAD(stock.StopDate));
}
else
{
cmd.Parameters.AddWithValue("@StopDate", DBNull.Value);
}
if (!string.IsNullOrEmpty(stock.DebitDate))
{
cmd.Parameters.AddWithValue("@DebitDate", PushUtils.RepublicToAD(stock.DebitDate));
}
else
{
cmd.Parameters.AddWithValue("@DebitDate", DBNull.Value);
}
if (!string.IsNullOrEmpty(stock.DrawDate))
{
cmd.Parameters.AddWithValue("@DrawDate", PushUtils.RepublicToAD(stock.DrawDate));
}
else
{
cmd.Parameters.AddWithValue("@DrawDate", DBNull.Value);
}
if (!string.IsNullOrEmpty(stock.Share))
{
cmd.Parameters.AddWithValue("@Share", stock.Share);
}
else
{
cmd.Parameters.AddWithValue("@Share", 0);
}
if (!string.IsNullOrEmpty(stock.Txun))
{
cmd.Parameters.AddWithValue("@Txun", stock.Txun);
}
else
{
cmd.Parameters.AddWithValue("@Txun", 0);
}
if (!string.IsNullOrEmpty(stock.TempRefer))
{
cmd.Parameters.AddWithValue("@TempRefer", stock.TempRefer);
}
else
{
cmd.Parameters.AddWithValue("@TempRefer", 0);
}
if (!string.IsNullOrEmpty(stock.Refer))
{
cmd.Parameters.AddWithValue("@Refer", stock.Refer);
}
else
{
cmd.Parameters.AddWithValue("@Refer", 0);
}
if (!string.IsNullOrEmpty(stock.Volumn))
{
cmd.Parameters.AddWithValue("@Volumn", stock.Volumn);
}
else
{
cmd.Parameters.AddWithValue("@Volumn", 0);
}
if (!string.IsNullOrEmpty(stock.CouponDate))
{
cmd.Parameters.AddWithValue("@CouponDate", PushUtils.RepublicToAD(stock.CouponDate));
}
else
{
cmd.Parameters.AddWithValue("@CouponDate", DBNull.Value);
}
if (!string.IsNullOrEmpty(stock.RefundDate))
{
cmd.Parameters.AddWithValue("@RefundDate", PushUtils.RepublicToAD(stock.RefundDate));
}
else
{
cmd.Parameters.AddWithValue("@RefundDate", DBNull.Value);
}
cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
if (!string.IsNullOrEmpty(stock.LastDeal))
{
cmd.Parameters.AddWithValue("@LastTDPrice", stock.LastDeal);
}
else
{
cmd.Parameters.AddWithValue("@LastTDPrice", DBNull.Value);
}
if (!string.IsNullOrEmpty(stock.PriceDiff))
{
cmd.Parameters.AddWithValue("@PriceDiff", stock.PriceDiff);
}
else
{
cmd.Parameters.AddWithValue("@PriceDiff", DBNull.Value);
}
if (!string.IsNullOrEmpty(stock.PriceDiffRatio))
{
cmd.Parameters.AddWithValue("@PriceDiffRatio", stock.PriceDiffRatio.Trim('%'));
}
else
{
cmd.Parameters.AddWithValue("@PriceDiffRatio", DBNull.Value);
}
cmd.Parameters.AddWithValue("@TAMarket", (object)stock.TAMarket ?? DBNull.Value);
cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
GD.aplogger.Error(ex.Message);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
conn.Close();
}
//using (SqlConnection SqlConn = new SqlConnection(constr))
//{
// /*SqlCommand Cmd = new SqlCommand(strSQL, SqlConn);
// SqlDataAdapter da = new SqlDataAdapter();
// try
// {
// .........
// }
// catch (Exception ex)
// {
// }
// finally
// {
// SqlConnection.ClearPool(SqlConn);
// }*/
//}
}
public void LoadList(string xml)
{
string referdumm = "";
this.Clear();
TextReader sr = new StringReader(xml);
XElement xe = XElement.Load(sr);
sr.Close();
//SubStock item = null;
/*var qitems = from qitem in xe.Descendants("NFSLISTS")
* select new
* {
* Symbol = qitem.Attribute("symb") == null ? "" : qitem.Attribute("symb").Value,
* SymbolName = qitem.Attribute("name") == null ? "" : qitem.Attribute("name").Value,
* Market = qitem.Attribute("mark") == null ? "" : qitem.Attribute("mark").Value,
* StartDate = qitem.Attribute("name") == null ? "" : qitem.Attribute("name").Value
* qitem.Element("NFSLIDURS").Element()
* };*/
var qitems = from qitem in xe.Descendants("NFSLISTS")
select qitem;
TcpClient tcpClientQuote = new TcpClient();
string ldate = "";
try
{
tcpClientQuote.Connect(ConfigurationManager.AppSettings["QueryHost"], int.Parse(ConfigurationManager.AppSettings["QueryPort"]));
ldate = PushUtils.GetTALastTradeDate(tcpClientQuote, int.Parse(ConfigurationManager.AppSettings["OneTryTime"]), int.Parse(ConfigurationManager.AppSettings["RetryTimes"]));
}
catch (Exception ex)
{
GD.aplogger.Error(string.Format("SubStock loadlist TCP connect exception - {0}", ex.Message));
}
foreach (var qitem in qitems)
{
SubStock ss = new SubStock();
var nitems = from nitem in qitem.Descendants("NFSLIDUR")
select nitem;
referdumm = "";
foreach (var nitem in nitems)
{
if (nitem.Attribute("duno") == null)
{
continue;
}
if (int.Parse(nitem.Attribute("duno").Value) == 0)
{
ss.StartDate = nitem.Attribute("duii").Value;
ss.StopDate = nitem.Attribute("ducl").Value;
}
else if (int.Parse(nitem.Attribute("duno").Value) == 5)
{
ss.DebitDate = nitem.Attribute("duii").Value;
}
else if (int.Parse(nitem.Attribute("duno").Value) == 10)
{
ss.DrawDate = nitem.Attribute("duii").Value;
}
else if (int.Parse(nitem.Attribute("duno").Value) == 20)
{
ss.RefundDate = nitem.Attribute("duii").Value;
}
else if (int.Parse(nitem.Attribute("duno").Value) == 30)
{
ss.CouponDate = nitem.Attribute("duii").Value;
}
else if (int.Parse(nitem.Attribute("duno").Value) == 91)
{
referdumm = nitem.Attribute("dumm").Value;
//if (referdumm == null || int.Parse(referdumm) > 0)
//int result= 0;
decimal result = 0;
//if (!int.TryParse(referdumm, out result) || result == 0)
// referdumm = null;
if (!decimal.TryParse(referdumm, out result) || result == 0)
{
referdumm = null;
}
}
}
ss.Symbol = qitem.Attribute("symb") == null ? "" : qitem.Attribute("symb").Value;
string memo = qitem.Attribute("memo") == null ? "" : qitem.Attribute("memo").Value;
ss.SymbolName = qitem.Attribute("name") == null ? "" : qitem.Attribute("name").Value;
if (memo.IndexOf('(') >= 0)
{
ss.Market = memo.Substring(memo.IndexOf('(') + 1, memo.IndexOf(')') - memo.IndexOf('(') - 1);
ss.SymbolName = memo.Substring(0, memo.IndexOf('('));
}
ss.Share = qitem.Attribute("shar") == null ? "" : qitem.Attribute("shar").Value;
ss.TempRefer = string.IsNullOrEmpty(referdumm) ? qitem.Attribute("refe").Value : referdumm;
if (DateTime.Today > DateTime.Parse(PushUtils.RepublicToAD(ss.DebitDate)))
{
ss.Refer = qitem.Attribute("refe") == null ? "" : qitem.Attribute("refe").Value;
}
ss.Volumn = qitem.Attribute("valu") == null ? "" : qitem.Attribute("valu").Value;
ss.Txun = qitem.Attribute("txun") == null ? "" : qitem.Attribute("txun").Value;
ss.TAMarket = qitem.Attribute("mark") == null ? "" : qitem.Attribute("mark").Value;
/*try{
* tcpClientQuote.Connect(ConfigurationManager.AppSettings["QueryHost"], int.Parse(ConfigurationManager.AppSettings["QueryPort"]));
* if (tcpClientQuote.Connected) {
* ss.LastDeal = PushUtils.QueryTAStockRefer(tcpClientQuote, ss.Symbol, int.Parse(ConfigurationManager.AppSettings["OneTryTime"]), int.Parse(ConfigurationManager.AppSettings["RetryTimes"]));
* }
* }catch (Exception ex) {
* GD.aplogger.Error(string.Format("SubStock loadlist exception - {0}", ex.Message));
* }finally{
* if (tcpClientQuote != null)
* tcpClientQuote.Close();
* }*/
//Add(ss.Symbol, ss);
if (DateTime.Today > DateTime.Parse(PushUtils.RepublicToAD(ss.CouponDate)))
{
ss = null;
}
else
{
try
{
if (tcpClientQuote.Connected)
{
ss.LastDeal = PushUtils.QueryTAStockRefer(tcpClientQuote, ss.Symbol, ldate,
int.Parse(ConfigurationManager.AppSettings["OneTryTime"]), int.Parse(ConfigurationManager.AppSettings["RetryTimes"]));
}
}
catch (Exception ex)
{
GD.aplogger.Error(string.Format("SubStock loadlist LastDeal exception - {0}", ex.Message));
}
Add(ss.Symbol, ss);
}
}//foreach
if (tcpClientQuote != null)
{
tcpClientQuote.Close();
}
}