Library Management System: C# Windows Forms Application with SQL Server

Safa Bayar included in University Projects

2025-04-25 3466 words 17 minutes

Contents

Library Management System: C# Windows Forms Application with SQL Server

In this post, I’ll share insights from my Library Management System project, which demonstrates comprehensive C# application development, Windows Forms GUI design, SQL Server database integration, and educational software development for library operations.

Project Overview

The Library Management System is a comprehensive desktop application designed for educational institutions to manage their library operations. Built with C# and Windows Forms, it provides an intuitive interface for librarians to manage books, members, borrowing transactions, and generate reports with SQL Server database backend.

Technical Architecture

Project Structure

LibraryManagementSystem/
├── LibraryManagementSystem/
│   ├── Forms/
│   │   ├── MainForm.cs
│   │   ├── BookManagement/
│   │   │   ├── AddBookForm.cs
│   │   │   ├── EditBookForm.cs
│   │   │   ├── BookSearchForm.cs
│   │   │   └── BookDetailsForm.cs
│   │   ├── MemberManagement/
│   │   │   ├── AddMemberForm.cs
│   │   │   ├── EditMemberForm.cs
│   │   │   ├── MemberSearchForm.cs
│   │   │   └── MemberDetailsForm.cs
│   │   ├── Borrowing/
│   │   │   ├── BorrowBookForm.cs
│   │   │   ├── ReturnBookForm.cs
│   │   │   ├── RenewBookForm.cs
│   │   │   └── OverdueBooksForm.cs
│   │   ├── Reports/
│   │   │   ├── BookReportForm.cs
│   │   │   ├── MemberReportForm.cs
│   │   │   ├── BorrowingReportForm.cs
│   │   │   └── OverdueReportForm.cs
│   │   └── Settings/
│   │       ├── DatabaseSettingsForm.cs
│   │       ├── SystemSettingsForm.cs
│   │       └── UserManagementForm.cs
│   ├── Models/
│   │   ├── Book.cs
│   │   ├── Member.cs
│   │   ├── Borrowing.cs
│   │   ├── Author.cs
│   │   ├── Category.cs
│   │   └── User.cs
│   ├── DataAccess/
│   │   ├── DatabaseConnection.cs
│   │   ├── BookDataAccess.cs
│   │   ├── MemberDataAccess.cs
│   │   ├── BorrowingDataAccess.cs
│   │   ├── AuthorDataAccess.cs
│   │   └── CategoryDataAccess.cs
│   ├── BusinessLogic/
│   │   ├── BookManager.cs
│   │   ├── MemberManager.cs
│   │   ├── BorrowingManager.cs
│   │   ├── ReportManager.cs
│   │   └── ValidationHelper.cs
│   ├── Utilities/
│   │   ├── Logger.cs
│   │   ├── ConfigManager.cs
│   │   ├── DateHelper.cs
│   │   ├── StringHelper.cs
│   │   └── EmailHelper.cs
│   ├── Resources/
│   │   ├── Images/
│   │   ├── Icons/
│   │   └── Strings/
│   ├── App.config
│   ├── Program.cs
│   └── LibraryManagementSystem.csproj
├── Database/
│   ├── Scripts/
│   │   ├── CreateDatabase.sql
│   │   ├── CreateTables.sql
│   │   ├── InsertSampleData.sql
│   │   └── StoredProcedures.sql
│   └── Backup/
├── Documentation/
│   ├── UserManual.pdf
│   ├── AdminGuide.pdf
│   └── TechnicalDocumentation.pdf
├── Tests/
│   ├── UnitTests/
│   ├── IntegrationTests/
│   └── TestData/
└── Setup/
    ├── LibraryManagementSystemSetup.msi
    └── DatabaseSetup.exe

Core Implementation

Database Models

// Models/Book.cs
using System;
using System.ComponentModel.DataAnnotations;

namespace LibraryManagementSystem.Models
{
    public class Book
    {
        [Key]
        public int BookId { get; set; }
        
        [Required]
        [StringLength(200)]
        public string Title { get; set; }
        
        [Required]
        [StringLength(100)]
        public string ISBN { get; set; }
        
        [Required]
        public int AuthorId { get; set; }
        
        [Required]
        public int CategoryId { get; set; }
        
        [Required]
        [StringLength(50)]
        public string Publisher { get; set; }
        
