Elysia Tools

Explora herramientas, ejemplos, explicaciones visuales y guías temáticas en un solo espacio claro.

Categorías populares

Temas

Idioma
ZHDERUPTENFRES

Elysia Tools

Navegación

C#

Base de Datos Windows - Ejemplos C#

Ejemplos completos de base de datos C# para plataforma Windows incluyendo conexiones SQLite, consultas SQL, transacciones y operaciones de base de datos

Ejemplos

Entradas de esta colección

CódigocsharpSimple

Conexión Base de Datos SQLite

Conectar a bases de datos SQLite usando C# y realizar operaciones básicas de base de datos

Dificultad

3/10

Tiempo estimado

15 min

Etiquetas

csharp, sqlite, database, windows

Requisitos previos

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

Operaciones de Consultas SQL

Ejecutar varios tipos de consultas SQL incluyendo SELECT, INSERT, UPDATE, DELETE con operaciones avanzadas

Dificultad

6/10

Tiempo estimado

25 min

Etiquetas

csharp, sql, database, queries, windows

Requisitos previos

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

Procesamiento de Transacciones

Implementar transacciones de base de datos con commit, rollback y niveles de aislamiento para integridad de datos

Dificultad

8/10

Tiempo estimado

30 min

Etiquetas

csharp, transaction, database, concurrency, windows

Requisitos previos

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

Herramientas

Herramientas que suelen usarse con este ejemplo

Relacionado

Colecciones de ejemplos relacionadas

Relacionado

Temas relacionados

Tema

Migracion de Esquema de Base de Datos

Planifica una migracion segura normalizando datos de origen, revisando diferencias de esquema, validando relaciones y preparando recuperacion.

Tema

Repare una importación CSV rechazada y cárguela en su base de datos

Averigüe por qué una importación CSV falla una y otra vez, alinee los encabezados caóticos con el esquema de destino, elimine duplicados bajo una política declarada y genere sentencias INSERT con tipos o un TSV para carga masiva.

Tema

Flujos de calidad de datos e investigación de anomalías

Perfila datos tabulares, aísla duplicados, faltantes, rupturas referenciales, valores atípicos y anomalías temporales, y convierte los hallazgos en evidencia defendible.

Tema

Inspección de metadatos de imagen, orientación y limpieza de privacidad

Revisa EXIF, IPTC y XMP, corrige la orientación con seguridad, decide entre editar metadatos o eliminarlos de forma irreversible y verifica que la exportación ya no exponga datos no deseados.

Inicio
Herramientas3212
Ejemplos593
Visualizaciones471
Visores de archivos103
Temas239
Etiquetas333
Imágenes, audio y video657

Herramientas de procesamiento, conversión y optimización de imágenes, audio y video

Matemáticas, fechas y finanzas647

Calculadoras, herramientas numéricas, lógica de fechas, estadística y finanzas

Diseño y color298

Herramientas de color, maquetación, gráficos, estilo visual y apoyo al diseño

Texto y redacción209

Herramientas de redacción, limpieza, formato, extracción y análisis de texto

Desarrollo y Web193

Utilidades para desarrolladores, red, depuración web y automatización

Conversión y codificación175

Herramientas de conversión de formatos, archivos, unidades, archivos comprimidos y codificación

Lanzamiento de un cuestionario de captación y entrega de contactos
Textos conmemorativos: revisión familiar y entrega para impresión
Hábitos privados: copia, restauración y revisión antes de compartir
De artículo a borradores de X y LinkedIn: revisión factual y entrega
Revisión de una lección Markdown interactiva y entrega sin conexión

© 2026 Elysia Tools. Todos los derechos reservados.

DocumentaciónPreciosAcerca deTérminos de servicioPolítica de privacidadCookiesContacto

Development

Generador de Respaldo de Base de Datos

Generar scripts de respaldo SQL para tablas

Development

Diff de Schema de Base de Datos

Compara dos schemas y genera un borrador de migracion

Development

Generador de Migración de Base de Datos

Generar scripts de migración SQL para cambios de esquema

Data Processing

Planificador de migracion CSV a base de datos

Infiere un schema relacional desde CSV y genera planes CREATE TABLE y ALTER para PostgreSQL, MySQL, SQLite o SQL Server

Format Conversion

Emisor de INSERT SQL desde CSV

Analiza CSV/Excel-CSV/TSV (detección automática, RFC 4180) y genera lotes INSERT para MySQL/PostgreSQL/SQLite/SQL Server con inferencia de tipos y ON CONFLICT/IGNORE

Data Processing

Unificador de Tablas Múltiples

Unir y fusionar múltiples tablas con varios tipos de unión (interno, izquierdo, derecho, completo)

Development

Puente de dialectos SQL

Traduce DDL, DML, funciones y tipos entre MySQL / PostgreSQL / SQLite / BigQuery / Snowflake / Redshift, señalando lo no soportado

Development

Visualizador de Plan de Ejecución SQL

Pega la salida de EXPLAIN / EXPLAIN ANALYZE (PostgreSQL/MySQL/SQLite) para renderizar un árbol de costos, marcar divergencias entre filas estimadas y reales, y sugerir índices

Java

Ejemplos de Base de Datos Android Java

Ejemplos de base de datos Android Java incluyendo conexión SQLite, consultas SQL y procesamiento de transacciones

Kotlin

Ejemplos de Base de Datos Android Kotlin

Ejemplos de base de datos Android Kotlin incluyendo conexión SQLite, ejecución de consultas SQL y procesamiento de transacciones

ORM de bases de datos

Ejemplos de ORM Drizzle

Ejemplos de ORM Drizzle ligero en TypeScript incluyendo definición de esquemas, consultas, migraciones, relaciones y patrones de base de datos

Objective-C

Base de Datos macOS Objective-C - Ejemplos

Ejemplos de base de datos macOS Objective-C incluyendo operaciones CRUD SQLite, consultas, transacciones y gestión de conexiones

Swift

Ejemplos de Base de Datos macOS Swift

Ejemplos de base de datos macOS Swift incluyendo conexión SQLite, ejecución de consultas y procesamiento de transacciones

Go

Ejemplos de Operaciones de Base de Datos Web Go

Ejemplos de operaciones de base de datos Web Go incluyendo conexiones SQLite, consultas SQL y manejo de transacciones