Sql server, .net and c# video tutorial: Implement search web page using ASP.NET and Stored Procedure

Suggested Videos
Part 136 - Guid in SQL Server
Part 137 - How to check guid is null or empty in SQL Server
Part 138 - Dynamic SQL in SQL Server

In this video we will discuss implementing a search web page using ASP.NET and Stored Procedure. This is continuation to Part 138. Please watch Part 138 from SQL Server Tutorial before proceeding.

The search page looks as shown below.
how to search data using stored procedure in asp.net c#

(追記) (追記ここまで)

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

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

(追記) (追記ここまで)

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

Step 3 : Add the connection string to your database in web.config

<addname="connectionStr"

connectionString="server=.;database=EmployeeDB;integrated security=true"/>

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

Step 5 : Copy and paste the following HTML 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.

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

<headrunat="server">

<title>Employee Search</title>

<linkrel="stylesheet"

href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"

type="text/css"/>

</head>

<bodystyle="padding-top: 10px">

<divclass="col-xs-8 col-xs-offset-2">

<formid="form1"runat="server"class="form-horizontal">

<divclass="panel panel-primary">

<divclass="panel-heading">

<h3>Employee Search Form</h3>

</div>

<divclass="panel-body">

<divclass="form-group">

<labelfor="inputFirstname"class="control-label col-xs-2">

Firstname

</label>

<divclass="col-xs-10">

<inputtype="text"runat="server"class="form-control"

id="inputFirstname"placeholder="Firstname"/>

</div>

<divclass="form-group">

<labelfor="inputLastname"class="control-label col-xs-2">

Lastname

</label>

<divclass="col-xs-10">

<inputtype="text"runat="server"class="form-control"

id="inputLastname"placeholder="Lastname"/>

</div>

<divclass="form-group">

<labelfor="inputGender"class="control-label col-xs-2">

Gender

</label>

<divclass="col-xs-10">

<inputtype="text"runat="server"class="form-control"

id="inputGender"placeholder="Gender"/>

</div>

<divclass="form-group">

<labelfor="inputSalary"class="control-label col-xs-2">

Salary

</label>

<divclass="col-xs-10">

<inputtype="number"runat="server"class="form-control"

id="inputSalary"placeholder="Salary"/>

</div>

<divclass="form-group">

<divclass="col-xs-10 col-xs-offset-2">

<asp:ButtonID="btnSearch"runat="server"Text="Search"

CssClass="btn btn-primary"OnClick="btnSearch_Click"/>

</div>

<divclass="panel panel-primary">

<divclass="panel-heading">

<h3>Search Results</h3>

</div>

<divclass="panel-body">

<divclass="col-xs-10">

<asp:GridViewCssClass="table table-bordered"

ID="gvSearchResults"runat="server">

</asp:GridView>

</div>

</form>

</div>

</body>

</html>

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

{

publicpartialclassSearchPageWithoutDynamicSQL : System.Web.UI.Page

{

protectedvoid Page_Load(object sender, EventArgs e)

{}

protectedvoid btnSearch_Click(object sender, EventArgs e)

{

string connectionStr = ConfigurationManager

.ConnectionStrings["connectionStr"].ConnectionString;

using(SqlConnection con = newSqlConnection(connectionStr))

{

SqlCommand cmd = newSqlCommand();

cmd.Connection = con;

cmd.CommandText = "spSearchEmployees";

cmd.CommandType = CommandType.StoredProcedure;

if(inputFirstname.Value.Trim() != "")

{

SqlParameter param = newSqlParameter

("@FirstName", inputFirstname.Value);

cmd.Parameters.Add(param);

}

if (inputLastname.Value.Trim() != "")

{

SqlParameter param = newSqlParameter

("@LastName", inputLastname.Value);

cmd.Parameters.Add(param);

}

if (inputGender.Value.Trim() != "")

{

SqlParameter param = newSqlParameter

("@Gender", inputGender.Value);

cmd.Parameters.Add(param);

}

if (inputSalary.Value.Trim() != "")

{

SqlParameter param = newSqlParameter

("@Salary", inputSalary.Value);

cmd.Parameters.Add(param);

}

con.Open();

SqlDataReader rdr = cmd.ExecuteReader();

gvSearchResults.DataSource = rdr;

gvSearchResults.DataBind();

}

2 comments:

Loug July 26, 2019 at 7:26 AM
Hey, nice tutorial. I have just one question: is this the same if i'm working with an oracle database ?
Thank you again for the good work
AhmedDiab June 28, 2021 at 7:49 PM
Thanks your easy and simple way to explain , many thanks .

It would be great if you can help share these free resources

[フレーム]