Untitled
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