.NET + MSSQL 開發抽獎程式完整架構與原始碼

需求分析

• 從資料庫隨機抽取使用者，依獎項等級與名額一次抽出所有得獎者。
• 結果需寫入資料庫並支援匯出。
• 避免 ID 不連續導致隨機抽取偏差，且盡量減少資料庫往返。

資料庫設計

-- 使用者表
CREATE TABLE [dbo].[users](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [name] [nvarchar](50) NOT NULL,
    [phone] [nvarchar](50) NULL
)

-- 獎項表
CREATE TABLE [dbo].[awards](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](50) NOT NULL,
    [Number] [int] NOT NULL
)

-- 得獎紀錄表
CREATE TABLE [dbo].[result](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [usersid] [int] NOT NULL,
    [awardsid] [int] NOT NULL
)

-- 檢視表：聯結查詢得獎明細
CREATE VIEW [dbo].[view1] AS
SELECT r.id AS resultid, u.id, u.name, u.phone, a.Name AS awardname
FROM dbo.awards a
INNER JOIN dbo.result r ON a.id = r.awardsid
INNER JOIN dbo.users u ON r.usersid = u.id

核心存儲過程：隨機抽取邏輯

CREATE PROCEDURE [dbo].[getranddata]
    @count INT,  -- 該獎項剩餘名額
    @awards INT  -- 獎項 ID
AS
BEGIN
    DECLARE @minid INT, @maxid INT, @randnum INT, @exist INT

    SELECT @minid = (SELECT TOP 1 id FROM users ORDER BY id ASC)
    SELECT @maxid = (SELECT TOP 1 id FROM users ORDER BY id DESC)

    WHILE @count > 0
    BEGIN
        -- 產生範圍內隨機整數
        SELECT @randnum = ROUND(((@maxid - @minid - 1) * RAND() + @minid), 0)
        -- 檢查 ID 是否存在
        SELECT @exist = COUNT(*) FROM users WHERE id = @randnum
        IF @exist = 1
        BEGIN
            INSERT INTO result(usersid, awardsid) VALUES(@randnum, @awards)
            SET @count = @count - 1
        END
    END
END

關鍵點：使用 ROUND((max-min-1)*RAND()+min,0) 產生範圍隨機數，配合 WHILE 迴圈與存在性檢查，解決 ID 不連續問題。

後台 C# 程式碼片段

protected void Button1_Click(object sender, EventArgs e)
{
    using (SqlConnection sqlcon = new SqlConnection("server=.;database=test;uid=sa;pwd=123"))
    {
        sqlcon.Open();
        SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM awards", sqlcon);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        DataTable dt = ds.Tables[0].Copy();

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            using (SqlCommand cmd = new SqlCommand("getranddata", sqlcon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@count", Convert.ToInt32(dt.Rows[i]["Number"]));
                cmd.Parameters.AddWithValue("@awards", Convert.ToInt32(dt.Rows[i]["id"]));
                cmd.ExecuteNonQuery();
            }

            // 繫結最新得獎結果到 GridView
            sda = new SqlDataAdapter(
                "SELECT TOP " + dt.Rows[i]["Number"] + " * FROM view1 ORDER BY resultid DESC", sqlcon);
            DataSet dsResult = new DataSet();
            sda.Fill(dsResult, "t" + i);

            GridView gv = (GridView)FindControl("GridView" + (i+1));
            if (gv != null) { gv.DataSource = dsResult.Tables[0]; gv.DataBind(); }
        }
    }
}

介面截圖

知識點補充

SQL 產生指定範圍隨機數

DECLARE @Result INT, @Upper INT, @Lower INT
SET @Lower = 1; SET @Upper = 10
SELECT @Result = ROUND(((@Upper - @Lower - 1) * RAND() + @Lower), 0)
SELECT @Result

WHILE 迴圈巢狀範例

DECLARE @i INT = 1
WHILE @i < 8
BEGIN
    IF @i < 5 PRINT SPACE(4-@i) + REPLICATE('*', 2*@i-1)
    ELSE PRINT SPACE(@i-4) + REPLICATE('*', 15-2*@i)
    SET @i = @i + 1
END