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

Suggested Videos
Part 137 - How to check guid is null or empty in SQL Server
Part 138 - Dynamic SQL in SQL Server
Part 139 - Implement search web page using ASP.NET and Stored Procedure

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

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

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.

<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 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

{

publicpartialclassSearchPageWithDynamicSQL : System.Web.UI.Page

{

protectedvoid Page_Load(object sender, EventArgs e)

{ }

protectedvoid btnSearch_Click(object sender, EventArgs e)

{

string strConnection = ConfigurationManager

.ConnectionStrings["connectionStr"].ConnectionString;

using (SqlConnection con = newSqlConnection(strConnection))

{

SqlCommand cmd = newSqlCommand();

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.
sql server profiler exec sp_executesql

In our next video, we will discuss the differences between using Dynamic SQL and Stored Procedures

Sql server, .net and c# video tutorial

Implement search web page using ASP.NET and Dynamic SQL

No comments:

Post a Comment