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();
}
}
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();
}