如何将Javascript+ASP.NET生成的问卷字段数据传入Microsoft SQL数据库？

Hey there! Let's break down how to get your survey form data into a database for your Google Forms-like app. Based on what you've shared, here's a step-by-step approach tailored to your ASP.NET and JavaScript setup:

You'll need tables to store survey metadata, questions, and (if applicable) answer options. Here's a simple SQL schema example you can adapt:

-- Table for survey details
CREATE TABLE Surveys (
    SurveyId INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(255) NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);

-- Table for individual questions
CREATE TABLE Questions (
    QuestionId INT PRIMARY KEY IDENTITY(1,1),
    SurveyId INT FOREIGN KEY REFERENCES Surveys(SurveyId),
    QuestionText NVARCHAR(MAX) NOT NULL,
    QuestionType NVARCHAR(50) NOT NULL -- e.g., "Text", "MultipleChoice", "Checkbox"
);

-- Table for multiple-choice options (if needed)
CREATE TABLE QuestionOptions (
    OptionId INT PRIMARY KEY IDENTITY(1,1),
    QuestionId INT FOREIGN KEY REFERENCES Questions(QuestionId),
    OptionText NVARCHAR(255) NOT NULL
);

Since you're using ASP.NET, let's adjust your existing form to work with server-side processing. First, update your form to include runat="server" and a submit button:

<div class="form-group" style="margin:auto; width:80%;">
    <form name="add_question" id="add_question" runat="server">
        <asp:Label ID="Label1" runat="server" Text="Survey Title: "></asp:Label>
        <asp:TextBox ID="txtSurveyTitle" runat="server" CssClass="form-control"></asp:TextBox>
        
        <!-- Your dynamically generated questions/fields will go here -->
        <div id="questionsContainer" class="mt-3"></div>
        
        <asp:Button ID="btnSaveSurvey" runat="server" Text="Save Survey" OnClick="btnSaveSurvey_Click" CssClass="btn btn-primary mt-3" />
    </form>
</div>

Then, in your code-behind file (e.g., YourPage.aspx.cs), write the click event handler to insert data into the database. Here's an example using ADO.NET (you can also use Entity Framework for cleaner code):

protected void btnSaveSurvey_Click(object sender, EventArgs e)
{
    // Get survey title from the textbox
    string surveyTitle = txtSurveyTitle.Text.Trim();
    
    if (string.IsNullOrEmpty(surveyTitle))
    {
        // Add validation error (e.g., show a label)
        lblError.Text = "Survey title is required!";
        return;
    }

    // Connection string (store this in web.config for security)
    string connString = ConfigurationManager.ConnectionStrings["YourDbConnection"].ConnectionString;

    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        
        // First, insert the survey and get the new SurveyId
        string insertSurveySql = "INSERT INTO Surveys (Title) OUTPUT INSERTED.SurveyId VALUES (@Title)";
        SqlCommand surveyCmd = new SqlCommand(insertSurveySql, conn);
        surveyCmd.Parameters.AddWithValue("@Title", surveyTitle);
        int surveyId = (int)surveyCmd.ExecuteScalar();

        // Now, handle dynamically generated questions from JavaScript
        int questionIndex = 0;
        while (Request.Form[$"questionText_{questionIndex}"] != null)
        {
            string questionText = Request.Form[$"questionText_{questionIndex}"].Trim();
            string questionType = Request.Form[$"questionType_{questionIndex}"].Trim();

            if (!string.IsNullOrEmpty(questionText))
            {
                // Insert the question
                string insertQuestionSql = "INSERT INTO Questions (SurveyId, QuestionText, QuestionType) VALUES (@SurveyId, @QuestionText, @QuestionType)";
                SqlCommand questionCmd = new SqlCommand(insertQuestionSql, conn);
                questionCmd.Parameters.AddWithValue("@SurveyId", surveyId);
                questionCmd.Parameters.AddWithValue("@QuestionText", questionText);
                questionCmd.Parameters.AddWithValue("@QuestionType", questionType);
                questionCmd.ExecuteNonQuery();

                // If it's a multiple-choice question, insert options too
                if (questionType == "MultipleChoice")
                {
                    int optionIndex = 0;
                    // Get the last inserted QuestionId
                    int questionId = (int)new SqlCommand("SELECT SCOPE_IDENTITY()", conn).ExecuteScalar();
                    
                    while (Request.Form[$"optionText_{questionIndex}_{optionIndex}"] != null)
                    {
                        string optionText = Request.Form[$"optionText_{questionIndex}_{optionIndex}"].Trim();
                        if (!string.IsNullOrEmpty(optionText))
                        {
                            string insertOptionSql = "INSERT INTO QuestionOptions (QuestionId, OptionText) VALUES (@QuestionId, @OptionText)";
                            SqlCommand optionCmd = new SqlCommand(insertOptionSql, conn);
                            optionCmd.Parameters.AddWithValue("@QuestionId", questionId);
                            optionCmd.Parameters.AddWithValue("@OptionText", optionText);
                            optionCmd.ExecuteNonQuery();
                        }
                        optionIndex++;
                    }
                }
            }
            questionIndex++;
        }

        // Redirect to a success page or show confirmation
        Response.Redirect("SurveySuccess.aspx");
    }
}

