public static int[] GetYears() { using (WeatherDBModelContainer db = new WeatherDBModelContainer()) { var query = (from st in db.WeatherEntries orderby st.Date.Year select st.Date.Year).Distinct(); int[] years = query.ToArray(); return(years); } }
public static int[] GetMonths(int year) { using (WeatherDBModelContainer db = new WeatherDBModelContainer()) { var query = (from st in db.WeatherEntries where st.Date.Year == year orderby st.Date.Month select st.Date.Month).Distinct(); int[] months = query.ToArray(); return(months); } }
public static StaticPagedList <WeatherEntry> GetData(int PageNumber, int PageSize) { using (WeatherDBModelContainer db = new WeatherDBModelContainer()) { var query = (from st in db.WeatherEntries orderby st.Date, st.Time select st).Skip((PageNumber - 1) * PageSize).Take(PageSize); List <WeatherEntry> data = query.ToList(); var itemCount = (from st in db.WeatherEntries select st).Count(); var dataAsIPagedList = new StaticPagedList <WeatherEntry>(data, PageNumber, PageSize, itemCount); return(dataAsIPagedList); } }
public static void FilesToDB(string filePath) { XSSFWorkbook xssfwb; using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { xssfwb = new XSSFWorkbook(file); } using (WeatherDBModelContainer db = new WeatherDBModelContainer()) { //Parallel.For(0, xssfwb.NumberOfSheets, //i => for (int i = 0; i < xssfwb.NumberOfSheets; i++) { ISheet sheet = xssfwb.GetSheetAt(i); for (int row = 4; row <= sheet.LastRowNum; row++) { if (sheet.GetRow(row) != null) { try { db.WeatherEntries.Add(new WeatherEntry { Date = DateTime.Parse(sheet.GetRow(row).GetCell(0).StringCellValue).Date, Time = DateTime.Parse(sheet.GetRow(row).GetCell(1).StringCellValue).TimeOfDay, Temp = sheet.GetRow(row).GetCell(2).NumericCellValue, Humidity = sheet.GetRow(row).GetCell(3).NumericCellValue, Td = sheet.GetRow(row).GetCell(4).NumericCellValue, Pressure = (short)sheet.GetRow(row).GetCell(5).NumericCellValue, WindDir = (sheet.GetRow(row).GetCell(6).StringCellValue == null) ? null : sheet.GetRow(row).GetCell(6).StringCellValue, WindSpd = (sheet.GetRow(row).GetCell(7).CellType.ToString() == "String") ? (short?)null : (short)sheet.GetRow(row).GetCell(7).NumericCellValue, Cloudness = (sheet.GetRow(row).GetCell(8).CellType.ToString() == "String") ? (short?)null : (short)sheet.GetRow(row).GetCell(8).NumericCellValue, h = (sheet.GetRow(row).GetCell(9).CellType.ToString() == "String") ? (short?)null : (short)sheet.GetRow(row).GetCell(9).NumericCellValue, VV = (sheet.GetRow(row).GetCell(10).CellType.ToString() == "String") ? (short?)null : (short)sheet.GetRow(row).GetCell(10).NumericCellValue, WeatherPhen = (sheet.GetRow(row).GetCell(11) == null) ? null : sheet.GetRow(row).GetCell(11).StringCellValue }); } catch (Exception ex) { Logger.Error(ex, string.Format("Failed to insert file {0} sheet {1} row {2} into the database", filePath, i, row)); } } } } db.SaveChanges(); } }