ASP.Net中带StatementType的存储过程与GridView绑定及连接实现问题

Hey there! Let's walk through this step by step since you're new to ASP.NET—this is totally doable, I promise. First, let's recap your stored procedure for reference:

CREATE PROCEDURE SP_Employees 
(
    @EmployeeID INT, 
    @EmployeeName VARCHAR(255), 
    @EmployeeUsername VARCHAR(255), 
    @EmployeeEmail VARCHAR(255), 
    @GroupID INT, 
    @Password VARCHAR(255), 
    @StatementType NVARCHAR(20) = '' 
) 
AS 
BEGIN 
    IF @StatementType = 'INSERT' 
    BEGIN 
        INSERT INTO [Employees]([EmployeeID], [EmployeeName], [EmployeeUsername], [EmployeeEmail], [GroupID], [Password]) 
        VALUES (@EmployeeID, @EmployeeName, @EmployeeUsername, @EmployeeEmail, @GroupID, @Password); 
    END 
    ELSE IF @StatementType = 'SELECT' 
    BEGIN 
        SELECT * FROM [Employees]; 
    END 
    ELSE IF @StatementType = 'Update' 
    BEGIN 
        UPDATE [Employees] 
        SET [EmployeeName] = @EmployeeName, [EmployeeUsername] = @EmployeeUsername, [EmployeeEmail] = @EmployeeEmail, [GroupID] = @GroupID, [Password] = @Password 
        WHERE [EmployeeID] = @EmployeeID; 
    END 
    ELSE IF @StatementType = 'DELETE' 
    BEGIN 
        DELETE FROM [Employees] WHERE [EmployeeID] = @EmployeeID; 
    END; 
END

1. First: Configure Your Database Connection String #

First, add a connection string to your web.config file—this lets your app connect to your database without hardcoding credentials everywhere:

<configuration>
  <connectionStrings>
    <add name="EmployeeDB" 
         connectionString="Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;Integrated Security=True;" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

Replace YOUR_SERVER_NAME and YOUR_DATABASE_NAME with your actual SQL Server details.

2. Bind GridView with SELECT Operation #

The GridView gets its data from the SELECT branch of your stored procedure. We'll write code in your page's code-behind file (e.g., Default.aspx.cs) to handle this.

First, add a method to fetch and bind data, then call it when the page loads for the first time:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        // Only bind data when the page first loads (not on postbacks like button clicks)
        BindEmployeeGridView();
    }
}

private void BindEmployeeGridView()
{
    string connString = ConfigurationManager.ConnectionStrings["EmployeeDB"].ConnectionString;
    
    // Use 'using' statements to automatically clean up database resources
    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand("SP_Employees", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            // Tell the stored procedure we want to SELECT data
            cmd.Parameters.AddWithValue("@StatementType", "SELECT");
            
            // Pass DBNull.Value for unused parameters (your stored procedure requires all parameters)
            cmd.Parameters.AddWithValue("@EmployeeID", DBNull.Value);
            cmd.Parameters.AddWithValue("@EmployeeName", DBNull.Value);
            cmd.Parameters.AddWithValue("@EmployeeUsername", DBNull.Value);
            cmd.Parameters.AddWithValue("@EmployeeEmail", DBNull.Value);
            cmd.Parameters.AddWithValue("@GroupID", DBNull.Value);
            cmd.Parameters.AddWithValue("@Password", DBNull.Value);
            
            // Fill a DataTable with the SELECT results
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            DataTable employeeTable = new DataTable();
            dataAdapter.Fill(employeeTable);
            
            // Bind the DataTable to your GridView
            GridView1.DataSource = employeeTable;
            GridView1.DataBind();
        }
    }
}

On your ASPX page, make sure your GridView is set up (you can auto-generate columns or define them manually):

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="True" />

3. Handle INSERT/UPDATE/DELETE Operations #

Assuming you have TextBoxes for input (e.g., txtEmployeeID, txtEmployeeName) and buttons for each action (e.g., btnInsert, btnUpdate, btnDelete), add these click event handlers to your code-behind:

Insert Operation

