private int getWeatherID(WeatherDataEntity item) { string sql1 = $@"SELECT TOP 1 * FROM WeatherData WHERE ClimateType = {(int)item.ClimateType} AND Region = {(int)item.Region} AND year = {item.Year}"; WeatherDataEntity newRecord = connection.QuerySingle <WeatherDataEntity>(sql1); return(newRecord.WeatherDataId); }
/// <summary> /// Insert weather data into table /// </summary> /// <param name="wd">Row data which needs to be inserted</param> /// <returns>identity id of the record inserted</returns> public long Insert(WeatherDataEntity wd) { long id = -1; try { id = connection.Insert <WeatherDataEntity>(wd); } catch (Exception e) { Log.Error(e, " : error"); } return(id); }
/// <summary> /// Check Whether the record exist in the database /// </summary> /// <param name="wd">Row data which needs to be verified if it exist</param> /// <returns>record exist status</returns> public bool RecordExist(WeatherDataEntity wd) { bool isExist = false; try { string sql1 = $"SELECT TOP 1 * FROM WeatherData WHERE ClimateType = {(int)wd.ClimateType} AND Region = {(int)wd.Region} AND year = {wd.Year}"; int count = connection.Query <dynamic>(sql1).AsList().Count; if (count > 0) { isExist = true; } } catch (Exception e) { Log.Error(e, " : error"); return(false); } return(isExist); }
/// <summary> /// Update weather data into table /// </summary> /// <param name="wd">Row data which needs to be updated</param> /// <returns>record update status</returns> public bool Update(int id, WeatherDataEntity wd) { bool isUpdated = false; try { //isUpdated = connection.Update<WeatherDataEntity>(wd); string sql = $@"UPDATE WeatherData SET [Annual] = {wd.Annual} ,[April] = {wd.April} ,[August] = {wd.August} ,[Autumn] = {wd.Autumn} ,[December]= {wd.December} ,[February]= {wd.February} ,[January]= {wd.January} ,[July]= {wd.July} ,[June]= {wd.June} ,[March]= {wd.March} ,[May]= {wd.May} ,[November]= {wd.November} ,[October]= {wd.October} ,[September]= {wd.September} ,[Spring]= {wd.Spring} ,[Summer]= {wd.Summer} ,[Winter]= {wd.Winter} WHERE WeatherDataId = {id}"; var affectedRows = connection.Execute(sql); isUpdated = true; } catch (Exception e) { Log.Error(e, " : error"); } return(isUpdated); }
/// <summary> /// Match all the column in WeatherData table with entity passed in the parameter /// </summary> /// <param name="wd">Row data which needs to be matched with database</param> /// <returns>record match status</returns> private bool matchRecord(WeatherDataEntity wd) { bool isMatch = false; try { string sql1 = @"SELECT TOP 1 * FROM WeatherData WHERE ClimateType = @ClimateType AND Region = @Region AND year = @year"; WeatherDataEntity newRecord = connection.QuerySingle <WeatherDataEntity>(sql1, new { ClimateType = wd.ClimateType, Region = wd.Region, Year = wd.Year }); if (newRecord.Annual == wd.Annual && newRecord.April == wd.April && newRecord.August == wd.August && newRecord.Autumn == wd.Autumn && newRecord.December == wd.December && newRecord.February == wd.February && newRecord.January == wd.January && newRecord.July == wd.July && newRecord.June == wd.June && newRecord.March == wd.March && newRecord.May == wd.May && newRecord.November == wd.November && newRecord.October == wd.October && newRecord.September == wd.September && newRecord.Spring == wd.Spring && newRecord.Summer == wd.Summer && newRecord.Winter == wd.Winter) { return(true); } } catch (Exception e) { Log.Error(e, " : error"); } return(isMatch); }