Untitled
unknown
plain_text
a year ago
4.0 kB
9
Indexable
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); } } }
Editor is loading...
Leave a Comment