public MultiValueDictionary<DateTime, ITable> getTableDateList(DbTabellen tblName)
{
#region tabellen-auswahl
string operation;
if (tblName == DbTabellen.Person)
{
operation = @"SELECT Person.ID, Person.Vorname, Person.Name, Veranstaltung.Beginn FROM Veranstaltung INNER JOIN (Person INNER JOIN Teilnahme ON Person.ID = Teilnahme.Person) ON Veranstaltung.ID = Teilnahme.Veranstaltung WHERE Person.[Name] IS NOT NULL AND Person.[Vorname] IS NOT NULL AND Person.[Geschlecht] IS NOT NULL AND Person.[ID] IS NOT NULL ORDER BY Veranstaltung.Beginn ASC;";
}
else if (tblName == DbTabellen.Institution)
{
operation = @"SELECT Institution.ID, Institution.Kurzname, Institution.Name, Veranstaltung.Beginn
FROM Veranstaltung INNER JOIN ((Institution RIGHT JOIN Person ON Institution.ID = Person.Institution) INNER JOIN Teilnahme ON Person.ID = Teilnahme.Person) ON Veranstaltung.ID = Teilnahme.Veranstaltung WHERE Institution.ID IS NOT NULL ORDER BY Veranstaltung.Beginn ASC;";
}
else
{
throw new NotImplementedException();
}
#endregion
var institList = new MultiValueDictionary<DateTime, ITable>();
using (var connection = new OleDbConnection(_connectionString))
{
OleDbCommand command = new OleDbCommand(operation, connection);
connection.Open();
using (OleDbDataReader dataReader = command.ExecuteReader())
{
while (dataReader != null && dataReader.Read())
{
var tableItem = TableItemFactory.CreateTableItem(tableName, dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2));
institList.Add(dataReader.GetDateTime(3), tableItem);
}
}
connection.Close();
}
return institList;
}
public DbSelTable(string filepath, DbTabellen tbl)
: base(filepath, tbl)
{
}
public static ITable CreateTableItem(DbTabellen tableName, int id, string prop1, string prop2, params string[] args)
{
if (tableName == DbTabellen.Institution)
{
Institution instit;
if (args.Any())
{
instit = new Institution(id, prop1, prop2)
{
typ = Convert.ToInt32(args[0])
};
}
else
{
instit = new Institution(id, prop1, prop2);
}
return instit;
}
else if (tableName == DbTabellen.Person)
{
Person person;
if (args.Any())
{
person = new Person(id, prop1, prop2)
{
titel = args[0],
geschlecht = args[1],
institution = Convert.ToInt32(args[2]),
abteilung = args[3],
funktion = args[4],
strasse = args[5],
plz = args[6],
ort = args[7],
bundesland = Convert.ToInt32(args[8]),
land = args[9],
mobil = args[10],
telefon = args[11],
fax = args[12],
email = args[13],
internet = args[14],
titelVname = args[15],
titelNname = args[16]
};
}
else
{
person = new Person(id, prop1, prop2);
}
return person;
}
else if (tableName == DbTabellen.Veranstaltung)
{
var veranstaltung = new Veranstaltung(id, prop1, prop2)
{
beginn = Convert.ToDateTime(args[0]),
ende = Convert.ToDateTime(args[1]),
beschreibung = args[2],
typ = Convert.ToInt32(args[3])
};
return veranstaltung;
}
else if (tableName == DbTabellen.Teilnahme)
{
var teilnahme = new Teilnahme(id, prop1, prop2)
{
veranstaltung = Convert.ToInt32(args[0]),
datum = Convert.ToDateTime(args[1]),
details = args[2],
person = Convert.ToInt32(args[3])
};
return teilnahme;
}
else
{
throw new NotImplementedException();
}
}
protected DbOperations(string filepath, DbTabellen tbl)
{
//_dbconnection
_connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath;
tableName = tbl;
}
public SortedList<int, ITable> getTableList(DbTabellen tblName)
{
#region tabellen-auswahl
string operation;
if (tblName == DbTabellen.Person)
{
operation = @"SELECT Person.[ID], Person.[Vorname], Person.[Name], Person.[Titel], Person.[Geschlecht], Person.[Institution], Person.[Abteilung], Person.[Funktion], Person.[Straße], Person.[PLZ], Person.[Ort], Person.[Bundesland], Person.[Land], Person.[Mobil], Person.[Telefon], Person.[Fax], Person.[EMail], Person.[Internet], Person.[TitelVName], Person.[TitelNName] FROM Person WHERE Person.[Name] IS NOT NULL AND Person.[Vorname] IS NOT NULL AND Person.[Geschlecht] IS NOT NULL ORDER BY Person.ID ASC;";
}
else if (tblName == DbTabellen.Institution)
{
operation = @"SELECT Institution.ID, Institution.Kurzname, Institution.Name, Institution.Typ FROM Institution ORDER BY Institution.ID ASC;";
//mitgliedshs
} else if(tblName == DbTabellen.Veranstaltung){
operation = @"SELECT Veranstaltung.[ID], Veranstaltung.[Titel], Veranstaltung.[Ort], Veranstaltung.[Beginn], Veranstaltung.[Ende], Veranstaltung.[Beschreibung], Veranstaltung.[Typ] FROM Veranstaltung ORDER BY Veranstaltung.ID ASC;";
} else if(tblName == DbTabellen.Teilnahme){
operation = @"SELECT Teilnahme.ID, Teilnahme.Teilnahmestatus, Teilnahme.Teilnahmefunktion, Teilnahme.Veranstaltung, Teilnahme.Datum, Teilnahme.Details, Teilnahme.Person, Person.Geschlecht FROM Teilnahme INNER JOIN Person ON Person.ID = Teilnahme.Person WHERE (Person.Name IS NOT NULL AND Person.Vorname IS NOT NULL AND Person.Geschlecht IS NOT NULL AND Person.ID IS NOT NULL);";
}
else
{
throw new NotImplementedException();
}
#endregion
string[] args;
var tableList = new SortedList<int, ITable>();
using (var connection = new OleDbConnection(_connectionString))
{
OleDbCommand command = new OleDbCommand(operation, connection);
connection.Open();
using (OleDbDataReader dataReader = command.ExecuteReader())
{
while (dataReader != null && dataReader.Read())
{
if (tblName == DbTabellen.Person)
{
args = new string[17];
args[0] = dataReader[3] as string; //titel
//args[0] = dataReader.GetString(3); //titel
args[1] = dataReader[4] as string; //geschlecht
var instId = dataReader[5] as int? ?? default(int);
args[2] = instId.ToString();
//args[2] = dataReader.GetInt32(5).ToString(); //instit
args[3] = dataReader[6] as string; //abteilung
args[4] = dataReader[7] as string; //funktion
args[5] = dataReader[8] as string; //strasse
args[6] = dataReader[9] as string; //plz
args[7] = dataReader[10] as string; //ort
//args[8] = dataReader.GetInt32(11).ToString(); //bundesland
//sqlreader[indexAge] as int? ?? default(int);
var bland = dataReader[11] as int? ?? default(int);
args[8] = bland.ToString(); //bundesland
args[9] = dataReader[12] as string; //land
args[10] = dataReader[13] as string; //mobil
args[11] = dataReader[14] as string; //telefon
args[12] = dataReader[15] as string; //fax
args[13] = dataReader[16] as string; //email
args[14] = dataReader[17] as string; //internet
args[15] = dataReader[18] as string; //titelVname
args[16] = dataReader[19] as string; //titelNname
}
else if (tblName == DbTabellen.Institution)
{
args = new string[1];
args[0] = dataReader.GetInt32(3).ToString(); //Typ
}
else if (tblName == DbTabellen.Veranstaltung)
{
args = new string[4];
var beginn = dataReader[3] as DateTime? ?? default(DateTime);
args[0] = beginn.ToString();//beginn
var ende = dataReader[4] as DateTime? ?? default(DateTime);
args[1] = ende.ToString();//ende
args[2] = dataReader[5] as string;//beschreibung memo
var typ = dataReader[6] as int? ?? default(int);
args[3] = typ.ToString();//typ int
// todo
}
else if (tblName == DbTabellen.Teilnahme)
{//strin,g string, int
args = new string[4];
var veranstaltung = dataReader[3] as int? ?? default(int);
args[0] = veranstaltung.ToString(); //veranstaltung
var datum = dataReader[4] as DateTime? ?? default(DateTime);//datum
args[1] = datum.ToString();
args[2] = dataReader[5] as string;//details }
var personId = dataReader[6] as int? ?? default(int);
args[3] = personId.ToString(); //person
}
else
{
throw new NotImplementedException();
}
var tableItem = TableItemFactory.CreateTableItem(tblName, dataReader.GetInt32(0), dataReader.GetString(1), dataReader.GetString(2), args);
tableList.Add(dataReader.GetInt32(0), tableItem);
}
}
connection.Close();
}
return tableList;
}
