Windows Database - C# Samples

using System;
using System.Data;
using System.Data.SQLite;
using System.IO;

class SQLiteConnection
{
    private static string connectionString = "Data Source=sample.db;Version=3;";

    // 1. Create database and connection
    public static void CreateDatabase()
    {
        Console.WriteLine("=== Creating SQLite Database ===");

        try
        {
            // Delete existing database if it exists
            if (File.Exists("sample.db"))
            {
                File.Delete("sample.db");
                Console.WriteLine("Removed existing database file");
            }

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                Console.WriteLine("Database created and connected successfully!");

                // Create tables
                string createUsersTable = @"
                    CREATE TABLE Users (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        Email TEXT UNIQUE NOT NULL,
                        Age INTEGER,
                        CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                    )";

                string createOrdersTable = @"
                    CREATE TABLE Orders (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        UserId INTEGER,
                        ProductName TEXT NOT NULL,
                        Quantity INTEGER,
                        Price DECIMAL(10,2),
                        OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (UserId) REFERENCES Users(Id)
                    )";

                using (SQLiteCommand command = new SQLiteCommand(createUsersTable, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine("Users table created");
                }

                using (SQLiteCommand command = new SQLiteCommand(createOrdersTable, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine("Orders table created");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error creating database: {ex.Message}");
        }
    }

    // 2. Insert data with parameters
    public static void InsertSampleData()
    {
        Console.WriteLine("\n=== Inserting Sample Data ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Insert users
                string insertUser = "INSERT INTO Users (Name, Email, Age) VALUES (@Name, @Email, @Age)";

                using (SQLiteCommand command = new SQLiteCommand(insertUser, connection))
                {
                    // User 1
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@Name", "John Doe");
                    command.Parameters.AddWithValue("@Email", "[email protected]");
                    command.Parameters.AddWithValue("@Age", 30);
                    command.ExecuteNonQuery();

                    // User 2
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@Name", "Jane Smith");
                    command.Parameters.AddWithValue("@Email", "[email protected]");
                    command.Parameters.AddWithValue("@Age", 25);
                    command.ExecuteNonQuery();

                    // User 3
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@Name", "Bob Johnson");
                    command.Parameters.AddWithValue("@Email", "[email protected]");
                    command.Parameters.AddWithValue("@Age", 35);
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("3 users inserted successfully");

                // Insert orders
                string insertOrder = "INSERT INTO Orders (UserId, ProductName, Quantity, Price) VALUES (@UserId, @ProductName, @Quantity, @Price)";

                using (SQLiteCommand command = new SQLiteCommand(insertOrder, connection))
                {
                    // Order 1
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@UserId", 1);
                    command.Parameters.AddWithValue("@ProductName", "Laptop");
                    command.Parameters.AddWithValue("@Quantity", 1);
                    command.Parameters.AddWithValue("@Price", 999.99m);
                    command.ExecuteNonQuery();

                    // Order 2
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@UserId", 2);
                    command.Parameters.AddWithValue("@ProductName", "Mouse");
                    command.Parameters.AddWithValue("@Quantity", 2);
                    command.Parameters.AddWithValue("@Price", 25.50m);
                    command.ExecuteNonQuery();
                }

                Console.WriteLine("Orders inserted successfully");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error inserting data: {ex.Message}");
        }
    }

    // 3. Read data with different methods
    public static void ReadData()
    {
        Console.WriteLine("\n=== Reading Database Data ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Read all users
                string selectUsers = "SELECT * FROM Users";
                Console.WriteLine("All Users:");

                using (SQLiteCommand command = new SQLiteCommand(selectUsers, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Email: {reader["Email"]}, Age: {reader["Age"]}");
                    }
                }

                // Read with parameters
                string selectOrdersByUser = @"
                    SELECT o.Id, u.Name as UserName, o.ProductName, o.Quantity, o.Price, o.OrderDate
                    FROM Orders o
                    INNER JOIN Users u ON o.UserId = u.Id
                    WHERE u.Age > @Age";

                Console.WriteLine("\nOrders from users older than 28:");

                using (SQLiteCommand command = new SQLiteCommand(selectOrdersByUser, connection))
                {
                    command.Parameters.AddWithValue("@Age", 28);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"Order {reader["Id"]}: {reader["UserName"]} bought {reader["Quantity"]}x {reader["ProductName"]} for ${reader["Price"]}");
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error reading data: {ex.Message}");
        }
    }

    // 4. Update and delete operations
    public static void UpdateAndDelete()
    {
        Console.WriteLine("\n=== Update and Delete Operations ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Update user age
                string updateUser = "UPDATE Users SET Age = @NewAge WHERE Id = @UserId";

                using (SQLiteCommand command = new SQLiteCommand(updateUser, connection))
                {
                    command.Parameters.AddWithValue("@NewAge", 31);
                    command.Parameters.AddWithValue("@UserId", 1);
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"Updated {rowsAffected} user record");
                }

                // Delete orders with low quantity
                string deleteOrders = "DELETE FROM Orders WHERE Quantity < @MinQuantity";

                using (SQLiteCommand command = new SQLiteCommand(deleteOrders, connection))
                {
                    command.Parameters.AddWithValue("@MinQuantity", 2);
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"Deleted {rowsAffected} order records");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in update/delete operations: {ex.Message}");
        }
    }

    public static void RunAllExamples()
    {
        Console.WriteLine("SQLite Database Operations Examples");
        Console.WriteLine("=====================================");

        CreateDatabase();
        InsertSampleData();
        ReadData();
        UpdateAndDelete();

        Console.WriteLine("\nDatabase operations completed!");
    }
}

using System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;

class SQLQueries
{
    private static string connectionString = "Data Source=sample.db;Version=3;";

    // 1. Basic SELECT queries
    public static void BasicSelectQueries()
    {
        Console.WriteLine("=== Basic SELECT Queries ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Simple SELECT
                string simpleSelect = "SELECT Name, Email FROM Users";
                Console.WriteLine("Simple SELECT - User names and emails:");

                using (SQLiteCommand command = new SQLiteCommand(simpleSelect, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"Name: {reader["Name"]}, Email: {reader["Email"]}");
                    }
                }

                // SELECT with WHERE clause
                string whereSelect = "SELECT * FROM Users WHERE Age BETWEEN @MinAge AND @MaxAge";
                Console.WriteLine("\nSELECT with WHERE - Users aged 25-35:");

                using (SQLiteCommand command = new SQLiteCommand(whereSelect, connection))
                {
                    command.Parameters.AddWithValue("@MinAge", 25);
                    command.Parameters.AddWithValue("@MaxAge", 35);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]}");
                        }
                    }
                }

