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);
}
}
}
public static RawDataRow FromCsv(string csvLine)
{
string[] values = csvLine.Split(',');
RawDataRow row = new RawDataRow();
row.Region = values[0];
row.SubRegion = values[1];
row.StudyArea = values[2];
row.SurveyYear = Convert.ToInt16(values[3]);
row.BatchCode = values[4];
row.SurveyIndex = Convert.ToInt16(values[5]);
row.SurveyDate = Convert.ToDateTime(values[6]);
row.Latitude = Convert.ToDecimal(values[7]);
row.Longitude = Convert.ToDecimal(values[8]);
row.Management = values[9];
row.StructureType = values[10];
row.Family = values[11];
row.ScientificName = values[12];
row.CommonName = values[13];
row.Trophic = values[14];
row.FishLength = Convert.ToDecimal(values[15]);
row.FishCount = Convert.ToInt16(values[16]);
return(row);
}
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();
}