        [Required]
        public DateTime PublicationDate { get; set; }
        
        [Required]
        public int TotalCopies { get; set; }
        
        [Required]
        public int AvailableCopies { get; set; }
        
        [StringLength(500)]
        public string Description { get; set; }
        
        [StringLength(50)]
        public string Location { get; set; }
        
        [Required]
        public decimal Price { get; set; }
        
        [Required]
        public bool IsActive { get; set; }
        
        public DateTime CreatedDate { get; set; }
        public DateTime? ModifiedDate { get; set; }
        
        // Navigation properties
        public virtual Author Author { get; set; }
        public virtual Category Category { get; set; }
        
        // Computed properties
        public bool IsAvailable => AvailableCopies > 0;
        public int BorrowedCopies => TotalCopies - AvailableCopies;
    }
}

// Models/Member.cs
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace LibraryManagementSystem.Models
{
    public class Member
    {
        [Key]
        public int MemberId { get; set; }
        
        [Required]
        [StringLength(50)]
        public string FirstName { get; set; }
        
        [Required]
        [StringLength(50)]
        public string LastName { get; set; }
        
        [Required]
        [StringLength(100)]
        [EmailAddress]
        public string Email { get; set; }
        
        [Required]
        [StringLength(20)]
        public string PhoneNumber { get; set; }
        
        [Required]
        [StringLength(200)]
        public string Address { get; set; }
        
        [Required]
        public DateTime DateOfBirth { get; set; }
        
        [Required]
        [StringLength(20)]
        public string MemberType { get; set; } // Student, Faculty, Staff, Public
        
        [Required]
        public DateTime JoinDate { get; set; }
        
        public DateTime? ExpiryDate { get; set; }
        
        [Required]
        public bool IsActive { get; set; }
        
        [StringLength(500)]
        public string Notes { get; set; }
        
        public DateTime CreatedDate { get; set; }
        public DateTime? ModifiedDate { get; set; }
        
        // Computed properties
        [NotMapped]
        public string FullName => $"{FirstName} {LastName}";
        
        [NotMapped]
        public int Age => DateTime.Now.Year - DateOfBirth.Year;
        
        [NotMapped]
        public bool IsExpired => ExpiryDate.HasValue && ExpiryDate.Value < DateTime.Now;
    }
}

// Models/Borrowing.cs
using System;
using System.ComponentModel.DataAnnotations;

namespace LibraryManagementSystem.Models
{
    public class Borrowing
    {
        [Key]
        public int BorrowingId { get; set; }
        
        [Required]
        public int BookId { get; set; }
        
        [Required]
        public int MemberId { get; set; }
        
        [Required]
        public DateTime BorrowDate { get; set; }
        
        [Required]
        public DateTime DueDate { get; set; }
        
        public DateTime? ReturnDate { get; set; }
        
        [Required]
        [StringLength(20)]
        public string Status { get; set; } // Borrowed, Returned, Overdue, Lost
        
        [StringLength(500)]
        public string Notes { get; set; }
        
        public DateTime CreatedDate { get; set; }
        public DateTime? ModifiedDate { get; set; }
        
        // Navigation properties
        public virtual Book Book { get; set; }
        public virtual Member Member { get; set; }
        
        // Computed properties
        public bool IsOverdue => DateTime.Now > DueDate && Status == "Borrowed";
        public int DaysOverdue => IsOverdue ? (DateTime.Now - DueDate).Days : 0;
        public decimal FineAmount => CalculateFine();
        
        private decimal CalculateFine()
        {
            if (!IsOverdue) return 0;
            
            int daysOverdue = DaysOverdue;
            decimal finePerDay = 0.50m; // $0.50 per day
            decimal maxFine = 50.00m; // Maximum fine of $50
            
            decimal fine = daysOverdue * finePerDay;
            return Math.Min(fine, maxFine);
        }
    }
}

Data Access Layer

// DataAccess/DatabaseConnection.cs
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace LibraryManagementSystem.DataAccess
{
    public class DatabaseConnection : IDisposable
    {
        private SqlConnection _connection;
        private bool _disposed = false;
        
        public DatabaseConnection()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["LibraryDB"].ConnectionString;
            _connection = new SqlConnection(connectionString);
        }
        
        public SqlConnection Connection
        {
            get
            {
                if (_connection.State == ConnectionState.Closed)
                {
                    _connection.Open();
                }
                return _connection;
            }
        }
        
        public bool TestConnection()
        {
            try
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LibraryDB"].ConnectionString))
                {
                    connection.Open();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Database connection test failed: {ex.Message}");
                return false;
            }
        }
        
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        
        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    _connection?.Close();
                    _connection?.Dispose();
                }
                _disposed = true;
            }
        }
    }
}

