Aishna Aishna - 3 months ago 14
SQL Question

How to fetch data according to my search in a grid

In textbox I have to type string which will be searched in a table. if data is found in any of the 3 columns data will be shown in a gridview.3 columns are title, description and keywords. If title chk box is checked it will search through title and if keyword s checked it will search in it. if any of the checkbox is not checked it wont search in tht particular column. I have 2 radio button also one of them need to be selected.if and_radio is clicked it will search data in both columns if or is clicked idf data is found in any of the column will be shown.... Help trying it for a long...It has to be done with procedure.. What code should i have to write on search button click for searching data in textbox and then populate the result in grid
STORED PROCEDURE i used

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[sp_Normal_Search_Library]

@title AS nvarchar(max),
@Description AS nvarchar(max),
@Keywords AS nvarchar(max),
@Chk_title AS BIT ,
@Chk_Description AS Bit ,
@Chk_Keywords AS BIT,
@RD_AND AS BIT,
@RD_OR AS BIT


AS
if @RD_AND = 1 Begin
if @Chk_title = 1
Begin
Select title from server_des where title Like '%'+@title+'%'
End
if @Chk_Description=1
Begin
Select Description from server_des where Description Like '%'+@Description+'%'
End
if @Chk_Keywords=1
Begin
Select Keywords from server_des where Keywords Like '%'+@Keywords+'%'
End
if @Chk_title = 1 AND @Chk_Description = 1
Begin
Select title, Description from server_des where title Like '%'+ @title+'%' AND Description Like '%'+@Description+'%'
End
if @Chk_Description=1 AND @Chk_Keywords=1
Begin
Select Description, Keywords from server_des where Description Like'%'+@Description+'%' AND Keywords Like '%'+@Keywords+'%'
End
if @Chk_title=1 AND @Chk_Keywords=1
Begin
Select title, Keywords from server_des where title Like'%'+@title+'%' AND Keywords Like'%'+@Keywords+'%'
End
if @Chk_title=1 AND @Description=1 AND @title=1
Begin
Select title,Description, Keywords from server_des where title Like '%'+@title+'%'AND Description Like '%'+@Description+'%' AND Keywords Like '%' +@Keywords+'%'
End
End
ELSE IF @RD_OR=0
Begin
if @Chk_title = 1
Begin
Select title from server_des where title Like'%'+ @title+'%'
End
if @Chk_Description=1
Begin
Select Description from server_des where Description Like '%'+@Description+'%'
End
if @Chk_Keywords=1
Begin
Select Keywords from server_des where Keywords Like '%'+@Keywords+'%'
End
if @Chk_title = 1 AND @Chk_Description = 1
Begin
Select title, Description from server_des where title Like '%'+ @title+'%' AND Description Like '%'+@Description+'%'
End
if @Chk_Description=1 AND @Chk_Keywords=1
Begin
Select Description, Keywords from server_des where Description Like '%'+@Description+'%' AND Keywords Like '%'+@Keywords+'%'
End
if @Chk_title=1 AND @Chk_Keywords=1
Begin
Select title, Keywords from server_des where title Like '%'+@title+'%' AND Keywords Like '%'+@Keywords+'%'
End
if @Chk_title=1 AND @Description=1 AND @title=1
Begin
Select title, Description, Keywords from server_des where title Like'%'+@title+'%'AND Description Like '%'+@Description+'%' AND Keywords Like '%' +@Keywords+'%'
End
End


In business logic I created method:

public DataTable Fillgrid(string title,string Description, string Keywords)
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["LibrarySql"].ConnectionString;
SqlCommand cmd = new SqlCommand();

cmd.CommandText = "sp_Normal_Search_Library";
cmd.CommandType = CommandType.StoredProcedure;
//string query = "select * from Server_des";
cmd.Parameters.AddWithValue("@title", title);
cmd.Parameters.AddWithValue("@Description",Description);
cmd.Parameters.AddWithValue("@Keywords",Keywords);
cmd.Connection = con;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sda.Fill(dt);


return dt;


Now how do I populate data in the grid on my web form?

Answer

DataGrid.DataSource = Fillgrid("title","description","keyword"); DataGrid.Bind();

Comments