When generating questions with JavaScript, make sure each input has a consistent name pattern so the server can pick them up. For example:

let questionCount = 0;

// Add a text input question
function addTextQuestion() {
    const container = document.getElementById('questionsContainer');
    const questionDiv = document.createElement('div');
    questionDiv.className = 'mb-3 p-2 border';
    
    questionDiv.innerHTML = `
        <label>Text Question:</label>
        <input type="text" name="questionText_${questionCount}" class="form-control mb-2" placeholder="Enter your question...">
        <input type="hidden" name="questionType_${questionCount}" value="Text">
        <button type="button" onclick="this.parentElement.remove()" class="btn btn-sm btn-danger">Remove</button>
    `;
    
    container.appendChild(questionDiv);
    questionCount++;
}

// Add a multiple-choice question
function addMultipleChoiceQuestion() {
    const container = document.getElementById('questionsContainer');
    const questionDiv = document.createElement('div');
    questionDiv.className = 'mb-3 p-2 border';
    
    questionDiv.innerHTML = `
        <label>Multiple Choice Question:</label>
        <input type="text" name="questionText_${questionCount}" class="form-control mb-2" placeholder="Enter your question...">
        <input type="hidden" name="questionType_${questionCount}" value="MultipleChoice">
        <div class="options-container" id="options_${questionCount}">
            <input type="text" name="optionText_${questionCount}_0" class="form-control mb-1" placeholder="Option 1">
        </div>
        <button type="button" onclick="addOption(${questionCount})" class="btn btn-sm btn-secondary mb-2">Add Option</button>
        <button type="button" onclick="this.parentElement.remove()" class="btn btn-sm btn-danger">Remove</button>
    `;
    
    container.appendChild(questionDiv);
    questionCount++;
}

// Add an option to a multiple-choice question
function addOption(questionIndex) {
    const optionsContainer = document.getElementById(`options_${questionIndex}`);
    const optionCount = optionsContainer.children.length;
    const optionInput = document.createElement('input');
    optionInput.type = 'text';
    optionInput.name = `optionText_${questionIndex}_${optionCount}`;
    optionInput.className = 'form-control mb-1';
    optionInput.placeholder = `Option ${optionCount + 1}`;
    optionsContainer.appendChild(optionInput);
}

• Always use parameterized queries (like the examples above) to prevent SQL injection. Entity Framework automatically handles this if you use it instead of raw ADO.NET.
• Store your database connection string in web.config (or appsettings.json for ASP.NET Core) instead of hardcoding it.
• Add client-side validation (with JavaScript) to catch errors before submitting, then server-side validation as a backup.
• For larger apps, consider ASP.NET Web API or MVC instead of Web Forms for more flexibility, especially if you want a single-page app feel like Google Forms.

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