lul.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Windows;
using System.Windows.Media.Imaging;
using Microsoft.Win32;
using Npgsql;


namespace Lab7
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        string connString = "Host=localhost;Username=postgres;Password=mypass;Database=postgres";
        public BitmapImage image = new BitmapImage();
        bool updateExecuting = false;

        public MainWindow()
        {
            InitializeComponent();
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO authors (name, country, gender) VALUES (@name, @country, @gender)";
                    cmd.Parameters.AddWithValue("name", AuthorName.Text);
                    cmd.Parameters.AddWithValue("country", AuthorCountry.Text);
                    if(Male.IsChecked == true && Female.IsChecked == false)
                    {
                        cmd.Parameters.AddWithValue("gender", 'm');
                    }
                    if(Female.IsChecked == true && Male.IsChecked == false)
                    {
                        cmd.Parameters.AddWithValue("gender", 'f');
                    }
                    if (Female.IsChecked == false && Male.IsChecked == false)
                    {
                        cmd.Parameters.AddWithValue("gender", 'n');
                    }
                    if (Female.IsChecked == true && Male.IsChecked == true)
                    {
                        cmd.Parameters.AddWithValue("gender", '?');
                    }

                    cmd.ExecuteNonQuery();
                }

                AuthorsList_Loaded(this, new RoutedEventArgs());
                PublishersList_Loaded(this, new RoutedEventArgs());

                conn.Close();
            }
        } //ADD AUTHOR

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO publishers (name, country, city, year, type) VALUES (@name, @country, @city, @year, @type)";
                    cmd.Parameters.AddWithValue("name", PublisherName.Text);
                    cmd.Parameters.AddWithValue("country", PublisherCountry.Text);
                    cmd.Parameters.AddWithValue("city", PublisherCity.Text);
                    cmd.Parameters.AddWithValue("year", PublisherYear.Text);

                    if(PublicCheck.IsChecked == true && CustomCheck.IsChecked == false)
                    {
                        cmd.Parameters.AddWithValue("type", 'p');
                    }

                    if(PublicCheck.IsChecked == false && CustomCheck.IsChecked == true)
                    {
                        cmd.Parameters.AddWithValue("type", 'c');
                    }

                    cmd.ExecuteNonQuery();
                }

                conn.Close();
                AuthorsList_Loaded(this, new RoutedEventArgs());
                PublishersList_Loaded(this, new RoutedEventArgs());
            }
        } // ADD PUBLISHER

        private void AuthorsList_Loaded(object sender, RoutedEventArgs e)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT * FROM authors", conn);

                DataTable dataTable = new DataTable("authors");

                adapter.Fill(dataTable);

                AuthorsList.ItemsSource = dataTable.DefaultView;
            }
        }

        private void PublishersList_Loaded(object sender, RoutedEventArgs e)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT * FROM publishers", conn);

                DataTable dataTable = new DataTable("publishers");

                adapter.Fill(dataTable);

                PublishersList.ItemsSource = dataTable.DefaultView;
            }
        }

        private void DataGrid_Loaded(object sender, RoutedEventArgs e)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT * FROM books", conn);

                DataTable dataTable = new DataTable("books");

                adapter.Fill(dataTable);

                BooksList.ItemsSource = dataTable.DefaultView;
            }
        }

        private void Button_Click_2(object sender, RoutedEventArgs e) //ADD BOOKS
        {

            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "INSERT INTO books (name, pageqty, publisher, cd, dvd, authors, year, cover_img) " +
                                                      "VALUES (@name, @pageqty, @publisher, @cd, @dvd, @authors, @year, @cover_img)";
                    cmd.Parameters.AddWithValue("name", NameField.Text);
                    cmd.Parameters.AddWithValue("authors", ((DataRowView)AuthorsList.SelectedItems[0])["authorid"]);
                    cmd.Parameters.AddWithValue("publisher", ((DataRowView)PublishersList.SelectedItems[0])["publisherid"]);
                    try
                    {
                        cmd.Parameters.AddWithValue("pageqty", Int32.Parse(PageQtyField.Text));
                        cmd.Parameters.AddWithValue("year", Int32.Parse(YearField.Text));
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("Wrong numbers :)");
                    }

                    try
                    {
                        cmd.Parameters.AddWithValue("cover_img", PictureByteConverter.ImageToBytes(image));
                    }
                    catch (InvalidOperationException)
                    {
                        MessageBox.Show("Choose an Image first!");
                        return;
                    }


                    if(Cd.IsChecked == true)
                    {
                        cmd.Parameters.AddWithValue("cd", true);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("cd", false);
                    }

                    if (Dvd.IsChecked == true)
                    {
                        cmd.Parameters.AddWithValue("dvd", true);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("dvd", false);
                    }

                    cmd.ExecuteNonQuery();
                }

                conn.Close();
                DataGrid_Loaded(this, new RoutedEventArgs());
