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