SqlDataReader仅读取单行数据问题及按分类ID显示按钮咨询

Hey Rebecca, let's tackle your two issues one by one—first the SqlDataReader problem that's only pulling one row, then the dynamic button logic based on CategoryID values.

1. Fixing SqlDataReader Only Returning One Row #

This is super common for folks new to SqlDataReader—here are the most likely fixes:

• You forgot to loop with Read(): SqlDataReader starts positioned before the first row. Calling Read() once only gets the first row; you need to wrap it in a while loop to iterate through all results. Example:

  using (SqlConnection conn = new SqlConnection(yourConnectionString))
  {
      string query = "SELECT * FROM CART WHERE UserID = @UserID";
      using (SqlCommand cmd = new SqlCommand(query, conn))
      {
          cmd.Parameters.AddWithValue("@UserID", currentUserID);
          conn.Open();
          using (SqlDataReader reader = cmd.ExecuteReader())
          {
              // Loop through EVERY row in the result set
              while (reader.Read()) 
              {
                  // Access columns like this:
                  int cartID = (int)reader["CartID"];
                  string productName = reader["ProductName"].ToString();
                  // Do something with the data here
              }
          }
      }
  }
  

• Your SQL query is only returning one row: Double-check your SELECT statement—did you accidentally add TOP 1? Or does your WHERE clause filter to a single record? Test the query directly in SQL Server Management Studio to confirm it returns all expected rows.
• You're closing the reader/connection early: Always use using statements (like the example above) to handle resource disposal correctly. This prevents accidental closure of the reader before you've finished looping through all rows.

2. Dynamic Button Display Based on CategoryID #

Your goal is to show "Create Booking" if any product in the cart has a CategoryID of 1, 2, or 7; otherwise show "Delivery". Here's an efficient way to implement this:

Step 1: Use an Efficient SQL Check #

Instead of fetching all CategoryIDs (which wastes resources), use EXISTS to check if a matching record exists—this stops searching as soon as it finds a match:

SELECT CASE 
         WHEN EXISTS (
             SELECT 1 
             FROM CART c
             JOIN PRODUCTS p ON c.ProductID = p.ProductID
             WHERE c.UserID = @UserID -- Filter to the current user's cart
               AND p.CategoryID IN (1,2,7)
         ) THEN 1 
         ELSE 0 
       END AS ShowBookingButton

Step 2: Toggle Buttons in C# #

Use ExecuteScalar() to get the result quickly, then adjust button visibility:

bool showBookingButton = false;

using (SqlConnection conn = new SqlConnection(yourConnectionString))
{
    string query = @"SELECT CASE 
                             WHEN EXISTS (
                                 SELECT 1 
                                 FROM CART c
                                 JOIN PRODUCTS p ON c.ProductID = p.ProductID
                                 WHERE c.UserID = @UserID
                                   AND p.CategoryID IN (1,2,7)
                             ) THEN 1 
                             ELSE 0 
                           END AS ShowBookingButton";
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        cmd.Parameters.AddWithValue("@UserID", currentUserID);
        conn.Open();
        // Convert the result to a boolean
        showBookingButton = (int)cmd.ExecuteScalar() == 1;
    }
}

// Toggle button visibility
btnCreateBooking.Visible = showBookingButton;
btnDelivery.Visible = !showBookingButton;

Alternative: If You're Already Using SqlDataReader #

If you're already fetching cart data with a reader, you can track the CategoryID as you loop:

bool hasBookingCategory = false;

// Make sure your query joins PRODUCTS to include CategoryID
string query = "SELECT c.*, p.CategoryID FROM CART c JOIN PRODUCTS p ON c.ProductID = p.ProductID WHERE c.UserID = @UserID";

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        int categoryID = (int)reader["CategoryID"];
        if (categoryID is 1 or 2 or 7)
        {
            hasBookingCategory = true;
            break; // No need to check further once we find a match
        }
    }
}

btnCreateBooking.Visible = hasBookingCategory;
btnDelivery.Visible = !hasBookingCategory;

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