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

Posted by Jprathap under ADO.NET on 8/5/2013 | Points: 10 | Views : 10314 | Status : [Member] | Replies : 5
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: Satyapriyanayak on: 8/7/2013 [Member] [MVP] Silver | Points: 25

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

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