Untitled

 avatar
unknown
plain_text
a year ago
4.9 kB
7
Indexable
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=;";

        private void LoadData(string sSQL)
        {
            MySqlConnection Conn = new MySqlConnection(ConnStr);

            try
            {
                Conn.Open();
                MySqlCommand cmd = new MySqlCommand(sSQL, Conn);

                // Add parameters here
                cmd.Parameters.AddWithValue("@bookcode", "%" + txtISBN.Text + "%");
                cmd.Parameters.AddWithValue("@title", "%" + txtTitle.Text + "%");
                cmd.Parameters.AddWithValue("@authorname", "%" + txtAuthor.Text + "%");

                MySqlDataReader dr = cmd.ExecuteReader();

                DataTable dt = new DataTable();
                dt.Load(dr);

                dataGridView1.DataSource = dt;
                Conn.Close();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Connection Error! \nError Number: " + ex.Number + "\nMessage: " + ex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred: " + ex.Message);
            }
        }

        private 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;

            if (!string.IsNullOrEmpty(bookcode))
            {
                queryBuilder.Append(whereAdded ? "AND " : "WHERE ");
                queryBuilder.Append("book.bookcode LIKE '%");
                queryBuilder.Append(bookcode);
                queryBuilder.Append("%' ");
                whereAdded = true;
            }

            if (!string.IsNullOrEmpty(title))
            {
                queryBuilder.Append(whereAdded ? " AND " : " WHERE ");
                queryBuilder.Append("book.title LIKE '%");
                queryBuilder.Append(title);
                queryBuilder.Append("%' ");
                whereAdded = true;
            }

            if (!string.IsNullOrEmpty(authorname))
            {
                queryBuilder.Append(whereAdded ? " AND " : " WHERE ");
                queryBuilder.Append("CONCAT(author.authorfirst, ', ', author.authorlast) LIKE '%");
                queryBuilder.Append(authorname);
                queryBuilder.Append("%' ");
                whereAdded = true;
            }

            return queryBuilder.ToString();
        }

            private void btnSearch_Click(object sender, EventArgs e)
        {
            // Input Validation
            if (string.IsNullOrEmpty(txtISBN.Text) && string.IsNullOrEmpty(txtTitle.Text) && string.IsNullOrEmpty(txtAuthor.Text))
            {
                MessageBox.Show("Please enter search criteria.");
                return;
            }

            string bookcode = txtISBN.Text;
            string title = txtTitle.Text;
            string authorname = txtAuthor.Text;

            string sSQL = BuildQuery(bookcode, title, authorname);

            // Execute parameterized query (Add parameter values to the command inside LoadData)
            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);

        }
    }
}
Editor is loading...
Leave a Comment