This article explains on searching in asp.net website and get the results and bind in gridview.
Introduction
Hello DNF Members, today we will be looking into very simple but very useful concept of search in asp.net,where the data comes from the database on the base of the search criteria.This is very helpful to requirement of developing admin panel for website
Objective
To make search easy in asp.net with Interaction of the database.
Using the code
We use one textbox where input is entered as the search criteria.If the search criteria matches the value in the database results will be displayed in gridview.
In this article, we have one table Category and searching the category by entering the first two alphabets.
One stored procedure where the procedure requires one parameter i.e. input for search criteria and that will get the data from the Table of Category.
Block of code should be set style as "Code" like below.
//Script for Database
create database SFW
// Script for Table
create table CategoryMaster
(
CategoryID int identity(1,1)
CategoryName varchar(50),
Rowdeleted tinyint
)
// Script for Stored Procedure
ALTER procedure [dbo].[GetCategories4Autocomp]
(
@input varchar(250)
)
as
BEGIN
Select distinct top 10 CategoryName from CategoriesMaster
where CategoryName like @input + '%' and RowDeleted = 0
END
//HTML Mark UP
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Search.aspx.cs" Inherits="Search" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div align="center">
Enter Search Criteria :
<asp:TextBox ID="xtxtSearch" runat="server"></asp:TextBox> <br /><br />
<asp:Button ID="btnSearch" runat="server" Text="Search"
onclick="btnSearch_Click" />
<br />
<br />
<br />
<br />
<asp:GridView ID="xgvDetails" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="CategoryName" HeaderText="Category Name" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class Search : System.Web.UI.Page
{
string strconn = ConfigurationManager.ConnectionStrings["cnnlocal"].ToString();
SqlConnection sqlcon = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
}
private void SearchData()
{
sqlcon = new SqlConnection(strconn);
cmd = new SqlCommand("GetCategories4Autocomp", sqlcon); // calling the stored procedure
cmd.CommandType = CommandType.StoredProcedure;
da = new SqlDataAdapter(cmd);
da.SelectCommand.Parameters.AddWithValue("@input", SqlDbType.VarChar).Value = xtxtSearch.Text; // passing parameter to the procedure to the database
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
xgvDetails.DataSource = ds;
xgvDetails.DataBind();
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
SearchData();
}
}Search Page
Results Fetched.
Conclusion
Hope this will get basic functionality working and it will help in application.