# example-csharp-app

DB sql

// pgsql

-- Вставка
CREATE PROCEDURE "sp_InsertBook"(
	Id UUID,
    Title VARCHAR(255),
    Author VARCHAR(255),
    YearPublished TimestampTz = NULL,
    BookIndex XML = NULL
)
LANGUAGE plpgsql
as $$
BEGIN   
    INSERT INTO "Books" ("Id", "Title", "Author", "YearPublished", "BookIndex")
    VALUES (Id, Title, Author, YearPublished, BookIndex);
    
end
$$

-- Обновление
CREATE PROCEDURE "sp_UpdateBook"(
	Id UUID,
    Title VARCHAR(255),
    Author VARCHAR(255),
    YearPublished TimestampTz,
    BookIndex xml
)
LANGUAGE plpgsql
as $$
BEGIN   
    UPDATE "Books"
    SET 
        "Title" = Title,
        "Author" = Author,
        "YearPublished" = YearPublished,
        "BookIndex" = BookIndex
    WHERE "Id" = Id;
end
$$

-- удаление
CREATE PROCEDURE "sp_DeleteBook"(
	Id UUID
)
LANGUAGE plpgsql
as $$
BEGIN   
    DELETE FROM "Books" WHERE "Id" = Id;
end
$$

-- получение данных
CREATE FUNCTION "sp_GetBookById"(BookId UUID)
returns table (
	"Id" UUID,
	"Author" TEXT,
	"Title" TEXT,
	"YearPublished" TIMESTAMPTZ,
	"BookIndex" XML
)
LANGUAGE plpgsql
as $$
BEGIN
	RETURN QUERY
    SELECT b."Id", b."Author", b."Title", b."YearPublished", b."BookIndex"
    FROM "Books" b
    WHERE b."Id" = BookId;
END
$$

-- поиск по библиотеке
CREATE FUNCTION "sp_SearchBooks"(p_Title TEXT, p_Author TEXT, p_Index TEXT)
returns table (
	"Id" UUID,
	"Author" TEXT,
	"Title" TEXT,
	"YearPublished" TIMESTAMPTZ,
	"BookIndex" XML
)
LANGUAGE plpgsql
as $$
BEGIN
	RETURN QUERY
    SELECT b."Id", b."Author", b."Title", b."YearPublished", b."BookIndex"
    FROM "Books" b
    WHERE 
	(p_Author IS NULL OR b."Author" ~* p_Author) AND 
	(p_Title IS NULL OR b."Title" ~* p_Title) AND
	(b."BookIndex"::TEXT ILIKE CONCAT('%', p_Index, '%'));
END
$$