CodeTalk
LANGUAGES: C#
ASP.NET VERSIONS: 2.0
Support Your Customers with an Online Help Desk: Part I
Architecture, Data Access, and Business Objects
By Bipin Joshi
Internet-era businesses target a potentially large customer base, often spanning multiple geographical regions. But getting customers is only one part of a business keeping customers is vitally important to your survival, as well. Maintaining customer satisfaction by providing after sales (or service) support goes a long way toward acquiring a good reputation in the industry (and keeping your customers). Different companies employ different ways to provide customer support; one popular means is Web-based help desk software. This multipart article takes you through the development of a simple help desk application with the help of ASP.NET, AJAX, and SQL Server 2005. To kick things off, we ll discuss the overall architecture, data access, and business objects.
Functionality
Our Web-based help desk needs to satisfy the following requirements:
- The application should allow customers to register and log in to the system.
- The customers should be able to raise their issues.
- The customers should be able to track the history and status of their issues.
- The support personnel should be able to see a list of all the pending issues.
- The support personnel should be able to mark an issue as selected so other support staff will not spend time on the same issue.
- The support staff should be able to see a history of the issues.
- An e-mail notification should be sent to the customers when a support person responds to their issues.
- To enhance the user experience, the application should make use of AJAX at appropriate places.
- The application should follow three-tier architecture.
Technical Feature Utilization
To meet the required functionality, we ll use the following features of the .NET Framework:
- ASP.NET 2.0
- ASP.NET AJAX Extensions
- Themes
- ADO.NET
- Object data source control and data bound controls
- SQL Server 2005
Overview of Architecture
The help desk application we ll develop follows a three-tier architecture design. The data access layer (DAL) encapsulates all the database access, such as executing queries and retrieving data. The Business Logic Layer (BLL) consists of a set of classes that encapsulate the business functionality of the application. Finally, ASP.NET data bound controls hosted on Web forms make up the user interface of the application. We frequently need to pass data from one layer to the other; this is accomplished with the help of generic-based collections. Communication between data bound controls and business objects is achieved via an object data source (ODS) control of ASP.NET 2.0. This helps us reduce coding that is otherwise needed for this communication.
One of our requirements is to enhance the user experience with the help of AJAX. There are many facets to the term user experience. Our focus will be on providing better response time from our application. To that end, we ll make use of the UpdatePanel and UpdateProgress controls of ASP.NET AJAX Extensions.
The application needs to store user information. We ll use ASP.NET membership and roles for this purpose. A SQL Server 2005 database stores all the information needed by the application, including membership and roles data.
Database and Data Access
Our help desk application uses a SQL Server 2005 database to store data. We ll create stored procedures to take the data in and out of the database. To call these stored procedures you must create a data access component. For our purposes we ll use BinaryIntellect s DatabaseHelper, an open source component available for download at http://www.binaryintellect.net. The component is also included in binary form in the code download associated with this article (see end of article for download details). The component contains a class named DatabaseHelper that acts as a data access layer for our application. We will not discuss this component in greater detail here; as and when we use it, the relevant properties and methods will be discussed.
To begin, create a new database in SQL Server 2005; name it HelpDeskDb. Figure 1 shows the New Database dialog box of SQL Server 2005 Management Studio. Then create two tables, namely Issues and IssueResponses. The Issues table stores issues posted by the customers, whereas the IssueResponses table stores responses to the issues. The structure of these tables is shown in Figure 2.
Figure 1: Creating a new database
using SQL Server 2005 Management Studio.
|
Table Name |
Column Name |
Data Type |
Description |
|
Issues |
IssueId |
integer (identity) |
Primary key. |
|
|
IssueTitle |
varchar(255) |
Title of the issue. |
|
|
IssueDescription |
varchar(MAX) |
Details of the issue. |
|
|
PostedBy |
varchar(255) |
User name of customer who posted the issue. |
|
|
PostedOn |
datetime |
Date and time the issue was posted. |
|
|
ResolvedOn |
datetime |
Date and time the issue was resolved. |
|
|
Status |
char(1) |
Status of the issue. Possible values are O - Open or C - Closed. |
|
|
SupportPersonId |
varchar(255) |
User name of the support person who resolved the issue. |
|
IssueResponses |
IssueResponseId |
integer (identity) |
Primary key. |
|
|
IssueId |
integer |
ID of the issue that has been responded. |
|
|
IssueResponseTitle |
varchar(255) |
Title of the response. |
|
|
IssueResponseDescription |
varchar(MAX) |
Details of the response. |
|
|
PostedBy |
varchar(255) |
User name of the person who posted the response. This user name can be of support personnel or the customer. |
|
|
PostedOn |
datetime |
Date and time the response was posted. |
Figure 2: Structure of the Issues and IssueResponses tables.
The next step is to create stored procedures that will be used by our application. Here we ll briefly glance over the list of stored procedures (see Figure 3); the complete T-SQL script of the stored procedures can be found in the code download accompanying this article.
|
Stored Procedure Name |
Description |
|
Issues_Add |
Adds a new record in the Issues table. |
|
Issues_SelectAll |
Returns all issues from the Issues table. |
|
Issues_SelectByCustomerId |
Returns all the issues for a specified customer. |
|
Issues_SelectById |
Returns an issue matching the supplied issue ID. |
|
Issues_SelectBySupportPersonId |
Returns all the issues selected by a support person. |
|
Issues_SelectClosed |
Returns all the issues marked as closed. |
|
Issues_SelectPending |
Returns all the issues marked as pending. |
|
Issues_SetStatus |
Sets the status of an issue. Possible values are O (Open) or C (Closed). |
|
Issues_SetSupportPersonId |
Stores the user name of the support person who is handling the issue in the Issues table. |
|
Issues_UnSetSupportPersonId |
Removes the user name of the support person for a specified issue from Issues table. |
|
IssueResponses_Add |
Adds a new response to an issue. |
|
IssueResponses_SelectByIssueId |
Returns all the responses from the IssueResponses table for a specified issue. |
Figure 3: Stored procedures used in our help desk application.
Classes
Now that we ve created the stored procedures required by our application, let s move ahead and create a Web site in Visual Studio 2005. Open Visual Studio 2005 and select File | New Web Site to open the New Web Site dialog box, as shown in Figure 4.
Figure 4: Creating a new Web site.
Make sure you choose the ASP.NET AJAX-Enabled Web Site template. Choosing this template ensures that the Web site has ASP.NET AJAX-specific configuration placed in the web.config file. Set the programming language as C#.
Once the Web site is created, right-click on the Web site and choose the Add Reference menu option to open the Add Reference dialog box. Browse to BinaryIntellect.DataAccess.dll (available in the code download) and click the OK button. This will copy the BinaryIntellect DatabaseHelper assembly to the Bin folder of your Web site (see Figure 5).
Figure 5: Adding a reference to
BinaryIntellect s DatabaseHelper.
Now right-click on the Web site and select Add ASP.NET Folder | App_Code from the shortcut menu. This adds the App_Code folder to our Web site. Our business classes will be stored in this folder. To add classes to the App_Code folder, right-click on the Web site and select the Add New Item menu option; this will open a dialog box, as shown in Figure 6.
Figure 6: Adding classes to the App_Code
folder.
Add four classes: Issue, IssueResponse, Issues, and IssueResponses. Now open the Issue class and add to it the code shown in Listing One.
The Issue class consists of eight properties: IssueID, Title, Description, PostedBy, PostedOn, ResolvedOn, SupportPersonID, and Status. As you may have guessed, each property of the Issue class represents a column from the Issues table. Thus, one instance of the Issue class represents one record of the Issues table. The IssueResponse class is shown in Listing Two.
IssueResponse contains six properties: IssueResponseID, IssueID, Title, Description, PostedBy, and PostedOn. One instance of the IssueResponse class represents a record of the IssueResponse table.
Next, we ll create an Issues class that works with data from the Issues table. All the methods of the Issues class are static. Internally they make use of the DatabaseHelper class to deal with the database. The following sections discuss this class in detail.
Adding an Issue
Adding an issue involves inserting a record in the Issues table. The Add method of Issues does that (see Figure 7).
public static void Add(string title,string description,string postedby)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@title", title);
db.AddParameter("@description", description);
db.AddParameter("@postedby", postedby);
db.ExecuteNonQuery("Issues_Add", CommandType.StoredProcedure);
}
Figure 7: Adding an issue.
The Add method accepts a title and description of an issue, along with the user name of the customer. It then creates an instance of the DatabaseHelper class. The AddParameter method of the DatabaseHelper class accepts a name and value of the stored procedure parameter. After adding the required parameters we call the ExecuteNonQuery method of DatabaseHelper. The ExecuteNonQuery method accepts the name of the stored procedure or SQL statement and command type. Note that the PostedOn database column is assigned internally by the Issues_Add stored procedure. Also, when the issue is added, by default its status is set to O , i.e., Open.
Returning a List of Issues
The help desk application frequently needs to display a list of issues. It can be a list of pending issues, a history of issues, or issues selected by a support person. All the methods of the Issues class that return issues to the caller return them in the form of a generic List of Issue objects. To facilitate our job, we ll create a helper method named FillIssues (see Figure 8).
private static List
{
List
while (reader.Read())
{
Issue issue = new Issue();
issue.IssueID = reader.GetInt32(reader.GetOrdinal("IssueId"));
issue.Title = reader.GetString(reader.GetOrdinal("issuetitle"));
issue.Description = reader.GetString(
reader.GetOrdinal("issuedescription"));
issue.PostedBy = reader.GetString(reader.GetOrdinal("postedby"));
issue.PostedOn = reader.GetDateTime(reader.GetOrdinal("postedon"));
issue.SupportPersonId = (reader.IsDBNull(reader.GetOrdinal("supportpersonid"))
==false?reader.GetString(reader.GetOrdinal("supportpersonid")):"");
issue.ResolvedOn = (reader.IsDBNull(reader.GetOrdinal("resolvedon"))
==false?reader.GetDateTime(reader.GetOrdinal("resolvedon")):DateTime.MinValue);
issue.Status = char.Parse(reader.GetString(reader.GetOrdinal("status")));
issues.Add(issue);
}
return issues;
}
Figure 8: Filling a generic list of issues.
The FillIssues method accepts a SqlDataReader instance. It then creates a generic List capable of holding instances of the Issue class we created earlier. It iterates through the SqlDataReader using the Read method of the SqlDataReader class; with each iteration a new Issue instance is filled with the data and added to the List created earlier. Finally, the generic List is returned. The FillIssues method is used by five other methods that are intended to return data to the caller. These five methods are shown in Figure 9.
public static List
{
DatabaseHelper db = new DatabaseHelper();
SqlDataReader reader = (SqlDataReader)db.ExecuteReader(
"Issues_SelectAll", CommandType.StoredProcedure);
List
reader.Close();
return issues;
}
public static Issue SelectById(int issueid)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@issueid", issueid);
SqlDataReader reader = (SqlDataReader)db.ExecuteReader(
"Issues_SelectById", CommandType.StoredProcedure);
List
reader.Close();
return issues[0];
}
public static List
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@postedby", postedby);
SqlDataReader reader = (SqlDataReader)db.ExecuteReader(
"Issues_SelectByCustomerId", CommandType.StoredProcedure);
List
reader.Close();
return issues;
}
public static List
string supportpersonid)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@supportpersonid", supportpersonid);
SqlDataReader reader = (SqlDataReader)db.ExecuteReader(
"Issues_SelectBySupportPersonId", CommandType.StoredProcedure);
List
reader.Close();
return issues;
}
public static List
{
DatabaseHelper db = new DatabaseHelper();
SqlDataReader reader = (SqlDataReader)db.ExecuteReader(
"Issues_SelectPending", CommandType.StoredProcedure);
List
reader.Close();
return issues;
}
Figure 9: Returning issues to the caller.
All the SelectXXXX methods (except SelectById) return a generic List of Issue objects. The SelectAll method returns all the issues from the Issues table. It does so by calling the Issues_SelectAll stored procedure. Note the use of the ExecuteReader method of DatabaseHelper to call the stored procedure. As you might have guessed, the ExecuteReader method executes the stored procedure and returns the results in the form of DbDataReader. DbDataReader is then type casted to SqlDataReader. The FillIssues helper method is then called by passing this SqlDataReader. The SelectAll method is used while displaying a history of issues to support personnel.
The SelectById method accepts an ID of the issue to be returned and returns an instance of the Issue class containing the details. It relies on the Issues_SelectById stored procedure to fetch the data. Once the FillIssues helper method returns a generic List of Issue objects, the 0th element of the List is returned to the caller. This is because we are returning only one issue matching the specified issue ID.
It is required by our application that customers see a history of issues posted by them. For that purpose, we ll create the SelectByCustomerId method. The SelectByCustomerId method accepts the user name of the customer and returns a generic List of issues they posted.
We also need to display a list of all the issues selected by support personnel. The SelectBySupportPersonId method accepts the user name of support personnel. Internally it calls the Issues_SelectBySupportPersonId stored procedure.
The help desk needs to display a list of all the issues not yet assigned to any support personnel (we call these pending issues). This is done via the SelectPending method. The SelectPending method returns a generic List of Issue objects.
Setting Support Personnel and Status of an Issue
Once support personnel select an issue, their user name is assigned to the SupportPersonId column of the Issue table. In some cases the support personnel may unselect an issue that they selected previously. To accomplish these operations we ll create two methods: SetSupportPersonId and UnSetSupportPersonId (see Figure 10).
public static void SetSupportPersonId(int issueid,string supportpersonid)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@issueid", issueid);
db.AddParameter("@supportpersonid", supportpersonid);
db.ExecuteNonQuery("Issues_SetSupportPersonId", CommandType.StoredProcedure);
}
public static void UnsetSupportPersonId(int issueid)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@issueid", issueid);
db.ExecuteNonQuery("Issues_UnSetSupportPersonId", CommandType.StoredProcedure);
}
Figure 10: Assigning support personnel to an issue.
The SetSupportPersonId method accepts an issue ID for which the support personnel is to be assigned, as well as the support personnel s user name. It then calls the Issues_SetSupportPersonId stored procedure using the ExecuteNonQuery method of the DatabaseHelper class. The UnSetSupportPersonId method accepts an issue ID from which the support personnel is to be removed. It then calls the Issues_UnSetSupportPersonId stored procedure. The Issues_UnSetSupportPersonId stored procedure simply sets the SupportPersonId column to NULL.
When a new issue is posted its status is Open (O). When support personnel marks the issue as closed the status must become C (Closed). Further, when a customer responds to a closed issue, the issue again becomes open. Thus, we need a method that sets the status of an issue, such as the SetStatus method (see Figure 11).
public static void SetStatus(int issueid, char status)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@issueid", issueid);
db.AddParameter("@status", status);
db.ExecuteNonQuery(
"Issues_SetStatus", CommandType.StoredProcedure);
}
Figure 11: Setting status of an issue.
The SetStatus method accepts the issue ID whose status is to be set, as well as the status itself. The status parameter can be O or C, depending on whether the status is to be marked as Open or Closed. It then calls the Issues_SetStatus stored procedure. The Issues_SetStatus stored procedure simply sets the Status column of the Issue table to the supplied status value.
Now that we ve completed the Issues class, it s time to create the IssueResponses class. The IssueResponses class contains several methods for managing responses to issues. The next few sections will discuss these methods in detail.
Adding a Response to an Issue
To post a response we ll create the Add method for the IssueResponses class (see Figure 12).
public static void Add(int issueid,
string title, string description,string postedby)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("@issueid", issueid);
db.AddParameter("@title", title);
db.AddParameter("@description", description);
db.AddParameter("@postedby", postedby);
db.ExecuteNonQuery("IssueResponses_Add",
CommandType.StoredProcedure);
}
Figure 12: Adding a response to an issue.
The Add method accepts the issue ID whose response is being posted, as well as the title and description of the issue and the user name of the poster. Note that a response here indicates a post from support staff and customer. Internally, the Add method calls the IssueResponses_Add stored procedure that simply adds a record in the IssueResponses table.
Returning a List of Responses
As in the case of issues, we also need to return responses to the issues in our application. The responses are returned as a generic List of IssueResponse objects. We ve created the FillIssues method to create and fill Issue objects in a generic List. Now we ll create the FillIssueResponses helper method for filling a List of IssueResponse objects (see Figure 13).
private static List
FillIssueResponses(SqlDataReader reader)
{
List
while (reader.Read())
{
IssueResponse response = new IssueResponse();
response.ResponseID= reader.GetInt32(
reader.GetOrdinal("IssueResponseId"));
response.IssueID = reader.GetInt32(
reader.GetOrdinal("IssueId"));
response.Title = reader.GetString(
reader.GetOrdinal("issueresponsetitle"));
response.Description = reader.GetString(
reader.GetOrdinal("issueresponsedescription"));
response.PostedBy = reader.GetString(
reader.GetOrdinal("postedby"));
response.PostedOn = reader.GetDateTime(
reader.GetOrdinal("postedon"));
responses.Add(response);
}
return responses;
}
Figure 13: Filling issue responses in a generic List.
The FillIssueResponses method accepts a SqlDataReader object and returns a generic List of IssueResponse objects (see Figure 14). It iterates through SqlDataReader; each iteration adds to the List a new IssueResponse object filled with data. Finally, the List is returned back to the caller. The FillIssueResponses helper method is used by the SelectByIssueId method, which is discussed next.
public static List
SelectByIssueId(int issueid)
{
DatabaseHelper db = new DatabaseHelper();
db.AddParameter("issueid", issueid);
SqlDataReader reader=(
SqlDataReader)db.ExecuteReader(
"IssueResponses_SelectByIssueId",
CommandType.StoredProcedure);
List
return responses;
}
Figure 14: Returning all the responses for an issue.
The SelectByIssueId method of the IssueResponses class accepts an issue ID and returns all the responses for that issue ID. Inside it calls the IssueResponses_SelectByIssueId stored procedure to retrieve all records from the IssueResponses table matching the supplied issue ID. The records are obtained in the form of a SqlDataReader object. The SelectByIssueId then uses the FillIssueResponses helper method to get a List of IssueResponse objects. Finally, the responses are returned to the caller.
That s it! We ve completed all the classes needed by our help desk application. Our next task will be to configure the Web site and design the Web forms, which we ll do in Part II.
Conclusion
The help desk application makes use of ASP.NET 2.0, ASP.NET AJAX Extensions, and a SQL Server 2005 database. In this part we developed all the classes required for our application, namely Issue, IssueResponse, Issues, and IssueResponses. The classes Issues and IssueResponses contain several static methods for manipulating the Issues and IssueResponses tables, respectively. The interlayer data transfer is done with the help of a generic-based List class. As you ll see, layering generic-based collections provides typed access to your data. Additionally, it performs better as compared to DataSet- or ArrayList-based data transfer. Part II of this article will discuss Web site configuration and Web form design.
The sample code for Part I is available for download.
Bipin Joshi is the founder and owner of BinaryIntellect Consulting (http://www.binaryintellect.com), where he conducts professional training programs on .NET technologies. He is the author of Developer s Guide to ASP.NET 2.0 (http://www.binaryintellect.com/books) and co-author of three Wrox books on .NET 1.x. He writes regularly for http://www.DotNetBips.com, a community Web site he founded in the early days of .NET. He is a Microsoft MVP, MCAD, MCT, and member of ASPInsiders. He jots down his thoughts about .NET, life, and Yoga at http://www.bipinjoshi.com. He also conducts workshops on Yoga and Meditation, where he helps IT professionals develop a positive personality. You can contact him at mailto:[email protected].
Begin Listing One The Issue class
public class Issue
{
private int intIssueId;
private string strTitle;
private string strDescription;
private string strPostedBy;
private DateTime dtPostedOn;
private DateTime dtResolvedOn;
private char chrStatus;
private string strSupportPersonId;
public int IssueID
{
get
{
return intIssueId;
}
set
{
intIssueId = value;
}
}
public string Title
{
get
{
return strTitle;
}
set
{
strTitle = value;
}
}
public string Description
{
get
{
return strDescription;
}
set
{
strDescription = value;
}
}
public string PostedBy
{
get
{
return strPostedBy;
}
set
{
strPostedBy = value;
}
}
public DateTime PostedOn
{
get
{
return dtPostedOn;
}
set
{
dtPostedOn = value;
}
}
public DateTime ResolvedOn
{
get
{
return dtResolvedOn;
}
set
{
dtResolvedOn = value;
}
}
public string SupportPersonID
{
get
{
return strSupportPersonId;
}
set
{
strSupportPersonId = value;
}
}
public char Status
{
get
{
return chrStatus;
}
set
{
chrStatus = value;
}
}
}
End Listing One
Begin Listing Two The IssueResponse class
public class IssueResponse
{
private int intResponseId;
private int intIssueId;
private string strTitle;
private string strDescription;
private string strPostedBy;
private DateTime dtPostedOn;
public int ResponseID
{
get
{
return intResponseId;
}
set
{
intResponseId = value;
}
}
public int IssueID
{
get
{
return intIssueId;
}
set
{
intIssueId = value;
}
}
public string Title
{
get
{
return strTitle;
}
set
{
strTitle = value;
}
}
public string Description
{
get
{
return strDescription;
}
set
{
strDescription = value;
}
}
public string PostedBy
{
get
{
return strPostedBy;
}
set
{
strPostedBy = value;
}
}
public DateTime PostedOn
{
get
{
return dtPostedOn;
}
set
{
dtPostedOn = value;
}
}
}
End Listing Two
