Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
4.0 kB
2
Indexable
Never
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

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

        private void LoadData(string sSQL)
        {
            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; 

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

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

                //close connection
                Conn.Close();

            }
            catch (MySqlException ex)
            {
                //Show message if the connection is failed
                MessageBox.Show("Connection Failed! " + ex.Message);
                Application.Exit(); 
            }
        }

        private string BuildQuery(string isbn, string title, string author)
        {
            StringBuilder queryBuilder = new StringBuilder();

            queryBuilder.Append("SELECT " +
                      "b.isbn, " +
                      "b.title, " +
                      "b.author, " +
                      "b.category, " +
                      "CASE WHEN s.instock >= 1 THEN 'Available' ELSE 'Not available' END AS STATUS " +
                      "FROM tblbooks b " +
                      "JOIN tblstocks s ON b.isbn = s.isbn ");

            bool whereAdded = false;


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

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

            if (!string.IsNullOrEmpty(author))
            {
                queryBuilder.Append(whereAdded ? "AND " : "WHERE ");
                queryBuilder.Append("b.author LIKE '%");
                queryBuilder.Append(author);
                queryBuilder.Append("%' ");
            }

            return queryBuilder.ToString();
        }
        private void btnSearch_Click(object sender, EventArgs e)
        {
            string sSQL = BuildQuery(txtISBN.Text, txtTitle.Text, txtAuthor.Text);
            LoadData(sSQL);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string sSQL = @"SELECT 
                    b.isbn, 
                    b.title, 
                    b.author, 
                    b.category,
                    CASE 
                        WHEN s.instock >= 1 THEN 'Available' 
                        ELSE 'Not available' 
                    END AS status 
                FROM tblbooks b 
                JOIN tblstocks s ON b.isbn = s.isbn";
            LoadData(sSQL);
        }
    }
}
Leave a Comment