CREATE DATABASE WebVoteDB;
GO
CREATE TABLE Votes (
VoteID INT IDENTITY(1,1) PRIMARY KEY,
Item NVARCHAR(200) NOT NULL,
VoteCount INT DEFAULT 0,
CreateDate DATETIME DEFAULT GETDATE(),
IsActive BIT DEFAULT 1
);
CREATE TABLE VoteLogs (
LogID BIGINT IDENTITY(1,1) PRIMARY KEY,
VoteID INT NOT NULL REFERENCES Votes(VoteID),
UserIP VARCHAR(45), -- 支援 IPv6
UserAgent NVARCHAR(500),
VoteTime DATETIME DEFAULT GETDATE()
);
-- 索引
CREATE INDEX IX_Votes_IsActive ON Votes(IsActive);
CREATE INDEX IX_VoteLogs_VoteID_Time ON VoteLogs(VoteID, VoteTime);
-- 取得所有啟用投票項目
CREATE PROC Pr_GetVotes
AS
SELECT VoteID, Item, VoteCount FROM Votes WHERE IsActive = 1 ORDER BY VoteID;
-- 取得單一項目
CREATE PROC Pr_GetSingleVote @VoteID INT
AS
SELECT VoteID, Item, VoteCount FROM Votes WHERE VoteID = @VoteID;
-- 新增投票項目
CREATE PROC Pr_AddVote @Item NVARCHAR(200)
AS
INSERT INTO Votes (Item, VoteCount) VALUES (@Item, 0);
SELECT SCOPE_IDENTITY() AS NewID;
-- 投票 (交易確保計數與日誌一致性)
CREATE PROC Pr_Vote @VoteID INT, @UserIP VARCHAR(45), @UserAgent NVARCHAR(500)
AS
BEGIN TRAN
IF NOT EXISTS (SELECT 1 FROM VoteLogs WHERE VoteID = @VoteID AND UserIP = @UserIP AND VoteTime > DATEADD(HOUR, -24, GETDATE()))
BEGIN
UPDATE Votes SET VoteCount = VoteCount + 1 WHERE VoteID = @VoteID;
INSERT INTO VoteLogs (VoteID, UserIP, UserAgent) VALUES (@VoteID, @UserIP, @UserAgent);
SELECT 1 AS Result;
END
ELSE
SELECT 0 AS Result; -- 24小時內重複投票
COMMIT TRAN;
-- 刪除投票項目 (級聯刪除日誌)
CREATE PROC Pr_DeleteVote @VoteID INT
AS
DELETE FROM VoteLogs WHERE VoteID = @VoteID;
DELETE FROM Votes WHERE VoteID = @VoteID;
// App_Code/SqlHelper.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public static class SqlHelper
{
static string connStr = ConfigurationManager.ConnectionStrings["VoteDB"].ConnectionString;
public static SqlDataReader RunProc(string procName, params SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null) cmd.Parameters.AddRange(parameters);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public static int RunProcScalar(string procName, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null) cmd.Parameters.AddRange(parameters);
conn.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static void RunProcNonQuery(string procName, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null) cmd.Parameters.AddRange(parameters);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
// App_Code/Vote.cs
using System;
using System.Data;
using System.Data.SqlClient;
public class Vote
{
public SqlDataReader GetVotes()
{
return SqlHelper.RunProc("Pr_GetVotes");
}
public int AddVote(string item)
{
return SqlHelper.RunProcScalar("Pr_AddVote",
new SqlParameter("@Item", item));
}
public bool Vote(int voteId, string userIp, string userAgent)
{
var result = SqlHelper.RunProcScalar("Pr_Vote",
new SqlParameter("@VoteID", voteId),
new SqlParameter("@UserIP", userIp),
new SqlParameter("@UserAgent", userAgent));
return result == 1;
}
public void DeleteVote(int voteId)
{
SqlHelper.RunProcNonQuery("Pr_DeleteVote",
new SqlParameter("@VoteID", voteId));
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Vote.aspx.cs" Inherits="Vote" %>
<!DOCTYPE html>
<html><head><title>線上投票</title></head><body>
<h2>請選擇您支持的項目</h2>
<asp:Repeater ID="rptVotes" runat="server">
<ItemTemplate>
<div class="vote-item">
<asp:RadioButton ID="rbVote" GroupName="voteGroup"
Value='<%# Eval("VoteID") %>' runat="server" />
<asp:Label ID="lblItem" runat="server" Text='<%# Eval("Item") %>' />
<asp:Label ID="lblCount" runat="server" Text='(<%# Eval("VoteCount") %> 票)' ForeColor="Gray" />
</div>
</ItemTemplate>
</asp:Repeater>
<br />
<asp:Button ID="btnVote" Text="提交投票" OnClick="btnVote_Click" runat="server" />
<asp:Label ID="lblMsg" runat="server" ForeColor="Red" />
<hr />
<h3>目前結果</h3>
<asp:Repeater ID="rptResults" runat="server">
<ItemTemplate>
<div><%# Eval("Item") %>: <%# Eval("VoteCount") %> 票
<div style="width:<%# Eval("VoteCount") * 2 %>px; height:20px; background:#4CAF50; display:inline-block;"></div>
</div>
</ItemTemplate>
</asp:Repeater>
</body></html>
public partial class Vote : System.Web.UI.Page
{
Vote bll = new Vote();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) BindData();
}
void BindData()
{
using (var dr = bll.GetVotes())
{
rptVotes.DataSource = dr;
rptVotes.DataBind();
}
// 結果也重新綁定
using (var dr2 = bll.GetVotes())
{
rptResults.DataSource = dr2;
rptResults.DataBind();
}
}
protected void btnVote_Click(object sender, EventArgs e)
{
string selectedId = Request.Form["rbVote"];
if (string.IsNullOrEmpty(selectedId))
{
lblMsg.Text = "請選擇一個項目";
return;
}
int voteId = int.Parse(selectedId);
string userIp = Request.UserHostAddress;
string userAgent = Request.UserAgent;
if (bll.Vote(voteId, userIp, userAgent))
{
lblMsg.ForeColor = System.Drawing.Color.Green;
lblMsg.Text = "投票成功!謝謝參與。";
}
else
{
lblMsg.Text = "您已投過票 (24小時內限投一次),請勿重複投票。";
}
BindData();
}
}
<connectionStrings>
<add name="VoteDB" connectionString="Server=.;Database=WebVoteDB;Uid=sa;Pwd=***;" providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<pages validateRequest="true" /> <!-- 預防 XSS -->
</system.web>
此系統展示 ASP.NET 2.0 三層架構核心模式:存儲過程封裝資料邏輯、SqlHelper 統一資料存取、BLL 封裝業務規則、UI 僅負責呈現與互動,結構清晰、易維護、易擴充。