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