224 lines
No EOL
12 KiB
C#
224 lines
No EOL
12 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using BuecherwurmAPI.Models;
|
|
using Microsoft.Data.Sqlite;
|
|
using Microsoft.VisualBasic.CompilerServices;
|
|
|
|
namespace BuecherwurmAPI.Models
|
|
{
|
|
public interface ICatalogue
|
|
{
|
|
|
|
}
|
|
internal class KatalogModel : ICatalogue
|
|
{
|
|
private SqliteConnection _dbConnection;
|
|
|
|
public KatalogModel()
|
|
{
|
|
var connectionBuilder = new SqliteConnectionStringBuilder {DataSource = "LongWormMemory.db"};
|
|
_dbConnection = new SqliteConnection(connectionBuilder.ConnectionString);
|
|
_dbConnection.Open();
|
|
}
|
|
|
|
public IEnumerable<IProduct> GetAllProducts()
|
|
{
|
|
var books= new List<IProduct>();
|
|
|
|
using (var command = _dbConnection.CreateCommand())
|
|
{
|
|
command.CommandText = @"SELECT * FROM Products";
|
|
var dataReader = command.ExecuteReader();
|
|
|
|
while (dataReader.Read())
|
|
{
|
|
if (!dataReader["Author"].Equals(null))
|
|
{
|
|
books.Add(new Book
|
|
{
|
|
Title = (string) dataReader["Title"],
|
|
Author = (string) dataReader["Author"],
|
|
Country = (string) dataReader["Country"],
|
|
Link = (string) dataReader["Link"],
|
|
Language = (string) dataReader["Language"],
|
|
Pages = (long) dataReader["Pages"],
|
|
Year = (long) dataReader["Year"],
|
|
ProductId = (long) dataReader["ProductId"],
|
|
Category = (CategoryEnum) Enum.Parse(typeof(CategoryEnum), dataReader["Category"].ToString()),
|
|
ImageLink = (string) dataReader["ImageLink"],
|
|
LendTime = (long) dataReader["LendTime"],
|
|
LendType = (LendTypeEnum) Enum.Parse(typeof(LendTypeEnum), dataReader["LendType"].ToString())
|
|
});
|
|
}
|
|
else
|
|
{
|
|
books.Add(new Magazin
|
|
{
|
|
Title = (string) dataReader["Title"],
|
|
LendTime = (long) dataReader["LendTime"],
|
|
LendType = (LendTypeEnum) Enum.Parse(typeof(LendTypeEnum), dataReader["LendType"].ToString()),
|
|
Category = (CategoryEnum) Enum.Parse(typeof(CategoryEnum), dataReader["Category"].ToString()),
|
|
Run = (long) dataReader["Run"],
|
|
Audience = (string) dataReader["Audience"],
|
|
Topic = (string) dataReader["Topic"]
|
|
});
|
|
}
|
|
}
|
|
}
|
|
return books;
|
|
}
|
|
|
|
public IProduct GetProductById(long id)
|
|
{
|
|
using (var command = _dbConnection.CreateCommand())
|
|
{
|
|
command.CommandText = "SELECT * FROM \"Products\" WHERE ProductId = @id";
|
|
command.Parameters.Add(new SqliteParameter("@id", id));
|
|
var dataReader = command.ExecuteReader();
|
|
|
|
IProduct book;
|
|
|
|
dataReader.Read();
|
|
|
|
if (dataReader["Author"].Equals(null))
|
|
{
|
|
book = new Magazin
|
|
{
|
|
Title = (string)dataReader["Title"],
|
|
LendTime = (long)dataReader["LendTime"],
|
|
LendType = (LendTypeEnum) Enum.Parse(typeof(LendTypeEnum), dataReader["LendType"].ToString()),
|
|
Category = (CategoryEnum) Enum.Parse(typeof(CategoryEnum), dataReader["Category"].ToString()),
|
|
Run = (long)dataReader["Run"],
|
|
Audience = (string)dataReader["Audience"],
|
|
Topic = (string)dataReader["Topic"]
|
|
};
|
|
}
|
|
else
|
|
{
|
|
book = new Book
|
|
{
|
|
Title = (string)dataReader["Title"],
|
|
Author = (string)dataReader["Author"],
|
|
Country = (string)dataReader["Country"],
|
|
Link = (string)dataReader["Link"],
|
|
Language = (string)dataReader["Language"],
|
|
Pages = (long)dataReader["Pages"],
|
|
Year = (long)dataReader["Year"],
|
|
ProductId = (long)dataReader["ProductId"],
|
|
Category = (CategoryEnum) Enum.Parse(typeof(CategoryEnum), dataReader["Category"].ToString()),
|
|
ImageLink = (string) dataReader["ImageLink"],
|
|
LendTime = (long) dataReader["LendTime"],
|
|
LendType = (LendTypeEnum) Enum.Parse(typeof(LendTypeEnum), dataReader["LendType"].ToString())
|
|
};
|
|
}
|
|
return book;
|
|
}
|
|
}
|
|
public void DeleteProduct(long id)
|
|
{
|
|
using (var command = _dbConnection.CreateCommand())
|
|
{
|
|
command.Parameters.Add(new SqliteParameter("@id", SqliteType.Integer)).Value = id;
|
|
command.CommandText = @"DELETE * FROM Produkts WHERE ProductId= @id";
|
|
var dataReader = command.ExecuteReader();
|
|
}
|
|
}
|
|
|
|
public long AddProduct (IProductPost book)
|
|
{
|
|
using (var command = _dbConnection.CreateCommand())
|
|
{
|
|
// funktioniert das so?
|
|
//muss ProduktId übergeben werden? -> ProductId wird weiter hinten mit 'NULL' befüllt, damit die ID automatisch vergeben wird.
|
|
command.CommandText = @"INSERT INTO Products (ProductId, Title, Author, Country, Link, Language, Pages, Year, Category, ImageLink, LendTime, LendType, Run, Audience, Topic) VALUES (NULL, @Title, @Author, @Country, @Link, @Language, @Pages, @Year, @Category, @ImageLink, @LendTime, @LendType, @Run, @Audience, @Topic)";
|
|
|
|
command.Parameters.Add(new SqliteParameter("@Title", book.Title));
|
|
command.Parameters.Add(new SqliteParameter("@Category", book.Category));
|
|
command.Parameters.Add(new SqliteParameter("@LendTime", book.LendTime));
|
|
command.Parameters.Add(new SqliteParameter("@LendType", book.LendType));
|
|
if (book.GetType() == typeof(BookPost))
|
|
{
|
|
command.Parameters.Add(new SqliteParameter("@Author", ((BookPost)book).Author));
|
|
command.Parameters.Add(new SqliteParameter("@Country", ((BookPost)book).Country));
|
|
command.Parameters.Add(new SqliteParameter("@Link", ((BookPost)book).Link));
|
|
command.Parameters.Add(new SqliteParameter("@Language", ((BookPost)book).Language));
|
|
command.Parameters.Add(new SqliteParameter("@Pages", ((BookPost)book).Pages));
|
|
command.Parameters.Add(new SqliteParameter("@Year", ((BookPost)book).Year));
|
|
command.Parameters.Add(new SqliteParameter("@ImageLink", ((BookPost)book).ImageLink));
|
|
command.Parameters.Add(new SqliteParameter("@Run", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Audience", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Topic", DBNull.Value));
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add(new SqliteParameter("@Author", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Country", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Link", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Language", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Pages", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Year", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@ImageLink", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Run", ((MagazinPost)book).Run));
|
|
command.Parameters.Add(new SqliteParameter("@Audience", ((MagazinPost)book).Audience));
|
|
command.Parameters.Add(new SqliteParameter("@Topic", ((MagazinPost)book).Topic));
|
|
}
|
|
|
|
var success = command.ExecuteNonQuery();
|
|
|
|
if (success == 1)
|
|
{
|
|
command.CommandText = @"SELECT last_insert_rowid()";
|
|
return (long)command.ExecuteScalar();
|
|
}
|
|
|
|
return -1;
|
|
}
|
|
}
|
|
|
|
public long EditProduct (long id, IProduct book)
|
|
{
|
|
using (var command = _dbConnection.CreateCommand())
|
|
{
|
|
// funktioniert das so?
|
|
command.Parameters.Add(new SqliteParameter("@id", id));
|
|
command.CommandText = @"UPDATE Products SET (Title, Author, Country, Link, Language, Pages, Year, Category, ImageLink, LendTime, LendType, Run, Audience, Topic) = (@Title, @Author, @Country, @Link, @Language, @Pages, @Year, @Category, @ImageLink, @LendTime, @LendType, @Run, @Audience, @Topic) WHERE ProductId = @id";
|
|
|
|
command.Parameters.Add(new SqliteParameter("@Title", book.Title));
|
|
command.Parameters.Add(new SqliteParameter("@Category", book.Category));
|
|
command.Parameters.Add(new SqliteParameter("@LendTime", book.LendTime));
|
|
command.Parameters.Add(new SqliteParameter("@LendType", book.LendType));
|
|
if (book.GetType() == typeof(Book))
|
|
{
|
|
command.Parameters.Add(new SqliteParameter("@Author", ((BookPost)book).Author));
|
|
command.Parameters.Add(new SqliteParameter("@Country", ((BookPost)book).Country));
|
|
command.Parameters.Add(new SqliteParameter("@Link", ((BookPost)book).Link));
|
|
command.Parameters.Add(new SqliteParameter("@Language", ((BookPost)book).Language));
|
|
command.Parameters.Add(new SqliteParameter("@Pages", ((BookPost)book).Pages));
|
|
command.Parameters.Add(new SqliteParameter("@Year", ((BookPost)book).Year));
|
|
command.Parameters.Add(new SqliteParameter("@ImageLink", ((BookPost)book).ImageLink));
|
|
command.Parameters.Add(new SqliteParameter("@Run", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Audience", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Topic", DBNull.Value));
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add(new SqliteParameter("@Author", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Country", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Link", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Language", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Pages", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Year", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@ImageLink", DBNull.Value));
|
|
command.Parameters.Add(new SqliteParameter("@Run", ((MagazinPost)book).Run));
|
|
command.Parameters.Add(new SqliteParameter("@Audience", ((MagazinPost)book).Audience));
|
|
command.Parameters.Add(new SqliteParameter("@Topic", ((MagazinPost)book).Topic));
|
|
}
|
|
|
|
var success = command.ExecuteNonQuery();
|
|
|
|
return id;
|
|
}
|
|
}
|
|
|
|
}
|
|
} |