Untitled

using MySql.Data.MySqlClient;
using System.Data;
using System.Text;

namespace SF_Brogada_201
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        string ConnStr = @"SERVER= localhost;
              DATABASE= tinylibrary_db;
              UID=root;
              PWD=root;";

        private void LoadData(string sSQL)
        {
            //1. Create connection wiht MySql Database
            MySqlConnection Conn = new MySqlConnection(ConnStr);

            try
            {
                Conn.Open(); //Open Connection
                             //2. Create Command
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sSQL; //Assign the Sql to SqlCommand (cmd)

                //3. Data Reader
                MySqlDataReader dr = cmd.ExecuteReader();
                //4. Handle retrieved data using DataTable
                DataTable dt = new DataTable(); //Handles retrieved Data
                dt.Load(dr); //Store data to DataTable (dt)

                //5. Show data
                dataGridView1.DataSource = dt;

                //close connection
                Conn.Close();
            }
            catch
            {
                //Show message if connection fails
                MessageBox.Show("Connection Error!");
                Application.Exit(); //Close App
            }
        }

        // Function to build the SQL query
        public string BuildQuery(string bookcode, string title, string authorname)
        {
            StringBuilder queryBuilder = new StringBuilder();

            queryBuilder.Append("SELECT " +
                                "book.bookcode, " +
                                "book.title, " +
                                "book.TYPE, " +
                                "CONCAT(author.authorfirst, ', ', author.authorlast) AS author, " +
                                "CASE WHEN inventory.onhand >= 1 THEN 'Available' ELSE 'Not available' END AS `inventory` " +
                                "FROM book " +
                                "JOIN wrote ON book.bookcode = wrote.bookcode " +
                                "JOIN author ON wrote.authornum = author.authornum " +
                                "JOIN inventory ON book.bookcode = inventory.bookcode ");

            bool whereAdded = false;

            // Add WHERE conditions only if necessary
            if (!string.IsNullOrEmpty(bookcode))
            {
                queryBuilder.Append(whereAdded ? "AND " : "WHERE ");
                queryBuilder.Append("book.bookcode LIKE '%");
                queryBuilder.Append(bookcode); // Ensure you sanitize the input value here!
                queryBuilder.Append("%' ");
                whereAdded = true;
            }

            if (!string.IsNullOrEmpty(title))
            {
                queryBuilder.Append(whereAdded ? "AND " : "WHERE ");
                queryBuilder.Append("book.title LIKE '%");
                queryBuilder.Append(title); // Ensure you sanitize the input value here!
                queryBuilder.Append("%' ");
                whereAdded = true;
            }

            if (!string.IsNullOrEmpty(authorname))
            {
                queryBuilder.Append(whereAdded ? "AND " : "WHERE ");
                queryBuilder.Append("CONCAT(author.authorfirst, ', ', author.authorlast) LIKE '%");
                queryBuilder.Append(authorname); // Ensure you sanitize the input value here!
                queryBuilder.Append("%' ");
            }

            return queryBuilder.ToString();
        }


        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrEmpty(txtAuthor.Text)) return;
            string sSQL = @"SELECT  
           book.bookcode, 
           book.title, 
           book.TYPE, 
           CONCAT(author.authorfirst, ', ', author.authorlast) AS author,
           CASE 
            WHEN inventory.onhand >= 1 THEN 'Available' 
            ELSE 'Not available'  
           END AS inventory
         FROM book
         JOIN wrote ON book.bookcode = wrote.bookcode
         JOIN author ON wrote.authornum = author.authornum  
         JOIN inventory ON book.bookcode = inventory.bookcode
         WHERE CONCAT(author.authorfirst, ', ', author.authorlast) LIKE '%" + txtAuthor.Text + "%'";
            LoadData(sSQL);

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string sSQL = @"SELECT 
            book.bookcode, 
            book.title, 
            book.TYPE, 
            CONCAT(author.authorfirst, ', ', author.authorlast) AS author_name,
            CASE 
              WHEN inventory.onhand >= 1 THEN 'Available' 
              ELSE 'Not available' 
            END AS book_status
          FROM book
          JOIN wrote ON book.bookcode = wrote.bookcode
          JOIN author ON wrote.authornum = author.authornum 
          JOIN inventory ON book.bookcode = inventory.bookcode";
            LoadData(sSQL);

        }
    }
}