public IEnumerable <ChannelDataViewModel> GetChannelData(string channelId, string start, string end) { string dtFormat = "yyyy-MM-dd HH:mm"; DateTime dStart = DateTime.FromOADate(double.Parse(start.Replace('_', '.'))); DateTime dEnd = DateTime.FromOADate(double.Parse(end.Replace('_', '.'))); dStart = dStart.AddMinutes(1); dEnd = dEnd.AddMinutes(1); List <ChannelDataViewModel> lst = new List <ChannelDataViewModel>(); string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionString)) { string cmdText = "select [timestamp],[value] from [t_Data_Logger_" + channelId + "] where [timestamp] between '" + dStart.ToString(dtFormat) + "' and '" + dEnd.ToString(dtFormat) + "' order by [timestamp]"; SqlCommand cmd = new SqlCommand(cmdText, connection); connection.Open(); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { ChannelDataViewModel d = new ChannelDataViewModel(); d.Timestamp = (DateTime)rd["timestamp"]; if (rd["value"] != DBNull.Value) { d.Value = (double)rd["value"]; } lst.Add(d); } connection.Close(); } return(lst); }
public IEnumerable <ChannelDataViewModel> GetChannelDataYearly(string siteId, string start, string end) { var ds = UnixToLocalDate(long.Parse(start)); var de = UnixToLocalDate(long.Parse(end)); List <ChannelDataViewModel> lst = new List <ChannelDataViewModel>(); string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand cmd = new SqlCommand("p_Calculate_One_Site_Yearly_Output", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add( new SqlParameter("@SiteId", siteId)); cmd.Parameters.Add( new SqlParameter("@Start", ds)); cmd.Parameters.Add( new SqlParameter("@End", de)); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { ChannelDataViewModel d = new ChannelDataViewModel(); d.Timestamp = (DateTime)rd["timestamp"]; if (rd["value"] != DBNull.Value) { d.Value = (double)rd["value"]; } lst.Add(d); } connection.Close(); } return(lst); }