Microsoft Data Access Block for .NET and the "like" statement

[Paul Wolpe]

Is there a way to use a like statement with a SqlParameter and the
Microsoft Data Access Block?

I would like to be able to do this:

int CustomerID = (int) SqlHelper.ExecuteScalar(
ConfigurationSettings.AppSettings["connectionString"],
CommandType.Text,
@"select CustomerId
from Northwind..Customers
where ContactName like '%@customer_name%'",
new SqlParameter("@customer_name",CustomerName)
);

Since the DAB already quotes my SqlParameter, SqlServer ends up seeing
extra quote signs. Something like:

where ContactName like '%'John Doe'%'

Any thoughts?

-Paul

Microsoft Data Access Block:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

 

[Dave Fancher]

I'm not really an expert on the data access block but you may be able to
use:
.... where ContactName like '%' + @customer_name + '%'

My understanding (someone please correct me if I'm wrong) is that the Data
Access Block does little more than provide a mechanism to simplify using the
ADO.NET classes. This being said, SQL Server, not the DAB, is manipulating
the parameters. If the DAB was actually processing your input and passing "
.... like '%'John Doe'%'", SQL Server would generate a syntax error.

What your command text is actually looking for is ContactName like
'%@customer_name%' rather than '%John Doe%'. The parameter is not parsed
since it is part of a string constant. Since you probably don't have any
customers named '@customer_name' or even like '%@customer_name%', the query
will return 0 rows. In the method I suggest, since the parameter is outside
of the string constant, the parameter will be parsed and value will then
concatenated with the % signs for your like statement allowing your query to
function as expected.

Hope this helps!

Is there a way to use a like statement with a SqlParameter and the
Microsoft Data Access Block?

I would like to be able to do this:

int CustomerID = (int) SqlHelper.ExecuteScalar(
ConfigurationSettings.AppSettings["connectionString"],
CommandType.Text,
@"select CustomerId
from Northwind..Customers
where ContactName like '%@customer_name%'",
new SqlParameter("@customer_name",CustomerName)
);

Since the DAB already quotes my SqlParameter, SqlServer ends up seeing
extra quote signs. Something like:

where ContactName like '%'John Doe'%'

Any thoughts?

-Paul

Microsoft Data Access Block:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp