/// <summary>Deletes the tourlog.</summary> /// <param name="Tourname">The tourname.</param> public static void DeleteTourlog(string Tourname) { int returnval = -3; using NpgsqlConnection conn = Connectionhander.returnConnection(); conn.Open(); try { var cmd = new NpgsqlCommand($"Delete FROM Tour_Log Where TID = (Select TID from Tour WHERE Name = @payload); ", conn); cmd.Parameters.Add(new NpgsqlParameter("payload", Tourname)); NpgsqlDataReader myReader = cmd.ExecuteReader(); if (myReader.HasRows) { Console.WriteLine("Query Generated result:"); while (myReader.Read()) { returnval = myReader.GetInt16(0); } } } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); throw; } finally { conn.Close(); } }
/// <summary>Simples the query.</summary> /// <param name="queryToRun">The query to run.</param> /// <param name="payload">The payload.</param> /// <returns> /// <br /> /// </returns> public static async Task <NpgsqlDataReader> SimpleQuery(string queryToRun, string payload) { using NpgsqlConnection conn = Connectionhander.returnConnection(); conn.Open(); try { var cmd = new NpgsqlCommand(queryToRun, conn); //databaseConnection.Open(); // MySqlDataReader myReader = commandDatabase.ExecuteReader(); cmd.Parameters.Add(new NpgsqlParameter("payload", payload)); NpgsqlDataReader ndr = await cmd.ExecuteReaderAsync(); return(ndr); } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); } return(null); }
/// <summary>Returns the tourlogs.</summary> /// <param name="nameoftour">Name der Tour</param> /// <returns> /// <para>Returns an Oberservable Collection with Tourlogs</para> /// </returns> public static ObservableCollection <Tourlog> SearchTourlogs(string nameoftour) { ObservableCollection <Tourlog> returnval = new ObservableCollection <Tourlog>(); using NpgsqlConnection conn = Connectionhander.returnConnection(); conn.Open(); try { var cmd = new NpgsqlCommand($"Select tlid ,DateTime,Report,Distance,Totaltime,Rating,AvgSpeed,Difficulty,EnergyBurn,Temperature,WaterRecomendation from Tour_Log WHERE TID = (Select TID from Tour WHERE Name = @payload); ", conn); cmd.Parameters.Add(new NpgsqlParameter("payload", nameoftour)); NpgsqlDataReader myReader = cmd.ExecuteReader(); if (myReader.HasRows) { Console.WriteLine("Query Generated result:"); while (myReader.Read()) { returnval.Add(new Tourlog( myReader.GetInt16(0), myReader.GetDateTime(1), myReader.GetString(2), myReader.GetDouble(3), myReader.GetInt16(4), myReader.GetInt16(5), myReader.GetDouble(6), myReader.GetInt16(7), myReader.GetInt16(8), myReader.GetInt16(9), myReader.GetDouble(10)));; } } } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); throw; } finally { conn.Close(); } return(returnval); }
/// <summary>Inserts the tourlogs.</summary> /// <param name="TL">The tl.</param> /// <param name="name">The name.</param> /// <returns> /// <br /> /// </returns> public static int InsertTourlogs(Tourlog TL, string name) { int returnval = -3; using NpgsqlConnection conn = Connectionhander.returnConnection(); conn.Open(); try { var cmd = new NpgsqlCommand($"Select insert_tourlog((Select TID from Tour WHERE Name = @payload), @report , @datetime,@dist,@tottime,@rateing,@diff,@temp); ", conn); cmd.Parameters.Add(new NpgsqlParameter("payload", name)); cmd.Parameters.Add(new NpgsqlParameter("report", TL.Report)); cmd.Parameters.Add(new NpgsqlParameter("datetime", TL.Timestamp)); cmd.Parameters.Add(new NpgsqlParameter("dist", TL.Distance)); cmd.Parameters.Add(new NpgsqlParameter("tottime", TL.Totaltime)); cmd.Parameters.Add(new NpgsqlParameter("rateing", TL.Rating)); cmd.Parameters.Add(new NpgsqlParameter("diff", TL.Difficulty)); cmd.Parameters.Add(new NpgsqlParameter("temp", TL.Temperature)); NpgsqlDataReader myReader = cmd.ExecuteReader(); if (myReader.HasRows) { Console.WriteLine("Query Generated result:"); while (myReader.Read()) { returnval = myReader.GetInt16(0); } } } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); throw; } finally { conn.Close(); } return(returnval); }
//Select insert_tourlog(2, 'THis is a report' , current_date, 1.0,60,3,3,20); /// <summary>Inserts the tour.</summary> /// <param name="tmo">The tmo.</param> /// <returns> /// <br /> /// </returns> public static int InsertTour(Tour tmo) { int returnval = -3; using NpgsqlConnection conn = Connectionhander.returnConnection(); conn.Open(); try { var cmd = new NpgsqlCommand($"SELECT insert_tours(@Name, @DESC ,@SRC, @DSC, @DIST);", conn); cmd.Parameters.Add(new NpgsqlParameter("Name", tmo.Name)); cmd.Parameters.Add(new NpgsqlParameter("SRC", tmo.Source)); cmd.Parameters.Add(new NpgsqlParameter("DSC", tmo.Destination)); cmd.Parameters.Add(new NpgsqlParameter("DESC", tmo.Description)); cmd.Parameters.Add(new NpgsqlParameter("DIST", tmo.Distance)); NpgsqlDataReader myReader = cmd.ExecuteReader(); if (myReader.HasRows) { Console.WriteLine("Query Generated result:"); while (myReader.Read()) { returnval = myReader.GetInt16(0); } } return(returnval); } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); throw; } finally { conn.Close(); } }
/// <summary>Searches the tours.</summary> /// <param name="Searchterm">The searchterm.</param> /// <returns>Oberservable Collection of said Tours</returns> public static ObservableCollection <Tour> SearchTours(string Searchterm = "") { Tour tmp = null; ObservableCollection <Tour> returnval = new ObservableCollection <Tour>(); using NpgsqlConnection conn = Connectionhander.returnConnection(); Searchterm = '%' + Searchterm + '%'; try { conn.Open(); var cmd = new NpgsqlCommand($"Select Name, Description ,Source, Destination,Distance from Tour WHERE Name Like @payload;", conn); cmd.Parameters.Add(new NpgsqlParameter("payload", Searchterm)); NpgsqlDataReader myReader = cmd.ExecuteReader(); if (myReader.HasRows) { Console.WriteLine("Query Generated result:"); while (myReader.Read()) { tmp = new Tour(null, myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3), myReader.GetDouble(4)); tmp.Tourlogs = SearchTourlogs(tmp.Name); returnval.Add(tmp); } } } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); throw; } finally { conn.Close(); } return(returnval); }
/// <summary>Deletes the tour.</summary> /// <param name="name">The name.</param> /// <returns> /// <br /> /// </returns> public static int DeleteTour(string name) { int returnval = -3; using NpgsqlConnection conn = Connectionhander.returnConnection(); conn.Open(); try { DeleteTourlog(name); var cmd = new NpgsqlCommand($"Delete FROM Tour Where Name = @name; ", conn); cmd.Parameters.Add(new NpgsqlParameter("name", name)); NpgsqlDataReader myReader = cmd.ExecuteReader(); if (myReader.HasRows) { Console.WriteLine("Query Generated result:"); while (myReader.Read()) { returnval = myReader.GetInt16(0); } } return(returnval); } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); throw; } finally { conn.Close(); } }
/// <summary> /// <para> /// Nukes the database. /// (Tactical Nuke incomeing)</para> /// </summary> public static void NukeDatabase() { int returnval = -3; using NpgsqlConnection conn = Connectionhander.returnConnection(); conn.Open(); try { var cmd = new NpgsqlCommand($"Select Nuke(); ", conn); NpgsqlDataReader myReader = cmd.ExecuteReader(); } catch (Exception e) { Console.WriteLine("SQ :Query Error: " + e.Message); throw; } finally { conn.Close(); } }