查询字符串为空时排除对应列的数据库搜索功能实现咨询

Hey there! Let's work through building this dynamic query logic where we only include filter conditions when the corresponding query string values are non-empty. Here's a step-by-step solution that's safe and clean:

First: Fix Parameter Handling (Avoid Exceptions!) #

Your original code uses Convert.ToInt32 directly for Port, which will throw an error if the query string has a non-numeric value (or no value at all). Let's use a nullable int and int.TryParse to handle this gracefully:

// Handle Port safely - use nullable int since it might not be provided/valid
int? port = null;
if (int.TryParse(Request.QueryString["Port"], out int parsedPort))
{
    port = parsedPort;
}

// Grab Status and TIN, no conversion needed but we'll check for empty later
var status = Request.QueryString["Status"];
var tin = Request.QueryString["TIN"];

Option 1: Using Entity Framework (LINQ to Entities) #

If you're using EF (the recommended approach for most .NET apps), you can dynamically build your query with LINQ. This is safe and avoids SQL injection automatically:

// Start with a base query
var query = db.YourTargetTable.AsQueryable();

// Add Port filter only if we have a valid value
if (port.HasValue)
{
    query = query.Where(record => record.Port == port.Value);
}

// Add Status filter only if the query string isn't null/empty
if (!string.IsNullOrEmpty(status))
{
    query = query.Where(record => record.Status == status);
}

// Add TIN filter only if the query string isn't null/empty
if (!string.IsNullOrEmpty(tin))
{
    query = query.Where(record => record.TIN == tin);
}

// Execute the query to get results
var matchingRecords = query.ToList();

Option 2: Using ADO.NET (Parameterized SQL) #

If you're working directly with ADO.NET, always use parameterized queries to avoid SQL injection. We'll build the SQL string dynamically while keeping parameters safe:

// Start with a base query that always evaluates to true (easy to append AND conditions)
var sqlBuilder = new StringBuilder("SELECT * FROM YourTargetTable WHERE 1=1");
var sqlParameters = new List<SqlParameter>();

// Add Port condition if valid
if (port.HasValue)
{
    sqlBuilder.Append(" AND Port = @Port");
    sqlParameters.Add(new SqlParameter("@Port", port.Value));
}

// Add Status condition if non-empty
if (!string.IsNullOrEmpty(status))
{
    sqlBuilder.Append(" AND Status = @Status");
    sqlParameters.Add(new SqlParameter("@Status", status));
}

// Add TIN condition if non-empty
if (!string.IsNullOrEmpty(tin))
{
    sqlBuilder.Append(" AND TIN = @TIN");
    sqlParameters.Add(new SqlParameter("@TIN", tin));
}

// Execute the query
using (var connection = new SqlConnection(YourDatabaseConnectionString))
{
    connection.Open();
    using (var command = new SqlCommand(sqlBuilder.ToString(), connection))
    {
        command.Parameters.AddRange(sqlParameters.ToArray());
        using (var reader = command.ExecuteReader())
        {
            // Process your results here (e.g., map to objects)
        }
    }
}

Key Notes: #

• Never skip parameterization: Directly concatenating user input into SQL strings is a huge security risk (SQL injection attacks). Both options above use safe parameterized queries.
• Check for null AND empty: For string parameters like Status and TIN, !string.IsNullOrEmpty() ensures we don't add a condition for empty strings or missing query string keys.
• Handle invalid Port values: Using int.TryParse prevents runtime exceptions when the Port query string is missing or non-numeric.

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