public ActionResult DailyQuote(string startDate ,string endDate)
{
var start = DateTime.ParseExact(startDate,"yyyyMMdd",null);
var end = DateTime.ParseExact(endDate,"yyyyMMdd",null);
using (var context = new JYDBContext(
@"Data Source=10.254.8.204,1435;Initial Catalog=JYDB;Persist Security Info=True
;User ID=sa;Password=hxdb666
;Pooling=False"))
{
var result = context.QT_DailyQuote.Where(d => d.TradingDay >= start && d.TradingDay <= end);
if(result != null)
{
new BinaryFormatter().Serialize(this.Response.OutputStream, result.ToArray());
return null;
XmlSerializer serializer = new XmlSerializer(typeof(QT_DailyQuote[]));
StringWriter writer = new StringWriter();
serializer.Serialize(writer, result.ToArray());
var s = writer.ToString();
return this.Content(s, "text/xml");
}
return null;
}
}
public StockPrice[] GetStockPrice(string secuCode,int numRecord,DateTime endDate)
{
// string commandText = @"
// DECLARE @Num int
// SET @Num = @p0
// DECLARE @minDate dateTime
// DECLARE @maxDate dateTime
// SET @maxDate = @p1
// DECLARE @SecuCode nvarchar(4000)
// SET @SecuCode = @p2
// ;
// WITH b as
// (
// SELECT A.InnerCode,E.Tradingday,A.SecuCode,CONVERT(float,E.ClosePrice) AS [Close],
// row_number() over (partition by A.innercode order by tradingDay DESC) as [rn]
// FROM JYDB.dbo.QT_DailyQuote E
// LEFT JOIN JYDB.dbo.SecuMain A
// ON A.InnerCode=E.InnerCode
// WHERE (@SecuCode IS NULL OR A.SecuCode = @SecuCode)
// AND (E.TradingDay <= @maxDate)
// AND A.SecuCategory IN (1,2)
// AND A.SecuMarket IN (83,90)
// AND E.TurnoverVolume>0
// )
// SELECT InnerCode,Tradingday,SecuCode,[Close] FROM b
// WHERE [rn] <= @Num
// ORDER BY InnerCode,TradingDay
// ";
var result = new List<StockPrice>();
using (var context = new JYDBContext(
"JYDBLocal"))
{
using (var con = context.Database.Connection)
{
var command = new SqlCommand("GetClosePrice", (SqlConnection)con);
command.CommandType = Data.CommandType.StoredProcedure;
command.Parameters.Add("@num", Data.SqlDbType.Int);
command.Parameters["@num"].Value = numRecord;
command.Parameters.Add("@maxDate", Data.SqlDbType.DateTime);
command.Parameters["@maxDate"].Value = endDate;
command.Parameters.Add("@secuCode", Data.SqlDbType.VarChar,4000);
command.Parameters["@secuCode"].Value = secuCode;
command.CommandTimeout = 0;
con.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var sp = new StockPrice();
var v0 = reader.GetValue(0);
if (v0 != DBNull.Value)
{
sp.InnerCode = (int)reader.GetValue(0);
}
var v1 = reader.GetValue(1);
if (v1 != DBNull.Value)
{
sp.Tradingday = (DateTime)reader.GetValue(1);
}
var v2 = reader.GetValue(2);
if (v2 != DBNull.Value)
{
sp.SecuCode = (string)reader.GetValue(2);
}
var v3 = reader.GetValue(3);
if (v3 != DBNull.Value)
{
sp.Close = (double)reader.GetValue(3);
}
result.Add(sp);
}
}
}
//var result = context.Database.SqlQuery<StockPrice>(commandText, numRecord, endDate, secuCode);
return result.ToArray<StockPrice>();
}
}
