Implement search web page using ASP NET and Stored Procedure

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In this video we will discuss implementing a search web page using ASP.NET and Stored Procedure.

Step 1 : Modify the "spSearchEmployees" stored procedure to include NULL as the default value for the parameters. The advantage of specifying default value for the parameters is that the ASP.NET page need not pass those parameters when calling the stored procedures if the user did not specify any values for the corresponding search fields on the Search Page.

Alter Procedure spSearchEmployees
@FirstName nvarchar(100) = NULL,
@LastName nvarchar(100) = NULL,
@Gender nvarchar(50) = NULL,
@Salary int = NULL
As
Begin

Select * from Employees where
(FirstName = @FirstName OR @FirstName IS NULL) AND
(LastName = @LastName OR @LastName IS NULL) AND
(Gender = @Gender OR @Gender IS NULL) AND
(Salary = @Salary OR @Salary IS NULL)
End
Go

Step 2 : Create a new empty ASP.NET Web Forms application. Name it "DynamicSQLDemo".

Step 4 : Add a WebForm to the project. Name it "SearchPageWithoutDynamicSQL.aspx"

Step 5 : Copy and paste the HTML on my blog at the following link on the ASPX page. Notice we are using Bootstrap to style the page. If you are new to Bootstrap, please check out our Bootstrap tutorial for beginners playlist.

Step 6 : Copy and paste the following code in the code-behind page. Notice we are using the stored procedure "spSearchEmployees". We are not using any dynamic SQL in this example. In our next video, we will discuss implementing the same "Search Page" using dynamic sql and understand the difference between using dynamic sql and stored procedure.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace DynamicSQLDemo
{
public partial class SearchPageWithoutDynamicSQL : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}

protected void btnSearch_Click(object sender, EventArgs e)
{
string strConnection = ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;

using (SqlConnection con = new SqlConnection(strConnection))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spSearchEmployees";
cmd.CommandType = CommandType.StoredProcedure;

if (inputFirstname.Value.Trim() != "")
{
cmd.Parameters.Add(param);
}

if (inputLastname.Value.Trim() != "")
{
cmd.Parameters.Add(param);
}

if (inputGender.Value.Trim() != "")
{
cmd.Parameters.Add(param);
}

if (inputSalary.Value.Trim() != "")
{
cmd.Parameters.Add(param);
}

con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
gvSearchResults.DataSource = rdr;
gvSearchResults.DataBind();
}
}
}
}

[krismaly]

Awesome video.

I recommend others to watch this video where in steps, functionalities explained crisply.

Thanks for educating the community and appreciate your volunteership.

Thanks a bunch

[satyendersaini]

one of the best tutorials...thanks sir for great tutorials

[sohanyennu]

I have to say...its simply excellent...keep up the good work..

[aliasadhassan]

much awaited tutorial... than you venkat

[pet]

Thanks for sharing!!
Excelent video!

[brandoncluff]

Is there a tutorial to teach us how to bind more columns to the results? I basically want to search, and then have input forms and an update button. A form within a form I guess. Any advice?

[PragatiPrakash]

tried this tutorial.. My search procedure executes fine, but when I search on the front end it gives me all values and does not filter with what I searched. Any help???

[mohdhashim]

public static void Main()
{
Hi Seekha Aapse Hi Jaana, Ap Hi Ko Humne Guru Hy Manna, Seekha Hy Sb Kuch Aapse Humne Shiksha Ka Matlb Aapse Hy
}

[rohitkumawat]

there is nothing like .value i did not find any class which contains .Value else put .Text

[netpoliss]

thanks for all of your videos. all of them are very useful. Abaut dynamic sql, i usually use sql COALESCE function. it can be use dynamic sql query.

SELECT * FROM Employee
WHERE

can you tell this sql function query performance next video, please.

[siddardhagokaraju]

If statement is not necessary while passing the param, If we comment that and use 'or' instead of 'and' in stored proc we can search using any one parameter Fname, Lname, Gender or Salary.