;            }
        }

        private void Button_Click_3(object sender, RoutedEventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            if (dialog.ShowDialog() == true)
            {
                image.BeginInit();
                image.UriSource = new Uri(dialog.FileName, UriKind.Absolute);
                image.EndInit();

                BookPreview.Source = image;
            }
        } //ADD BOOK COVER

        private void Submit_Click(object sender, RoutedEventArgs e) //SUBMIT DB CHANGES
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "UPDATE books SET name = @name, pageqty = @pageqty, publisher = @publisher, cd = @cd, dvd = @dvd, authors = @authors, year= @year " +
                        "WHERE udk = " + ((DataRowView)BooksList.SelectedItem)["udk"];

                    cmd.Parameters.AddWithValue("name", ((DataRowView)BooksList.SelectedItem)["name"]);
                    cmd.Parameters.AddWithValue("authors", (int)((DataRowView)BooksList.SelectedItem)["authors"]);
                    cmd.Parameters.AddWithValue("publisher", (int)((DataRowView)BooksList.SelectedItem)["publisher"]);

                    cmd.Parameters.AddWithValue("pageqty", ((DataRowView)BooksList.SelectedItem)["pageqty"]);
                    cmd.Parameters.AddWithValue("year", ((DataRowView)BooksList.SelectedItem)["year"]);


                    cmd.Parameters.AddWithValue("cd", ((DataRowView)BooksList.SelectedItem)["cd"]);
                    cmd.Parameters.AddWithValue("dvd", ((DataRowView)BooksList.SelectedItem)["dvd"]);

                    cmd.ExecuteNonQuery();
                }
            }
            DataGrid_Loaded(this, new RoutedEventArgs());
        }

        private void Delete_Click(object sender, RoutedEventArgs e)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(connString))
            {
                conn.Open();

                using (NpgsqlCommand cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM books WHERE udk=" + ((DataRowView)BooksList.SelectedItem)["udk"];


                    cmd.ExecuteNonQuery();
                }

                DataGrid_Loaded(this, new RoutedEventArgs());
            }
        } //DELETE DB ENTRY


        private void BooksList_SelectionChanged(object sender, System.Windows.Controls.SelectionChangedEventArgs e)
        {
            if (updateExecuting == false)
            {
                using (NpgsqlConnection conn = new NpgsqlConnection(connString))
                {
                    conn.Open();

                    using (NpgsqlCommand cmd = new NpgsqlCommand())
                    {
                        try
                        {
                            cmd.Connection = conn;
                            cmd.CommandText = "SELECT * FROM " +
                                "(SELECT books.name AS BookName, authors.name AS AuthorName, cover_img, pageqty, udk FROM books INNER JOIN authors ON " + ((DataRowView)BooksList.SelectedItem)["authors"] +
                                "=authors.authorid) AS weird WHERE udk = " + ((DataRowView)BooksList.SelectedItem)["udk"];
                            NpgsqlDataReader dr = cmd.ExecuteReader();

                            while (dr.Read())
                            {
                                BookName.Content = dr["bookname"];
                                Author.Content = dr["authorname"];
                                PageQty.Content = dr["pageqty"];
                                BookCover.Source = PictureByteConverter.BytesToImage((byte[])dr["cover_img"]);
                            }
                        }
                        catch(NullReferenceException)
                        {
                            BooksList.SelectedItem = BooksList.Items[0];
                            BooksList_SelectionChanged(this, e);
                        }
                    }
                }
            }
        }
    }
}