protected void btnInsert_Click(object sender, EventArgs e)
{
    try
    {
        string connString = ConfigurationManager.ConnectionStrings["EmployeeDB"].ConnectionString;
        
        using (SqlConnection conn = new SqlConnection(connString))
        {
            using (SqlCommand cmd = new SqlCommand("SP_Employees", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@StatementType", "INSERT");
                
                // Map TextBox values to stored procedure parameters
                cmd.Parameters.AddWithValue("@EmployeeID", int.Parse(txtEmployeeID.Text));
                cmd.Parameters.AddWithValue("@EmployeeName", txtEmployeeName.Text);
                cmd.Parameters.AddWithValue("@EmployeeUsername", txtEmployeeUsername.Text);
                cmd.Parameters.AddWithValue("@EmployeeEmail", txtEmployeeEmail.Text);
                cmd.Parameters.AddWithValue("@GroupID", int.Parse(txtGroupID.Text));
                cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
                
                conn.Open();
                cmd.ExecuteNonQuery(); // Run the INSERT command
                conn.Close();
            }
        }
        
        // Refresh the GridView to show the new record
        BindEmployeeGridView();
        ClearInputTextBoxes();
    }
    catch (Exception ex)
    {
        // Show an error message (use a Label control named lblError)
        lblError.Text = $"Insert failed: {ex.Message}";
    }
}

Update Operation

Note: Your stored procedure uses 'Update' (capital U) for this branch—make sure the parameter value matches exactly!

protected void btnUpdate_Click(object sender, EventArgs e)
{
    try
    {
        string connString = ConfigurationManager.ConnectionStrings["EmployeeDB"].ConnectionString;
        
        using (SqlConnection conn = new SqlConnection(connString))
        {
            using (SqlCommand cmd = new SqlCommand("SP_Employees", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@StatementType", "Update");
                
                cmd.Parameters.AddWithValue("@EmployeeID", int.Parse(txtEmployeeID.Text));
                cmd.Parameters.AddWithValue("@EmployeeName", txtEmployeeName.Text);
                cmd.Parameters.AddWithValue("@EmployeeUsername", txtEmployeeUsername.Text);
                cmd.Parameters.AddWithValue("@EmployeeEmail", txtEmployeeEmail.Text);
                cmd.Parameters.AddWithValue("@GroupID", int.Parse(txtGroupID.Text));
                cmd.Parameters.AddWithValue("@Password", txtPassword.Text);
                
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        
        BindEmployeeGridView();
        ClearInputTextBoxes();
    }
    catch (Exception ex)
    {
        lblError.Text = $"Update failed: {ex.Message}";
    }
}

Delete Operation

protected void btnDelete_Click(object sender, EventArgs e)
{
    try
    {
        string connString = ConfigurationManager.ConnectionStrings["EmployeeDB"].ConnectionString;
        
        using (SqlConnection conn = new SqlConnection(connString))
        {
            using (SqlCommand cmd = new SqlCommand("SP_Employees", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@StatementType", "DELETE");
                
                // Only EmployeeID is needed for DELETE
                cmd.Parameters.AddWithValue("@EmployeeID", int.Parse(txtEmployeeID.Text));
                cmd.Parameters.AddWithValue("@EmployeeName", DBNull.Value);
                cmd.Parameters.AddWithValue("@EmployeeUsername", DBNull.Value);
                cmd.Parameters.AddWithValue("@EmployeeEmail", DBNull.Value);
                cmd.Parameters.AddWithValue("@GroupID", DBNull.Value);
                cmd.Parameters.AddWithValue("@Password", DBNull.Value);
                
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        
        BindEmployeeGridView();
        ClearInputTextBoxes();
    }
    catch (Exception ex)
    {
        lblError.Text = $"Delete failed: {ex.Message}";
    }
}

// Helper method to clear all input TextBoxes after an action
private void ClearInputTextBoxes()
{
    txtEmployeeID.Text = "";
    txtEmployeeName.Text = "";
    txtEmployeeUsername.Text = "";
    txtEmployeeEmail.Text = "";
    txtGroupID.Text = "";
    txtPassword.Text = "";
    lblError.Text = "";
}

Quick Notes on Where to Put Code #

• Connection string: Goes in web.config (root of your project)
• Code-behind logic: All the C# methods go in your page's .aspx.cs file (e.g., Default.aspx.cs)
• UI controls: TextBoxes, Buttons, and GridView go in your .aspx front-end file

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