Elysia Tools

在一个清晰的工作台里浏览工具、案例、可视化和主题指南。

热门分类

专题

语言
ZHDERUPTENFRES

Elysia Tools

导航

C#

Windows 数据库 - C# 示例

Windows平台C#数据库示例,包括SQLite连接、SQL查询、事务和数据库操作

案例

这个案例集合中的条目

代码csharp简单

SQLite数据库连接

使用C#连接SQLite数据库并执行基本数据库操作

难度

3/10

预计时间

15 min

标签

csharp, sqlite, database, windows

前置条件

C# basics, SQL fundamentals, ADO.NET

csharp
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", "john@example.com");
                    command.Parameters.AddWithValue("@Age", 30);
                    command.ExecuteNonQuery();

                    // User 2
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@Name", "Jane Smith");
                    command.Parameters.AddWithValue("@Email", "jane@example.com");
                    command.Parameters.AddWithValue("@Age", 25);
                    command.ExecuteNonQuery();

                    // User 3
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@Name", "Bob Johnson");
                    command.Parameters.AddWithValue("@Email", "bob@example.com");
                    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!");
    }
}
代码csharp中等

SQL查询操作

执行各种类型的SQL查询,包括SELECT、INSERT、UPDATE、DELETE和高级操作

难度

6/10

预计时间

25 min

标签

csharp, sql, database, queries, windows

前置条件

C# ADO.NET, SQL basics, Database concepts

csharp
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!");
    }
}
代码csharp复杂

事务处理

实现数据库事务,包括提交、回滚和隔离级别以确保数据完整性

难度

8/10

预计时间

30 min

标签

csharp, transaction, database, concurrency, windows

前置条件

C# ADO.NET, Database transactions, SQL concepts

csharp
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", "alice@example.com");
                            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", "charlie@example.com");
                            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", "diana@example.com");
                            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", "eve@example.com");
                            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!");
    }
}

工具

经常与该案例搭配使用的工具

相关内容

相关案例集合

相关内容

相关专题

专题

数据库 Schema 迁移

通过规范化源数据、审阅结构差异、校验关系并准备回滚资产,规划更安全的数据库迁移。

专题

修复被数据库拒绝的 CSV 导入并完成入库

定位 CSV 导入反复失败的结构原因,把混乱表头对齐到目标表结构,按声明的策略清除重复行,最终产出带类型的 INSERT 语句或供批量装载的 TSV。

专题

容器发布前的 Dockerfile 与环境配置审查

检查 Dockerfile 规则、镜像引用格式与环境变量重复键,解释预发布和生产配置差异,形成可核对的容器发布记录。

专题

域名上线前的 DNS、DNSSEC 与 HTTPS 验收

先拟定 DNS 区域记录,再核对在线解析、DNSSEC 委派和实际提供的 HTTPS 证书,为域名上线留下可复核的依据。

首页
工具3212
案例593
可视化471
文件查看器103
专题239
标签333
图像、音频与视频657

图像、音频、视频处理、转换与优化工具

数学、日期与金融647

计算器、数值计算、日期逻辑、统计与金融工具

设计与颜色298

颜色、版式、图形、视觉样式与设计辅助工具

文本与写作209

写作、清洗、格式化、提取与文本分析工具

开发与 Web193

开发者工具、网络处理、Web 调试与自动化辅助工具

转换与编码175

格式、文件、压缩包、单位与编码转换工具

潜客测验发布与联系人交接
讣告、悼词与纪念卡的家属复核及交付
本地习惯记录的备份、恢复演练与分享前检查
长文改编 X 串文与 LinkedIn 帖子的事实复核与交付
Markdown 互动课件审校、离线演示与交付

© 2026 Elysia Tools. 保留所有权利。

文档价格关于我们服务条款隐私政策Cookie 政策联系我们

Development

数据库备份生成器

为数据库表生成SQL备份脚本

Development

数据库 Schema 对比

比较两个数据库 Schema 并生成迁移草案

Development

数据库迁移生成器

为数据库架构更改生成SQL迁移脚本

Data Processing

CSV 转数据库迁移规划器

根据 CSV 数据推断关系型 schema,并为 PostgreSQL、MySQL、SQLite 或 SQL Server 生成建表和 ALTER 迁移计划

Format Conversion

CSV 转多方言 SQL INSERT 生成器

解析 CSV/Excel-CSV/TSV(自动方言检测,RFC 4180),生成 MySQL/PostgreSQL/SQLite/SQL Server 的批量 INSERT,带类型推断和 ON CONFLICT/IGNORE 变体

Data Processing

多表合并连接器

使用多种连接方式合并和连接多个表格(内连接、左连接、右连接、全连接)

Development

SQL 方言互译桥

在 MySQL / PostgreSQL / SQLite / BigQuery / Snowflake / Redshift 之间互译 DDL、DML、函数与类型,并标注不支持的写法

Development

SQL 执行计划可视化器

粘贴 EXPLAIN / EXPLAIN ANALYZE 输出(PostgreSQL/MySQL/SQLite),渲染为成本树,标注估算行数与实际行数的偏差热点,并给出具体的索引建议

Java

Android Java 数据库示例

Android Java 数据库示例,包括SQLite连接、SQL查询和事务处理

Kotlin

Android Kotlin 数据库示例

Android Kotlin 数据库示例,包括SQLite连接、执行SQL查询和事务处理

数据库 ORM

Drizzle ORM 示例

Drizzle ORM轻量级TypeScript ORM示例,包括模式定义、查询、迁移、关系和数据库模式

Objective-C

macOS Objective-C 数据库示例

macOS Objective-C 数据库示例,包括SQLite CRUD操作、查询、事务和连接管理

Swift

macOS Swift 数据库示例

macOS Swift 数据库示例,包括 SQLite 连接、查询执行和事务处理

Go

Web Go 数据库操作示例

Web Go 数据库操作示例,包括SQLite连接、SQL查询和事务处理