Search

Thursday, January 16, 2014
0 comments

How to search item from database using stored procedure in ASP.NET

4:41 AMThursday, January 16, 2014
Step-1: First create a Database Table in visual studio
I have some columns like

SNO    int      primarykey with auto increment by 1
Title      nvarchar(50)  Allow Null
MetaDescription  nvarchar(MAX)  Allow Null
URL   nvarchar(200) Allow Null

Step-2: Add TextBox, Button and GridView control into the .aspx page
Step-3: Create a static class for connection.
Step-4: Handle Button_click event

<p>
        Enter Text Here :
        <asp:TextBox ID="TextBox1" runat="server" Height="32px" Width="237px"></asp:TextBox>
    </p>
    <p>
        <asp:Button ID="Button1" runat="server" Height="33px" onclick="Button1_Click" 
            Text="Search" Width="122px" />
    </p>
    <p>
        <asp:GridView
         AutoGenerateColumns ="false"
          ID="GridView1" runat="server"
           Height="180px" Width="426px"
                        GridLines ="None" 
                                AllowPaging="True" 
            onpageindexchanging="GridView1_PageIndexChanging" PageSize="5" 
            ShowHeader="False">

           <Columns>
           <asp:TemplateField>
              <ItemTemplate>
                  <asp:HyperLink ID="HyperLink1" runat="server" Text ='<%# Eval("title") %>' NavigateUrl='<%# Eval("URL") %>'/>
                  <br />
                  <asp:Label ID="Label1" runat="server" Text='<%# Eval("title") %>'/><br />
               <asp:Label ID="description" runat="server" Text='<%# Eval("metaDescription") %>'/>
               <asp:Label ID="Label2" runat="server" Text='<%# Eval("keywords") %>'/>
              </ItemTemplate>
               
           </asp:TemplateField>
           
          
           </Columns>
        
        </asp:GridView>
    </p>
<p>
        &nbsp;</p>

.aspx.cs file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    DataSet ds = new DataSet();
        
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        loadgrid();

    }

    private void loadgrid()
    {
        con.ConnectionString = connection.Connectionstring;
        con.Open();
        cmd.CommandText = "GetEngine";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@parameter", TextBox1.Text);
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        loadgrid();
    }
}

Connection Class  file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

/// <summary>
/// Summary description for connection
/// </summary>
public static class connection
{
    private static string DBConnectionString;
static connection()
{
        DBConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
}
    public static string Connectionstring
    {
        get
        {
            return DBConnectionString;
        }
    
    }
}

Stored Procedure code

Create Procedure GetEngine
@parameter nvarchar(100)
As
Select * from SearchEngine where Title like '%'+@parameter+'%' or MetaDescription like '%'+@parameter+'%' or  URL like '%'+@parameter+'%'


Code generate the following output

How to search item from database using stored procedure in ASP.NET

Protected by Copyscape Online Copyright Protection Software

0 comments :

Post a Comment

 
Toggle Footer
Top