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