public static Users GetUserByLogin(string username) { Result result; string sql = ""; username = username.Replace("'", "\'"); sql = string.Format("SELECT Author_ID, Author_Name FROM Author WHERE Author_Name = '{0}'", username); try { result = SQLet.GetResult(sql); } catch (Microsoft.Data.SqlClient.SqlException e) { Logger.WriteToFile(e); return(null); } if (result.Count == 0) { return(null); } Users user = new Users(int.Parse(result[0]["Author_ID"]), result[0]["Author_Name"]); return(user); }
public static void SetupSQLServer() { try { SQLet.Execute(@"CREATE TABLE Author ( Author_ID int primary key IDENTITY(1,1) NOT NULL, Author_Name nvarchar(32) NOT NULL )"); SQLet.Execute(@"CREATE TABLE Messages ( Message_ID int primary key IDENTITY(1,1) NOT NULL, Message_Date datetime NOT NULL DEFAULT GETDATE(), Message_Text text NOT NULL, Message_Author_ID int NOT NULL CONSTRAINT FK_user_message FOREIGN KEY (Message_Author_ID) REFERENCES Author ON DELETE Cascade ON Update Cascade )"); SQLet.Execute(@"CREATE VIEW Discard_Info AS SELECT Message_Text, Message_ID, Message_Author_ID, Message_Date, sender.Author_Name, Recipient_ID, Recipient.Author_Name AS Recipient FROM Messages INNER JOIN Author AS sender ON Messages.Message_Author_ID=sender.Author_ID LEFT JOIN Author AS Recipient ON messages.Recipient_ID = Recipient.Author_ID "); } catch (Microsoft.Data.SqlClient.SqlException) { } }
public static void updateAuthor(string ID, string Author) { string sql = @" UPDATE Author SET Author_Name = '{1}' WHERE Author_ID = {0} "; string formatted = string.Format(sql, ID, Author); //Console.WriteLine(formatted); SQLet.Execute(formatted); }
public static void updateMessage(string ID, string Message) { string sql = @" UPDATE Messages SET Message_Text = '{1}' WHERE Message_ID = {0} "; string formatted = string.Format(sql, ID, Message); //Console.WriteLine(formatted); SQLet.Execute(formatted); }
public static void deleteAuthor(string ID) { string sql = @" DELETE FROM Author WHERE Author_ID = {0}"; string formatted = string.Format(sql, ID); //Console.WriteLine(formatted); SQLet.Execute(formatted); }
public static void deleteMessage(string ID) { string sql = @" DELETE FROM Messages WHERE Message_ID = {0}"; string formatted = string.Format(sql, ID); //Console.WriteLine(formatted); SQLet.Execute(formatted); }
public static void insertAuthor(string name) { string sql = @" INSERT INTO Author (Author_Name) VALUES ('{0}')"; string formatted = string.Format(sql, name); //Console.WriteLine(formatted); SQLet.Execute(formatted); }
public static void insertMessage(string message, int author, int recipientId = 0) { string sql = @" INSERT INTO Messages (Message_text, Message_Author_ID, Message_Date, Recipient_ID) VALUES ('{0}', {1}, '{2}', '{3}')"; string formatted = string.Format(sql, message, author, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), recipientId); //Console.WriteLine(formatted); SQLet.Execute(formatted); }
public static Users GetUser(int userID) { string sql = ""; sql = string.Format("SELECT Author_ID, Author_Name FROM Author WHERE Author_ID = '{0}'", userID); Result result = SQLet.GetResult(sql); if (result.Count == 0) { return(null); } Users user = new Users(int.Parse(result[0]["Author_ID"]), result[0]["Author_Name"]); return(user); }
static void Main(string[] args) { string host = "http://*****:*****@"MADS-V-KLAUSEN\MSSQLSERVER01"); RequestHandler requestHandler = new RequestHandler(host); requestHandler.Start(); Console.Read(); }
public static List <Users> GetUsers() { List <Users> users = new List <Users>(); Result result = SQLet.GetResult("SELECT Author_ID, Author_Name FROM Author"); int number1 = 0; foreach (var row in result) { int.TryParse(row["Author_ID"], out number1); Users user = new Users(number1, row["Author_Name"]); users.Add(user); } return(users); }
public static void SelectAllAuthors() { string sql = @" SELECT * FROM Author ORDER BY Author_ID DESC"; string formatted = string.Format(sql); //Console.WriteLine(formatted); Result result = SQLet.GetResult(formatted); foreach (var record in result) { Console.WriteLine(record["Author_Name"]); } }
public static void SelectAllMessage() { string sql = @" SELECT * FROM Messages ORDER BY Message_ID DESC"; string formatted = string.Format(sql); //Console.WriteLine(formatted); Result result = SQLet.GetResult(formatted); foreach (var record in result) { Console.WriteLine(record["Message_ID"] + "\t" + record["Message_Text"] + "\t" + record["Message_Author_ID"] + "\t" + record["Message_Date"]); } }
static void Main(string[] args) { SQLet.ConnectSQLite("webshop.db"); Console.Write("Indtast kundens fornavn: "); string firstName = Console.ReadLine(); Console.Write("Indtast kundens efternavn: "); string lastName = Console.ReadLine(); Console.Write("Indtast kundens postnummer: "); string zipCode = Console.ReadLine(); string sql = $"INSERT INTO customer (firstName, lastName, zipcode) VALUES ('{firstName}', '{lastName}', {zipCode})"; SQLet.Execute(sql); PrintResults(SQLet.GetResult("SELECT * FROM customer")); }
public static List <Message> GetMessages() { List <Message> messages = new List <Message>(); Result result = SQLet.GetResult(@"SELECT * FROM Discard_Info"); int number = 0; foreach (var row in result) { int.TryParse(row["Message_Author_ID"], out number); //Parser tekseten Athor_ID om til et tal Message msg = new Message(row["Message_Text"], number); //Opretter en instans af message msg.Message_Date = DateTime.Parse(row["Message_Date"]); //Sætter Datoen på instansen til samme dato som fra databasen int.TryParse(row["Message_ID"], out number); //Parser teksten fra MessageId om til et tal og lægger det i number msg.Message_ID = number; //Putter tallet fra number over i MessageId på instansen // Sæt propertien Users til en bruger instans int.TryParse(row["Message_Author_ID"], out number); msg.User = new Users(number, row["Author_Name"]); //Tilføj instansen til listen messages.Add(msg); } return(messages); }
static void Main_(string[] args) { SQLet.ConnectSqlServer("Discard", @"MADS-V-KLAUSEN\MSSQLSERVER01", "SA", "Muskel-Jack"); Database.SetupSQLServer(); Database.GetMessages(); List <Message> beskeder = Database.GetMessages(); string beskedHTML = HTML_Generator.GenerateMessage(beskeder); Console.WriteLine(beskedHTML); List <Users> user = Database.GetUsers(); string userList = HTML_Generator.GenerateUser(user); Console.WriteLine(user); string userHTML = HTML_Generator.GenerateUser(user); string index = HTML_Generator.GenerateIndex(beskeder, user); Console.WriteLine(index); System.IO.File.WriteAllText("C:/Users/MadsV/OneDrive/Dokumenter/GitHub/Mads-Verner-Klausen-Portfolio/Discard/Studio Project/Discard (message)/Discard (message)/HTML/test.html", index); Console.ReadKey(); const ConsoleKey keyInfo1 = ConsoleKey.D1; const ConsoleKey keyInfo2 = ConsoleKey.D2; const ConsoleKey keyInfo3 = ConsoleKey.D3; const ConsoleKey keyInfo4 = ConsoleKey.D4; const ConsoleKey keyInfo5 = ConsoleKey.D5; const ConsoleKey keyInfo6 = ConsoleKey.D6; const ConsoleKey keyInfo7 = ConsoleKey.D7; const ConsoleKey keyInfo8 = ConsoleKey.D8; while (true) { Console.Clear(); Console.ForegroundColor = ConsoleColor.DarkGray; MessagesMethods.SelectAllMessage(); Console.WriteLine("" + "Press 1 :Insert Message.\n" + "Press 2 :Delete Message.\n" + "Press 3 :Update Message.\n" + "Press 4 :Show Message from user.\n\n" + "Press 5 :Insert new Author.\n" + "Press 6 :Delete Author.\n" + "Press 7 :Update Author.\n" + "Press 8 :Show Message from Author.\n"); ConsoleKey pressedKey = PressedKey(); switch (pressedKey) { case keyInfo1: Console.ForegroundColor = ConsoleColor.Blue; Console.Write("Enter new message > "); string input = Console.ReadLine(); Console.Write("What author ID is this message connected to? > "); int messagesauthor = int.Parse(Console.ReadLine()); MessagesMethods.insertMessage(input, messagesauthor); break; case keyInfo2: Console.ForegroundColor = ConsoleColor.Blue; Console.Write("Specify what Message_ID you want to delete > "); string inputdelete = Console.ReadLine(); MessagesMethods.deleteMessage(inputdelete); break; case keyInfo3: Console.ForegroundColor = ConsoleColor.Blue; Console.Write("What Message_ID do you want to update > "); string inputUpdate = Console.ReadLine(); Console.Write("What do you want to update the message to > "); string inputMessage = Console.ReadLine(); MessagesMethods.updateMessage(inputUpdate, inputMessage); break; case keyInfo4: Console.ForegroundColor = ConsoleColor.Green; Console.Write("What Author_ID do you want to see messages from? > "); string aInputID = Console.ReadLine(); MessagesMethods.SelectMessage(aInputID); break; case keyInfo5: Console.ForegroundColor = ConsoleColor.Green; Console.Write("Enter new Author Name > "); string Authorinput = Console.ReadLine(); UserMethods.insertAuthor(Authorinput); break; case keyInfo6: Console.ForegroundColor = ConsoleColor.Green; Console.Write("Specify what Author_ID you want to delete > "); string Authorinputdelete = Console.ReadLine(); UserMethods.deleteAuthor(Authorinputdelete); break; case keyInfo7: Console.ForegroundColor = ConsoleColor.Green; Console.Write("What Author_ID do you want to update > "); string AuthorinputUpdate = Console.ReadLine(); Console.Write("What do you want to update the Author_Name to > "); string AuthorinputMessage = Console.ReadLine(); UserMethods.updateAuthor(AuthorinputUpdate, AuthorinputMessage); break; case keyInfo8: Console.ForegroundColor = ConsoleColor.Green; Console.Write("What Author_ID do you want to see messages from(Under construction)? > "); string AuthoraInputID = Console.ReadLine(); UserMethods.SelectAuthor(AuthoraInputID); break; } } ConsoleKey PressedKey() { do { while (!Console.KeyAvailable) { ; } ConsoleKey pressed = Console.ReadKey(true).Key; if (pressed == keyInfo1 || pressed == keyInfo2 || pressed == keyInfo3 || pressed == keyInfo4 || pressed == keyInfo5 || pressed == keyInfo6 || pressed == keyInfo7 || pressed == keyInfo8) { return(pressed); } Console.Clear(); } while (true); } }