Intranet Journal
The online resource for intranet professionals
Building a Searchable Phone Directory with Windows SharePoint Services
Paul Schaeflein
4/6/2005
|
|
This month, I want to continue to build out the intranet site we've started in our previous articles. A standard item in every intranet is a staff/employee list. Most companies implementing Windows SharePoint Services (WSS) are large enough to have a database that contains all employees. We will use the aggregation features of SharePoint to include data from this database into our intranet.
Last month, we discussed customizing the site home page using the built-in Web Parts. That customization was performed using only a Web browser. This month, we will also use FrontPage 2003 to build a page that searches a database table. For this article, the database we will use is the Northwind sample database that is included in Microsoft's SQL Server.
On our Web page, we will display the information pertinent to calling a fellow employee from your desk: name, telephone extension, and country. While Northwind has only a few employees, we want to provide the ability to search by first and/or last name.
Database Setup
Before we can include the database content on our intranet, we need to ensure that the Web server can access the database. Since an employee database usually contains data that should not be publicly available, we need to provide a way to read basic information without compromising the sensitive data. A complete discussion of database security is beyond the scope of this article, but we will do some steps to provide a basic level of protection.
The default configuration of a WSS site is Integrated Security. This means that each Web request runs in the context of the user account requesting the page (the end-user). While this is fine for most tasks, not every user account will have access to the employee database. To provide the connection from the Web server to the database, we will create a SQL Server user account. This account will have access only to the Northwind database and will only be allowed to execute a specific stored procedure to perform the staff search.
Stored Procedure
To perform the search of the database, we will create a stored procedure. In the Northwind database, we will search the Employees table, selecting only the columns required. The results are ordered alphabetically, as you would expect.
CREATE PROCEDURE StaffSearch ( @LastName nvarchar(20), @FirstName nvarchar(10) ) AS SET @LastName = RTRIM(@LastName) + '%' SET @FirstName = RTRIM(@FirstName) + '%' SELECT LastName, FirstName, Extension, Country FROM Employees WHERE LastName LIKE @LastName AND FirstName LIKE @FirstName ORDER BY LastName, FirstName |
Database User Account
The following commands will create an SQL User account, and allow that account to connect to the Northwind database. Then, the account is granted permission to execute the stored procedure.
EXEC sp_addlogin 'spuser', 'passwd', 'Northwind' USE Northwind EXEC sp_adduser 'spuser' GRANT EXEC ON StaffSearch TO spuser |
Web Server Setup
Data Retrieval Services
WSS uses a feature called Data Retrieval Services to access data in external databases (databases other than the configuration and content databases). By default, the Data Retrieval service will not allow access to stored procedures. This setting is changed via the Central Administration page:
Document Library for Web Part Pages
Since WSS stores the content of the site in a database, we cannot just create an HTML page and put it on the server. To add pages to a site, they must be stored in a document library. WSS comes with a default "Shared Documents" library, but this library is configured to store MS Word documents. We will create a new document library to contain all Web pages we might need in our intranet:
The new page will be displayed in design mode.
At this point, we are finished with the set-up tasks for the database and the Web server.
Building the Web Page
We are now ready to build the Web page. Start by adding a Content Editor Web Part to the Left Column. This part will contain the instructions. Below the Instructions, add a Form Web Part. The Form Web Part, in combination with a feature called Web Part Connections, will allow users to perform the partial name search. On the Web Part Menu for the Form Web Part, click Modify Shared Web Part to open the Tool Pane.
In the Form Web Part tool pane, click the Source Editor button. Replace the default HTML with the following:
<div style="padding: 2;" onkeydown="javascript:if (event.keyCode == 13) _SFSUBMIT_">
Last name: <br/>
<input type="text" name="LastName" value=""/><br/>
First name: <br/>
<input type="text" name="FirstName" value=""/><br/>
<input type="button" value="Search" onclick="javascript:_SFSUBMIT_"/>
& nbsp;
<input type="button" value="Clear" onclick="javascript:ClearTextBoxes(this.form);"/>
</div>
<script lang="javascript">
function ClearTextBoxes(form) {
for ( var i=0; i<form.elements.tags("INPUT").length; i++ ) {
if ( form.elements.tags("INPUT")(i).type.toUpperCase() == "TEXT" ) {
if ( form.elements.tags("INPUT")(i).disabled == false ) {
form.elements.tags("INPUT")(i).value = " ";
}
}
}
}
</script>
|
After these changes, the page will look like this:
At this point, all that remains is to link the Web page to the database and then connect the Form Web Part to the result list. These tasks will be completed using FrontPage 2003.
Add Data View Web Part to the Page
Open the WSS Site in FrontPage. The folder list will include the new document library, and selecting the library will display its contents. (We have only one document, but any future Web pages you create will display here.)
Return to Internet Explorer and navigate to the Phone Search page. All of the employees will be displayed. Using the Form Web Part, enter the beginning characters of employees' first and/or last name and click Search. This will cause the stored procedure to execute using the values from the form.
Since SharePoint is using client-side javascript instead of posting the form, the page is very responsive. However, removing the values from the text boxes will not completely clear the Web part connection parameters. You need to enter new values. The stored procedure is written to remove spaces from the value entered, so entering a space will clear the connection parameters. The Clear button will replace the text box values with a single space.
Other Resources
About this series
This series of articles on SharePoint is intended to help you understand the capabilities of the product, as well as provide tips and tricks, development ideas, information from Microsoft, information from the community, and perhaps some samples. Like many other series on IntranetJournal.com, I plan to include how-to articles that can help you with your deployments — ways to customize a page; deployment scenarios; content management; etc. With such a diverse product, there is no lack of topics for this series of articles. What would you like to read?
About the author
Paul Schaeflein is a developer with more than 20 years experience. Paul has been developing dynamic and interactive Web sites since 1996. Paul has worked on all of the versions of SharePoint and has worked with the .NET framework since its debut. You can reach Paul through his blog at http://www.schaeflein.net/blog/.
Intranet Journal's Tutorials |
|
Managing Editor |
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
|
Video: How System Center Helps Reduce IT Costs IBM Cloud Computing for Developers Virtual Event, April 6-8 |
Microsoft Video: OCS and Exchange: Platform Futures MORE WEBCASTS, PODCASTS, AND VIDEOS |