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