Library Management System in C# (Windows Forms) with SQL Server

The tutorial begins by outlining the project: a simple library management system using Visual Studio and SQL Server Management Studio. The system will be a Windows application with three core tables: Member, Book, and Borrow. The speaker details the fields for each table, including primary and foreign key relationships, and explains the CRUD operations (Create, Read, Update, Delete) that will be implemented for each table. Key learning points include setting up primary and foreign keys, understanding relationships, and creating a new database and tables without writing complex queries, focusing on the database diagram approach.

The speaker demonstrates how to connect to SQL Server Management Studio, create a new database named 'Library MIS', and enable database diagrams. They then proceed to create the 'Member', 'Book', and 'Borrow' tables within the database diagram interface, specifying column names, data types (e.g., INT, NVARCHAR), and setting primary keys. The importance of consistent data types for foreign keys is emphasized to avoid errors. The tutorial also covers setting identity specifications (auto-increment) for primary keys in each table.

The video shows how to manually add data to the 'Member' and 'Book' tables in SQL Server to test the auto-increment functionality and relationships. Following this, the tutorial shifts to Visual Studio to create a new C# Windows Forms application project named 'Library MIStutorial'. The goal is to create three forms for book management, member management, and borrow management, with the initial 'Form1' serving as a dashboard for navigation.

The speaker designs the main 'Form1' as a dashboard with a menu strip for navigation to 'Manage Members', 'Manage Books', and 'Manage Borrows'. Separate forms ('MemberForm', 'BookForm', 'BorrowForm') are created. The 'MemberForm' is designed with text boxes for full name, phone, email, address, and buttons for save, update, delete, along with a DataGridView to display members. The 'BookForm' and 'BorrowForm' designs are outlined, including combo boxes for selecting existing members and books in the 'BorrowForm'.

This section covers connecting the C# application to the SQL Server database. The process involves adding a new database connection in Visual Studio's Server Explorer, retrieving the connection string from SQL Server, and configuring it in the application's 'App.config' file. The speaker demonstrates how to integrate `System.Configuration`, `System.Data`, and `System.Data.SqlClient` namespaces in C# code for database interaction and defines a connection string variable.

The tutorial demonstrates how to create a 'DisplayMembers' function in C# to retrieve data from the 'Member' table and populate a DataGridView. It uses `SqlConnection`, `SqlDataAdapter`, and `DataTable` for this purpose. The same logic is then applied to create a 'DisplayBooks' function for the 'Book' table. The dashboard's menu items are also configured to open the respective management forms upon click. The initial data display on form load is verified.

The speaker guides through implementing the 'Save' functionality for both 'Member' and 'Book' forms. This involves creating an SQL INSERT query programmatically using `SqlCommand` and parameterized queries to prevent SQL injection. The data from text boxes on the forms is passed as parameters. The importance of opening and closing the database connection and using `ExecuteNonQuery` (for insert/update/delete operations) is highlighted. Successful insertion is confirmed with a `MessageBox`, and the display function is called to refresh the DataGridView.

The tutorial moves on to implement the 'Update' functionality. The existing 'Save' code is repurposed and modified to create an SQL UPDATE query. The update operation targets specific records based on their ID, which is retrieved from a hidden text box on the form. Troubleshooting common errors related to SQL syntax and parameter handling is also covered, ensuring that the 'Publish Year' and 'Join Date' fields are correctly handled in the update queries.

The speaker implements the 'Delete' functionality for both members and books. Similar to 'Save' and 'Update', an SQL DELETE query is constructed, typically based on the record's ID. A hidden text box is used to pass the ID of the item to be deleted. The functionality is tested by deleting existing records from the database, confirming that the DataGridView reflects the changes.

This final part focuses on the 'BorrowForm'. Functions are created to display lists of members and books in combo boxes. Instead of displaying full names and titles, the respective IDs are saved when an item is selected from the combo box. The 'Save' functionality for borrows involves inserting 'Member ID', 'Book ID', 'Borrow Date', and 'Return Status' into the 'Borrow' table. The importance of auto-incrementing the 'Borrow ID' is reviewed, and the save process is demonstrated, including refreshing the DataGridView with the newly added borrow record.