C#窗体TextBox从两个SQL表搜索数据时存储过程参数报错

问题背景 #

需求为在C#窗体的搜索TextBox中输入值，从Customer（客户）和Supplier（供应商）两个SQL表检索对应数据并展示；采用带内连接的SQL存储过程关联两表获取数据，但调用时出现「参数过多」错误。

原C#代码 #

try
{
    using(SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SearchCustSupplier";

        if(Main_Form.TransactionType == "Purchase")
        {
            cmd.Parameters.AddWithValue("@type", "Supplier");
        }

        else
        {
            cmd.Parameters.AddWithValue("@type", "Customer");
        }
        
        cmd.Parameters.AddWithValue("@search" , "%"  +txtSearchSC.Text+ "%");

        SqlDataAdapter da = new SqlDataAdapter(cmd);   
        DataTable dt = new DataTable();
        da.Fill(dt);    

        if(dt.Rows.Count > 0)
        {
            DataRow dr = dt.Rows[0];

            //cmd.Parameters.AddWithValue("@Customer_ID", Cust_ID);
            txtSCName.Text = dr["CustName"].ToString();    
            txtEmail.Text = dr["CustEmail"].ToString();
            txtPhone.Text = dr["CustPhone"].ToString();
            txtMobile.Text = dr["CustMobile"].ToString();
            txtAddress.Text = dr["CustAddress"].ToString();

            //cmd.Parameters.AddWithValue("@Supplier_ID", Supp_ID);
            txtSCName.Text = dr["Supp_Name"].ToString();
            txtEmail.Text = dr["Supp_Email"].ToString();
            txtPhone.Text = dr["Supp_Phone"].ToString();
            txtMobile.Text = dr["Supp_Mobile"].ToString();
            txtAddress.Text = dr["Supp_Address"].ToString();
        }
    }
}
catch (Exception ex)
{
    MessageBox.Show("Customer/Supplier Not Found, Something Went Wrong", "Warning" , MessageBoxButtons.OK, MessageBoxIcon.Error);
}

原SQL存储过程代码 #

Create Or Alter Procedure SearchCustSupplier
    (@search varchar (300),
     @type varchar (200)
     --@Customer_ID Int,
     --@Supplier_ID Int
    )
As 
Begin 
    Select 
        C.Cust_ID, C.CustName, C.CustEmail, C.CustPhone, 
        C.CustMobile, C.CustAddress,
        S.Supp_ID, S.Supp_Name, S.Supp_Email, 
        S.Supp_Phone, S.Supp_Mobile, S.Supp_Address 
    From 
        Customer As C 
    Inner Join 
        Supplier As S ON C.Cust_ID = S.Supp_ID 
    Where 
        C.CustName Like @search 
        Or S.Supp_Name Like @search
End

窗体截图 #

错误原因 #

1. 参数顺序不匹配：存储过程定义的参数顺序是@search在前、@type在后，但C#代码先添加@type再添加@search。SQL Server默认按参数顺序匹配（而非参数名），导致参数传递混乱，触发「参数过多」或参数不匹配错误。
2. 存储过程逻辑冗余：定义了@type参数但未在查询中使用，无论交易类型是采购还是销售，都会同时搜索客户和供应商，不符合需求。
3. 内连接逻辑错误：用C.Cust_ID = S.Supp_ID做内连接，仅当客户ID与供应商ID完全一致时才返回数据，不符合常规业务逻辑，大概率返回空结果。
4. C#赋值覆盖：获取数据后先赋值客户信息，随即用供应商信息覆盖，导致最终仅能看到供应商数据，客户信息丢失。

修复方案 #

1. 修正存储过程 #

调整参数顺序，根据@type参数筛选对应表的数据，同时简化查询逻辑：

Create Or Alter Procedure SearchCustSupplier
    @type varchar(200),
    @search varchar(300)
As 
Begin 
    If @type = 'Customer'
    Begin
        -- 仅查询客户表，统一返回字段名
        Select 
            Cust_ID As ID,
            CustName As Name,
            CustEmail As Email,
            CustPhone As Phone,
            CustMobile As Mobile,
            CustAddress As Address
        From Customer
        Where CustName Like @search
    End
    Else If @type = 'Supplier'
    Begin
        -- 仅查询供应商表，统一返回字段名
        Select 
            Supp_ID As ID,
            Supp_Name As Name,
            Supp_Email As Email,
            Supp_Phone As Phone,
            Supp_Mobile As Mobile,
            Supp_Address As Address
        From Supplier
        Where Supp_Name Like @search
    End
End

2. 修正C#代码 #

• 确保参数传递逻辑正确，避免顺序问题
• 修复文本框赋值覆盖问题
• 捕获异常时显示具体错误信息，便于调试

try
{
    using(SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("SearchCustSupplier", con);
        cmd.CommandType = CommandType.StoredProcedure;

        // 显式指定参数名，彻底避免顺序问题
        string searchType = Main_Form.TransactionType == "Purchase" ? "Supplier" : "Customer";
        cmd.Parameters.AddWithValue("@type", searchType);
        cmd.Parameters.AddWithValue("@search", "%" + txtSearchSC.Text.Trim() + "%");

        SqlDataAdapter da = new SqlDataAdapter(cmd);   
        DataTable dt = new DataTable();
        da.Fill(dt);    

        if(dt.Rows.Count > 0)
        {
            DataRow dr = dt.Rows[0];
            // 使用统一字段名赋值，避免覆盖
            txtSCName.Text = dr["Name"].ToString();    
            txtEmail.Text = dr["Email"].ToString();
            txtPhone.Text = dr["Phone"].ToString();
            txtMobile.Text = dr["Mobile"].ToString();
            txtAddress.Text = dr["Address"].ToString();
        }
        else
        {
            MessageBox.Show("未找到匹配的客户/供应商", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            // 清空文本框
            txtSCName.Clear();
            txtEmail.Clear();
            txtPhone.Clear();
            txtMobile.Clear();
            txtAddress.Clear();
        }
    }
}
catch (Exception ex)
{
    // 显示具体错误信息，方便排查问题
    MessageBox.Show($"错误信息：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

额外优化建议 #

• 避免使用AddWithValue，推荐显式指定参数类型和长度，防止SQL注入和类型转换问题：

  cmd.Parameters.Add(new SqlParameter("@type", SqlDbType.VarChar, 200) { Value = searchType });
  cmd.Parameters.Add(new SqlParameter("@search", SqlDbType.VarChar, 300) { Value = "%" + txtSearchSC.Text.Trim() + "%" });
  

• 对搜索文本做Trim()处理，避免多余空格导致搜索失效
• 若需同时查询客户和供应商并合并结果，可在存储过程中使用UNION ALL实现

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