抽奖系统开发：如何从数据库选取无重复的指定条件随机数据？

Got it, let's tackle this lottery system requirement where we need to pick random, non-repeating IDs from users with attendance = 'Present' when the draw button is clicked. Here are two solid approaches depending on your use case:

This method is the most reliable for shared lottery systems because it uses the database to track drawn entries, ensuring no duplicates even with concurrent clicks or server restarts.

First, add a IsDrawn bit column (default value 0/false) to your attendees table to mark who's already been selected. Then use an atomic UPDATE + OUTPUT query to pick and mark a random eligible user in one step:

protected void btnDraw_Click(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
    Guid? selectedId = null;

    using (SqlConnection conn = new SqlConnection(constr))
    {
        conn.Open();
        // Atomic operation: pick a random eligible user and mark them as drawn
        string sql = @"
            UPDATE TOP(1) Attendees
            SET IsDrawn = 1
            OUTPUT inserted.Id
            WHERE Attendance = 'Present' AND IsDrawn = 0
            ORDER BY NEWID()";

        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            var result = cmd.ExecuteScalar();
            selectedId = result != DBNull.Value ? (Guid?)result : null;
        }
    }

    if (selectedId.HasValue)
    {
        // Fetch and display user details if needed
        lblDrawResult.Text = $"Success! Selected ID: {selectedId.Value}";
    }
    else
    {
        lblDrawResult.Text = "No eligible users left to draw!";
    }
}

Why this works: #

• The UPDATE + OUTPUT clause ensures the selection and marking are atomic—no two requests can pick the same user.
• State is persisted in the database, so server restarts won't reset your drawn list.
• Handles concurrency out of the box, which is critical for multi-user systems.

If you're building a single-user or temporary lottery (e.g., a local event tool), you can load all eligible IDs into memory and track drawn entries with a HashSet (stored in Session to survive postbacks):

private List<Guid> _eligibleIds;
private HashSet<Guid> _drawnIds;

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // Load all eligible IDs on first page load
        _eligibleIds = LoadEligibleIdsFromDb();
        _drawnIds = new HashSet<Guid>();
        
        // Store in Session to persist across postbacks
        Session["EligibleIds"] = _eligibleIds;
        Session["DrawnIds"] = _drawnIds;
    }
    else
    {
        // Restore from Session on subsequent clicks
        _eligibleIds = Session["EligibleIds"] as List<Guid>;
        _drawnIds = Session["DrawnIds"] as HashSet<Guid>;
    }
}

private List<Guid> LoadEligibleIdsFromDb()
{
    string constr = ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
    var ids = new List<Guid>();

    using (SqlConnection conn = new SqlConnection(constr))
    {
        conn.Open();
        string sql = "SELECT Id FROM Attendees WHERE Attendance = 'Present'";
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    ids.Add(reader.GetGuid(0));
                }
            }
        }
    }
    return ids;
}

protected void btnDraw_Click(object sender, EventArgs e)
{
    if (_eligibleIds == null || _drawnIds == null)
    {
        lblDrawResult.Text = "Error: Failed to initialize lottery pool. Refresh the page.";
        return;
    }

    // Filter out already drawn IDs
    var availableIds = _eligibleIds.Except(_drawnIds).ToList();
    if (availableIds.Count == 0)
    {
        lblDrawResult.Text = "All eligible users have been drawn!";
        return;
    }

    // Pick a random ID
    var rnd = new Random();
    int randomIndex = rnd.Next(availableIds.Count);
    Guid selectedId = availableIds[randomIndex];

    // Mark as drawn and update Session
    _drawnIds.Add(selectedId);
    Session["DrawnIds"] = _drawnIds;

    lblDrawResult.Text = $"Success! Selected ID: {selectedId}";
}

Notes for this approach: #

• Random number caveat: Avoid creating a new Random() instance inside a loop or rapid repeated clicks—use a static instance if needed (just make it thread-safe).
• Session limitation: This is user-specific—each visitor will have their own lottery pool. If you need a shared pool across users, use a global cache (like MemoryCache) with lock logic to prevent race conditions.
• Data freshness: If user attendance statuses change after page load, you'll need to reload the eligible IDs periodically.

内容的提问来源于stack exchange，提问作者Ash93