                // SELECT with ORDER BY
                string orderedSelect = "SELECT Name, Age FROM Users ORDER BY Age DESC, Name ASC";
                Console.WriteLine("\nSELECT with ORDER BY - Users by age (descending):");

                using (SQLiteCommand command = new SQLiteCommand(orderedSelect, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"Name: {reader["Name"]}, Age: {reader["Age"]}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in SELECT queries: {ex.Message}");
        }
    }

    // 2. Aggregate functions
    public static void AggregateQueries()
    {
        Console.WriteLine("\n=== Aggregate Function Queries ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // COUNT
                string countQuery = "SELECT COUNT(*) as TotalUsers FROM Users";

                using (SQLiteCommand command = new SQLiteCommand(countQuery, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Console.WriteLine($"Total users: {reader["TotalUsers"]}");
                    }
                }

                // AVG and SUM
                string statsQuery = "SELECT AVG(Price) as AvgPrice, SUM(Quantity) as TotalQuantity FROM Orders";

                using (SQLiteCommand command = new SQLiteCommand(statsQuery, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Console.WriteLine($"Average order price: ${reader["AvgPrice"]:F2}");
                        Console.WriteLine($"Total items ordered: {reader["TotalQuantity"]}");
                    }
                }

                // GROUP BY
                string groupByQuery = @"
                    SELECT u.Name, COUNT(o.Id) as OrderCount, SUM(o.Price * o.Quantity) as TotalSpent
                    FROM Users u
                    LEFT JOIN Orders o ON u.Id = o.UserId
                    GROUP BY u.Id, u.Name
                    HAVING COUNT(o.Id) > 0";

                Console.WriteLine("\nGROUP BY - Order summary by user:");

                using (SQLiteCommand command = new SQLiteCommand(groupByQuery, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"User: {reader["Name"]}, Orders: {reader["OrderCount"]}, Total: ${reader["TotalSpent"]:F2}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in aggregate queries: {ex.Message}");
        }
    }

    // 3. JOIN operations
    public static void JoinQueries()
    {
        Console.WriteLine("\n=== JOIN Operations ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // INNER JOIN
                string innerJoin = @"
                    SELECT u.Name, u.Email, o.ProductName, o.Quantity, o.Price
                    FROM Users u
                    INNER JOIN Orders o ON u.Id = o.UserId";

                Console.WriteLine("INNER JOIN - Users with their orders:");

                using (SQLiteCommand command = new SQLiteCommand(innerJoin, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader["Name"]} bought {reader["Quantity"]}x {reader["ProductName"]} for ${reader["Price"]:F2}");
                    }
                }

                // LEFT JOIN
                string leftJoin = @"
                    SELECT u.Name, COUNT(o.Id) as OrderCount
                    FROM Users u
                    LEFT JOIN Orders o ON u.Id = o.UserId
                    GROUP BY u.Id, u.Name
                    ORDER BY OrderCount DESC";

                Console.WriteLine("\nLEFT JOIN - All users with order counts:");

                using (SQLiteCommand command = new SQLiteCommand(leftJoin, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"User: {reader["Name"]}, Orders: {reader["OrderCount"]}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in JOIN queries: {ex.Message}");
        }
    }

    // 4. Advanced queries with subqueries
    public static void AdvancedQueries()
    {
        Console.WriteLine("\n=== Advanced Queries with Subqueries ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Subquery in WHERE clause
                string subqueryWhere = @"
                    SELECT Name, Age FROM Users
                    WHERE Id IN (SELECT DISTINCT UserId FROM Orders WHERE Price > @MinPrice)";

                Console.WriteLine("Subquery - Users with expensive orders:");

                using (SQLiteCommand command = new SQLiteCommand(subqueryWhere, connection))
                {
                    command.Parameters.AddWithValue("@MinPrice", 100m);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"Name: {reader["Name"]}, Age: {reader["Age"]}");
                        }
                    }
                }

                // Correlated subquery
                string correlatedSubquery = @"
                    SELECT u.Name, u.Email,
                           (SELECT COUNT(*) FROM Orders o WHERE o.UserId = u.Id) as OrderCount
                    FROM Users u
                    ORDER BY OrderCount DESC";

                Console.WriteLine("\nCorrelated subquery - User order counts:");

                using (SQLiteCommand command = new SQLiteCommand(correlatedSubquery, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"User: {reader["Name"]}, Orders: {reader["OrderCount"]}");
                    }
                }

                // EXISTS clause
                string existsQuery = @"
                    SELECT u.Name, u.Email FROM Users u
                    WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.UserId = u.Id)";

                Console.WriteLine("\nEXISTS - Users who have placed orders:");

                using (SQLiteCommand command = new SQLiteCommand(existsQuery, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"User: {reader["Name"]}, Email: {reader["Email"]}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in advanced queries: {ex.Message}");
        }
    }

    // 5. Data modification with conditions
    public static void ConditionalModifications()
    {
        Console.WriteLine("\n=== Conditional Data Modifications ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Conditional UPDATE with JOIN
                string conditionalUpdate = @"
                    UPDATE Users
                    SET Age = Age + 1
                    WHERE Id IN (SELECT DISTINCT UserId FROM Orders WHERE TotalPrice > @Amount)";

                // First, let's add a TotalPrice column for demonstration
                string addColumn = "ALTER TABLE Orders ADD COLUMN TotalPrice DECIMAL(10,2)";
                try
                {
                    using (SQLiteCommand command = new SQLiteCommand(addColumn, connection))
                    {
                        command.ExecuteNonQuery();
                    }

                    // Update TotalPrice
                    string updateTotalPrice = "UPDATE Orders SET TotalPrice = Price * Quantity";
                    using (SQLiteCommand command = new SQLiteCommand(updateTotalPrice, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                catch { /* Column might already exist */ }

                using (SQLiteCommand command = new SQLiteCommand(conditionalUpdate, connection))
                {
                    command.Parameters.AddWithValue("@Amount", 500m);
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"Updated age for {rowsAffected} users with expensive orders");
                }

                // Conditional DELETE with subquery
                string conditionalDelete = @"
                    DELETE FROM Orders
                    WHERE UserId IN (SELECT Id FROM Users WHERE Age < @MinAge)";

                using (SQLiteCommand command = new SQLiteCommand(conditionalDelete, connection))
                {
                    command.Parameters.AddWithValue("@MinAge", 26);
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"Deleted {rowsAffected} orders from young users");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in conditional modifications: {ex.Message}");
        }
    }

    public static void RunAllExamples()
    {
        Console.WriteLine("SQL Query Operations Examples");
        Console.WriteLine("==============================");

        BasicSelectQueries();
        AggregateQueries();
        JoinQueries();
        AdvancedQueries();
        ConditionalModifications();

        Console.WriteLine("\nSQL query operations completed!");
    }
}

using System;
using System.Data;
using System.Data.SQLite;

class TransactionProcessing
{
    private static string connectionString = "Data Source=sample.db;Version=3;";

    // 1. Basic transaction example
    public static void BasicTransaction()
    {
        Console.WriteLine("=== Basic Transaction Example ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Start transaction
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        Console.WriteLine("Transaction started...");

                        // Insert new user
                        string insertUser = "INSERT INTO Users (Name, Email, Age) VALUES (@Name, @Email, @Age)";
                        using (SQLiteCommand command = new SQLiteCommand(insertUser, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Name", "Alice Wilson");
                            command.Parameters.AddWithValue("@Email", "[email protected]");
                            command.Parameters.AddWithValue("@Age", 28);
                            command.ExecuteNonQuery();
                            Console.WriteLine("New user inserted");
                        }

                        // Get the new user's ID
                        long newUserId = connection.LastInsertRowId;

                        // Insert multiple orders for the new user
                        string insertOrder = "INSERT INTO Orders (UserId, ProductName, Quantity, Price) VALUES (@UserId, @ProductName, @Quantity, @Price)";

                        using (SQLiteCommand command = new SQLiteCommand(insertOrder, connection, transaction))
                        {
                            // Order 1
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@UserId", newUserId);
                            command.Parameters.AddWithValue("@ProductName", "Keyboard");
                            command.Parameters.AddWithValue("@Quantity", 1);
                            command.Parameters.AddWithValue("@Price", 75.00m);
                            command.ExecuteNonQuery();

                            // Order 2
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@UserId", newUserId);
                            command.Parameters.AddWithValue("@ProductName", "Monitor");
                            command.Parameters.AddWithValue("@Quantity", 1);
                            command.Parameters.AddWithValue("@Price", 299.99m);
                            command.ExecuteNonQuery();

                            Console.WriteLine("2 orders inserted for new user");
                        }

                        // Commit transaction
                        transaction.Commit();
                        Console.WriteLine("Transaction committed successfully!");
                    }
                    catch (Exception ex)
                    {
                        // Rollback on error
                        transaction.Rollback();
                        Console.WriteLine($"Transaction rolled back due to error: {ex.Message}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in basic transaction: {ex.Message}");
        }
    }

    // 2. Transaction with explicit rollback
    public static void TransactionWithRollback()
    {
        Console.WriteLine("\n=== Transaction with Explicit Rollback ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        Console.WriteLine("Starting transaction that will be rolled back...");

                        // Update user age
                        string updateUser = "UPDATE Users SET Age = Age + 10 WHERE Id = @UserId";
                        using (SQLiteCommand command = new SQLiteCommand(updateUser, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@UserId", 1);
                            int rowsUpdated = command.ExecuteNonQuery();
                            Console.WriteLine($"Updated {rowsUpdated} user(s)");
                        }

                        // Insert a problematic order (this will cause an error)
                        string badInsert = "INSERT INTO Orders (UserId, ProductName, Quantity, Price) VALUES (@UserId, @ProductName, @Quantity, @Price)";
                        using (SQLiteCommand command = new SQLiteCommand(badInsert, connection, transaction))
                        {
                            // Use invalid UserId to trigger rollback
                            command.Parameters.AddWithValue("@UserId", 99999);
                            command.Parameters.AddWithValue("@ProductName", "Invalid Product");
                            command.Parameters.AddWithValue("@Quantity", 1);
                            command.Parameters.AddWithValue("@Price", 100.00m);
                            command.ExecuteNonQuery(); // This might fail
                        }

                        transaction.Commit();
                        Console.WriteLine("Transaction completed successfully");
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"Transaction intentionally rolled back: {ex.Message}");
                        Console.WriteLine("All changes have been reverted");
                    }
                }

                // Verify rollback by checking user data
                Console.WriteLine("\nVerifying rollback - checking user ages:");
                string checkUsers = "SELECT Id, Name, Age FROM Users WHERE Id <= 3";

                using (SQLiteCommand command = new SQLiteCommand(checkUsers, connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"User {reader["Id"]}: {reader["Name"]}, Age: {reader["Age"]}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in rollback transaction: {ex.Message}");
        }
    }

    // 3. Savepoint transactions
    public static void SavepointTransaction()
    {
        Console.WriteLine("\n=== Savepoint Transaction Example ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        Console.WriteLine("Starting transaction with savepoints...");

                        // Initial insert
                        string insertUser = "INSERT INTO Users (Name, Email, Age) VALUES (@Name, @Email, @Age)";
                        using (SQLiteCommand command = new SQLiteCommand(insertUser, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Name", "Charlie Brown");
                            command.Parameters.AddWithValue("@Email", "[email protected]");
                            command.Parameters.AddWithValue("@Age", 32);
                            command.ExecuteNonQuery();
                            Console.WriteLine("User 'Charlie Brown' inserted");
                        }

                        // Create savepoint
                        transaction.Save("Savepoint1");
                        Console.WriteLine("Savepoint 'Savepoint1' created");

                        // Another insert
                        using (SQLiteCommand command = new SQLiteCommand(insertUser, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Name", "Diana Prince");
                            command.Parameters.AddWithValue("@Email", "[email protected]");
                            command.Parameters.AddWithValue("@Age", 27);
                            command.ExecuteNonQuery();
                            Console.WriteLine("User 'Diana Prince' inserted");
                        }

                        // Create another savepoint
                        transaction.Save("Savepoint2");
                        Console.WriteLine("Savepoint 'Savepoint2' created");

                        // Problematic operation that we'll roll back to Savepoint1
                        try
                        {
                            string problematicUpdate = "UPDATE Users SET Age = @NewAge WHERE Name = @Name";
                            using (SQLiteCommand command = new SQLiteCommand(problematicUpdate, connection, transaction))
                            {
                                command.Parameters.AddWithValue("@NewAge", -5); // Invalid age
                                command.Parameters.AddWithValue("@Name", "Charlie Brown");
                                command.ExecuteNonQuery();
                            }
                            Console.WriteLine("This shouldn't print");
                        }
                        catch
                        {
                            Console.WriteLine("Detected problem with update, rolling back to Savepoint1");
                            transaction.Rollback("Savepoint1");
                        }

                        // Continue with valid operations
                        using (SQLiteCommand command = new SQLiteCommand(insertUser, connection, transaction))
                        {
                            command.Parameters.AddWithValue("@Name", "Eve Adams");
                            command.Parameters.AddWithValue("@Email", "[email protected]");
                            command.Parameters.AddWithValue("@Age", 29);
                            command.ExecuteNonQuery();
                            Console.WriteLine("User 'Eve Adams' inserted after rollback");
                        }

                        transaction.Commit();
                        Console.WriteLine("Transaction with savepoints completed successfully!");
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        Console.WriteLine($"Transaction rolled back completely: {ex.Message}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in savepoint transaction: {ex.Message}");
        }
    }

    // 4. Nested transaction simulation
    public static void NestedTransactionSimulation()
    {
        Console.WriteLine("\n=== Nested Transaction Simulation ===");

        try
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Outer transaction
                using (SQLiteTransaction outerTransaction = connection.BeginTransaction())
                {
                    try
                    {
                        Console.WriteLine("Outer transaction started...");

                        // Simulate nested transaction 1 - Update user
                        ExecuteNestedTransaction(connection, outerTransaction,
                            "UPDATE Users SET Age = Age + 1 WHERE Id <= 2",
                            "Nested operation 1: Updated ages for users 1-2");

                        // Simulate nested transaction 2 - Insert order
                        ExecuteNestedTransaction(connection, outerTransaction,
                            "INSERT INTO Orders (UserId, ProductName, Quantity, Price) VALUES (1, 'Headphones', 1, 49.99)",
                            "Nested operation 2: Added headphones order for user 1");

                        // Simulate nested transaction 3 - This one might fail
                        try
                        {
                            ExecuteNestedTransaction(connection, outerTransaction,
                                "INSERT INTO Orders (UserId, ProductName, Quantity, Price) VALUES (999, 'Ghost Product', 1, 0.01)",
                                "Nested operation 3: This should fail");
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"Nested operation 3 failed as expected: {ex.Message}");
                        }

                        // Continue with more valid operations
                        ExecuteNestedTransaction(connection, outerTransaction,
                            "UPDATE Users SET Email = Email || '.verified' WHERE Id = 1",
                            "Nested operation 4: Verified email for user 1");

                        outerTransaction.Commit();
                        Console.WriteLine("Outer transaction committed with all nested operations!");
                    }
                    catch (Exception ex)
                    {
                        outerTransaction.Rollback();
                        Console.WriteLine($"Outer transaction rolled back: {ex.Message}");
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in nested transaction simulation: {ex.Message}");
        }
    }

    private static void ExecuteNestedTransaction(SQLiteConnection connection, SQLiteTransaction parentTransaction, string sql, string description)
    {
        Console.WriteLine($"\nExecuting: {description}");

        using (SQLiteCommand command = new SQLiteCommand(sql, connection, parentTransaction))
        {
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine($"Completed: {rowsAffected} row(s) affected");
        }
    }

    // 5. Transaction isolation and concurrency
    public static void TransactionIsolationDemo()
    {
        Console.WriteLine("\n=== Transaction Isolation Demo ===");

        try
        {
            using (SQLiteConnection connection1 = new SQLiteConnection(connectionString))
            using (SQLiteConnection connection2 = new SQLiteConnection(connectionString))
            {
                connection1.Open();
                connection2.Open();

                // Transaction 1 - Read committed data
                using (SQLiteTransaction transaction1 = connection1.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    Console.WriteLine("Transaction 1 started (Read Committed)");

                    // Read initial data
                    string readQuery = "SELECT COUNT(*) as UserCount FROM Users";
                    using (SQLiteCommand command = new SQLiteCommand(readQuery, connection1, transaction1))
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"Transaction 1 sees {reader["UserCount"]} users");
                        }
                    }

                    // Simulate some work
                    System.Threading.Thread.Sleep(100);

                    // Transaction 2 - Make changes
                    using (SQLiteTransaction transaction2 = connection2.BeginTransaction())
                    {
                        Console.WriteLine("Transaction 2 started - making changes");

                        string updateUser = "UPDATE Users SET Age = Age + 1 WHERE Id = 1";
                        using (SQLiteCommand command = new SQLiteCommand(updateUser, connection2, transaction2))
                        {
                            command.ExecuteNonQuery();
                        }

                        transaction2.Commit();
                        Console.WriteLine("Transaction 2 committed changes");
                    }

                    // Read again in Transaction 1
                    using (SQLiteCommand command = new SQLiteCommand(readQuery, connection1, transaction1))
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"Transaction 1 still sees {reader["UserCount"]} users (isolation working)");
                        }
                    }

                    transaction1.Commit();
                    Console.WriteLine("Transaction 1 completed");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error in isolation demo: {ex.Message}");
        }
    }

    public static void RunAllExamples()
    {
        Console.WriteLine("Transaction Processing Examples");
        Console.WriteLine("===============================");

        BasicTransaction();
        TransactionWithRollback();
        SavepointTransaction();
        NestedTransactionSimulation();
        TransactionIsolationDemo();

        Console.WriteLine("\nTransaction processing examples completed!");
    }
}