元哥的筆記

Active Record 是每一張表都對應一個業務物件，業務物件表示表中的一行，包含資料、行為、持久化的物件的工具，此外匯增加一個新實體和查找物件集合所需的方法。

以下的範例中，Customer 類別代表資料表 Customers 中的記錄。

每個 Customer 物件的屬性對應到資料表的一個欄位。

可以使用 Create、Read、Update 和 Delete 方法來執行相應的資料庫操作。

實際上在現代的開發中能使用 Entity Framework 或其他 ORM 工具來簡化這些操作。

using System;
using System.Data.SqlClient;
using System.Data;

// 定義 Active Record 類別
public class Customer
{
    // 每個欄位對應到資料表的一個欄位
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }

    // 資料庫連線字串
    private const string ConnectionString = "YourConnectionString";

    // 建立新的顧客記錄
    public void Create()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "INSERT INTO Customers (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email);";
                command.Parameters.AddWithValue("@FirstName", FirstName);
                command.Parameters.AddWithValue("@LastName", LastName);
                command.Parameters.AddWithValue("@Email", Email);

                command.ExecuteNonQuery();
            }
        }
    }

    // 讀取顧客記錄
    public static Customer Read(int customerId)
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "SELECT * FROM Customers WHERE Id = @Id;";
                command.Parameters.AddWithValue("@Id", customerId);

                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var customer = new Customer
                        {
                            Id = (int)reader["Id"],
                            FirstName = (string)reader["FirstName"],
                            LastName = (string)reader["LastName"],
                            Email = (string)reader["Email"]
                        };
                        return customer;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }
    }

    // 更新顧客記錄
    public void Update()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "UPDATE Customers SET FirstName = @FirstName, LastName = @LastName, Email = @Email WHERE Id = @Id;";
                command.Parameters.AddWithValue("@Id", Id);
                command.Parameters.AddWithValue("@FirstName", FirstName);
                command.Parameters.AddWithValue("@LastName", LastName);
                command.Parameters.AddWithValue("@Email", Email);

                command.ExecuteNonQuery();
            }
        }
    }

    // 刪除顧客記錄
    public void Delete()
    {
        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            using (var command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandType = CommandType.Text;
                command.CommandText = "DELETE FROM Customers WHERE Id = @Id;";
                command.Parameters.AddWithValue("@Id", Id);

                command.ExecuteNonQuery();
            }
        }
    }
}

class Program
{
    static void Main()
    {
        // 新增顧客記錄
        var newCustomer = new Customer { FirstName = "John", LastName = "Doe", Email = "john.doe@example.com" };
        newCustomer.Create();

        // 讀取顧客記錄
        var customer = Customer.Read(newCustomer.Id);
        if (customer != null)
        {
            Console.WriteLine($"Customer: {customer.Id}, {customer.FirstName} {customer.LastName}, {customer.Email}");

            // 更新顧客記錄
            customer.Email = "john.doe.updated@example.com";
            customer.Update();

            // 再次讀取顧客記錄
            var updatedCustomer = Customer.Read(customer.Id);
            Console.WriteLine($"Updated Customer: {updatedCustomer.Id}, {updatedCustomer.FirstName} {updatedCustomer.LastName}, {updatedCustomer.Email}");

            // 刪除顧客記錄
            updatedCustomer.Delete();
        }
        else
        {
            Console.WriteLine("Customer not found.");
        }
    }
}

 

元哥的筆記