private void Do(string json)
{
if (json.Contains("\"Publications\": ["))
{
this.publikationen.Items.Clear();
if (json.Contains("\"PublisherUrl\":"))
{
var publicationData = JsonConvert.DeserializeObject <OldPublicationData>(json);
MediaRepo.Media.Clear();
PublisherRepo.Publisher.Clear();
this.publikationen.Items.Clear();
foreach (var publication in publicationData.Publications)
{
Publisher publ = new Publisher();
publ.Id = PublisherRepo.Publisher.Count + 1;
publ.Name = publication.Publisher;
publ.Link = publication.PublisherUrl;
PublisherRepo.Publisher.Add(publ);
Medium med = new Medium();
med.Id = MediaRepo.Media.Count + 1;
med.Name = publication.Medium;
med.PublisherId = publ.Id;
MediaRepo.Media.Add(med);
Publication pub = new Publication();
pub.Id = publication.Id;
pub.Name = publication.Name;
pub.Description = publication.Description;
pub.Date = publication.Date;
pub.Link = publication.Link;
pub.Reviewed = false;
pub.Type = publication.Type;
pub.MediumId = med.Id;
this.publikationen.Items.Add(pub);
PublisherRepo.OnPublisherCollectionImport();
MediaRepo.OnMediaCollectionImport();
}
}
else
{
//TODO Check if table exist, drop and create new
var publicationData = JsonConvert.DeserializeObject <PublicationData>(json);
foreach (var publication in publicationData.Publications)
{
this.publikationen.Items.Add(publication);
}
}
}
else if (json.Contains("\"Publisher\": ["))
{
PublisherRepo.Publisher.Clear();
var publicationData = JsonConvert.DeserializeObject <PublisherRepoInstance>(json);
PublisherRepo.Publisher = publicationData.Publisher;
PublisherRepo.OnPublisherCollectionImport();
}
else if (json.Contains("\"Media\": ["))
{
MediaRepo.Media.Clear();
var publicationData = JsonConvert.DeserializeObject <MediaRepoInstance>(json);
MediaRepo.Media = publicationData.Media;
MediaRepo.OnMediaCollectionImport();
}
else if (json.Contains("\"Projects\": ["))
{
var publicationData = JsonConvert.DeserializeObject <ProjectRepoInstance>(json);
ProjectRepo.Projects = publicationData.Projects;
ProjectRepo.OnProjectCollectionImport();
}
}
private void ImportDB1()
{
// read publications
string q = @"SELECT Id, Bezeichnung AS 'Name', Beschreibung AS 'Description', Datum AS 'Date', Link, Typ AS 'Type', MediumId, Geprueft AS Reviewed FROM Publikationen FOR JSON PATH;";
SqlCommand commandX = new SqlCommand(q, MisterDeleteDB.connection);
SqlDataReader result = commandX.ExecuteReader();
if (result.Read())
{
string json = result.GetFieldValue <string>(0);
int year = 1990;
while (year <= DateTime.Today.Year)
{
int index = json.IndexOf("\"Date\":\"" + year + "-");
while (index > -1)
{
int index2 = index + 8;
string date = json.Substring(index2, 10);
string date2 = FormatDate(date);
json = json.Replace(date, date2);
index = json.IndexOf("\"Date\":\"" + year + "-");
}
year++;
}
string before = "{\"types\": [\"Workshop\",\"Vortrag\",\"Artikel\",\"Videotraining\",\"Usergroup\",\"Buch\"],\"Publications\": ";
string after = "}";
Do(before + json + after);
}
result.Close();
// read media
string q2 = @"SELECT Id, Bezeichnung AS 'Name', Link, PublisherId FROM Medien FOR JSON PATH;";
SqlCommand command2 = new SqlCommand(q2, MisterDeleteDB.connection);
SqlDataReader result2 = command2.ExecuteReader();
if (result2.Read())
{
string json2 = result2.GetFieldValue <string>(0);
string before2 = "{\"Media\": ";
string after2 = "}";
Do(before2 + json2 + after2);
}
result2.Close();
// read publisher data
string q3 = @"SELECT Id, Bezeichnung AS 'Name', Link FROM Publisher FOR JSON PATH;";
SqlCommand command3 = new SqlCommand(q3, MisterDeleteDB.connection);
SqlDataReader result3 = command3.ExecuteReader();
if (result3.Read())
{
string json3 = result3.GetFieldValue <string>(0);
string before3 = "{\"Publisher\": ";
string after3 = "}";
Do(before3 + json3 + after3);
}
result3.Close();
// read projects
string q4 = @"SELECT Id, Bezeichnung AS Abstract, Position, Von AS 'From', Bis AS 'To', BisHeute AS UntilToday, Beschreibung AS 'Description', Branche AS Sector FROM Projekte FOR JSON PATH;";
SqlCommand command4 = new SqlCommand(q4, MisterDeleteDB.connection);
SqlDataReader result4 = command4.ExecuteReader();
if (result4.Read())
{
List <Project> projects = new List <Project>();
string json4 = result4.GetFieldValue <string>(0);
result4.Close();
var publicationData = JsonConvert.DeserializeObject <ProjectRepoInstanceWithoutCollections>("{\"Projects\": " + json4 + "}");
foreach (var item in publicationData.Projects)
{
List <string> tasks = new List <string>();
List <string> tools = new List <string>();
Project p = new Project();
p.Abstract = item.Abstract;
p.Description = item.Description;
p.From = item.From;
p.Id = item.Id;
p.Position = item.Position;
p.Sector = item.Sector;
p.To = item.To;
p.UntilToday = item.UntilToday;
string query1 = $"SELECT Aufgaben.Bezeichnung AS Aufgabe FROM Projekte JOIN Projekte_Aufgaben ON Projekte_Aufgaben.ProjektId = Projekte.Id JOIN Aufgaben ON Aufgaben.Id = Projekte_Aufgaben.AufgabeId WHERE Projekte.Id = {item.Id};";
SqlDataReader dataReader = new SqlCommand(query1, MisterDeleteDB.connection).ExecuteReader();
while (dataReader.Read())
{
string aufgabe = (string)dataReader["Aufgabe"];
tasks.Add(aufgabe);
}
p.Tasks = tasks.ToArray();
dataReader.Close();
string query2 = $"SELECT Werkzeuge.Bezeichnung AS Werkzeug FROM Projekte JOIN Projekte_Werkzeuge ON Projekte_Werkzeuge.ProjektId = Projekte.Id JOIN Werkzeuge ON Werkzeuge.Id = Projekte_Werkzeuge.WerkzeugId WHERE Projekte.Id = {item.Id}; ";
SqlDataReader dataReader2 = new SqlCommand(query2, MisterDeleteDB.connection).ExecuteReader();
while (dataReader2.Read())
{
string tool = (string)dataReader2["Werkzeug"];
tools.Add(tool);
}
p.Tools = tools.ToArray();
dataReader2.Close();
projects.Add(p);
}
ProjectRepo.Projects = projects;
ProjectRepo.OnProjectCollectionImport();
}
}
private void ImportDB2()
{
// Read DB
using (var db = new DataContext())
{
// Drop DB
// db.Database.Delete();
// Init Database for EF
if (!db.Publications.Any() && !db.Media.Any() && !db.Publishers.Any() && !db.Projects.Any())
{
InitEFDb();
}
// Import Publication
// Display all Publications from the database
var query = from b in db.Publications
orderby b.Id
select b;
Console.WriteLine("Read all Publications in the database...");
this.publikationen.Items.Clear();
foreach (var item in query)
{
this.publikationen.Items.Add(item);
}
// Import Media
// Display all Media from the database
var query2 = from b in db.Media
orderby b.Id
select b;
Console.WriteLine("Read all Media in the database...");
MediaRepo.Media.Clear();
foreach (var item in query2)
{
MediaRepo.Media.Add(item);
}
MediaRepo.OnMediaCollectionImport();
// Import Publishers
// Display all Publishers from the database
var query3 = from b in db.Publishers
orderby b.Id
select b;
Console.WriteLine("Read all Publishers in the database...");
PublisherRepo.Publisher.Clear();
foreach (var item in query3)
{
PublisherRepo.Publisher.Add(item);
}
PublisherRepo.OnPublisherCollectionImport();
// Import Projects
// Display all Projects from the database
var query4 = from b in db.Projects
orderby b.Id
select b;
Console.WriteLine("Read all Projects in the database...");
ProjectRepo.Projects.Clear();
foreach (var item in query4)
{
ProjectRepo.Projects.Add(item);
}
ProjectRepo.OnProjectCollectionImport();
}
Console.WriteLine("Import Database with EF completed!");
}
