ASP.NET中如何同时向双表提交数据及动态生成Chart.js饼图

Let's break this down into two main tasks: updating the Vote table on submit, and fetching dynamic data for your pie chart.

1. Update Vote Table on Submit Button Click #

Right now, your code only inserts into the Survey table. We need to add an update to increment the amount field in the Vote table for the selected pizza. To ensure data integrity, we'll wrap both operations in a database transaction so if either fails, neither change is saved.

Modify your Button1_Click method in Survey.aspx.cs like this:

protected void Button1_Click(object sender, EventArgs e)
{
    String CS = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        con.Open();
        // Start a transaction to ensure both operations succeed/fail together
        using (SqlTransaction tran = con.BeginTransaction())
        {
            try
            {
                // Insert into Survey table
                SqlCommand cmdInsertSurvey = new SqlCommand(
                    "INSERT INTO Survey (MemberName, PizzaVote) VALUES (@MemberName, @PizzaVote)", 
                    con, tran);
                cmdInsertSurvey.Parameters.AddWithValue("@MemberName", txtSurName.Text);
                cmdInsertSurvey.Parameters.AddWithValue("@PizzaVote", ddlPrePizza.SelectedItem.Text);
                cmdInsertSurvey.ExecuteNonQuery();

                // Update Vote table: increment amount for selected pizza
                SqlCommand cmdUpdateVote = new SqlCommand(
                    "UPDATE Vote SET amount = amount + 1 WHERE PizzaName = @PizzaName", 
                    con, tran);
                cmdUpdateVote.Parameters.AddWithValue("@PizzaName", ddlPrePizza.SelectedItem.Text);
                int rowsUpdated = cmdUpdateVote.ExecuteNonQuery();

                // Optional: Handle case where no pizza was found in Vote table (in case of typos)
                if (rowsUpdated == 0)
                {
                    throw new Exception("Selected pizza not found in Vote table.");
                }

                // Commit the transaction if everything worked
                tran.Commit();

                // Reset form fields
                txtSurName.Text = string.Empty;
                ddlPrePizza.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                // Rollback if anything went wrong
                tran.Rollback();
                // You can add error logging or show a user-friendly message here
                Response.Write($"Error: {ex.Message}");
            }
        }
    }
}

Key Notes: #

• Transaction: Ensures that if inserting into Survey fails, the Vote table doesn't get updated (and vice versa).
• Parameterized Queries: Prevents SQL injection (you were already doing this, great job!).
• Error Handling: Catches issues like missing pizza entries in the Vote table and rolls back changes.

2. Load Chart.js Data from the Vote Table #

Right now your pie chart uses hardcoded values. We'll modify the ChartJS page to fetch data directly from the Vote table and pass it to the frontend script.

Step 1: Add Code to Fetch Data in ChartJS.aspx.cs #

Add this method to your ChartJS class:

using System.Collections.Generic;
using System.Web.Script.Serialization; // Make sure to add this using statement

protected string GetVoteData()
{
    string CS = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
    List<VoteItem> voteItems = new List<VoteItem>();

    using (SqlConnection con = new SqlConnection(CS))
    {
        // Fetch pizza names and their vote counts, ordered to match your original label sequence
        SqlCommand cmd = new SqlCommand(
            "SELECT PizzaName, amount FROM Vote ORDER BY VoteId", 
            con);
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        
        while (reader.Read())
        {
            voteItems.Add(new VoteItem
            {
                PizzaName = reader["PizzaName"].ToString(),
                Amount = Convert.ToInt32(reader["amount"])
            });
        }
    }

    // Convert the list to JSON for the frontend
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    return serializer.Serialize(voteItems);
}

// Helper class to hold vote data
private class VoteItem
{
    public string PizzaName { get; set; }
    public int Amount { get; set; }
}

Step 2: Update the Frontend Script in ChartJS.aspx #

Replace your hardcoded Chart.js script with this dynamic version:

<script>
// Get the JSON data from the server-side method
var voteData = <%= GetVoteData() %>;

// Extract labels and amounts from the JSON
var pizzaLabels = voteData.map(item => item.PizzaName.toUpperCase());
var voteAmounts = voteData.map(item => item.Amount);

var ctx = document.getElementById("myChart").getContext('2d');
var myChart = new Chart(ctx, {
    type: 'pie',
    data: {
        labels: pizzaLabels,
        datasets: [{
            backgroundColor: [
                "#2ecc71", "#3498db", "#95a5a6", "#9b59b6", "#f1c40f", "#e74c3c", "#34495e"
            ],
            data: voteAmounts
        }]
    }
});
</script>

Key Notes: #

• Dynamic Data: The GetVoteData() method fetches the latest vote counts from the database and converts them to JSON.
• Label Consistency: We order the results by VoteId to match your original label order (make sure your Vote table entries are in the same sequence as your original dropdown list).
• Reusability: Using a helper class (VoteItem) makes it easy to extend the data later if needed.

Final Checks: #

• Ensure your web.config has the correct connection string named myConnection.
• Verify that the Vote table has entries for all the pizzas in your dropdown list (with amount starting at 0).
• Remove the duplicate Chart.js script tag (you have both a CDN and local reference; keep one for consistency).

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