The Interview Feedback Application

 CD-ROM     The Interview Feedback application is designed to collect comments and opinions of people who conducted interviews. The entire application can be installed from the CD-ROM (\Code\Chapter 4\Manager Feedback\RetrieveFeedback.xsn). This application is broken into three tiers with a Web Service acting as the middle tier , as shown in Figure 4.5.

Figure 4.5: Architecture view of the Interview Feedback sample application.

The front end acts as the data collection that submits completed data to the middle-tier service component. For this example, an InfoPath form provides the data-collection engine. For other scenarios, this may also include other front-end collection schemes like an ASP.NET application that leverages the same service component. The actual data repository is maintained in an SQL Server database that contains the schema shown in Figure 4.6.

Figure 4.6: The database structure of the Interview Feedback sample application.

The Middle Tier

The middle-tier Web Service component is a public method and contains the following parameters:

 <webmethod(): Public Function SendFeedback(ByVal EvaluatorName As  String, ByVal ApplicantName As String, ByVal PositionAppliedFor As  String, ByVal InterviewDate As String, ByVal InterviewType As String, ByVal  ExperienceO As Boolean, ByVal ExperienceG As Boolean, ByVal ExperienceI  As Boolean, ByVal ExperienceU As Boolean, ByVal ExperienceN As  Boolean, ByVal ExperienceComment As String, ByVal JobKnowledgeO As  Boolean, ByVal JobKnowledgeG As Boolean, ByVal JobKnowledgeI As  Boolean, ByVal JobKnowledgeU As Boolean, ByVal JobKnowledgeN As  Boolean, ByVal JobKnowledgeComment As String, ByVal EducationO As  Boolean, ByVal EducationG As Boolean, ByVal EducationI As Boolean,  ByVal EducationU As Boolean, ByVal EducationN As Boolean, ByVal  EducationComment As String, ByVal CommunicationSkillsO As Boolean,  ByVal CommunicationSkillsG As Boolean, ByVal CommunicationSkillsI As  Boolean, ByVal CommunicationSkillsU As Boolean, ByVal  CommunicationSkillsN As Boolean, ByVal CommunicationSkillsComment As  String, ByVal MotivationO As Boolean, ByVal MotivationG As Boolean,  ByVal MotivationI As Boolean, ByVal MotivationU As Boolean, ByVal  MotivationN As Boolean, ByVal MotivationComment As String) 

The Web Services interface is responsible for collecting a set of parameters from the front-end InfoPath form. Once collected, the data is inserted into the SQL database using a stored procedure. The Web Method contains the code shown in Listing 4.2 that receives the parameters, validates , and then calls the stored procedure.

Listing 4.2: Performing an insert into an SQL Server database using ADO.NET.

 'creating the DB connection Dim sqlConn As SqlConnection Dim sqlCmd As SqlCommand Dim strConstring As String Dim FeedbackDA As SqlDataAdapter strConstring = ConfigurationSettings.AppSettings("constring") sqlConn = New SqlConnection(strConstring) sqlConn.Open() sqlCmd = New SqlCommand   With sqlCmd     .Connection = sqlConn     .CommandTimeout = 30     .CommandType = CommandType.StoredProcedure     .CommandText = "spInsertFeedback"     .Parameters.Add("@EvaluatorName", EvaluatorName)     .Parameters.Add("@ApplicantName", ApplicantName)     .Parameters.Add("@PositionAppliedFor", PositionAppliedFor)     .Parameters.Add("@InterviewDate", InterviewDate)     .Parameters.Add("@InterviewType", InterviewType)     .Parameters.Add("@ExperienceO", ExperienceO)     .Parameters.Add("@ExperienceG", ExperienceG)     .Parameters.Add("@ExperienceI", ExperienceI)     .Parameters.Add("@ExperienceU", ExperienceU)     .Parameters.Add("@ExperienceN", ExperienceN)     .Parameters.Add("@ExperienceComment", ExperienceComment)     .Parameters.Add("@CommunicationSkillsO", CommunicationSkillsO)     .Parameters.Add("@CommunicationSkillsG", CommunicationSkillsG)     .Parameters.Add("@CommunicationSkillsI", CommunicationSkillsI)     .Parameters.Add("@CommunicationSkillsU", CommunicationSkillsU)     .Parameters.Add("@CommunicationSkillsN", CommunicationSkillsN)     .Parameters.Add("@CommunicationSkillsComment", CommunicationSkillsComment)     .Parameters.Add("@JobknowledgeO", JobKnowledgeO)     .Parameters.Add("@JobknowledgeG", JobKnowledgeG)     .Parameters.Add("@JobknowledgeI", JobKnowledgeI)     .Parameters.Add("@JobknowledgeU", JobKnowledgeU)     .Parameters.Add("@JobknowledgeN", JobKnowledgeN)     .Parameters.Add("@JobknowledgeComment", JobKnowledgeComment)     .Parameters.Add("@EducationO", EducationO)     .Parameters.Add("@EducationG", EducationG)     .Parameters.Add("@EducationI", EducationI)     .Parameters.Add("@EducationU", EducationU)     .Parameters.Add("@EducationN", EducationN)     .Parameters.Add("@EducationComment", EducationComment)     .Parameters.Add("@MotivationO", MotivationO)     .Parameters.Add("@MotivationG", MotivationG)     .Parameters.Add("@MotivationI", MotivationI)     .Parameters.Add("@MotivationU", MotivationU)     .Parameters.Add("@MotivationN", MotivationN)     .Parameters.Add("@MotivationComment", MotivationComment)     .ExecuteNonQuery()   End With   sqlConn.Close() End Function 

 

