we will discuss implementing a search web page using ASP.NET and Stored Procedure.
The search page looks as shown below.
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 3 : Add the connection string to your database in web.config
<add name="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.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Employee Search</title>
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"
type="text/css" />
</head>
<body style="padding-top: 10px">
<div class="col-xs-8 col-xs-offset-2">
<form id="form1" runat="server" class="form-horizontal">
<div class="panel panel-primary">
<div class="panel-heading">
<h3>Employee Search Form</h3>
</div>
<div class="panel-body">
<div class="form-group">
<label for="inputFirstname" class="control-label col-xs-2">
Firstname
</label>
<div class="col-xs-10">
<input type="text" runat="server" class="form-control"
id="inputFirstname" placeholder="Firstname" />
</div>
</div>
<div class="form-group">
<label for="inputLastname" class="control-label col-xs-2">
Lastname
</label>
<div class="col-xs-10">
<input type="text" runat="server" class="form-control"
id="inputLastname" placeholder="Lastname" />
</div>
</div>
<div class="form-group">
<label for="inputGender" class="control-label col-xs-2">
Gender
</label>
<div class="col-xs-10">
<input type="text" runat="server" class="form-control"
id="inputGender" placeholder="Gender" />
</div>
</div>
<div class="form-group">
<label for="inputSalary" class="control-label col-xs-2">
Salary
</label>
<div class="col-xs-10">
<input type="number" runat="server" class="form-control"
id="inputSalary" placeholder="Salary" />
</div>
</div>
<div class="form-group">
<div class="col-xs-10 col-xs-offset-2">
<asp:Button ID="btnSearch" runat="server" Text="Search"
CssClass="btn btn-primary" OnClick="btnSearch_Click" />
</div>
</div>
</div>
</div>
<div class="panel panel-primary">
<div class="panel-heading">
<h3>Search Results</h3>
</div>
<div class="panel-body">
<div class="col-xs-10">
<asp:GridView CssClass="table table-bordered"
ID="gvSearchResults" runat="server">
</asp:GridView>
</div>
</div>
</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
{
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 connectionStr = ConfigurationManager
.ConnectionStrings["connectionStr"].ConnectionString;
using(SqlConnection con = new SqlConnection(connectionStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "spSearchEmployees";
cmd.CommandType = CommandType.StoredProcedure;
if(inputFirstname.Value.Trim() != "")
{
SqlParameter param = new SqlParameter
("@FirstName", inputFirstname.Value);
cmd.Parameters.Add(param);
}
if (inputLastname.Value.Trim() != "")
{
SqlParameter param = new SqlParameter
("@LastName", inputLastname.Value);
cmd.Parameters.Add(param);
}
if (inputGender.Value.Trim() != "")
{
SqlParameter param = new SqlParameter
("@Gender", inputGender.Value);
cmd.Parameters.Add(param);
}
if (inputSalary.Value.Trim() != "")
{
SqlParameter param = new SqlParameter
("@Salary", inputSalary.Value);
cmd.Parameters.Add(param);
}
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
gvSearchResults.DataSource = rdr;
gvSearchResults.DataBind();
}
}
}
}
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.