Elysia Tools

Durchsuche Tools, Beispiele, visuelle Erklärungen und Themenguides an einem klaren Ort.

Beliebte Kategorien

Themen

Sprache
ZHDERUPTENFRES

Elysia Tools

Mobile Navigation

C#

Windows-Datenbank - C# Beispiele

Umfassende C# Datenbankbeispiele für Windows-Plattform einschließlich SQLite-Verbindungen, SQL-Abfragen, Transaktionen und Datenbankoperationen

Beispiele

Einträge in dieser Sammlung

QuellcodecsharpEinfach

SQLite-Datenbankverbindung

Verbindung zu SQLite-Datenbanken mit C# herstellen und grundlegende Datenbankoperationen durchführen

Schwierigkeit

3/10

Geschätzte Zeit

15 min

Schlagwörter

csharp, sqlite, database, windows

Voraussetzungen

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!");
    }
}
QuellcodecsharpMittel

SQL-Abfrageoperationen

Verschiedene SQL-Abfragetypen ausführen, einschließlich SELECT, INSERT, UPDATE, DELETE mit erweiterten Operationen

Schwierigkeit

6/10

Geschätzte Zeit

25 min

Schlagwörter

csharp, sql, database, queries, windows

Voraussetzungen

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!");
    }
}
QuellcodecsharpKomplex

Transaktionsverarbeitung

Datenbanktransaktionen mit Commit, Rollback und Isolationsstufen für Datenintegrität implementieren

Schwierigkeit

8/10

Geschätzte Zeit

30 min

Schlagwörter

csharp, transaction, database, concurrency, windows

Voraussetzungen

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!");
    }
}

Werkzeuge

Tools, die oft mit diesem Beispiel genutzt werden

Verwandt

Verwandte Beispielsammlungen

Verwandt

Verwandte Themen

Thema

JSON Schema aus Beispielen und Tabellen ableiten

Leiten Sie aus repräsentativen JSON-Beispielen oder XLSX-Zeilen einen prüfbaren JSON-Schema-Entwurf ab und kontrollieren Sie Typen, Pflichtfelder, Nullwerte und Ausreißer, bevor er als Vertrag dient.

Thema

Abgelehnten CSV-Import reparieren und in die Datenbank laden

Finden Sie heraus, warum ein CSV-Import immer wieder scheitert, ordnen Sie chaotische Kopfzeilen dem Zielschema zu, entfernen Sie Duplikate nach einer festgelegten Regel und erzeugen Sie typisierte INSERT-Anweisungen oder ein TSV für das Massenladen.

Thema

Datenbankschema-Migration

Planen Sie eine sichere Migration durch Normalisieren der Quelldaten, Prufen von Schemadifferenzen, Validieren von Beziehungen und Vorbereiten der Wiederherstellung.

Thema

Standardisierung und Komprimierung von Bildern im Batch

Bereiten Sie einen einheitlichen Bildbestand vor: Ausrichtung korrigieren, unerwünschte Ränder entfernen, eine Größenregel anwenden, Dateien komprimieren und repräsentative Ausgaben vor der Übergabe prüfen.

Start
Werkzeuge3212
Beispiele593
Visualisierungen471
Dateibetrachter103
Themen239
Schlagwörter333
Bilder, Audio & Video657

Werkzeuge für Bild-, Audio- und Videobearbeitung, Konvertierung und Optimierung

Mathe, Datum & Finanzen647

Rechner, Zahlenwerkzeuge, Datumslogik, Statistik und Finanztools

Design & Farbe298

Werkzeuge für Farben, Layouts, Grafiken, visuelle Gestaltung und Designhilfen

Text & Schreiben209

Werkzeuge für Schreiben, Bereinigung, Formatierung, Extraktion und Textanalyse

Entwicklung & Web193

Entwicklerwerkzeuge, Netzwerk-Utilities, Web-Debugging und Automatisierungshelfer

Konvertierung & Kodierung175

Werkzeuge für Format-, Datei-, Archiv-, Einheiten- und Kodierungskonvertierung

Lead-Quiz veröffentlichen und Kontakte sicher übergeben
Gedenktexte: Familienprüfung und Übergabe zum Druck
Private Gewohnheiten: Sicherung, Wiederherstellung und Freigabeprüfung
Vom Artikel zu X- und LinkedIn-Entwürfen: Faktenprüfung und Übergabe
Interaktive Markdown-Lektion prüfen und offline übergeben

© 2026 Elysia Tools. Alle Rechte vorbehalten.

DokumentationTarifeÜber unsNutzungsbedingungenDatenschutzerklärungCookiesKontakt

Development

Datenbank-Backup-Generator

SQL-Backup-Skripte für Datenbanktabellen generieren

Development

Datenbank Schema Diff

Vergleicht zwei Datenbankschemas und erstellt einen Migrationsentwurf

Development

Datenbank-Migrations-Generator

SQL-Migrationsskripte für Schemaänderungen generieren

Data Processing

CSV-zu-Datenbank-Migrationsplaner

Leitet aus CSV-Daten ein relationales Schema ab und erzeugt CREATE-TABLE- und ALTER-Plaene fuer PostgreSQL, MySQL, SQLite oder SQL Server

Format Conversion

CSV-zu-SQL-INSERT-Generator

Parst CSV/Excel-CSV/TSV (Auto-Erkennung, RFC 4180) und erzeugt INSERT-Stapel für MySQL/PostgreSQL/SQLite/SQL Server mit Typinferenz und ON CONFLICT/IGNORE

Data Processing

Mehrfach-Tabellen-Verbinder

Mehrere Tabellen mit verschiedenen Verknüpfungstypen (inner, links, rechts, voll) zusammenführen und verbinden

Development

SQL-Dialekt-Brücke

Übersetzt DDL, DML, Funktionen und Typen zwischen MySQL / PostgreSQL / SQLite / BigQuery / Snowflake / Redshift und markiert Nicht-Unterstütztes

Development

SQL Ausführungsplan-Visualisierer

Füge EXPLAIN-/EXPLAIN-ANALYZE-Ausgaben (PostgreSQL/MySQL/SQLite) ein, um einen Kostenbaum zu erhalten, Abweichungen zwischen geschätzten und tatsächlichen Zeilen zu markieren und Indexvorschläge zu erhalten

Java

Android Java Datenbankbeispiele

Android Java Datenbankbeispiele einschließlich SQLite-Verbindung, SQL-Abfragen und Transaktionsverarbeitung

Kotlin

Android Kotlin Datenbankbeispiele

Android Kotlin Datenbankbeispiele einschließlich SQLite-Verbindung, SQL-Abfrageausführung und Transaktionsverarbeitung

Datenbank-ORMs

Drizzle ORM Beispiele

Drizzle ORM leichtgewichtiges TypeScript ORM Beispiele einschließlich Schema-Definition, Queries, Migrationen, Beziehungen und Datenbank-Mustern

Objective-C

macOS Objective-C Datenbank - Beispiele

macOS Objective-C Datenbank-Beispiele einschließlich SQLite-CRD-Operationen, Abfragen, Transaktionen und Verbindungsverwaltung

Swift

macOS Swift Datenbank Beispiele

macOS Swift Datenbankbeispiele einschließlich SQLite-Verbindung, Abfrageausführung und Transaktionsverarbeitung

Go

Web Go Datenbankoperationsbeispiele

Web Go Datenbankoperationsbeispiele einschließlich SQLite-Verbindungen, SQL-Abfragen und Transaktionsverarbeitung