In this we will discuss implementing a search web page using ASP.NET and Dynamic SQL.
Step 1 : Add a WebForm to the web project. Name it "SearchPageWithDynamicSQL.aspx"
Step 2 : 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 3 : Copy and paste the following code in the code-behind page. Notice we are using dynamic sql instead of the stored procedure "spSearchEmployees".
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace DynamicSQLDemo
{
public partial class SearchPageWithDynamicSQL : 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;
StringBuilder sbCommand = new
StringBuilder("Select * from Employees where 1 = 1");
if (inputFirstname.Value.Trim() != "")
{
sbCommand.Append(" AND FirstName=@FirstName");
SqlParameter param = new
SqlParameter("@FirstName", inputFirstname.Value);
cmd.Parameters.Add(param);
}
if (inputLastname.Value.Trim() != "")
{
sbCommand.Append(" AND LastName=@LastName");
SqlParameter param = new
SqlParameter("@LastName", inputLastname.Value);
cmd.Parameters.Add(param);
}
if (inputGender.Value.Trim() != "")
{
sbCommand.Append(" AND Gender=@Gender");
SqlParameter param = new
SqlParameter("@Gender", inputGender.Value);
cmd.Parameters.Add(param);
}
if (inputSalary.Value.Trim() != "")
{
sbCommand.Append(" AND Salary=@Salary");
SqlParameter param = new
SqlParameter("@Salary", inputSalary.Value);
cmd.Parameters.Add(param);
}
cmd.CommandText = sbCommand.ToString();
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
gvSearchResults.DataSource = rdr;
gvSearchResults.DataBind();
}
}
}
}
At this point, run the application and SQL profiler. To run SQL profiler
1. Open SQL Server Management Studio
2. Click on "Tools" and select "SQL Server Profiler"
3. Click the "Connect" button to connect to local SQl Server instance
4. Leave the "Defaults" on "Trace Properties" window and click on "Run" button
5. We now have the SQL Profiler running and in action
On the "Search Page" set "Gender" filter to Male and click the "Search" button. Notice we get all the Male employees as expected. Also in the SQL Server profiler you can see the Dynamic SQL statement is executed using system stored procedure sp_executesql.
In our next , we will discuss the differences between using Dynamic SQL and Stored Procedures
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.