BuecherwurmAPI/Models/ItemModel.cs

121 lines
4.1 KiB
C#

using System;
using System.Collections.Generic;
using BuecherwurmAPI.Models;
using Microsoft.Data.Sqlite;
using Microsoft.VisualBasic.CompilerServices;
namespace BuecherwurmAPI.Models
{
public interface IItem
{
}
internal class ItemModel : IItem
{
private SqliteConnection _dbConnection;
public ItemModel()
{
var connectionBuilder = new SqliteConnectionStringBuilder {DataSource = "LongWormMemory.db"};
_dbConnection = new SqliteConnection(connectionBuilder.ConnectionString);
_dbConnection.Open();
}
public bool IdExits(string table, long id)
{
using (var command = _dbConnection.CreateCommand())
{
command.Parameters.Add(new SqliteParameter("@Id", SqliteType.Integer)).Value = id;
// Certain parts of the query can't be filled with variables for security reasons.
switch (table)
{
case Tables.Table.Items:
command.CommandText = @"SELECT EXISTS(SELECT 0 FROM Items WHERE Id = @Id)";
break;
}
var dr = command.ExecuteReader();
long result = 0;
while (dr.Read())
{
result = (long) dr[0];
}
return result != 0;
}
}
public IEnumerable<Item> GetAllItems()
{
var items = new List<Item>();
// using automatically disposes the command after completion
using (var command = _dbConnection.CreateCommand())
{
command.CommandText = "SELECT * FROM Items";
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
items.Add(new Item
{
ItemId = (long) dataReader["ItemId"],
BookId = (long) dataReader["BookId"]
});
}
}
return items;
}
public Item GetItemById(long itemId)
{
using (var command = _dbConnection.CreateCommand())
{
command.Parameters.Add(new SqliteParameter("@itemId", itemId));
command.CommandText = "SELECT * FROM Items WHERE ItemId = @itemId";
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var item = new Item
{
ItemId = (long) dataReader["ItemId"],
BookId = (long) dataReader["BookId"]
};
return item;
}
}
return null;
}
public Item NewItem(ItemPost itemPost)
{
using (var command = _dbConnection.CreateCommand())
{
//hier soll jetz der NewItem Command folgen. ein POST mit EingabeInfo {id} = "BookId"
command.CommandText = "INSERT INTO Items (BookId) VALUES (@bookId)";
command.CommandType = System.Data.CommandType.Text;
command.Parameters.Add(new SqliteParameter("@bookId", itemPost.BookId));
command.ExecuteNonQuery();
command.CommandText = "select last_insert_rowid()";
var LastRowId64 = (long)command.ExecuteScalar();
var item = new Item{
ItemId = LastRowId64,
BookId = itemPost.BookId
};
return item;
}
}
public void DeleteItem(long itemId)
{
using (var command = _dbConnection.CreateCommand())
{
command.CommandText = "DELETE FROM Items WHERE ItemId = @itemId";
command.Parameters.Add(new SqliteParameter("@itemId", itemId));
command.ExecuteNonQuery();
}
}
}
}