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

Posted by Jprathap under ADO.NET on 8/5/2013 | Points: 10 | Views : 13446 | Status : [Member] | Replies : 4
How do i search a text in stored procedure using C#?

pls explain with examples?

Thanks in advance




Responses

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

Up
0
Down
--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

Up
0
Down
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

Up
0
Down
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: Snaveen on: 2/14/2014 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response