private void ReadExcelData(OleDbConnection conn, string sheetName)
{
Console.WriteLine("Reading data...");
var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", conn);
using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
{
DataSet ds = new DataSet();
oleDbDataAdapter.Fill(ds);
var locations = new List<Location>();
using (var reader = ds.CreateDataReader())
{
while (reader.Read())
{
var loc = new Location();
loc.Name = reader["City"].ToString();
locations.Add(loc);
}
}
var db = new MagicalCreatureDbContext();
foreach (var loc in locations)
{
db.Locations.Add(loc);
}
db.SaveChanges();
}
}
public static void Main()
{
System.Data.Entity.Database.SetInitializer(new MigrateDatabaseToLatestVersion<MagicalCreatureDbContext, Configuration>());
var dbContext = new MagicalCreatureDbContext();
List<MagicalCreatureModel> list;
string lastCommnad = "";
while (true)
{
var input = Console.ReadLine();
switch(input)
{
case "init":
GenerateSqlDatabBaseIfNeeded();
CreteMongoDb();
break;
case "excel":
LoadInitialExcelDataFromZipFile();
break;
case "importMongo":
ImportMongoToSql();
break;
case "pdf":
GeneratePDfReport();
break;
case "xmlReport":
dbContext = new MagicalCreatureDbContext();
list = AllCreatures(dbContext);
XmlReport(list);
break;
case "json":
dbContext = new MagicalCreatureDbContext();
list = AllCreatures(dbContext);
JsonReport(list);
break;
case "mysql":
dbContext = new MagicalCreatureDbContext();
list = AllCreatures(dbContext);
addToMySql(list);
break;
case "update":
UpdateMythologies();
break;
case "exit":
return;
default:
break;
}
}
}
private static void UpdateMythologies()
{
XmlDocument doc = new XmlDocument();
doc.Load("../../../DataSystem/AddData.xml");
XmlNode rootNode = doc.DocumentElement;
var dbsql = new MagicalCreatureDbContext();
var mongoCreator = new MongoCreator();
var db = mongoCreator.GetDatabase(MongoCreator.DatabaseName, MongoCreator.DatabaseHost);
foreach (XmlNode node in rootNode.ChildNodes)
{
var name = node["name"].InnerText;
var description = node["description"].InnerText;
var myth = dbsql.Mythologies.First(x => x.Name == name);
myth.Discription = description;
var collection = db.GetCollection<BsonDocument>("MagicalCreatureMythologyData");
var mythDb = collection.FindAll().First(x => x["Name"] == name);
mythDb["Description"] = description;
Console.WriteLine(mythDb["Description"].AsString);
collection.Save(mythDb);
}
dbsql.SaveChanges();
}
private static void ImportMongoToSql()
{
var mongoCreator = new MongoCreator();
var db = mongoCreator.GetDatabase(MongoCreator.DatabaseName, MongoCreator.DatabaseHost);
var myths = db.GetCollection<BsonDocument>("MagicalCreatureMythologyData");
var data = myths.FindAll();
var dbsql = new MagicalCreatureDbContext();
var locations = dbsql.Locations.Select(o => o.Name).ToString();
foreach (var item in data)
{
var loctionData = item["LocationOfOrigin"].AsString;
if(!locations.Contains(loctionData))
{
var loc = new Location { Name = loctionData };
dbsql.Locations.Add(loc);
var mythDataNme = item["Name"].AsString;
var mythology = new Mythology { Name = mythDataNme, Location = loc };
dbsql.Mythologies.Add(mythology);
}
else
{
var mythDataNme = item["Name"].AsString;
var mythology = new Mythology { Name = mythDataNme, LocationId = locations.IndexOf(loctionData) };
dbsql.Mythologies.Add(mythology);
}
}
dbsql.SaveChanges();
}
private static void GenerateSqlDatabBaseIfNeeded()
{
var db = new MagicalCreatureDbContext();
var loc = new Location
{
Name = "Sofia"
};
if (db.Locations.Count() == 0)
{
db.Locations.Add(loc);
}
db.SaveChanges();
}
private static void GeneratePDfReport()
{
/*
var species = new Species();
species.Name = "Dragon";
species.Mythology = db.Mythologies.FirstOrDefault();
var creature = new MagicalCreature();
creature.Name = "Bob";
creature.AssesedDangerLevel = DangerLevel.High;
creature.DateSpotted = DateTime.Now.AddDays(-10);
creature.AggressionWhenSpotted = AggressionLevel.Aggitated;
creature.Location = db.Locations.FirstOrDefault();
creature.Species = species;
db.MagicalCreatures.Add(creature);
db.SaveChanges();*/
var report = new PdfReportGenerator();
var db = new MagicalCreatureDbContext();
report.CreateUserReport(db.MagicalCreatures,"Report1.pdf",DateTime.Now);
}
/* private static void AddElf()
{
var db = new MagicalCreatureDbContext();
var mythology = new Models.Mythology
{
Name = "Norse",
AreaOfOrigin = "Denmakr"
};
var goblins = new Models.Species
{
Name = "Elf",
Mythology = mythology,
Description = "Tall,Fast and pretty",
};
db.Species.Add(goblins);
//context.SaveChanges();
var loc = new Models.Location { Name = "Plovdiv" };
var supAb = new Models.SuperNaturalAbility
{
Name = "Magic Missle",
RangeInMeters = 10,
DangerLevel = DangerLevel.Medium
};
db.Locations.Add(loc);
//context.SaveChanges();
db.SuperNaturalAbilities.Add(supAb);
var creature = new MagicalCreature
{
Name = "Bob1",
DateSpotted = DateTime.Now,
AggressionWhenSpotted = AggressionLevel.Aggitated,
AssesedDangerLevel = DangerLevel.Medium,
Species = goblins,
Location = loc,
};
creature.SuperNaturalAbilities.Add(supAb);
db.MagicalCreatures.AddOrUpdate(creature);
db.SaveChanges();
Console.WriteLine(db.Mythologies.Count());
}*/
private static ICollection<MagicalCreatureModel> ExtractMagicalCreaturesByMythologyName(string mythology)
{
var db = new MagicalCreatureDbContext();
var list = db.MagicalCreatures
.Where(c => c.Species.Mythology.Name == "Norse")
.Select(c => new MagicalCreatureModel
{
Name = c.Name,
DateSpotted = c.DateSpotted,
AggressionWhenSpotted = c.AggressionWhenSpotted,
AssesedDangerLevel = c.AssesedDangerLevel,
Location = c.Location.Name,
Species = c.Species.Name + " from" + c.Species.Mythology.Name + " mythology"
})
.ToList();
return list;
}
private static List<MagicalCreatureModel> AllCreatures(MagicalCreatureDbContext dbContext)
{
return dbContext.MagicalCreatures
.Where(c => c.LocationId != c.Species.Mythology.LocationId)
.Select(c => new MagicalCreatureModel
{
Name = c.Name,
AggressionWhenSpotted = c.AggressionWhenSpotted,
DateSpotted = c.DateSpotted,
AssesedDangerLevel = c.AssesedDangerLevel,
Location = c.Location.Name,
Species = c.Species.Name
})
.ToList();
}
