When I run a C # parameterized query the time expires. The same query with hardcoded parameters works fine
I want to get all ids where the "Text" column contains a row filter.
When I run this parameterized query, it expires:
SqlCommand cmd =
new SqlCommand("SELECT ID FROM TableName WHERE Text LIKE @filter", conn);
if (filter != null)
{
cmd.Parameters.AddWithValue("@filter", "%" + filter + "%");
SqlDataReader reader;
reader = cmd.ExecuteReader(); //Locks up here!
}
When I run the same code, but with hardcoded search parameters, it returns in a timely manner with the results I wanted:
SqlCommand cmd =
new SqlCommand("SELECT ID FROM TableName WHERE Text LIKE '%patternToMatch%'", conn);
SqlDataReader reader;
reader = cmd.ExecuteReader();
How are these two questions different? I think parameterization does more than just replace text.
CONFIRMATION: I am talking to Microsoft SQL Server 2012.
NEW: It only expires if the search filter is longer than 6 characters.
EDIT: RESOLVED! I set up search as a stored procedure:
CREATE PROCEDURE TextSearch
@filter varchar(MAX) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT ID FROM TableName WHERE [Text] LIKE '%' + @filter + '%'
END
GO
+3
source to share
2 answers
DECIDE! I set up search as a stored procedure:
SQL:
CREATE PROCEDURE TextSearch
@filter varchar(MAX) = ''
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT ID FROM TableName WHERE [Text] LIKE '%' + @filter + '%'
END
GO
FROM#:
SqlCommand cmd = new SqlCommand("TextSearch", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@filter", filter));
It now runs as fast as I expect for requests of all lengths.
-1
source to share