Beispiel #1
0
        private static void ParseFile(string pathname)
        {
            List <RawDataRow> allrows = File.ReadAllLines(pathname)
                                        .Skip(1)
                                        .Select(v => RawDataRow.FromCsv(v))
                                        .ToList();

            // RawDataRow row1 = allrows[0];
            using (var db = new FISHSURVEYEntities())
            {
                foreach (var row in allrows)
                {
                    AddRowToDB(row, db);
                }
            }
        }
Beispiel #2
0
        private static void AddRowToDB(RawDataRow row1, FISHSURVEYEntities db)
        {
            //Fill GEO Schema

            //add new region
            if (!db.Regions.Any(x => x.RegionName == row1.Region))
            {
                var region1 = new Region {
                    RegionName = row1.Region
                };
                db.Regions.Add(region1);
                Console.WriteLine($"Region {row1.Region}  added");
                db.SaveChanges();
            }


            //insert subregion

            if (!db.SubRegions.Any(x => x.SubRegionName == row1.SubRegion))
            {
                var subregion1 = new SubRegion {
                    SubRegionName = row1.SubRegion, RegionId = db.Regions.Single(x => x.RegionName == row1.Region).RegionId
                };
                db.SubRegions.Add(subregion1);
                Console.WriteLine($"SubRegion {row1.SubRegion}  added");
                db.SaveChanges();
            }

            //insert study area

            if (!db.StudyAreas.Any(x => x.StudyAreaName == row1.StudyArea))
            {
                var studyarea1 = new StudyArea {
                    StudyAreaName = row1.StudyArea, SubRegionId = db.SubRegions.Single(x => x.SubRegionName == row1.SubRegion).SubRegionId
                };
                db.StudyAreas.Add(studyarea1);
                Console.WriteLine($"StudyArea {row1.StudyArea}  added");
                db.SaveChanges();
            }


            //add batchcode
            if (!db.BatchCodes.Any(x => x.BatchCodeDescription == row1.BatchCode))
            {
                var batchcode1 = new BatchCode {
                    BatchCodeDescription = row1.BatchCode, SurveyYear = row1.SurveyYear
                };
                db.BatchCodes.Add(batchcode1);
                Console.WriteLine($"BatchCode {row1.BatchCode}  added");
                db.SaveChanges();
            }

            //add Management
            if (!db.Managements.Any(x => x.ManagementName == row1.Management))
            {
                var management1 = new Management {
                    ManagementName = row1.Management
                };
                db.Managements.Add(management1);
                Console.WriteLine($"Management {row1.Management}  added");
                db.SaveChanges();
            }


            //add Survey
            if (!db.Surveys.Any(x => x.SurveyIndex == row1.SurveyIndex))
            {
                var survey = new Survey
                {
                    SurveyIndex  = row1.SurveyIndex,
                    Latitude     = (decimal)row1.Latitude,
                    Longitude    = (decimal)row1.Longitude,
                    SurveyDate   = row1.SurveyDate,
                    StudyAreaId  = db.StudyAreas.Single(x => x.StudyAreaName == row1.StudyArea).StudyAreaId,
                    ManagementId = db.Managements.Single(x => x.ManagementName == row1.Management).ManagementId,
                    BatchCodeId  = db.BatchCodes.Single(x => x.BatchCodeDescription == row1.BatchCode).BatchCodeId
                };
                db.Surveys.Add(survey);
                Console.WriteLine($"Survey {row1.SurveyIndex}  added");
                db.SaveChanges();
            }

            //insert trophic
            if (!db.Trophics.Any(x => x.TrophicLevel == row1.Trophic))
            {
                var trophic = new Trophic {
                    TrophicLevel = row1.Trophic
                };
                db.Trophics.Add(trophic);
                Console.WriteLine($"Trophoc {row1.Trophic} added");
                db.SaveChanges();
            }

            //insert family
            if (!db.Families.Any(x => x.FamilyName == row1.Family))
            {
                var family = new Family {
                    FamilyName = row1.Family
                };
                db.Families.Add(family);
                Console.WriteLine($"Family {row1.Family}  added");
                db.SaveChanges();
            }

            //insert species
            if (!db.Species.Any(x => x.ScientificName == row1.ScientificName))
            {
                var common = new Species
                {
                    CommonName     = row1.CommonName,
                    ScientificName = row1.ScientificName,
                    FamilyId       = db.Families.Single(x => x.FamilyName == row1.Family).FamilyId,
                    TrophicId      = db.Trophics.Single(x => x.TrophicLevel == row1.Trophic).TrophicId
                };

                db.Species.Add(common);
                db.SaveChanges();
                Console.WriteLine($"Species {row1.ScientificName}  added");
            }

            //insert structure
            if (!db.StructureTypes.Any(x => x.StructureTypeDescription == row1.StructureType))
            {
                var structuretype = new StructureType
                {
                    StructureTypeDescription = row1.StructureType.Trim() //trim the extra space at the end
                };
                db.StructureTypes.Add(structuretype);
                db.SaveChanges();
                Console.WriteLine($"StructureType {row1.StructureType}  added");
            }



            //insert Fish
            var fish = new Fish
            {
                StructureTypeId = db.StructureTypes.Single(x => x.StructureTypeDescription == row1.StructureType).StructureTypeId,
                FishLength      = (decimal)row1.FishLength,
                FishCount       = row1.FishCount,
                SpeciesId       = db.Species.Single(x => x.ScientificName == row1.ScientificName).SpeciesId,
                SurveyIndex     = row1.SurveyIndex
            };

            db.Fishes.Add(fish);

            //save database
            db.SaveChanges();
        }