public bool save(loggedInUser currentUser)
{
string selectCSQL = "select cid from Countries where name = @nam";
SqlCommand selectCCMD = new SqlCommand();
selectCCMD.CommandText = selectCSQL;
selectCCMD.Connection = Starter.GetConnection();
selectCCMD.Parameters.Add(new SqlParameter("nam", country.countryName));
SqlDataReader Creader = selectCCMD.ExecuteReader();
Creader.Read(); //setzt den Reader auf den ersten / nächsten DS
country.cID = Creader.GetInt32(0);
string selectTSQL = "select tid from Transport where name = @nam";
SqlCommand selectTCMD = new SqlCommand();
selectTCMD.CommandText = selectTSQL;
selectTCMD.Connection = Starter.GetConnection();
selectTCMD.Parameters.Add(new SqlParameter("nam", transport.transportName));
SqlDataReader Treader = selectTCMD.ExecuteReader();
Treader.Read();
transport.transportID = Treader.GetInt32(0);
if (postID != -1)
{
//test, if user who created this post, is currently logged in user
if (currentUser.uID.Equals(user.uID))
{
string SQLSTMT = "update Posts set cid = @cid, content = @content, tid = @tid where postID = @id";
SqlCommand updateCMD = new SqlCommand();
updateCMD.CommandText = SQLSTMT;
updateCMD.Connection = Starter.GetConnection();
//Die Parameter in SQL-String mit Werten versehen...
updateCMD.Parameters.Add(new SqlParameter("cid", country.cID));
updateCMD.Parameters.Add(new SqlParameter("content", content));
updateCMD.Parameters.Add(new SqlParameter("tid", transport.transportID));
updateCMD.Parameters.Add(new SqlParameter("id", postID));
// ExecuteNonQuery() gibt die Anzahl der veränderten/angelegten Records zurück.
return(updateCMD.ExecuteNonQuery() > 0);
}
}
user.uID = currentUser.uID;
user.userName = currentUser.userName;
string SQL = "insert into Posts (uid, cid, content, tid) values (@uid, @cid, @content, @tid)";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Connection = Starter.GetConnection();
//Die Parameter in SQL-String mit Werten versehen...
cmd.Parameters.Add(new SqlParameter("uid", currentUser.uID));
cmd.Parameters.Add(new SqlParameter("cid", country.cID));
cmd.Parameters.Add(new SqlParameter("content", content));
cmd.Parameters.Add(new SqlParameter("tid", transport.transportID));
// ExecuteNonQuery() gibt die Anzahl der veränderten/angelegten Records zurück.
return(cmd.ExecuteNonQuery() > 0);
}
public bool changePassword(string oldPassword, string newPassword)
{
string pwHashDB;
string SQL = "select pwHash from Users where uID = @id";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Connection = Starter.GetConnection();
cmd.Parameters.Add(new SqlParameter("id", uID));
SqlDataReader reader = cmd.ExecuteReader();
reader.Read(); //setzt den Reader auf den ersten / nächsten DS
pwHashDB = reader.GetString(0);
if (pwHashDB.Equals(md5(oldPassword)))
{
string updateSTMT = "update Users set pwHash = @pH where uID = @id";
SqlCommand updateCMD = new SqlCommand();
updateCMD.CommandText = updateSTMT;
updateCMD.Connection = Starter.GetConnection();
updateCMD.Parameters.Add(new SqlParameter("pH", md5(newPassword)));
updateCMD.Parameters.Add(new SqlParameter("id", uID));
return(updateCMD.ExecuteNonQuery() > 0);
}
else
{
Console.WriteLine("Passwort nicht gefunden");
return(false);
}
}
internal bool checkPassword(string pwEntered)
{
string pwHashDB;
string SQL = "select pwHash from Users where uID = @id";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Connection = Starter.GetConnection();
cmd.Parameters.Add(new SqlParameter("id", uID));
SqlDataReader reader = cmd.ExecuteReader();
reader.Read(); //setzt den Reader auf den ersten / nächsten DS
try
{
pwHashDB = reader.GetString(0);
if (pwHashDB.Equals(md5(pwEntered)))
{
return(true);
}
}
catch (Exception e)
{
}
return(false);
}
internal bool insert(string password)
{
string SQL = "insert into Users (username, pwHash) values (@nam, @pw)";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Connection = Starter.GetConnection();
//Die Parameter in SQL-String mit Werten versehen...
cmd.Parameters.Add(new SqlParameter("nam", userName));
cmd.Parameters.Add(new SqlParameter("pw", md5(password)));
// ExecuteNonQuery() gibt die Anzahl der veränderten/angelegten Records zurück.
return(cmd.ExecuteNonQuery() > 0);
}
public bool save(AdminUser admin)
{
if (admin != null)
{
string SQL = "insert into Countries (name) values (@nam)";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Connection = Starter.GetConnection();
//Die Parameter in SQL-String mit Werten versehen...
cmd.Parameters.Add(new SqlParameter("nam", countryName));
// ExecuteNonQuery() gibt die Anzahl der veränderten/angelegten Records zurück.
return(cmd.ExecuteNonQuery() > 0);
}
return(false);
}
internal bool checkAdmin()
{
string SQL = "select admin from Users where uID = @id";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SQL;
cmd.Connection = Starter.GetConnection();
cmd.Parameters.Add(new SqlParameter("id", uID));
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
try {
return(reader.GetBoolean(0));
} catch (Exception e) { }
return(false);
}
//Statische Methoden, aufgerufen von der Starter-Klasse, damit alle Datenbank-Aufrufe über dieses Objekt in der
//derselben Klasse gesammelt sind
//bei Select * Abfrage gibt er folgende Reihenfolge aus: postID, uid, cid, date, content, tid, cid, name, tid, name, uid, username, pwHash, admin
internal static Countries getAllCountries()
{
SqlCommand cmd = new SqlCommand("select c.cid, c.name, p.postID from Countries as c left join Posts as p on c.cid = p.cid", Starter.GetConnection());
SqlDataReader reader = cmd.ExecuteReader();
Countries allCountries = new Countries(); //initialisiere lehre Liste
Country currentObject = new Country();
currentObject.cID = 0;
while (reader.Read())
{
if (currentObject.cID != reader.GetInt32(0))
{
Country oneCountry = new Country();
currentObject = oneCountry;
allCountries.Add(currentObject);
currentObject.cID = reader.GetInt32(0);
currentObject.countryName = reader.GetString(1);
}
try
{
currentObject.postIDs.Add(reader.GetInt32(2));
}
catch (Exception e) {
}
}
return(allCountries);
}
public Transport getTransport()
{
//Load transport from DB
Transport loadedTransport = new Transport();
SqlCommand cmd = new SqlCommand("select t.tid, t.name, p.postID from Transport as t left join Posts as p on t.tid = p.tid where t.tid = @id", Starter.GetConnection());
cmd.Parameters.Add(new SqlParameter("id", transport.transportID));
SqlDataReader reader = cmd.ExecuteReader();
loadedTransport.transportID = 0;
while (reader.Read())
{
if (loadedTransport.transportID != reader.GetInt32(0))
{
loadedTransport.transportID = reader.GetInt32(0);
loadedTransport.transportName = reader.GetString(1);
}
try
{
loadedTransport.postIDs.Add(reader.GetInt32(2));
}
catch (Exception e)
{
}
}
return(loadedTransport);
}
public Country getCountry()
{
//Load country from DB
Country loadedCountry = new Country();
SqlCommand cmd = new SqlCommand("select c.cid, c.name, p.postID from Countries as c left join Posts as p on c.cid = p.cid where c.cid = @id", Starter.GetConnection());
cmd.Parameters.Add(new SqlParameter("id", country.cID));
SqlDataReader reader = cmd.ExecuteReader();
loadedCountry.cID = 0;
while (reader.Read())
{
if (loadedCountry.cID != reader.GetInt32(0))
{
loadedCountry.cID = reader.GetInt32(0);
loadedCountry.countryName = reader.GetString(1);
}
try
{
loadedCountry.postIDs.Add(reader.GetInt32(2));
}
catch (Exception e)
{
}
}
return(loadedCountry);
}
public User getUser()
{
//Load user from DB
User loadedUser = new User();
SqlCommand cmd = new SqlCommand("select u.uid, u.username, p.postID from Users as u left join Posts as p on u.uid = p.uid where u.uid = @id", Starter.GetConnection());
cmd.Parameters.Add(new SqlParameter("id", user.uID));
SqlDataReader reader = cmd.ExecuteReader();
loadedUser.uID = 0;
while (reader.Read())
{
if (loadedUser.uID != reader.GetInt32(0))
{
loadedUser.uID = reader.GetInt32(0);
loadedUser.userName = reader.GetString(1);
}
try
{
loadedUser.postIDs.Add(reader.GetInt32(2));
}
catch (Exception e) {
}
}
return(loadedUser);
}
internal static Post getOnePost(int postID)
{
Post onePost = new Post();
SqlCommand cmd = new SqlCommand("select * from Posts as p inner join Countries as c on p.cid = c.cid inner join Transport as t on p.tid = t.tid inner join Users as u on p.uid = u.uid where postID = @id", Starter.GetConnection());
cmd.Parameters.Add(new SqlParameter("id", postID));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
onePost = fillPostFromSQLDataReader(reader);
}
return(onePost);
}
internal static Posts getAllPosts()
{
SqlCommand cmd = new SqlCommand("select * from Posts as p inner join Countries as c on p.cid = c.cid inner join Transport as t on p.tid = t.tid inner join Users as u on p.uid = u.uid;", Starter.GetConnection());
SqlDataReader reader = cmd.ExecuteReader();
Posts allPosts = new Posts(); //initialisiere lehre Liste
while (reader.Read())
{
Post onePost = fillPostFromSQLDataReader(reader);
allPosts.Add(onePost);
}
return(allPosts);
}
internal static Transports getAllTransports()
{
SqlCommand cmd = new SqlCommand("select t.tid, t.name, p.postID from Transport as t left join Posts as p on t.tid = p.tid", Starter.GetConnection());
SqlDataReader reader = cmd.ExecuteReader();
Transports allTransports = new Transports(); //initialisiere lehre Liste
Transport currentObject = new Transport();
currentObject.transportID = 0;
while (reader.Read())
{
if (currentObject.transportID != reader.GetInt32(0))
{
Transport oneTransport = new Transport();
currentObject = oneTransport;
allTransports.Add(currentObject);
currentObject.transportID = reader.GetInt32(0);
currentObject.transportName = reader.GetString(1);
}
try
{
currentObject.postIDs.Add(reader.GetInt32(2));
}
catch (Exception e)
{
}
}
return(allTransports);
}
internal static Users getAllUsers()
{
SqlCommand cmd = new SqlCommand("select u.uid, u.username, p.postID from Users as u left join Posts as p on u.uid = p.uid", Starter.GetConnection());
SqlDataReader reader = cmd.ExecuteReader();
Users allUsers = new Users(); //initialisiere lehre Liste
User currentObject = new User();
currentObject.uID = 0;
while (reader.Read())
{
if (currentObject.uID != reader.GetInt32(0))
{
User oneUser = new User();
currentObject = oneUser;
allUsers.Add(currentObject);
currentObject.uID = reader.GetInt32(0);
currentObject.userName = reader.GetString(1);
}
try
{
currentObject.postIDs.Add(reader.GetInt32(2));
}
catch (Exception e) {
}
}
return(allUsers);
}