// DataAccess/BookDataAccess.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using LibraryManagementSystem.Models;

namespace LibraryManagementSystem.DataAccess
{
    public class BookDataAccess
    {
        private readonly DatabaseConnection _dbConnection;
        
        public BookDataAccess()
        {
            _dbConnection = new DatabaseConnection();
        }
        
        public List<Book> GetAllBooks()
        {
            var books = new List<Book>();
            
            try
            {
                using (var command = new SqlCommand("sp_GetAllBooks", _dbConnection.Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            books.Add(MapReaderToBook(reader));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error retrieving all books: {ex.Message}");
                throw;
            }
            
            return books;
        }
        
        public Book GetBookById(int bookId)
        {
            try
            {
                using (var command = new SqlCommand("sp_GetBookById", _dbConnection.Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@BookId", bookId);
                    
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return MapReaderToBook(reader);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error retrieving book by ID: {ex.Message}");
                throw;
            }
            
            return null;
        }
        
        public List<Book> SearchBooks(string searchTerm)
        {
            var books = new List<Book>();
            
            try
            {
                using (var command = new SqlCommand("sp_SearchBooks", _dbConnection.Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@SearchTerm", searchTerm);
                    
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            books.Add(MapReaderToBook(reader));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error searching books: {ex.Message}");
                throw;
            }
            
            return books;
        }
        
        public bool AddBook(Book book)
        {
            try
            {
                using (var command = new SqlCommand("sp_AddBook", _dbConnection.Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@Title", book.Title);
                    command.Parameters.AddWithValue("@ISBN", book.ISBN);
                    command.Parameters.AddWithValue("@AuthorId", book.AuthorId);
                    command.Parameters.AddWithValue("@CategoryId", book.CategoryId);
                    command.Parameters.AddWithValue("@Publisher", book.Publisher);
                    command.Parameters.AddWithValue("@PublicationDate", book.PublicationDate);
                    command.Parameters.AddWithValue("@TotalCopies", book.TotalCopies);
                    command.Parameters.AddWithValue("@AvailableCopies", book.AvailableCopies);
                    command.Parameters.AddWithValue("@Description", book.Description ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@Location", book.Location ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@Price", book.Price);
                    
                    int rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected > 0;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error adding book: {ex.Message}");
                throw;
            }
        }
        
        public bool UpdateBook(Book book)
        {
            try
            {
                using (var command = new SqlCommand("sp_UpdateBook", _dbConnection.Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@BookId", book.BookId);
                    command.Parameters.AddWithValue("@Title", book.Title);
                    command.Parameters.AddWithValue("@ISBN", book.ISBN);
                    command.Parameters.AddWithValue("@AuthorId", book.AuthorId);
                    command.Parameters.AddWithValue("@CategoryId", book.CategoryId);
                    command.Parameters.AddWithValue("@Publisher", book.Publisher);
                    command.Parameters.AddWithValue("@PublicationDate", book.PublicationDate);
                    command.Parameters.AddWithValue("@TotalCopies", book.TotalCopies);
                    command.Parameters.AddWithValue("@AvailableCopies", book.AvailableCopies);
                    command.Parameters.AddWithValue("@Description", book.Description ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@Location", book.Location ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@Price", book.Price);
                    command.Parameters.AddWithValue("@IsActive", book.IsActive);
                    
                    int rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected > 0;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error updating book: {ex.Message}");
                throw;
            }
        }
        
        public bool DeleteBook(int bookId)
        {
            try
            {
                using (var command = new SqlCommand("sp_DeleteBook", _dbConnection.Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@BookId", bookId);
                    
                    int rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected > 0;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error deleting book: {ex.Message}");
                throw;
            }
        }
        
        public bool UpdateAvailableCopies(int bookId, int change)
        {
            try
            {
                using (var command = new SqlCommand("sp_UpdateAvailableCopies", _dbConnection.Connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@BookId", bookId);
                    command.Parameters.AddWithValue("@Change", change);
                    
                    int rowsAffected = command.ExecuteNonQuery();
                    return rowsAffected > 0;
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error updating available copies: {ex.Message}");
                throw;
            }
        }
        
        private Book MapReaderToBook(SqlDataReader reader)
        {
            return new Book
            {
                BookId = reader.GetInt32("BookId"),
                Title = reader.GetString("Title"),
                ISBN = reader.GetString("ISBN"),
                AuthorId = reader.GetInt32("AuthorId"),
                CategoryId = reader.GetInt32("CategoryId"),
                Publisher = reader.GetString("Publisher"),
                PublicationDate = reader.GetDateTime("PublicationDate"),
                TotalCopies = reader.GetInt32("TotalCopies"),
                AvailableCopies = reader.GetInt32("AvailableCopies"),
                Description = reader.IsDBNull("Description") ? null : reader.GetString("Description"),
                Location = reader.IsDBNull("Location") ? null : reader.GetString("Location"),
                Price = reader.GetDecimal("Price"),
                IsActive = reader.GetBoolean("IsActive"),
                CreatedDate = reader.GetDateTime("CreatedDate"),
                ModifiedDate = reader.IsDBNull("ModifiedDate") ? (DateTime?)null : reader.GetDateTime("ModifiedDate")
            };
        }
    }
}

Business Logic Layer

// BusinessLogic/BookManager.cs
using System;
using System.Collections.Generic;
using System.Linq;
using LibraryManagementSystem.Models;
using LibraryManagementSystem.DataAccess;

namespace LibraryManagementSystem.BusinessLogic
{
    public class BookManager
    {
        private readonly BookDataAccess _bookDataAccess;
        private readonly AuthorDataAccess _authorDataAccess;
        private readonly CategoryDataAccess _categoryDataAccess;
        
        public BookManager()
        {
            _bookDataAccess = new BookDataAccess();
            _authorDataAccess = new AuthorDataAccess();
            _categoryDataAccess = new CategoryDataAccess();
        }
        
        public List<Book> GetAllBooks()
        {
            try
            {
                return _bookDataAccess.GetAllBooks();
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.GetAllBooks: {ex.Message}");
                throw;
            }
        }
        
        public Book GetBookById(int bookId)
        {
            if (bookId <= 0)
            {
                throw new ArgumentException("Invalid book ID", nameof(bookId));
            }
            
            try
            {
                return _bookDataAccess.GetBookById(bookId);
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.GetBookById: {ex.Message}");
                throw;
            }
        }
        
        public List<Book> SearchBooks(string searchTerm)
        {
            if (string.IsNullOrWhiteSpace(searchTerm))
            {
                return GetAllBooks();
            }
            
            try
            {
                return _bookDataAccess.SearchBooks(searchTerm);
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.SearchBooks: {ex.Message}");
                throw;
            }
        }
        
        public bool AddBook(Book book)
        {
            if (!ValidateBook(book))
            {
                return false;
            }
        
            try
            {
                // Check if ISBN already exists
                if (IsISBNExists(book.ISBN))
                {
                    throw new InvalidOperationException("A book with this ISBN already exists.");
                }
                
                // Set default values
                book.CreatedDate = DateTime.Now;
                book.IsActive = true;
                
                bool result = _bookDataAccess.AddBook(book);
                
                if (result)
                {
                    Logger.LogInfo($"Book added successfully: {book.Title}");
                }
                
                return result;
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.AddBook: {ex.Message}");
                throw;
            }
        }
        
        public bool UpdateBook(Book book)
        {
            if (!ValidateBook(book))
            {
                return false;
            }
            
            try
            {
                // Check if ISBN exists for another book
                var existingBook = GetBookById(book.BookId);
                if (existingBook != null && existingBook.ISBN != book.ISBN && IsISBNExists(book.ISBN))
                {
                    throw new InvalidOperationException("A book with this ISBN already exists.");
                }
                
                book.ModifiedDate = DateTime.Now;
                
                bool result = _bookDataAccess.UpdateBook(book);
                
                if (result)
                {
                    Logger.LogInfo($"Book updated successfully: {book.Title}");
                }
                
                return result;
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.UpdateBook: {ex.Message}");
                throw;
            }
        }
        
        public bool DeleteBook(int bookId)
        {
            if (bookId <= 0)
            {
                throw new ArgumentException("Invalid book ID", nameof(bookId));
            }
            
            try
            {
                // Check if book has active borrowings
                var borrowingManager = new BorrowingManager();
                var activeBorrowings = borrowingManager.GetActiveBorrowingsByBook(bookId);
                
                if (activeBorrowings.Any())
                {
                    throw new InvalidOperationException("Cannot delete book with active borrowings.");
                }
                
                bool result = _bookDataAccess.DeleteBook(bookId);
                
                if (result)
                {
                    Logger.LogInfo($"Book deleted successfully: ID {bookId}");
                }
                
                return result;
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.DeleteBook: {ex.Message}");
                throw;
            }
        }
        
        public bool BorrowBook(int bookId, int memberId)
        {
            try
            {
                var book = GetBookById(bookId);
                if (book == null)
                {
                    throw new ArgumentException("Book not found", nameof(bookId));
                }
                
                if (!book.IsAvailable)
                {
                    throw new InvalidOperationException("Book is not available for borrowing.");
                }
                
                var memberManager = new MemberManager();
                var member = memberManager.GetMemberById(memberId);
                if (member == null)
                {
                    throw new ArgumentException("Member not found", nameof(memberId));
                }
                
                if (!member.IsActive)
                {
                    throw new InvalidOperationException("Member account is not active.");
                }
                
                if (member.IsExpired)
                {
                    throw new InvalidOperationException("Member account has expired.");
                }
                
                // Check if member has reached borrowing limit
                var borrowingManager = new BorrowingManager();
                var activeBorrowings = borrowingManager.GetActiveBorrowingsByMember(memberId);
                
                int maxBorrowings = GetMaxBorrowingsForMemberType(member.MemberType);
                if (activeBorrowings.Count >= maxBorrowings)
                {
                    throw new InvalidOperationException($"Member has reached maximum borrowing limit ({maxBorrowings}).");
                }
                
                // Create borrowing record
                var borrowing = new Borrowing
                {
                    BookId = bookId,
                    MemberId = memberId,
                    BorrowDate = DateTime.Now,
                    DueDate = DateTime.Now.AddDays(GetBorrowingPeriodForMemberType(member.MemberType)),
                    Status = "Borrowed",
                    CreatedDate = DateTime.Now
                };
                
                bool borrowingResult = borrowingManager.AddBorrowing(borrowing);
                
                if (borrowingResult)
                {
                    // Update available copies
                    bool updateResult = _bookDataAccess.UpdateAvailableCopies(bookId, -1);
                    
                    if (updateResult)
                    {
                        Logger.LogInfo($"Book borrowed successfully: {book.Title} by {member.FullName}");
                        return true;
                    }
                    else
                    {
                        // Rollback borrowing if copy update fails
                        borrowingManager.DeleteBorrowing(borrowing.BorrowingId);
                        throw new InvalidOperationException("Failed to update book availability.");
                    }
                }
                
                return false;
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.BorrowBook: {ex.Message}");
                throw;
            }
        }
        
        public bool ReturnBook(int borrowingId)
        {
            try
            {
                var borrowingManager = new BorrowingManager();
                var borrowing = borrowingManager.GetBorrowingById(borrowingId);
                
                if (borrowing == null)
                {
                    throw new ArgumentException("Borrowing record not found", nameof(borrowingId));
                }
                
                if (borrowing.Status != "Borrowed")
                {
                    throw new InvalidOperationException("Book is not currently borrowed.");
                }
                
                // Update borrowing record
                borrowing.ReturnDate = DateTime.Now;
                borrowing.Status = "Returned";
                borrowing.ModifiedDate = DateTime.Now;
                
                bool borrowingResult = borrowingManager.UpdateBorrowing(borrowing);
                
                if (borrowingResult)
                {
                    // Update available copies
                    bool updateResult = _bookDataAccess.UpdateAvailableCopies(borrowing.BookId, 1);
                    
                    if (updateResult)
                    {
                        Logger.LogInfo($"Book returned successfully: Borrowing ID {borrowingId}");
                        return true;
                    }
                    else
                    {
                        // Rollback borrowing if copy update fails
                        borrowing.ReturnDate = null;
                        borrowing.Status = "Borrowed";
                        borrowingManager.UpdateBorrowing(borrowing);
                        throw new InvalidOperationException("Failed to update book availability.");
                    }
                }
                
                return false;
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error in BookManager.ReturnBook: {ex.Message}");
                throw;
            }
        }
        
        private bool ValidateBook(Book book)
        {
            if (book == null)
            {
                Logger.LogError("Book object is null");
                return false;
            }
            
            if (string.IsNullOrWhiteSpace(book.Title))
            {
                Logger.LogError("Book title is required");
                return false;
            }
            
            if (string.IsNullOrWhiteSpace(book.ISBN))
            {
                Logger.LogError("Book ISBN is required");
                return false;
            }
            
            if (!ValidationHelper.IsValidISBN(book.ISBN))
            {
                Logger.LogError("Invalid ISBN format");
                return false;
            }
            
            if (book.AuthorId <= 0)
            {
                Logger.LogError("Valid author ID is required");
                return false;
            }
            
            if (book.CategoryId <= 0)
            {
                Logger.LogError("Valid category ID is required");
                return false;
            }
            
            if (book.TotalCopies <= 0)
            {
                Logger.LogError("Total copies must be greater than 0");
                return false;
            }
            
            if (book.AvailableCopies < 0 || book.AvailableCopies > book.TotalCopies)
            {
                Logger.LogError("Invalid available copies count");
                return false;
            }
            
            if (book.Price < 0)
            {
                Logger.LogError("Price cannot be negative");
                return false;
            }
            
            return true;
        }
        
        private bool IsISBNExists(string isbn)
        {
            var books = GetAllBooks();
            return books.Any(b => b.ISBN.Equals(isbn, StringComparison.OrdinalIgnoreCase));
        }
        
        private int GetMaxBorrowingsForMemberType(string memberType)
        {
            return memberType.ToLower() switch
            {
                "student" => 5,
                "faculty" => 10,
                "staff" => 7,
                "public" => 3,
                _ => 3
            };
        }
        
        private int GetBorrowingPeriodForMemberType(string memberType)
        {
            return memberType.ToLower() switch
            {
                "student" => 14, // 2 weeks
                "faculty" => 30, // 1 month
                "staff" => 21,   // 3 weeks
                "public" => 14,   // 2 weeks
                _ => 14
            };
        }
    }
}

Windows Forms Interface

// Forms/MainForm.cs
using System;
using System.Windows.Forms;
using LibraryManagementSystem.BusinessLogic;

namespace LibraryManagementSystem.Forms
{
    public partial class MainForm : Form
    {
        private BookManager _bookManager;
        private MemberManager _memberManager;
        private BorrowingManager _borrowingManager;
        
        public MainForm()
        {
            InitializeComponent();
            InitializeManagers();
            LoadDashboardData();
        }
        
        private void InitializeManagers()
        {
            _bookManager = new BookManager();
            _memberManager = new MemberManager();
            _borrowingManager = new BorrowingManager();
        }
        
        private void LoadDashboardData()
        {
            try
            {
                // Load statistics
                var totalBooks = _bookManager.GetAllBooks().Count;
                var totalMembers = _memberManager.GetAllMembers().Count;
                var activeBorrowings = _borrowingManager.GetActiveBorrowings().Count;
                var overdueBooks = _borrowingManager.GetOverdueBorrowings().Count;
                
                lblTotalBooks.Text = totalBooks.ToString();
                lblTotalMembers.Text = totalMembers.ToString();
                lblActiveBorrowings.Text = activeBorrowings.ToString();
                lblOverdueBooks.Text = overdueBooks.ToString();
                
                // Load recent activities
                LoadRecentActivities();
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error loading dashboard data: {ex.Message}", "Error", 
                               MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        
        private void LoadRecentActivities()
        {
            try
            {
                var recentBorrowings = _borrowingManager.GetRecentBorrowings(10);
                
                listViewRecentActivities.Items.Clear();
                
                foreach (var borrowing in recentBorrowings)
                {
                    var item = new ListViewItem(borrowing.Book.Title);
                    item.SubItems.Add(borrowing.Member.FullName);
                    item.SubItems.Add(borrowing.BorrowDate.ToString("MM/dd/yyyy"));
                    item.SubItems.Add(borrowing.Status);
                    
                    listViewRecentActivities.Items.Add(item);
                }
            }
            catch (Exception ex)
            {
                Logger.LogError($"Error loading recent activities: {ex.Message}");
            }
        }
        
        private void btnBookManagement_Click(object sender, EventArgs e)
        {
            var bookSearchForm = new BookSearchForm();
            bookSearchForm.ShowDialog();
            LoadDashboardData(); // Refresh data
        }
        
        private void btnMemberManagement_Click(object sender, EventArgs e)
        {
            var memberSearchForm = new MemberSearchForm();
            memberSearchForm.ShowDialog();
            LoadDashboardData(); // Refresh data
        }
        
        private void btnBorrowBook_Click(object sender, EventArgs e)
        {
            var borrowBookForm = new BorrowBookForm();
            borrowBookForm.ShowDialog();
            LoadDashboardData(); // Refresh data
        }
        
        private void btnReturnBook_Click(object sender, EventArgs e)
        {
            var returnBookForm = new ReturnBookForm();
            returnBookForm.ShowDialog();
            LoadDashboardData(); // Refresh data
        }
        
        private void btnReports_Click(object sender, EventArgs e)
        {
            var reportsForm = new ReportsForm();
            reportsForm.ShowDialog();
        }
        
        private void btnSettings_Click(object sender, EventArgs e)
        {
            var settingsForm = new SystemSettingsForm();
            settingsForm.ShowDialog();
        }
        
        private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            var result = MessageBox.Show("Are you sure you want to exit?", "Confirm Exit", 
                                        MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            
            if (result == DialogResult.No)
            {
                e.Cancel = true;
            }
        }
    }
}

Database Schema

-- Database/Scripts/CreateTables.sql

-- Authors table
CREATE TABLE Authors (
    AuthorId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Biography NVARCHAR(MAX),
    DateOfBirth DATE,
    DateOfDeath DATE,
    Nationality NVARCHAR(50),
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    ModifiedDate DATETIME2
);

-- Categories table
CREATE TABLE Categories (
    CategoryId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL UNIQUE,
    Description NVARCHAR(500),
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    ModifiedDate DATETIME2
);

-- Books table
CREATE TABLE Books (
    BookId INT IDENTITY(1,1) PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    ISBN NVARCHAR(20) NOT NULL UNIQUE,
    AuthorId INT NOT NULL,
    CategoryId INT NOT NULL,
    Publisher NVARCHAR(100) NOT NULL,
    PublicationDate DATE NOT NULL,
    TotalCopies INT NOT NULL DEFAULT 1 CHECK (TotalCopies > 0),
    AvailableCopies INT NOT NULL DEFAULT 1 CHECK (AvailableCopies >= 0),
    Description NVARCHAR(MAX),
    Location NVARCHAR(50),
    Price DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (Price >= 0),
    IsActive BIT NOT NULL DEFAULT 1,
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    ModifiedDate DATETIME2,
    FOREIGN KEY (AuthorId) REFERENCES Authors(AuthorId),
    FOREIGN KEY (CategoryId) REFERENCES Categories(CategoryId),
    CHECK (AvailableCopies <= TotalCopies)
);

-- Members table
CREATE TABLE Members (
    MemberId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE,
    PhoneNumber NVARCHAR(20) NOT NULL,
    Address NVARCHAR(200) NOT NULL,
    DateOfBirth DATE NOT NULL,
    MemberType NVARCHAR(20) NOT NULL CHECK (MemberType IN ('Student', 'Faculty', 'Staff', 'Public')),
    JoinDate DATE NOT NULL DEFAULT GETDATE(),
    ExpiryDate DATE,
    IsActive BIT NOT NULL DEFAULT 1,
    Notes NVARCHAR(MAX),
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    ModifiedDate DATETIME2
);

-- Borrowings table
CREATE TABLE Borrowings (
    BorrowingId INT IDENTITY(1,1) PRIMARY KEY,
    BookId INT NOT NULL,
    MemberId INT NOT NULL,
    BorrowDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    DueDate DATETIME2 NOT NULL,
    ReturnDate DATETIME2,
    Status NVARCHAR(20) NOT NULL DEFAULT 'Borrowed' CHECK (Status IN ('Borrowed', 'Returned', 'Overdue', 'Lost')),
    Notes NVARCHAR(MAX),
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    ModifiedDate DATETIME2,
    FOREIGN KEY (BookId) REFERENCES Books(BookId),
    FOREIGN KEY (MemberId) REFERENCES Members(MemberId)
);

-- Users table (for system administration)
CREATE TABLE Users (
    UserId INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(50) NOT NULL UNIQUE,
    PasswordHash NVARCHAR(255) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE,
    Role NVARCHAR(20) NOT NULL DEFAULT 'Librarian' CHECK (Role IN ('Admin', 'Librarian')),
    IsActive BIT NOT NULL DEFAULT 1,
    LastLogin DATETIME2,
    CreatedDate DATETIME2 DEFAULT GETDATE(),
    ModifiedDate DATETIME2
);

-- Create indexes for better performance
CREATE INDEX IX_Books_Title ON Books(Title);
CREATE INDEX IX_Books_ISBN ON Books(ISBN);
CREATE INDEX IX_Books_AuthorId ON Books(AuthorId);
CREATE INDEX IX_Books_CategoryId ON Books(CategoryId);
CREATE INDEX IX_Books_IsActive ON Books(IsActive);

CREATE INDEX IX_Members_Email ON Members(Email);
CREATE INDEX IX_Members_MemberType ON Members(MemberType);
CREATE INDEX IX_Members_IsActive ON Members(IsActive);

CREATE INDEX IX_Borrowings_BookId ON Borrowings(BookId);
CREATE INDEX IX_Borrowings_MemberId ON Borrowings(MemberId);
CREATE INDEX IX_Borrowings_Status ON Borrowings(Status);
CREATE INDEX IX_Borrowings_DueDate ON Borrowings(DueDate);

-- Create triggers for updated timestamps
CREATE TRIGGER TR_Books_UpdateModifiedDate
ON Books
AFTER UPDATE
AS
BEGIN
    UPDATE Books
    SET ModifiedDate = GETDATE()
    FROM Books b
    INNER JOIN inserted i ON b.BookId = i.BookId;
END;

CREATE TRIGGER TR_Members_UpdateModifiedDate
ON Members
AFTER UPDATE
AS
BEGIN
    UPDATE Members
    SET ModifiedDate = GETDATE()
    FROM Members m
    INNER JOIN inserted i ON m.MemberId = i.MemberId;
END;

CREATE TRIGGER TR_Borrowings_UpdateModifiedDate
ON Borrowings
AFTER UPDATE
AS
BEGIN
    UPDATE Borrowings
    SET ModifiedDate = GETDATE()
    FROM Borrowings b
    INNER JOIN inserted i ON b.BorrowingId = i.BorrowingId;
END;

Lessons Learned

C# Application Development

Windows Forms: Comprehensive GUI development with Windows Forms
Data Binding: Advanced data binding techniques
Event Handling: Proper event handling and user interaction
Form Management: Multi-form application architecture

Database Integration

SQL Server: Advanced SQL Server integration with ADO.NET
Stored Procedures: Comprehensive stored procedure usage
Connection Management: Proper database connection handling
Transaction Management: ACID compliance and transaction safety

Software Architecture

Layered Architecture: Clean separation of concerns
Data Access Layer: Abstracted data access patterns
Business Logic: Comprehensive business rule implementation
Validation: Input validation and error handling

User Experience

Intuitive Interface: User-friendly design principles
Error Handling: Comprehensive error handling and user feedback
Data Validation: Real-time data validation
Reporting: Comprehensive reporting capabilities

Future Enhancements

Advanced Features

Web Interface: Web-based library management system
Mobile App: Mobile application for members
Barcode Integration: Barcode scanning for books and members
Email Notifications: Automated email notifications

Technical Improvements

Entity Framework: Migration to Entity Framework
WPF Interface: Modern WPF interface
Cloud Integration: Cloud-based backup and synchronization
API Development: REST API for third-party integrations

Conclusion

The Library Management System demonstrates comprehensive C# application development and database integration expertise. Key achievements include:

Desktop Application: Complete Windows Forms application
Database Integration: Advanced SQL Server integration
Business Logic: Comprehensive library management features
User Interface: Intuitive and user-friendly design
Testing: Comprehensive testing framework
Documentation: Clear documentation and user guides

The project is available on GitHub and serves as a comprehensive example of C# desktop application development for educational institutions.

This project represents my deep dive into C# application development and demonstrates how modern C# can be used to build robust, efficient desktop applications with comprehensive database integration. The lessons learned here continue to influence my approach to desktop application development and educational software design.