.NET系统中实现数据库项任意位置子字符串搜索的方案咨询

Alright, let's get that search function working the way you want—supporting matches anywhere in the animal name instead of just the start. Here's the step-by-step fix for your stored procedure and C# code:

1. Fix the Stored Procedure #

Your current stored procedure has two main issues: it only does prefix matching (LIKE @p_nm_animal +'%') and uses ELSE IF which blocks multiple filters from working together. Here's the updated version:

/****** Object: StoredProcedure [dbo].[sp_search_animal] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_search_animal]
(
    @p_id_animal int = null,
    @p_nm_animal varchar(200) = null
)
AS
DECLARE @sql_where NVARCHAR(1000),
        @sql_expr NVARCHAR(2000),
        @sql_orderby NVARCHAR(500),
        @sql_groupby NVARCHAR(500);
BEGIN
    -- Initialize the base query (your original code missed this, which would break the SQL)
    SET @sql_expr = 'SELECT d.id_animal, d.nm_animal FROM [YourAnimalTableName] d WHERE 1=1 ';
    SET @sql_orderby = ' ORDER BY d.nm_animal ';
    SET @sql_where = '';

    -- Check each parameter separately (no ELSE IF, so both filters can work together)
    IF @p_id_animal IS NOT NULL
        SET @sql_where = @sql_where + ' AND d.id_animal = @p_id_animal ';

    IF @p_nm_animal IS NOT NULL AND @p_nm_animal <> ''
        -- Wrap the parameter with % for any-position matching, and use UPPER() to avoid case sensitivity
        SET @sql_where = @sql_where + ' AND UPPER(d.nm_animal) LIKE ''%'' + UPPER(@p_nm_animal) + ''%'' ';

    SET @sql_expr = @sql_expr + @sql_where + @sql_orderby;
    EXECUTE sp_executesql @sql_expr, 
        N'@p_id_animal int = NULL, @p_nm_animal VARCHAR(200) = NULL', 
        @p_id_animal, @p_nm_animal;
END
GO

Key Changes: #

• Added the base SELECT statement to @sql_expr (your original code didn't initialize this, so the final SQL would be invalid)
• Swapped ELSE IF for separate IF checks so both ID and name filters can run at the same time
• Changed LIKE @p_nm_animal +'%' to LIKE '%' + @p_nm_animal + '%' to match substrings anywhere in the name
• Added UPPER() around both the column and parameter to make the search case-insensitive
• Added a check for empty strings on @p_nm_animal to avoid returning every animal when the search box is empty

2. Tweak the C# Code (Optional but Recommended) #

Your C# code already converts the search term to uppercase, which pairs perfectly with the stored procedure's UPPER() handling. We can add a few checks to make it more robust:

[HttpPost]
public ActionResult PerformSearch(FormCollection collection)
{
    var parameters = new List<DataAccessParameter>();
    
    // Only add ID parameter if it's not empty
    var idAnimalInput = collection["id_animal"].ToDatabase();
    if (!string.IsNullOrEmpty(idAnimalInput))
    {
        parameters.Add(new DataAccessParameter("@p_id_animal", System.Data.DbType.Int32, idAnimalInput));
    }
    
    // Trim whitespace and check for empty before adding the name parameter
    var nmAnimalInput = collection["nm_animal"]?.Trim().ToUpper().ToDatabase();
    if (!string.IsNullOrEmpty(nmAnimalInput))
    {
        parameters.Add(new DataAccessParameter("@p_nm_animal", System.Data.DbType.String, nmAnimalInput));
    }

    var list = DataHelper.Execute<BVAnimalSearch>(
        System.Data.CommandType.StoredProcedure,
        "sp_search_animal",
        parameters,
        dr => new BVAnimalSearch
        {
            id_animal = dr.ToInt("id_animal"), // Note: Your original code used id_doenca_animal, which looks like a typo
            nm_animal = dr.ToString("nm_animal") // Same here—nm_doenca_animal was probably a mistake
        }).ToList();

    return new ObjectResult<object>(new { success = true, data = list, total = list.Count });
}

Key Adjustments: #

• Added checks to avoid adding empty parameters to the query (prevents unnecessary filters)
• Fixed a likely typo where you were assigning id_doenca_animal and nm_doenca_animal instead of matching the stored procedure's id_animal and nm_animal fields
• Trimmed whitespace from the name input to avoid accidental spaces breaking the search

3. Test It Out #

After making these changes, test these scenarios to confirm it works:

• Search for Lion—you should get Lion, Lion fish, and Mountain Lion
• Search for M—all animals with an M anywhere in their name (including Mountain Lion) should show up
• Try combining an animal ID and a name term—only animals matching both should return

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