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

this is part 139 of secret server
tutorial in this video well difficut
implemented a search web page using
asp.net and stored procedure
this is continuation to part 138 so
please watch part 138 before proceeding
so here's what we want to do we want to
implement an employee search form as you
can see here it has got four search
fields first name last name gender and
salary using this search form we want to
search this employee's table and then
display the matching rules as you can
see here now to be able to search the
employees table we are going to make use
of the stored procedure we have
implemented the search stored procedure
in our previous video so let's see how
to call the search stored procedure from
asp.net web application project so let's
flip to visual studio now and let's
create a new project so let's click on
file new project we want to create an
asp.net web application so under install
under templates on the visual c-sharp
select web and in the right hand pane
select a square dotnet web application
let's give our project a name let's call
it dynamic sequel demo and let's click
OK and in here select empty and then web
forms click OK the project is now
successfully created the first thing
that I'm going to do is add a connection
string in web config file so under
configuration section let's include a
section for connection strings here we
have added a connection string with the
name connection STR this connects to
sequel server on my local machine so I
have set server to dot and the database
is set to employee DB which contains the
employees table from which we want to
retrieve data and finally we want to use
Windows authentication to connect the
sequel server so we have said integrated
security equals true the next thing that
we want to do is add a perform to a
project so with in solution Explorer
right click on the project name ad and
we want to add a web form
let's name a webform search page without
dynamic sequel let's click ok we're
going to make use of bootstrap to style
our employee search form
so let's include a reference to
bootstraps TD and link so within the
head section let's include link element
let's set the rel attribute to
stylesheet and type attribute to text
slash CSS and the href attribute to the
CDN link to get the bootstrap CDN link
visit this website and right here we
have the CDN link so let's copy that and
specify it right here now we want to
design this employee search page like
this using bootstrap CSS classes if
you're new to bootstrap please check our
bootstrap couturier for beginners
playlist in the interest of time I'm not
going to go into the details of
designing this employee search form I'm
going to replace this body section with
the HTML that I have copied to clipboard
so what we have here is essentially some
bootstrap classes and components in
addition to that they've got four label
controls to display these labels on the
left and then through spawning text
boxes and then we have got a search
button and a grid view control to
display the search results I'll have all
this HTML available on my blog in case
you need it now let's flip this webform
to the design mode and let's double
click on the button control to generate
a click event handler
they don't write some idiotic code so
let's bring in a few idiotic namespaces
we need system dot configuration system
dot data and finally system dot data dot
sequel client and the first thing that
we are going to do when we click the
search button is read the connection
string from web config file we are going
to store our connection string in this
variable connection STR equals to read
the connection string from web config
file we're going to make use of the
configuration
manager class so configuration manager
dot connection strings of the name of
the connection string within our web
config is connection STR so let's
specify that right here so we want to
read that connection string value now
using the connection string that we have
read from a web config file we want to
create a sequel connection object let's
call the instance sequel connection and
let's pass our connection string to the
constructor of our sequel connection
class next let's create sequel command
object let's call the instance CMD
equals new sequel command and we want
this command object to use this
connection that we have already created
so let's set the connection property of
the command object to the connection
object that we have already created now
the command text for the command object
is going to be the name of Oster
procedure the name of our stored
procedure is SD search employees so
let's specify that as the value for
command text property now we know this
is a stored procedure we have to tell
that to the command object and the baby
do that is by setting command type
property of the command object let's set
that to stored procedure and if you look
at the stored procedure it has got four
parameters so we need to specify those
parameters and the values for them and
then add those parameters to the command
object now first let's check if the user
has typed something into the first name
textbox now the ID of the first name
textbox is input first name so let's use
the ID of the text box here so f input
first name dot value and I'm also going
to use the trim method to remove white
spaces on either sides and then let's
compare that to an empty string now
if the value within the input first name
textbox is not equal to an empty string
that means the user
has typed something into that textbox so
in that case let's go ahead and create a
sequel parameter object let's call it
param equals new sequel parameter and
the name of the parameter is at first
name so let's specify the name of the
parameter here and then we need to
specify the value for the parameter
where is the value going to come from
it's going to come from that expected
text box the idea of the text box for
first name is input first name so let's
retrieve the value out of the text box
and then we need to add this parameter
to the parameters collection of the
command object so to do that let's use
command or parameters dot add are going
to add the parameter object that we have
created and we have to do the same thing
for last name gender and salary in the
interest of time I've already typed the
required code so let's copy that
and specify it right here so similarly
we have created parameters for last name
gender salary and now let's open the
connection and execute the command by
calling execute reader and whatever
results we get we're going to store that
in sequel data reader let's call the
instance RDR and we are going to set
this data reader as the data source for
our grid view control and if you look at
the ID of the grid view control it is GV
search results so GV search results dot
data source equals RDR and let's call
the data bind method so with all these
changes let's run our application by
pressing control f5 now let's search for
male employees so within the gender text
box let's type male click the search
button notice we are getting an error
procedure function as
research employees expects a parameter
at first name which was not supplied and
look at the code that we have just
written notice we are creating this at
first name parameter and adding it to
the parameters collection of the command
object so why are we getting this error
they are getting this error because
first name textbox on the search page is
empty string we haven't set it to
anything so what happens is it doesn't
come inside this if block so it's not
adding the access name parameter to the
parameters collection of the command
object and since we also haven't
specified any default values for these
parameters it expects the parameter to
be passed so let's go ahead and specify
null as the default value for these
parameters so we don't get this error
when we don't specify any values for the
parameters on the search page so let's
set null as the default value for all
the parameters let's alter the scope
seizure and now let's go back and let's
search for male employees now look at
that we get all male employees similarly
if we search for female employees we
only get female employees so here
they're using a stored procedure to do
that in our next video we'll discuss
rewriting the same example using dynamic
sequel and understand the difference
between using a stored procedure and
dynamic sequel here is the stored
procedure and here is the ideologue net
code to call that suit sita SP search
employees thank you for listening and
have a great day