Build a Searchable Online Employee Directory with ASP.NET | 2

Build a Searchable Online Employee Directory with ASP.NET [con't]

Create the Stored Procedure for Employee Directory

If you still have the database on your task menu, simply restore it. From the object explorer, follow these steps to create the procedure for searching employees:

Expand the plus sign (+) next to our database, mwd.
Expand the plus sign (+) next to programmability.
Expand the plus sign (+) next to stored procedures.
Right click on stored procedures and select new stored procedure.

Modify the generated code as shown below:

use mwd
 -- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
CREATE PROCEDURE spEmployeeDirectoryViewDetail 
   -- Add the parameters for the stored procedure here
   @LName varchar(50)
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
    -- Insert statements for procedure here
   SELECT LName + ', ' + FName as [Name], Guid
   FROM EmployeeDirectory
   WHERE LName LIKE @LName + '%'
END
GO

As you can see from the SQL above, our stored procedure is named spEmployeeDirectoryViewDetail, and we simply query our table, employee directory. In our query, we concatenate (append) last and first name together using the alias Name, surrounded in brackets, because Name is a reserved SQL keyword. More importantly, on the WHERE clause we search on the LName column using the LIKE keyword, which searches our database for any results containing our search term. Next, if we have two last names such as Rose, we want to return that name only if they type in R or Rose, not o. We solve this by using the wildcard character (%). Before running the stored procedure on the database, follow these steps from the main menu to save the stored procedure:

From the main menu, choose File→Save As.
In the Save As dialog, type "spEmployeeDirectoryViewDetail" and then left click OK.

When finished, run the stored procedure by clicking execute right below the main menu. If run successfully, SQL Server will report "Commands executed successfully." Minimize SQL Server Management Studio for the time being.

Restore Visual Studio and run the project by pressing F5. On the initial screen, type D or Doe and click Search. This screen should return:

If you move your mouse over the link, you'll see a GUID with a random string appended. After we implement the functionality, when a user clicks on the link, we'll pass the GUID through the query string and display additional employee detail. The reason we use a GUID is simple: an attacker could very easily cycle through primary keys in a query string to determine the structure of your database, but guessing a GUID is virtually impossible.

Validating Search Text Field

Currently, a visitor could type nothing or anything into our search field and it would accept it. Since we're searching only for last names, if they type nothing or a number we shouldn't execute our query. We enforce this by adding required field and regular expression validation controls. The first ensures something is typed into the search field. Because we shouldn't search unless it's a character, the second will allow searching only if a character is typed in, based on pattern matching. Let's add these two controls as shown below:

<form id="form1" runat="server">
<h2>Employee Search</h2>
<p>Search an employee by typing a last name.</p>
<asp:TextBox ID="txtName" runat="server"/>
<asp:Button ID="btnSearch" runat="server" OnClick="btnSearch_Click" Text="Search" />
<asp:RequiredFieldValidator ID="rfvName" runat="server" ControlToValidate="txtName" ErrorMessage="Search cannot be empty!" Display="Dynamic" />
<asp:RegularExpressionValidator ID="revName" runat="server" ControlToValidate="txtName" ValidationExpression="^\s*[a-zA-Z,\s]+\s*$" ErrorMessage="Search can only contain letters!" Display="Dynamic" />
<asp:PlaceHolder ID="phEmployeeSearch" runat="server" Visible="false">   
<asp:Repeater ID="rpEmployeeSearchView" runat="server">
<ItemTemplate>
    <p><asp:HyperLink ID="hlLink" runat="server" NavigateUrl='<%#Eval("guid", "viewdetail.aspx?guid={0}")%>' Text='<%#Eval("Name")%>' /></p>
</ItemTemplate>
</asp:Repeater>
</asp:PlaceHolder>
<asp:PlaceHolder ID="phNoEmployeeFound" runat="server" Visible="false">
<p>No Employee exists.</p>
</asp:PlaceHolder>
</form>

As you can see from the code above, we have two controls: (1) required field validation, which consists of the following:

ID
- Gives our control a unique name
Runat
- Tells us the control will be run server-side first, and then HTML output will be sent to the browser when necessary
ControlToValidate
- Indicates which control we want to validate
ErrorMessage
- Message displayed to our visitor
Display
- Set to dynamic means the source markup for displaying will be visible only when this control is executed

And (2) regular expression validation, which consists of the following:

ID
- Gives our control a unique name
Runat
- Tells us the control will be run server-side first, and then HTML output will be sent to the browser when necessary
ControlToValidate
- Instructs which control we want to validate
ValidationExpression
- A regular expression that ensures only text is allowed in the search field
ErrorMessage
- Message displayed to our visitor
Display
- Set to dynamic means the source markup for displaying will be visible only when this control is executed

Save your file and press F5 to run the project. Try typing a number in the search field. The error message should show. Also, try leaving the search field empty and you should get an error message. Before moving to the other file, we need to ensure that when JavaScript is disabled we still check the search field appropriately. In our code-behind file, add the following:

As you can see from the code above, the IsValid method checks server side to ensure the search field is not empty and contains an appropriate pattern. Save your file, run the project, turn off JavaScript and try the same scenario as before, ensuring it still works. When finished, return to Visual Studio and stop the application from running.

Create viewdetail.aspx

When our visitors view this page, they will see additional details of the employee in a read-only format. Let's create this file by following these steps:

From the solution explorer, right click on your solution and choose Add→New Item.
In the Add New Item window, select ASP.NET Web Form.
In the name text field, type "viewdetail.aspx" and left click Add.

Double click viewdetail.aspx from the solution explorer and remove everything inside the opening and closing <form> tags. Next, add the following markup inside the opening and closing <form> tags as shown below:

As you can see from the code above, we create a placeholder control, set its visibility to true, and nested inside, we use a fieldset and definition lists. Fieldsets, along with definition lists, are another way of making form layouts accessible, much like labels. In a definition list, the <dt> tag is your label and the <dd> tag is where you place your form(s) element(s). We use label controls and we'll set their text property in the code behind to the approach value. Lastly, we use another placeholder control, set its visibility to false, and put a simple error message inside. If for any reason our query string isn't there, we'll display this.

Create Our Style Sheet

Let's create our style sheet so we can control the positioning of our definition lists:

From the solution explorer, right click on the solution and choose Add→New Item.
In the add new item window, choose style sheet.
In the name text field, type "style" and then left click Add.

In our style sheet, add the following rules as shown below:

As you can see from the code above, we set the dl tag to a width of 400 and center it with a margin of auto. Then we float both the dt and dd left, giving them an appropriate width. Save your file. In viewdetail.aspx, add the reference to our style sheet as shown below:

As you can see from the code above, we create a reference to the style sheet. Save your file.

Creating View Detail Method in employeedirectory.cs

We know from our main page (default.aspx), viewdetail.aspx needs to accept a parameter, specifically, a GUID from the query string. What we'll do is create a method in our class file that will take one parameter, a GUID, and return that to our code behind file and then display additional detail about our employee. Let's add the code for that as shown below:

public void GetEmployeesViewGuid(Guid guid)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mwd"].ConnectionString);
            SqlCommand cmd = new SqlCommand("spEmployeeDirectoryViewGuid", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter parameterGuid = new SqlParameter("@Guid", SqlDbType. UniqueIdentifier, 37);
            parameterGuid.Value = guid;
            cmd.Parameters.Add(parameterGuid);
            try
            {
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                if(rdr.Read())
                {
                    lname = rdr["LName"].ToString();
                    fname = rdr["FName"].ToString();
                    email = rdr["Email"].ToString();
                    officeloc = rdr["OfficeLoc"].ToString();
                    phonenum = rdr["PhoneNum"].ToString();
                    jobtitle = rdr["JobTitle"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

In the code above, we do the following:

Create a method GetEmployeesViewGuid and pass in our GUID.
Create a connection object and pass in our key from our configuration file.
Create a command object and pass in our stored procedure (created next) and our connection object.
Set our command object to accept a stored procedure.
Create a SQL parameter and pass in our GUID as a parameter.
Set our parameter to the value of our GUID variable.

If for any reason we can't open the connection or read from our data reader object, we wrap that inside a try/catch block and do the following:

Call the open method of our connection object.
Set our data reader object to the execute reader method of our command object.
If the data reader can read, we do the following:
- Set each of our class-level variables to the appropriate data reader value
Catch and throw an exception if necessary.
Call the dispose method of the command object and the close method of our connection object

Save your file.

Create Stored Procedure View Detail

As before, we need to create another stored procedure. From the object explorer, follow these steps to create the procedure for viewing an employee's specific information:

Expand the plus sign (+) next to our database, mwd.
Expand the plus sign (+) next to programmability.
Expand the plus sign (+) next to stored procedures.
Right click on stored procedures and select new stored procedure.

Modify the generated code with the following as shown below:

use mwd
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
CREATE PROCEDURE spEmployeeDirectoryViewGuid 
   -- Add the parameters for the stored procedure here
   @Guid uniqueidentifier
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;
    -- Insert statements for procedure here
   SELECT FName, LName, Guid, Email, OfficeLoc, JobTitle, PhoneNum
   FROM EmployeeDirectory
   WHERE Guid=@Guid
END
GO

As you can see from the SQL query above, we create a parameter, GUID, set its size to 37, select from our table the columns we want and then append on a WHERE clause for our GUID parameter. Before running the stored procedure on the database, follow these steps from the main menu to save the stored procedure:

From the main menu, choose File→Save As.
In the Save As dialog, type "spEmployeeDirectoryViewGuid" and then left click OK.

When finished, run the stored procedure by clicking execute right below the main menu. If run succesfully, SQL Server will report "Commands executed successfully." Minimize SQL Server Management Studio for the time being.

Open viewdetail.aspx.cs

From the solution explorer, follow these steps:

Left click the plus sign (+) next to default.aspx.
Double click default.aspx.cs. Inside your partial class, add the following code as shown below:
namespace EmployeeDirectory { public partial class viewdetail : System.Web.UI.Page { #region Declarations EmployeeDirectory ed = new EmployeeDirectory(); #endregion } }

As you can see from the code above, we create a new instance of our employee directory class.

In our page load event, let's add the following code as shown below:

As you can see from the code above, we do the following inside our page load event:

Create a variable with a data type of GUID and set it to empty.
Set our GUID variable to accept a new GUID, and pass in our query string.
Check our GUID variable to see if it's null.
- If it is not, call GetData and pass in our variable.
- If it is, show our error message.

You may wonder why we had to set the GUID variable to empty. The reason is simple: if for any reason we don't get a query string passed in, we need a default value (empty string) in our variable. Otherwise, our application will throw an exception because we're accessing a null object.

Let's create our GetData method as shown below:

As you can see from the code above, we pass in our variable as a GUID data type, and then we do the following:

Set our initial placeholder's visibility to true.
Call our method, GetEmployeesViewguid, and pass in our variable.
After our method runs, if the reader can read data, we set each of our label controls to their appropriate values from our object.

Save your file. Run the project by pressing F5, and type "D" or "Doe" in the search field, which returns Doe. Click on the hyperlink and you should be able to view additional details regarding our employee.

Summary

In this article, you learned how to create an employee search directory using ASP.NET. Additionally, you learned the following:

SQL
- Create a database
- Create a table
- Create and understand stored procedures
Visual Studio
- Create a project
- Create two Web forms:
  - One that searches for an employee
  - One that views additional detail about an employee
Definition lists and how to style them appropriately
Create a class that had:
- A generic list object
- A method that accepted a GUID as a parameter

Take the knowledge gained in this article and build or enhance our search application to meet any need or requirement you may have.

If you have questions, please contact me.

Code Download

ASP.NET Searchable Employee Directory application source code

About the Author

Ryan Butler is the founder of Midwest Web Design. His skill sets include HTML, CSS, Flash, JavaScript, ASP.NET, PHP and database technologies.

Original: Jul. 29, 2010

[prev]