using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace _09.MySQLConnect
{
    class Program
    {
        static void Main(string[] args)
        {
            //change the Uid and Pwd with your username and password at your local MySQL server
            string connectionString = "Server = localhost; Port = 3306; Database = world; Uid = root; Pwd = rootroot; pooling = true;";
            MySqlConnection dbConnection = new MySqlConnection(connectionString);
            dbConnection.Open();

            using (dbConnection)
            {
                #region Checking for existing table 'Books' in database 'World'
                MySqlCommand checkExistingTable = new MySqlCommand(
@"SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Books'", dbConnection);

                int existing = Convert.ToInt32(checkExistingTable.ExecuteScalar());
                if (existing != 1)
                {
                    MySqlCommand createTable = new MySqlCommand(
 @"CREATE TABLE Books
(
    BookID int PRIMARY KEY AUTO_INCREMENT,
    Title nvarchar(100) NOT NULL,
    Author nvarchar(50) NOT NULL,
    PublishDate date NOT NULL,
    ISBN nvarchar(50) NOT NULL

)", dbConnection);
                    Console.WriteLine("Created table 'Book' ", createTable.ExecuteNonQuery());
                }

                #endregion

                AddProduct(dbConnection, "KillingSpree", "Johnson", new DateTime(2013, 7, 12), "123-123-435");
                AddProduct(dbConnection, "AAAAAAAA", "BBBBBBBBB", new DateTime(2013, 7, 15), "124-12-35");
                ListAllBooks(dbConnection);
                FindBookByName(dbConnection, "illi");
            }
        }

        private static void FindBookByName(MySqlConnection dbConnection, string input)
        {
            MySqlCommand selectProducts = new MySqlCommand("SELECT * FROM Books WHERE Title LIKE @searchString", dbConnection);
            input = input.Replace("%", "[%]").Replace("'", "[']").Replace("\\", "[\\]").Replace("_", "[_]").Replace("\"", "[\"]");
            selectProducts.Parameters.AddWithValue("@searchString", "%" + input + "%");

            MySqlDataReader result = selectProducts.ExecuteReader();

            using (result)
            {
                Console.WriteLine(new String('-', 20));
                Console.WriteLine("Found Books");
                Console.WriteLine(new String('-', 20));
                while (result.Read())
                {
                    Console.WriteLine("{0} {1} {2} {3} {4}", result["BookId"], result["Title"], result["Author"], result["PublishDate"], result["ISBN"]);
                }

            }
        }

        private static void ListAllBooks(MySqlConnection dbConnection)
        {
            Console.WriteLine(new String('-', 20));
            Console.WriteLine("List All Books");
            Console.WriteLine(new String('-', 20));
            MySqlCommand getAllBooks = new MySqlCommand("SELECT * FROM Books", dbConnection);

            MySqlDataReader result = getAllBooks.ExecuteReader();

            using (result)
            {
                while (result.Read())
                {
                    Console.WriteLine("{0} {1} {2} {3} {4}", result["BookId"], result["Title"], result["Author"], result["PublishDate"], result["ISBN"]);
                }
            }
        }

        private static void AddProduct(MySqlConnection dbConnection, string title, string author, DateTime publishDate, string ISBN)
        {
            MySqlCommand insertIntoProducts = new MySqlCommand(
 @"
INSERT INTO Books(Title, Author, PublishDate, ISBN)
VALUES
    (
        @Title,
        @Author,
        @PublishDate,
        @ISBN
    )

", dbConnection);
            insertIntoProducts.Parameters.AddWithValue("@Title", title);
            insertIntoProducts.Parameters.AddWithValue("@Author", author);
            insertIntoProducts.Parameters.AddWithValue("@PublishDate", publishDate.ToString("yyyy-MM-dd"));
            insertIntoProducts.Parameters.AddWithValue("@ISBN", ISBN);


            Console.WriteLine("({0}) Rows Added", insertIntoProducts.ExecuteNonQuery());
        }
    }
}