public static List <WCCountry> getWCCountryList() { Database WeatherHelperDataContext = DatabaseConfig.GetWeatherHelperDatabase(); List <WCCountry> result = null; try { using (DbCommand command = WeatherHelperDataContext.GetSqlStringCommand( @"select AreaCode,WCCode,WCCountryName from WCCode order by AreaCode")) { IDataReader reader = WeatherHelperDataContext.ExecuteReader(command); result = new List <WCCountry>(); while (reader.Read()) { WCCountry response = new WCCountry(); response.AreaCode = reader["AreaCode"].ToString(); response.WCCode = reader["WCCode"].ToString(); response.Name = reader["WCCountryName"].ToString(); result.Add(response); } return(result); } } catch (Exception ex) { throw ex; } }
public static List <NotifyRule> getAllNotifyRule() { Database WeatherHelperDataContext = DatabaseConfig.GetWeatherHelperDatabase(); List <NotifyRule> result = null; try { using (DbCommand command = WeatherHelperDataContext.GetSqlStringCommand( @"select RuleID,RuleName,RuleDetail,SchedulingTime from NotifyRule")) { IDataReader reader = WeatherHelperDataContext.ExecuteReader(command); result = new List <NotifyRule>(); while (reader.Read()) { NotifyRule response = new NotifyRule(); response.RuleID = reader["RuleID"].ToString(); response.RuleName = reader["RuleName"].ToString(); response.RuleDetail = reader["RuleDetail"].ToString(); response.SchedulingTime = reader["SchedulingTime"].ToString(); result.Add(response); } return(result); } } catch (Exception ex) { throw ex; } }
public static List <MemberInfo> getAllMemberInfo() { Database WeatherHelperDataContext = DatabaseConfig.GetWeatherHelperDatabase(); List <MemberInfo> result = null; try { using (DbCommand command = WeatherHelperDataContext.GetSqlStringCommand( @"select UserNO,UserID,UserName,UserEmail from MemberInfo")) { IDataReader reader = WeatherHelperDataContext.ExecuteReader(command); result = new List <MemberInfo>(); while (reader.Read()) { MemberInfo response = new MemberInfo(); response.UserNO = Convert.ToInt32(reader["UserNO"]); response.UserID = reader["UserID"].ToString(); response.UserName = reader["UserName"].ToString(); response.UserEmail = reader["UserEmail"].ToString(); result.Add(response); } return(result); } } catch (Exception ex) { throw ex; } }
public static List <ForeseeWeatherInfo> GetForeseeWeatherData() { Database WeatherHelperDataContext = DatabaseConfig.GetWeatherHelperDatabase(); List <ForeseeWeatherInfo> result = null; try { using (DbCommand command = WeatherHelperDataContext.GetSqlStringCommand( @"select * from V_FWeatherData order by [date],areacode")) { IDataReader reader = WeatherHelperDataContext.ExecuteReader(command); result = new List <ForeseeWeatherInfo>(); while (reader.Read()) { ForeseeWeatherInfo response = new ForeseeWeatherInfo(); response.AreaName = reader["AreaName"].ToString(); response.CountryName = reader["WCCountryName"].ToString(); response.ForeseWeatherID = reader["ForeseWeatherID"].ToString(); response.AreaCode = reader["AreaCode"].ToString(); response.WCCode = reader["WCCode"].ToString(); response.Skycodeday = reader["Skycodeday"].ToString(); response.Date = reader["Date"].ToString(); response.Skytextday = reader["Skytextday"].ToString(); response.Day = reader["Day"].ToString(); response.Shortday = reader["Shortday"].ToString(); response.Precip = reader["Precip"].ToString(); //response.SkycodeImg = reader["SkycodeImg"].ToString(); response.Low = reader["Low"].ToString(); response.High = reader["High"].ToString(); response.WeatherStatus = reader["WeatherStatus"].ToString(); result.Add(response); } return(result); } } catch (Exception ex) { throw ex; } }
public static void AddForeseeWeatherData(List <WeatherInfo> othersDayInfo) { Database WeatherHelperDataContext = DatabaseConfig.GetWeatherHelperDatabase(); if (othersDayInfo == null || othersDayInfo.Count == 0) { throw new ArgumentNullException("othersDayInfo"); } try { string updateSql = @"INSERT INTO [ForeseeWeatherData] ([AreaCode],[WCCode],[Skycodeday], [Skytextday],[Date],[Day],[Shortday],[Precip],[SkycodeImg] ,[Low] ,[High] ,[WeatherStatus]) VALUES (@AreaCode,@WCCode,@Skycodeday,@Skytextday,@Date,@Day,@Shortday,@Precip,@SkycodeImg,@Low,@High,@WeatherStatus) "; DbCommand queryCommand = WeatherHelperDataContext.GetSqlStringCommand( updateSql); foreach (var day in othersDayInfo) { queryCommand.Parameters.Clear(); WeatherHelperDataContext.AddInParameter(queryCommand, "@AreaCode", DbType.Int32, Convert.ToInt32(day.AreaCode)); WeatherHelperDataContext.AddInParameter(queryCommand, "@WCCode", DbType.String, day.WCCode); WeatherHelperDataContext.AddInParameter(queryCommand, "@Skycodeday", DbType.String, day.Skycodeday); WeatherHelperDataContext.AddInParameter(queryCommand, "@Skytextday", DbType.String, day.Skytextday); WeatherHelperDataContext.AddInParameter(queryCommand, "@Date", DbType.String, day.Date); WeatherHelperDataContext.AddInParameter(queryCommand, "@Day", DbType.String, day.Day); WeatherHelperDataContext.AddInParameter(queryCommand, "@Shortday", DbType.String, day.Shortday); WeatherHelperDataContext.AddInParameter(queryCommand, "@Precip", DbType.Int32, Convert.ToInt32(day.Precip)); WeatherHelperDataContext.AddInParameter(queryCommand, "@SkycodeImg", DbType.String, day.SkycodeImg); WeatherHelperDataContext.AddInParameter(queryCommand, "@Low", DbType.Int32, Convert.ToInt32(day.Low)); WeatherHelperDataContext.AddInParameter(queryCommand, "@High", DbType.Int32, Convert.ToInt32(day.High)); WeatherHelperDataContext.AddInParameter(queryCommand, "@WeatherStatus", DbType.String, day.WeatherStatus); WeatherHelperDataContext.ExecuteNonQuery(queryCommand); } } catch (Exception ex) { throw ex; } }
public static void NewWeatherData(WeatherInfo currnetDayInfo) { Database WeatherHelperDataContext = DatabaseConfig.GetWeatherHelperDatabase(); if (currnetDayInfo == null) { throw new ArgumentNullException("currnetDayInfo"); } try { string updateSql = @"INSERT INTO [WeatherData] ([AreaCode],[WCCode],[Temperature] ,[Skycode] ,[Skytext] ,[Observationtime] ,[Observationpoint] ,[Feelslike] ,[Humidity] ,[Windspeed] ,[Winddisplay] ,[SkycodeImg] ,[Low] ,[High] ,[Shortday] ,[WeatherStatus]) VALUES (@AreaCode,@WCCode,@Temperature,@Skycode,@Skytext,@Observationtime,@Observationpoint,@Feelslike,@Humidity,@Windspeed,@Winddisplay,@SkycodeImg,@Low,@High,@Shortday,@WeatherStatus) "; DbCommand queryCommand = WeatherHelperDataContext.GetSqlStringCommand( updateSql); WeatherHelperDataContext.AddInParameter(queryCommand, "@AreaCode", DbType.Int32, Convert.ToInt32(currnetDayInfo.AreaCode)); WeatherHelperDataContext.AddInParameter(queryCommand, "@WCCode", DbType.String, currnetDayInfo.WCCode); WeatherHelperDataContext.AddInParameter(queryCommand, "@Temperature", DbType.Int32, Convert.ToInt32(currnetDayInfo.Temperature)); WeatherHelperDataContext.AddInParameter(queryCommand, "@Skycode", DbType.String, currnetDayInfo.Skycode); WeatherHelperDataContext.AddInParameter(queryCommand, "@Skytext", DbType.String, currnetDayInfo.Skytext); WeatherHelperDataContext.AddInParameter(queryCommand, "@Observationtime", DbType.String, currnetDayInfo.Observationtime); WeatherHelperDataContext.AddInParameter(queryCommand, "@Observationpoint", DbType.String, currnetDayInfo.Observationpoint); WeatherHelperDataContext.AddInParameter(queryCommand, "@Feelslike", DbType.String, currnetDayInfo.Feelslike); WeatherHelperDataContext.AddInParameter(queryCommand, "@Humidity", DbType.Int32, Convert.ToInt32(currnetDayInfo.Humidity)); WeatherHelperDataContext.AddInParameter(queryCommand, "@Windspeed", DbType.String, currnetDayInfo.Windspeed); WeatherHelperDataContext.AddInParameter(queryCommand, "@Winddisplay", DbType.String, currnetDayInfo.Winddisplay); WeatherHelperDataContext.AddInParameter(queryCommand, "@SkycodeImg", DbType.String, currnetDayInfo.SkycodeImg); WeatherHelperDataContext.AddInParameter(queryCommand, "@Low", DbType.Int32, Convert.ToInt32(currnetDayInfo.Low)); WeatherHelperDataContext.AddInParameter(queryCommand, "@High", DbType.Int32, Convert.ToInt32(currnetDayInfo.High)); WeatherHelperDataContext.AddInParameter(queryCommand, "@Shortday", DbType.String, currnetDayInfo.Shortday); WeatherHelperDataContext.AddInParameter(queryCommand, "@WeatherStatus", DbType.String, currnetDayInfo.WeatherStatus); WeatherHelperDataContext.ExecuteNonQuery(queryCommand); } catch (Exception ex) { throw ex; } }
public static List <WeatherInfo> GetWeatherInfo(DateTime sdate, DateTime edate, int q_count = 1000) { Database WeatherHelperDataContext = DatabaseConfig.GetWeatherHelperDatabase(); if (q_count <= 0 || q_count > 1000) { throw new ArgumentOutOfRangeException("q_count should > 0 and q_count should <= 10000"); } List <WeatherInfo> result = null; try { using (DbCommand command = WeatherHelperDataContext.GetSqlStringCommand( @"SELECT TOP " + q_count + @" [AreaName] ,[WCCountryName] ,[WeatherID] ,[AreaCode] ,[WCCode] ,[Temperature] ,[Skycode] ,[Skytext] ,[Observationtime] ,[Observationpoint] ,[Feelslike] ,[Humidity] ,[Windspeed] ,[Winddisplay] ,[SkycodeImg] ,[Low] ,[High] ,[Shortday] ,[WeatherStatus] ,[CDate] FROM [V_WeatherData] where (CDate <= @edate and CDate >= @sdate) order by left(CDate,8), CAST([AreaCode] as int) ")) { WeatherHelperDataContext.AddInParameter(command, "@sdate", DbType.DateTime, sdate); WeatherHelperDataContext.AddInParameter(command, "@edate", DbType.DateTime, edate); IDataReader reader = WeatherHelperDataContext.ExecuteReader(command); result = new List <WeatherInfo>(); while (reader.Read()) { WeatherInfo response = new WeatherInfo(); response.AreaName = reader["AreaName"].ToString(); response.CountryName = reader["WCCountryName"].ToString(); response.WeatherID = reader["WeatherID"].ToString(); response.AreaCode = reader["AreaCode"].ToString(); response.WCCode = reader["WCCode"].ToString(); response.Temperature = reader["Temperature"].ToString(); response.Skycode = reader["Skycode"].ToString(); response.Skytext = reader["Skytext"].ToString(); response.Observationtime = reader["Observationtime"].ToString(); response.Observationpoint = reader["Observationpoint"].ToString(); response.Feelslike = reader["Feelslike"].ToString(); response.Humidity = reader["Humidity"].ToString(); response.Windspeed = reader["Windspeed"].ToString(); response.Winddisplay = reader["Winddisplay"].ToString(); //response.SkycodeImg = reader["SkycodeImg"].ToString(); response.Low = reader["Low"].ToString(); response.High = reader["High"].ToString(); response.Shortday = reader["Shortday"].ToString(); response.WeatherStatus = reader["WeatherStatus"].ToString(); response.CDate = Convert.ToDateTime(reader["CDate"]); result.Add(response); } return(result); } } catch (Exception ex) { throw ex; } }