Implement search web page using ASP NET and Dynamic SQL Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 140 of sequel server
tutorial in this video we'll discuss
implementing a search web page using
asp.net and dynamic sequel in a previous
video in part 139 we discussed
implementing a search web page using
asp.net and stored procedure let's
rewrite that same example using dynamic
sequel instead of the stored procedure
so let's slip to visual studio let's add
a new web form to a project so I'm going
to right click on the project name ad
and we want to add a bed form let's name
or web form search page with dynamic
sequel and I'm going to copy the HTML
that we have on the other page so let's
go to the search page without dynamic
sequel which we implemented in our
previous video and let's copy the entire
HTML and paste that on the page that we
have just created so I'm going to
replace this HTML with HTML that we have
copied from the other page so this is
going to give us the same look and feel
that we have on the other page now let's
implement a code behind page let's
double click on the button control to
generate a click event handler we need
some ad or nickname spaces let's copy
them over from the other page we need
these three namespaces let's copy them
and paste them right here in addition to
these three namespaces we also need
system dot text namespace this namespace
has got a class called string builder
which we are going to make use of to
build our sequel statement dynamically
and I'm also going to copy the code that
we have in this search buttons click
event handler so let's copy all this
code and paste it within the search
button click event handler of the page
that we have just created
we'll then change the bits that are
required to build our dynamic sequel
statement now some of the code is going
to stay as ease so we are still going to
read the connection string from about
that config file using that connection
string we are going to build a sequel
connection object
and then we are creating a sequel
command object to that command object we
are associating this connection object
using the connection property of the
command object we're not going to make
use of this SP search employees stored
procedure so I'm going to delete these
two lines of code from here instead we
are going to make use of string builder
class and the string builder class is
present in systems or text namespace
let's call the instance SB command
equals new string builder and of a base
query is going to be select from
employees I'm also going to include the
where clause and use this condition one
equals one there is a reason for using
this condition one equals one I
explained that in just a bit so that's
our base query just in case if the user
has not provided a value for any of
these search filters then our query is
going to be select star from employees
where one equals one which is going to
retrieve all the rules and columns in
case if the user has provided a value
for the first name filter then it's
going to come inside this F block and in
here we will add the and class
dynamically so to the SP command object
we are going to append some text and
that's going to be and first name equals
at first name make sure you have a
single space here otherwise you would
get a syntax error notice here we have
caught a parameter in the query a class
name and next we are creating a sequel
parameter object with that name and the
value for that parameter is coming from
the first name textbox and then we are
associating the parameter object to the
command object let's do the same thing
with the other search filters that we
have so if a user has entered a value
for the last name filter then we want to
dynamically add a condition and last
name equals at last name let's do the
same thing with
gender and gender equals at gender and
finally the same thing for salary and
salary equals at salary so we have build
our dynamic sequel statement this SB
command object has a lot dynamics equal
statement depending on for which search
filters the user has provided values now
we need to associate this dynamic sequel
statement with the command object so to
do that let's use the command text
property command text equals FB command
object dot to string so whatever we have
in our SP command object that is set as
the value for the command text property
and you know we know that this is not a
stored procedure so command object dot
command type equals command type dot
text and then we open the connection
execute a command and whatever result we
get in the data reader we're going to
set that as the data source for the
gridview and call the data bind method
so with all these changes let's run our
project by pressing ctrl f5 and quickly
test the stage to make sure it works as
expected notice we have not provided
values for any of the search filters
when we click the search button we get
the list of all employees as expected
now let's set the agenda filter to
female and when we click the search
button
you only get female employees so this
page is working as expected
now let's understand the reason for this
condition where one equals one let's
remove that for a minute now the
confusion is should these filters start
with where or and now let's say this
filter is going to start with where
first name is the first filter on the
page so for example if a user has
supplied a value for this filter then we
don't have a problem the query will be
select star from employees where
first-name equals at first name so
everything is going to work as expected
but imagine if the user has not provided
a value for the first name filter but he
has toid a value for the last name
filter in this case the query is going
to be select star from employees and
last name equals at last name so we have
a syntax error there it's going to fail
but the confusion here is to start these
filters with where clause or add keyword
on the other hand if we have the weight
loss in the base query where 1 equals 1
then we know for sure this query
contains a where clause so all the other
filters can be appended using the and
keyword that makes our code a lot easier
to write if we don't have this condition
here then every time we add these
filters we will have to check ok does
the query contain where clause if so
append the filter using an keyword if
not add where clause so we have to do
that for every filter that we append and
if we have 20 search filters we have to
do that check 20 times so that makes our
code difficult to write and maintain as
well so to keep things simple I have
included this condition where 1 equals 1
now let's fire up secrets of a profiler
and see how this dynamic sequel
statement is executed at sequel server
so let's slip to sequel server
management studio click on Tools sequel
server profiler let's connect to my
local secret server instance on this
trace properties window I'm going to
leave all the defaults and click the Run
button
so we have our sequel server trace
running here let's clear this existing
trace information and then issue a
request from our web page let's change
the gender filter to male click the
search button we have the matching
records and look at what we have
captured in sequence of a profiler look
at the dynamic sequel statement
select star from employees where one
equals one and gender equals at gender
and a value for this gender parameter is
male and the dynamic secret statement is
executed using SP underscore execute SQL
systems - a procedure now let's include
another filter in addition to gender
filter let's also set first-name Zelda
and execute it we get the matching
record and notice within sequence of a
profiler in addition to the gender
filter we also have the first name
filter and a value for first name Delta
s mark and this dynamic sequence
statement is still executed using our
system store procedure SP underscore
execute SQL in our previous video we
have implemented the same search page
using our custom stored procedure SP
search employees so let's navigate to
that page and let's issue a Request and
look at what we have captured in
sequence of a profiler so here it is
using our custom stored procedure SP
search employees so in our next video
we'll discuss the differences between
using dynamic sequel and store
procedures there are several differences
both in terms of performance and
security which will talk in detail on
this slide right here we have the ad or
audit code that we have used to build
our dynamic sequence statement thank you
for listening and have a great day