C# 簡易留言板實作 (一)：分層架構與資料存取層設計

專案結構：三層式架構雛形

• Model (notepage.cs)：留言實體類別，含 ID、標題、作者、內容、時間屬性與建構函式
• DAL (notepage.cs 同檔)：資料存取邏輯，使用預存程序操作 SQL Server
• BLL/頁面：(後續文章將實作) 列表頁、詳細頁、發文頁

實體類別：notepage

public class notepage
{
    private int _id;
    private string _title, _author, _content;
    private DateTime _addDate;

    // 屬性封裝
    public int ID { get => _id; set => _id = value; }
    public string Title { get => _title; set => _title = value; }
    public string Author { get => _author; set => _author = value; }
    public string Content { get => _content; set => _content = value; }
    public DateTime AddDate { get => _addDate; set => _addDate = value; }

    public notepage()
    {
        _id = 0; _title = _author = _content = "";
        _addDate = DateTime.Now;
    }
}

資料存取方法 (ADO.NET + 預存程序)

1. 取得單筆留言 GetTopic(int id)

public notepage GetTopic(int topicId)
{
    var np = new notepage();
    using (var conn = new MyConnection()) // 自訂連線類別
    using (var cmd = new SqlCommand("n_GetTopicInfo", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@a_intTopicID", SqlDbType.Int).Value = topicId;
        
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                np.ID = (int)reader["ID"];
                np.Title = (string)reader["Title"];
                np.Author = (string)reader["Author"];
                np.Content = (string)reader["Content"];
                np.AddDate = (DateTime)reader["adddate"];
            }
        }
    }
    return np;
}

2. 新增留言 AddTopic(notepage topic)

public bool AddTopic(notepage topic)
{
    using (var conn = new MyConnection())
    using (var cmd = new SqlCommand("n_AddTopic", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@a_strTitle", SqlDbType.VarChar, 100).Value = topic.Title;
        cmd.Parameters.Add("@a_strAuthor", SqlDbType.VarChar, 50).Value = topic.Author;
        cmd.Parameters.Add("@a_strContent", SqlDbType.VarChar, 2000).Value = topic.Content;
        
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    return true;
}

3. 取得留言列表 GetTopicList()

public ArrayList GetTopicList()
{
    var list = new ArrayList(); // 建議改用 List
    using (var conn = new MyConnection())
    using (var cmd = new SqlCommand("n_GetTopicList", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                list.Add(new notepage
                {
                    ID = Convert.ToInt32(reader["ID"]),
                    Title = reader["Title"].ToString(),
                    Author = reader["Author"].ToString(),
                    AddDate = Convert.ToDateTime(reader["adddate"]),
                    Content = reader["Content"].ToString()
                });
            }
        }
    }
    return list;
}

現代化改進建議

• 泛型集合：List<notepage> 取代 ArrayList，避免裝箱/拆箱
• 參數化查詢：已使用 SqlParameter 防 SQL Injection
• using 區塊：確保連線/Reader 正確釋放 (原碼用 try/finally 手動 Close)
• 異步支援：ASP.NET Core 應改用 ExecuteReaderAsync、OpenAsync
• ORM：實務專案建議 Entity Framework Core / Dapper 簡化 DAL
• 連線字串外部化：存 web.config / appsettings.json

預存程序介面定義 (參考)

-- n_GetTopicInfo
CREATE PROC n_GetTopicInfo @a_intTopicID INT AS
SELECT ID, Title, Author, Content, adddate FROM Topics WHERE ID = @a_intTopicID;

-- n_AddTopic
CREATE PROC n_AddTopic @a_strTitle VARCHAR(100), @a_strAuthor VARCHAR(50), @a_strContent VARCHAR(2000) AS
INSERT INTO Topics (Title, Author, Content, adddate) VALUES (@a_strTitle, @a_strAuthor, @a_strContent, GETDATE());

-- n_GetTopicList
CREATE PROC n_GetTopicList AS
SELECT ID, Title, Author, Content, adddate FROM Topics ORDER BY adddate DESC;