<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>SQL Demos: Using SQL LIKE (Example 1)</title>
<link rel="stylesheet" href="/shared/netdemos.css">
<script runat="server" src="~/shared/fetchData_sql.cs"></script>
<script runat="server">
string key, query;
void Page_Load ( object src, EventArgs e ) {
searchBox.Focus ( );
key = searchBox.Text;
if ( key == "" ) return;
string [ ] split = key.Split ( new Char [ ] { ' ' } );
if ( split.Length > 1 ) {
query = "SELECT * FROM aspx_articles WHERE ";
foreach ( string s in split ) {
if ( s.Trim ( ) != "" ) {
query += "( title LIKE '%" + s + "%' OR url LIKE '%" + s + "%' ) AND ";
}
}
query = query.Substring ( 0, query.Length - 4 );
} else {
query = "SELECT * FROM aspx_articles WHERE ( title LIKE '%" + key + "%' OR url LIKE '%" + key + "%' )";
}
query += " ORDER BY title";
searchGrid.DataSource = fetchData ( query );
searchGrid.DataBind ( );
}
void setBindings ( object src, GridViewRowEventArgs e ) {
if ( e.Row.RowType == DataControlRowType.DataRow ) {
DataRowView rowView = ( DataRowView ) e.Row.DataItem;
Label type = ( Label ) e.Row.FindControl ( "type" );
if ( rowView [ "url" ].ToString ( ).IndexOf ( "demos" ) > -1 ) {
type.Text = "Demo";
HyperLink navLink = ( HyperLink ) e.Row.Cells [ 0 ].Controls [ 0 ];
navLink.Target = "demo";
}
else if ( rowView [ "url" ].ToString ( ).IndexOf ( "syntax" ) > -1 ||
rowView [ "url" ].ToString ( ).IndexOf ( "sys" ) > -1 ||
rowView [ "url" ].ToString ( ).IndexOf ( "refs" ) > -1 ||
rowView [ "url" ].ToString ( ).IndexOf ( "microsoft" ) > -1 )
type.Text = "Reference";
else type.Text = "Tutorial";
}
}
void setPager ( Object src, GridViewRowEventArgs e ) {
if ( e.Row.RowType == DataControlRowType.Pager ) {
TableCellCollection myRow = e.Row.Cells;
myRow.AddAt ( 0, new TableCell ( ) );
myRow [ 0 ].Text = "Page " + ( searchGrid.PageIndex + 1 ) + " of " + searchGrid.PageCount;
myRow [ 0 ].HorizontalAlign = HorizontalAlign.Left;
}
}
void pageChanged ( object src, GridViewPageEventArgs e ) {
searchGrid.PageIndex = e.NewPageIndex;
searchGrid.DataBind ( );
}
</script>
<body>
<div class="header"><h2>Using <b>SQL LIKE</b> (Example 1)</h2></div>
<!-- #include virtual="~/shared/viewsrc_top.inc" -->
<hr size=1 width=92%>
<center>
<form runat="server">
<p align="center">Search for <asp:textbox id="searchBox" runat="server" />
<asp:button text="Search" runat="server" /></p>
<asp:gridview id="searchGrid" runat="server"
width="80%" cellpadding=5 font-size="10pt"
autogeneratecolumns=false
allowsorting allowpaging pagesize=12
onRowCreated="setPager"
onRowDataBound="setBindings"
onPageIndexChanging="pageChanged">
<headerstyle font-bold backcolor="lightsteelblue" />
<rowstyle verticalalign="top" />
<pagersettings position="top"
mode="nextprevious"
nextpagetext="Next"
previouspagetext="Prev" />
<pagerstyle font-bold
backcolor="cadetblue" forecolor="beige"
horizontalalign="right" />
<columns>
<asp:hyperlinkfield headertext="Title"
datatextfield="title"
datanavigateurlfields="url"
datanavigateurlformatstring="~/{0}" />
<asp:templatefield>
<headertemplate>Content Type</headertemplate>
<itemtemplate>
<asp:label id="type" runat="server" />
</itemtemplate>
</asp:templatefield>
<asp:boundfield headertext="Last Updated"
datafield="updated"
dataformatstring="{0:d}" />
</columns>
<emptydatatemplate>
<div style="font:bold 13pt arial; color:navy; text-align:center">
<p>Sorry, there was no match found for <i><%= searchBox.Text %></i>. </div>
</emptydatatemplate>
</asp:gridview>
</form>
</center>
<% if ( searchGrid.Rows.Count > 0 ) {%>
<hr size=1 width=92%>
<!-- #include virtual="~/shared/viewsrc.inc" -->
<% } %>
</body>
</html>