How do i search a text in stored procedure using C#?

Posted by Jprathap under ADO.NET on 8/5/2013 | Points: 10 | Views : 12570 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

How do i search a text in stored procedure using C#?

pls explain with examples?

Thanks in advance

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

0	--This query will give you the procedure/function name where you have the searchText SELECT DISTINCT o.name AS Object_Name,o.type_desc, m.definition FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%SearchText%' Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jprathap, if this helps please login to Mark As Answer. \| Alert Moderator

--This query will give you the procedure/function name where you have the searchText

SELECT DISTINCT o.name AS Object_Name,o.type_desc, m.definition

FROM sys.sql_modules m 

INNER JOIN sys.objects o 

ON m.object_id=o.object_id

WHERE m.definition Like '%SearchText%'

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/5/2013 [Member] [MVP] Platinum | Points: 25

0	The following C# code explains you that whether the entered search string is available in any stored procedure of current database or not? <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:TextBox runat="server" ID="txtsearch" /> <asp:Button Text="Search" runat="server" ID="btnSearch" onclick="btnSearch_Click" /><br /> <asp:Label Text="" runat="server" ID="lblFb" ForeColor="Blue" /> </div> </form> </body> </html> protected void Page_Load(object sender, EventArgs e) { } protected void btnSearch_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=SDW2249; Initial Catalog=MedChive; User Id=testLogin; Password=span@1234"); if (conn.State == ConnectionState.Closed) { conn.Open();//error shows here The ConnectionString property has not been initialized. } SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%" +txtsearch.Text+"%'"; SqlDataReader dReader = cmd.ExecuteReader(); if (dReader.HasRows == true) { lblFb.Text = "Procedure with search string is existed"; } else { lblFb.Text = "Procedure with search string is NOT existed"; } } EDIT: Hi Prathap, Have you checked the above solution? Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jprathap, if this helps please login to Mark As Answer. \| Alert Moderator

The following C# code explains you that whether the entered search string is available in any stored procedure of current database or not?

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:TextBox runat="server" ID="txtsearch" /> 

        <asp:Button Text="Search" runat="server" ID="btnSearch" onclick="btnSearch_Click"  /><br />

        <asp:Label Text="" runat="server" ID="lblFb"  ForeColor="Blue" />

    </div>

    </form>

</body>

</html>

        protected void Page_Load(object sender, EventArgs e)

        {



        }



        protected void btnSearch_Click(object sender, EventArgs e)

        {

            SqlConnection conn = new SqlConnection("Data Source=SDW2249; Initial Catalog=MedChive; User Id=testLogin; Password=span@1234");

            if (conn.State == ConnectionState.Closed)

            {

                conn.Open();//error shows here The ConnectionString property has not been initialized. 

            }

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%" +txtsearch.Text+"%'";

            

            SqlDataReader dReader = cmd.ExecuteReader();

            if (dReader.HasRows == true)

            {

                lblFb.Text = "Procedure with search string is existed";

            }

            else 

            {

                lblFb.Text = "Procedure with search string is NOT existed";

            }

          }

EDIT: Hi Prathap, Have you checked the above solution?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/7/2013 [Member] [MVP] Platinum | Points: 25

0	what do you mean by "search a text in stored procedure" using C#? searching for a value of column in the table using stored procedure? can you explain your requirement clearly.. Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Jprathap, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Satyapriyanayak on: 8/7/2013 [Member] [MVP] Silver | Points: 25

0	Presentation Layer (aspx) <table cellpadding="0" cellspacing="0" width="70%"> <colgroup> <col width="15%" class="TableCol" /> <col width="85%" /> </colgroup> <tr> <td> <asp:Label ID="lblFirstName" runat="server" Text="First name" /> </td> <td> <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox> </td> </tr> <tr> <td> <asp:Label ID="lblLastName" runat="server" Text="Last name" /> </td> <td> <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox> </td> </tr> <tr> <td> <asp:Label ID="lblEmail" runat="server" Text="Email" /> </td> <td> <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> </td> </tr> <tr> <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" ValidationGroup="btnSearch" /> </tr> </table> Business logic layer (code behind page) /// <summary> /// Execute the SP by passing search criteria ///using parameters /// </summary> /// <returns>DataTable with search results</returns> private DataTable Search() { //DataTable to hold search resutls DataTable SearchResultsTable = new DataTable(); //Read connectionString from web.config SqlConnection conn = new SqlConnection(ConnectionString); try { //Create SQL command object by passing //connection and stored procedure name SqlCommand cmd = new SqlCommand("spSearch", conn); //command type to Stored procedure cmd.CommandType = CommandType.StoredProcedure; //pass search criteria using parameters cmd.Parameters.AddWithValue("@firstName",txtFirstName.Text); cmd.Parameters.AddWithValue("@lastName" , txtLastName.Text); cmd.Parameters.AddWithValue("@email " , txtEmail.Text); //create sql adapter by passing command object SqlDataAdapter adapter = new SqlDataAdapter(cmd); //fill the search results table adapter.Fill(SearchResultsTable); } catch (Exception ex) { Response.Write(ex.ToString(); } finally { if (conn != null) { conn.Close(); } } //Return search results DataTable return SearchResultsTable ; } } Search button click event protected void btnSearch_Click(object sender, EventArgs e) { //Bind search results to GridView control GridView1.DataSource = Search(); GridView1.DataBind(); } Stored Procedure Stored procedure that reads parameterised input from code behind, executes the SQL and returns search results. Create Procedure spSearch ( @firstName nvarchar(50) = null, @lastName nvarchar(50) = null, @email nvarchar(50) = null ) AS BEGIN //Set NULL when variable is NULL or lenght is zero If @firstName is not null and Len(@firstName )=0 Set @firstName = null If @lastName is not null and Len(@lastName )=0 Set @lastName = null If @email is not null and Len(@email )=0 Set @email = null Select * From Students Where ( @firstName is null or firstName Like @firstName ) and ( @lastName is null or lastName Like @lastName ) and ( @email is null or email Like @email ) END If this post helps you mark it as answer Thanks Jprathap, if this helps please login to Mark As Answer. \| Alert Moderator