ADO.NET classes are found in the System.Data.Dll and are integrated into the base XML classes found in the System.XML.dll. These ADO.NET components are designed to provide all data access and manipulation within the .NET Framework using a common structure. The Connection and Command objects are the two main objects within the hierarchy.

The Connection object is responsible for providing connectivity to the data source. In order to take advantage of connection pooling within SQL Server, it is important to use the same connection string for each connection. The easiest way to maintain this is by adding the connection string to the Web Services Web.Config file. The System.Configuration namespace gives you access to these settings and enables error handling within the configuration files. In the XML configuration file, the < appSettings > tag is appended to the < System.Web > tag, as shown here:

 <appSettings>   <add key="constring" value="server=localhost;database=applicant;uid=sa;password=Robbins" /> </appSettings> 

This tag is appended in a key/value combination that allows for an indexed retrieval of the data using the following code from the load event of the Web Service:

 strConstring = ConfigurationSettings.AppSettings("constring") 

Database Access

The Command object is responsible for enabling database commands to return, modify, or run stored procedures from an associated Connection object. It is always a best practice within SQL Server to access and manipulate data using stored procedures. A stored procedure is a pre-compiled query that guarantees the fastest data retrieval. Insert statements are used to update the database. SQL statements that don t return data should use the ExecuteNonQuery option, which allows the passing of ADO.NET-based parameters that match the stored procedure input parameters. Within the Interview Feedback sample, the Insert statement shown in Listing 4.3 is used to update the feedback data.

Listing 4.3:

 CREATE PROCEDURE [SpInsertFeedback]   (@EvaluatorName   [varchar](75),     @ApplicantName   [varchar](75),   @PositionAppliedFor   [varchar](50),    @InterviewDate   [datetime],    @InterviewType   [varchar](50),    @ExperienceO   [bit],    @ExperienceG   [bit],    @ExperienceI   [bit],    @ExperienceU   [bit],    @ExperienceN   [bit],    @ExperienceComment   [varchar](255),    @JobKnowledgeO   [bit],    @JobKnowledgeG   [bit],    @JobKnowledgeI   [bit],    @JobKnowledgeU   [bit],    @JobKnowledgeN   [bit],    @JobKnowledgeComment   [varchar](255),    @CommunicationSkillsO   [bit],    @CommunicationSkillsG   [bit],    @CommunicationSkillsI   [bit],    @CommunicationSkillsU   [bit],    @CommunicationSkillsN   [bit],    @CommunicationSkillsComment   [varchar](255),    @EducationO   [bit],    @EducationG   [bit],    @EducationI   [bit],    @EducationU   [bit],    @EducationN   [bit],    @EducationComment   [varchar](255),    @MotivationO   [bit],    @MotivationG   [bit],    @MotivationI   [bit],    @MotivationU   [bit],    @MotivationN   [bit],    @MotivationComment   [varchar](255)) AS INSERT INTO [Applicant].[dbo].[Applicant]   ([EvaluatorName],[ApplicantName],[PositionAppliedFor],[InterviewDate],   [InterviewType],[ExperienceO],[ExperienceG],[ExperienceI],[ExperienceU],   [ExperienceN],[ExperienceComment],[JobKnowledgeO],[JobKnowledgeG],[   JobKnowledgeI],[JobKnowledgeU],[JobKnowledgeN],[JobKnowledgeComment],   [CommunicationSkillsO],[CommunicationSkillsG],[CommunicationSkillsI],    [CommunicationSkillsU],[CommunicationSkillsN],[CommunicationSkills    Comment],    [EducationO],[EducationG],[EducationI],[EducationU],[EducationN],  [EducationComment],[MotivationO],[MotivationG],[MotivationI],[MotivationU],   [MotivationN],[MotivationComment])  VALUES   (@EvaluatorName,@ApplicantName,@PositionAppliedFor,@InterviewDate, @InterviewType,@ExperienceO,@ExperienceG,@ExperienceI,@ExperienceU, @ExperienceN,@ExperienceComment,@JobKnowledgeO,@JobKnowledgeG,@JobKnowl edgeI,@JobKnowledgeU,@JobKnowledgeN,@JobKnowledgeComment,  @dis:@CommunicationSkillsO,@CommunicationSkillsG,@CommunicationSkillsI, @CommunicationSkillsU,@CommunicationSkillsN,@CommunicationSkillsComment, @EducationO,@EducationG,@EducationI,@EducationU,@EducationN, @EducationComment,@MotivationO,@MotivationG,@MotivationI,@MotivationU, @MotivationN,@MotivationComment) GO 

 

When executed with no return parameters within an Update , Delete , or Insert statement, the return value is always the number of rows affected by the Command object. Any type of error will always return a -1 .

Compile and Run

When the Web Service class is compiled, the class is turned into an assembly, copied to the assigned IIS virtual directory, and exposed through an .asmx endpoint. This IIS virtual directory contains the Web Service declaration that points requests to the compiled assembly. This declaration is contained in the .NET code behind page (a page created when you develop the application). This page is defined during development and contains the following information:

 <%@ WebService Language="vb" Codebehind="Feedback.asmx.vb"  Class="InterviewFeedback.Feedback" %> 

This declaration stored in the .asmx file deployed to the IIS directory notifies and directs the Web Service handler to the class that contains the exposed methods to use when a specific SOAP message is received.

Anytime the .asmx page is called directly from a Web browser, the .NET Documentation Handler produces an HTML page that enables you to test and document the exposed Web methods, as shown in Figure 4.7.

Figure 4.7: The Documentation Handler displays a default page.

The Documentation Handler is a standard .aspx page stored in the .NET Framework configuration directory named DefaultWsdlHelpGenerator.aspx . When a direct browser request is received, IIS maps to the .asmx file and then links to the associated Documentation Handler specified in the .NET machine configuration.