Ask your technical questions on forums or here :
ASP.NET or MVC | C# | Windows Phone
Microsoft Technology Journals by Abhimanyu K Vatsa
HOME ABOUT RAZOR BOOK SPEAKING MVC ASP.NET JQUERY VIDEOS EBOOK ARCHIVE

23 Jul 2012

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.

Comment using Google Services (2 comments):