c# - 通过单击按钮使用带参数的存储过程在 asp.net winform 中填充 ListView

Question

在我的 Winforms 应用程序中，我必须使用三个参数进行搜索。我使用存储过程从数据库中检索。但是抛出一个异常:

Procedure or function 'sp_searchProduct' expects parameter '@categoryId', which was not supplied

请帮助我。

这是我的代码:

private void button2_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-U1OP1S9\SQLEXPRESS;Initial Catalog=PaintStores;Integrated Security=True");

    SqlCommand command = new SqlCommand("sp_searchProduct", con);
    command.Parameters.AddWithValue("@categoryId", comboBox1.SelectedValue.ToString());
    command.Parameters.AddWithValue("@typeId", comboBox2.SelectedValue.ToString());
    command.Parameters.AddWithValue("@productId", comboBox3.SelectedValue.ToString());

    SqlDataAdapter da = new SqlDataAdapter(command);
    DataTable dataTable = new DataTable();
    da.Fill(dataTable);

    for (int i = 0; i < dataTable.Rows.Count; i++)
    {
        DataRow drow = dataTable.Rows[i];

        if (drow.RowState != DataRowState.Deleted)
        {
            ListViewItem lvi = new ListViewItem(drow["ProductId"].ToString());
            lvi.SubItems.Add(drow["ProductName"].ToString());
            lvi.SubItems.Add(drow["TypeName"].ToString());
            lvi.SubItems.Add(drow["Quantity"].ToString());
            lvi.SubItems.Add(drow["Price"].ToString());
            lvi.SubItems.Add(drow["Stock"].ToString());

            listView1.Items.Add(lvi);
        }
    }

    con.Close();
}

这是我的存储过程:

ALTER PROCEDURE [dbo].[sp_searchProduct]
    @categoryId INT,
    @typeId INT,
    @productId INT
AS
    IF (@categoryId = 0)
    BEGIN
        SET @categoryId = NULL
    END

    IF (@typeId = 0)
    BEGIN
        SET @typeId = NULL
    END

    IF (@productId = 0)
    BEGIN
        SET @productId = NULL
    END
BEGIN
    SET NOCOUNT ON;

    SELECT p.ProductId, p.ProductName, p.Quantity, p.Price, p.Stock
    FROM tblProductNew p
    INNER JOIN tblTypes t ON t.TypeId = p.TypeId
    WHERE p.ProductId = ISNULL(@productId, p.ProductId) 
      AND p.TypeId = ISNULL(@typeId, p.TypeId) 
      AND t.MainCategoryId = ISNULL(@categoryId, t.MainCategoryId)
END

Answer 1

确保在调用 da.Fill(dataTable);

之前将其添加到代码中

command.CommandType = CommandType.StoredProcedure;

你的最终代码应该是这样的..

SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-U1OP1S9\SQLEXPRESS;Initial Catalog=PaintStores;Integrated Security=True");
SqlCommand command = new SqlCommand("sp_searchProduct", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@categoryId",comboBox1.SelectedValue.ToString());