Presentation Layer (aspx)



<table cellpadding="0" cellspacing="0" width="70%">

<colgroup>

    <col width="15%" class="TableCol" />

    <col width="85%" />

</colgroup>

<tr>

    <td>

        <asp:Label ID="lblFirstName" runat="server" 

Text="First name" />

    </td>

    <td>

        <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>

    </td>

</tr>

<tr>

    <td>

<asp:Label ID="lblLastName" runat="server" Text="Last name" />

    </td>

    <td>

<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>

    </td>

</tr>

<tr>

    <td>

<asp:Label ID="lblEmail" runat="server" Text="Email" />

 </td>

 <td>

<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<asp:Button ID="btnSearch" runat="server" Text="Search" 

OnClick="btnSearch_Click" ValidationGroup="btnSearch" />

</tr>

</table>



Business logic layer (code behind page)



/// <summary>

/// Execute the SP by passing search criteria

///using parameters

/// </summary>

/// <returns>DataTable with search results</returns>

private DataTable Search()

{



//DataTable to hold search resutls

DataTable SearchResultsTable = new DataTable();

//Read connectionString from web.config

SqlConnection conn = new SqlConnection(ConnectionString);



try

   {

//Create SQL command object by passing 

//connection and stored procedure name

SqlCommand cmd = new SqlCommand("spSearch", conn);

//command type to Stored procedure

cmd.CommandType = CommandType.StoredProcedure;



//pass search criteria using parameters

cmd.Parameters.AddWithValue("@firstName",txtFirstName.Text);

cmd.Parameters.AddWithValue("@lastName" , txtLastName.Text);

cmd.Parameters.AddWithValue("@email " , txtEmail.Text);



//create sql adapter by passing command object

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

//fill the search results table

adapter.Fill(SearchResultsTable);

}



catch (Exception ex)

        {

Response.Write(ex.ToString();

        }

        finally

        {

            if (conn != null)

            {

                conn.Close();

            }

        }

        

        //Return search results DataTable

        return SearchResultsTable ;

    }



}



Search button click event



protected void btnSearch_Click(object sender, EventArgs e)

{

//Bind search results to GridView control

GridView1.DataSource = Search();

GridView1.DataBind();

}



Stored Procedure



Stored procedure that reads parameterised input from code behind, executes the SQL and returns search results.



Create Procedure spSearch

( 

@firstName nvarchar(50) = null,

@lastName nvarchar(50) = null,

@email nvarchar(50) = null

) 

AS 

BEGIN

//Set NULL when variable is NULL or lenght is zero

If @firstName is not null and Len(@firstName )=0 Set @firstName = null

If @lastName is not null and Len(@lastName )=0 Set @lastName = null

If @email is not null and Len(@email )=0 Set @email = null



Select 



*

From Students

Where 

( @firstName is null or firstName Like @firstName )

and ( @lastName is null or lastName Like @lastName )

and ( @email is null or email Like @email )

END

If this post helps you mark it as answer
Thanks

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Snaveen on: 2/14/2014 [Member] Starter | Points: 25

0	Hi, If you want to search data based on enter text in textbox then you just prepare your stored procedure based on your need. create procedure searched_data ( @search varchar(100)=null ) as begin select * from tablename where col like '%'+@search+'%' end and in your application you just pass that textbox value as a input parameter, then it will display the result based on searched content. Jprathap, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

If you want to search data based on enter text in textbox then you just prepare your stored procedure based on your need.

create procedure searched_data

(

    @search varchar(100)=null

)

as

begin

   select * from tablename where col like '%'+@search+'%'

end

and in your application you just pass that textbox value as a input parameter, then it will display the result based on searched content.

Jprathap, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts