Using 'Like' operator in parameterized queries

The main advantage of parameterized query is to protect the database from SQL Injection. Today I used this concept in my one of the project. Do you know using ‘Like’ operator in parameterized query is bit different?

First, let’s look at sample project to explore this.

My ASPX Page:

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1" EmptyDataText="No data to display.">

    <Columns>

        <asp:BoundField DataField="id" HeaderText="ID" SortExpression="id" />

        <asp:BoundField DataField="name" HeaderText="Name" SortExpression="name" />

        <asp:BoundField DataField="mobile" HeaderText="Mobile" SortExpression="mobile" />

        <asp:BoundField DataField="address" HeaderText="Address" SortExpression="address" />

    </Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource2" runat="server"

    ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>"

    ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString1.ProviderName %>"

    SelectCommand="??">

    <SelectParameters>

        <asp:Parameter Name="canName"/>

    </SelectParameters>

</asp:SqlDataSource>

Code-Behind Page:

    protected void btnSearch_Click(object sender, EventArgs e)

    {

        string name = txtStudentNameToSearch.Text;

        SqlDataSource1.SelectParameters["canName"].DefaultValue = name;

    }

So, what query you will write for above SelectCommand?

"SELECT * FROM [TableName] WHERE name LIKE '%@canName%'"

OR

"SELECT * FROM [TableName] WHERE name LIKE '%'+ @canName + '%'"

At very first try, you will use first one but this will not work for you, you need to use second one.