如何在jQuery文本框自动补全的C#代码中加入Session['location']变量

Hey there! Let's get your autocomplete filtering working with the Session["location"] value smoothly. Here's how to integrate it properly into your existing code:

Step 1: Update Your C# Backend Logic #

First, we'll modify your SQL command to include the location filter from the session, while keeping things safe with parameterized queries (to avoid SQL injection) and adding a quick check for null session values. Here's the complete adjusted code:

// Assuming this is in your autocomplete handler (like an .ashx file or MVC action)
using (SqlConnection conn = new SqlConnection(yourDatabaseConnectionString))
{
    conn.Open();
    string searchPrefix = Request.QueryString["term"]; // This comes from jQuery's autocomplete request

    // Guard clause: Make sure Session["location"] has a value to avoid null errors
    if (Session["location"] == null)
    {
        // Handle missing location - return empty results or add your own fallback logic
        Response.Write("[]");
        return;
    }

    string userLocation = Session["location"].ToString();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    // Your updated query with the Location1 filter
    cmd.CommandText = @"SELECT ContactName, CustomerId 
                        FROM Customers 
                        WHERE ContactName LIKE @SearchText + '%' 
                        AND Location1 = @Location1";

    // Add parameters securely
    cmd.Parameters.AddWithValue("@SearchText", searchPrefix);
    cmd.Parameters.AddWithValue("@Location1", userLocation);

    // Fetch and return results in JSON format (jQuery autocomplete expects this)
    List<dynamic> autocompleteResults = new List<dynamic>();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            autocompleteResults.Add(new
            {
                label = reader["ContactName"].ToString(),
                value = reader["CustomerId"].ToString()
            });
        }
    }

    // Serialize to JSON and send response
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    Response.Write(serializer.Serialize(autocompleteResults));
}

Key Things to Keep in Mind: #

• Parameterized Queries: Using @SearchText and @Location1 instead of string concatenation keeps your code safe from SQL injection attacks—this is non-negotiable for secure apps.
• Session Null Check: Always validate that Session["location"] exists before using it. The guard clause above prevents null reference errors; adjust the fallback logic (like returning empty results) to match your app's needs.
• Frontend No Changes: Since we're pulling the location directly from server-side Session, you don't need to touch your existing jQuery autocomplete code. It will keep sending the search term as usual, and the backend will handle the location filtering automatically.

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