IssueBookscs.cs

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LibraryManagementSystem
{
    public partial class IssueBookscs : UserControl
    {
        string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=LibraryManagementSystem;Integrated Security=True;Pooling=False;Encrypt=True;TrustServerCertificate=True";
        int selectedID = 0;

        public IssueBookscs()
        {
            InitializeComponent();
        }

        private void IssueBookscs_Load(object sender, EventArgs e)
        {
            displayBookIssueData();
            DataBookTitle();
        }

        public void displayBookIssueData()
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(connectionString))
                {
                    connect.Open();
                    string selectData = "SELECT id AS ID, issue_id AS IssueID, full_name AS Name, " +
                                        "contact AS Contact, email AS Email, book_title AS BookTitle, " +
                                        "author AS Author, issue_date AS DateIssue, return_date AS DateReturn, " +
                                        "status AS Status FROM issue_books WHERE date_deleted IS NULL ORDER BY id DESC";

                    SqlDataAdapter adapter = new SqlDataAdapter(selectData, connect);
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    dataGridView1.DataSource = table;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message, "Error Message",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void DataBookTitle()
        {
            try
            {
                using (SqlConnection connect = new SqlConnection(connectionString))
                {
                    connect.Open();
                    string selectData = "SELECT id, book_title, author FROM books WHERE status = 'Available' AND date_deleted IS NULL";

                    SqlDataAdapter adapter = new SqlDataAdapter(selectData, connect);
                    DataTable table = new DataTable();
                    adapter.Fill(table);

                    bookissue_bookTitle.DataSource = table;
                    bookissue_bookTitle.DisplayMember = "book_title";
                    bookissue_bookTitle.ValueMember = "id";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message, "Error Message",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void bookissue_bookTitle_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (bookissue_bookTitle.SelectedItem != null)
            {
                DataRowView selectedRow = (DataRowView)bookissue_bookTitle.SelectedItem;
                bookissue_author.Text = selectedRow["author"].ToString();
            }
        }

        private void bookissue_addBtn_Click(object sender, EventArgs e)
        {
            if (bookissue_id.Text == "" || bookissue_name.Text == "" ||
                bookissue_contact.Text == "" || bookissue_email.Text == "" ||
                bookissue_bookTitle.Text == "" || bookissue_author.Text == "" ||
                bookissue_status.Text == "")
            {
                MessageBox.Show("Please fill all blank fields", "Error Message",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            try
            {
                using (SqlConnection connect = new SqlConnection(connectionString))
                {
                    connect.Open();

                    string insertData = "INSERT INTO issue_books (issue_id, full_name, contact, email, book_title, author, status, issue_date, return_date, date_insert) " +
                        "VALUES(@issueID, @fullname, @contact, @email, @bookTitle, @author, @status, @issueDate, @returnDate, @dateInsert)";

                    using (SqlCommand cmd = new SqlCommand(insertData, connect))
                    {
                        cmd.Parameters.AddWithValue("@issueID", bookissue_id.Text.Trim());
                        cmd.Parameters.AddWithValue("@fullname", bookissue_name.Text.Trim());
                        cmd.Parameters.AddWithValue("@contact", bookissue_contact.Text.Trim());
                        cmd.Parameters.AddWithValue("@email", bookissue_email.Text.Trim());
                        cmd.Parameters.AddWithValue("@bookTitle", bookissue_bookTitle.Text.Trim());
                        cmd.Parameters.AddWithValue("@author", bookissue_author.Text.Trim());
                        cmd.Parameters.AddWithValue("@status", bookissue_status.Text.Trim());
                        cmd.Parameters.AddWithValue("@issueDate", bookissue_issueDate.Value);
                        cmd.Parameters.AddWithValue("@returnDate", bookissue_returnDate.Value);
                        cmd.Parameters.AddWithValue("@dateInsert", DateTime.Today);
                        cmd.ExecuteNonQuery();
                    }

                    string updateBook = "UPDATE books SET status='Issued' WHERE book_title=@bookTitle";
                    using (SqlCommand cmd = new SqlCommand(updateBook, connect))
                    {
                        cmd.Parameters.AddWithValue("@bookTitle", bookissue_bookTitle.Text.Trim());
                        cmd.ExecuteNonQuery();
                    }
                }

                MessageBox.Show("Issued successfully!", "Information Message",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                clearFields();
                displayBookIssueData();
                DataBookTitle();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message, "Error Message",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void bookissue_updateBtn_Click(object sender, EventArgs e)
        {
            if (selectedID == 0)
            {
                MessageBox.Show("Please select item first", "Error Message",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            DialogResult check = MessageBox.Show("Are you sure you want to UPDATE?",
                "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (check == DialogResult.Yes)
            {
                try
                {
                    using (SqlConnection connect = new SqlConnection(connectionString))
                    {
                        connect.Open();
                        string updateData = "UPDATE issue_books SET full_name=@fullName, contact=@contact, " +
                            "email=@email, book_title=@bookTitle, author=@author, status=@status, " +
                            "issue_date=@issueDate, return_date=@returnDate, date_update=@dateUpdate WHERE id=@id";

                        using (SqlCommand cmd = new SqlCommand(updateData, connect))
                        {
                            cmd.Parameters.AddWithValue("@fullName", bookissue_name.Text.Trim());
                            cmd.Parameters.AddWithValue("@contact", bookissue_contact.Text.Trim());
                            cmd.Parameters.AddWithValue("@email", bookissue_email.Text.Trim());
                            cmd.Parameters.AddWithValue("@bookTitle", bookissue_bookTitle.Text.Trim());
                            cmd.Parameters.AddWithValue("@author", bookissue_author.Text.Trim());
                            cmd.Parameters.AddWithValue("@status", bookissue_status.Text.Trim());
                            cmd.Parameters.AddWithValue("@issueDate", bookissue_issueDate.Value);
                            cmd.Parameters.AddWithValue("@returnDate", bookissue_returnDate.Value);
                            cmd.Parameters.AddWithValue("@dateUpdate", DateTime.Today);
                            cmd.Parameters.AddWithValue("@id", selectedID);
                            cmd.ExecuteNonQuery();
                        }
                    }

                    MessageBox.Show("Updated successfully!", "Information Message",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                    clearFields();
                    displayBookIssueData();
                    DataBookTitle();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message, "Error Message",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void bookissue_deleteBtn_Click(object sender, EventArgs e)
        {
            if (selectedID == 0)
            {
                MessageBox.Show("Please select item first", "Error Message",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            DialogResult check = MessageBox.Show("Are you sure you want to DELETE?",
                "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (check == DialogResult.Yes)
            {
                try
                {
                    using (SqlConnection connect = new SqlConnection(connectionString))
                    {
                        connect.Open();

                        string deleteData = "UPDATE issue_books SET date_deleted=@dateDelete WHERE id=@id";
                        using (SqlCommand cmd = new SqlCommand(deleteData, connect))
                        {
                            cmd.Parameters.AddWithValue("@dateDelete", DateTime.Today);
                            cmd.Parameters.AddWithValue("@id", selectedID);
                            cmd.ExecuteNonQuery();
                        }

                        string updateBook = "UPDATE books SET status='Available' WHERE book_title=@bookTitle";
                        using (SqlCommand cmd = new SqlCommand(updateBook, connect))
                        {
                            cmd.Parameters.AddWithValue("@bookTitle", bookissue_bookTitle.Text.Trim());
                            cmd.ExecuteNonQuery();
                        }
                    }

                    MessageBox.Show("Deleted successfully!", "Information Message",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                    clearFields();
                    displayBookIssueData();
                    DataBookTitle();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error: " + ex.Message, "Error Message",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void bookissue_clearBtn_Click(object sender, EventArgs e)
        {
            clearFields();
        }

        public void clearFields()
        {
            selectedID = 0;
            bookissue_id.Text = "";
            bookissue_name.Text = "";
            bookissue_contact.Text = "";
            bookissue_email.Text = "";
            bookissue_bookTitle.SelectedIndex = -1;
            bookissue_author.Text = "";
            bookissue_status.SelectedIndex = -1;
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
                selectedID = Convert.ToInt32(row.Cells["ID"].Value);
                bookissue_id.Text = row.Cells["IssueID"].Value.ToString();
                bookissue_name.Text = row.Cells["Name"].Value.ToString();
                bookissue_contact.Text = row.Cells["Contact"].Value.ToString();
                bookissue_email.Text = row.Cells["Email"].Value.ToString();
                bookissue_bookTitle.Text = row.Cells["BookTitle"].Value.ToString();
                bookissue_author.Text = row.Cells["Author"].Value.ToString();
                bookissue_status.Text = row.Cells["Status"].Value.ToString();

                if (DateTime.TryParse(row.Cells["DateIssue"].Value.ToString(), out DateTime issueDate))
                    bookissue_issueDate.Value = issueDate;

                if (DateTime.TryParse(row.Cells["DateReturn"].Value.ToString(), out DateTime returnDate))
                    bookissue_returnDate.Value = returnDate;
            }
        }

        private void panel1_Paint(object sender, PaintEventArgs e) { }
    }
}