Create a Help Desk Web Application Using ASP.NET | 2

Create a Help Desk Web Application Using ASP.NET [con't]

Creating the JavaScript File

Because creating the JavaScript file is the easiest of the work left, we'll do this next. From the Solution Explorer, follow these steps:

Right click on the project name.
Choose Add>New Item.
In the Add New Item window, select Jscript File.
In the name text box, name the file clear.js.

Reference the JavaScript File

We need to reference the JavaScript file so our page knows JavaScript is there. Let's add the code to do this as shown below:

As you can see from the code above, we reference our JavaScript file right below our ending style tag.

Writing the JavaScript Reset Functionality

From the Solution Explorer, double click clear.js to open the file and add the following code:

As you can see from the code above, we create a function named clear_Fields. Inside the fucntion, we grab each of our controls by its unique ID and set its value to an empty string (except for our dropdown lists, which we set to zero). Save your file(s).

Run the Project

Before we can preview the page, we need to add the code that's associated to our Submit button. Let's add this by adding the following code:

Save your file. When you initially run the project, you'll see the window in Figure 3.

Figure 3. Debugging Not Enabled Window

Click OK and you should be able to enter data in each control; press the Clear button and each control should be reset to its initial value(s). When you're done, return to Visual Studio and press the Stop button (Shift + F5). Minimize Visual Studio for the time being.

Filling the Dropdown Lists

Currently, the dropdown list(s) are not populated with data from our database table(s). We have a few different approaches we can take:

Use a SQL data source in our default.aspx file and then data bind directly to our dropdown list
Create a new C# class and use embedded SQL to query our data
Create a new C# class and use a stored procedure to query our data

Using our first option has two tradeoffs:

You have a SQL data source that has specific attributes.
You are data binding to a control directly in the ASPX page, which isn't best practice.

Using the second option is acceptable; the only tradeoff is that you have SQL embedded in your application logic, which is generally not a good thing unless it's referencing an interface or abstract class. Furthermore, if anything in that query changes, you shouldn't need to sift through application logic. That change should be made to the data access layer (DAL).

The third option is the best because it calls for using a stored procedure, which is SQL code that is executed as a function in our application logic. Plus, we'll code the operation that gets the data against a generic list collection. This means that if we ever needed to compile this class for global use, all a developer would need to do is add a reference to the DLL and know what arguments it needs or returns. So, we'll use the third option.

Stored Procedures

You can think of stored procedures in databases as blocks of SQL code that have been executed against the database. This is great for a number reasons, including:

Because the SQL code is executed against the database, it's already indexed on the database and knows the tables it needs. This enhances performance.
Since we're calling the function in our application, we aren't executing a SQL query from our application, which is faster than embedding SQL.
If the query requires a change, you change it in the saved SQL file rather than in our business layer.

Create the Stored Procedure for Severities

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 severity:

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 you can see from the SQL above, our stored procedure is named spHelpDeskGetStatuses and then we simply query our table, status. Before running the stored procedure on the database, save the stored procedure by selecting File>Save from the main menu and then saving your stored procedure to a location of your choice. Next, run the stored procedure by clicking Execute right below the main menu. If ran succesfully, SQL Server will report Commands executed successfully.

Minimize SQL Server Management Studio for the time being.

Creating the Severity Class

From the task menu, restore Visual Studio. From the Solution Explorer, right click on the project and follow these steps:

Select Add>New Item.
In the add new item window, select class.
In the name text field, type "Severity.cs".
Click add.

Double click Severity.cs from the Solution Explorer, which will show the empty class as shown below:

In order to work with our SQL database, we need to import the following libraries as shown below:

As you can see from the code above, we added three libraries. The first (System.Data) allows us to work with stored procedures in ADO.NET, the second (System.Configuration) allows us to reference a connection key from our configuration file, and the last (System.Data.SqlClient) allows us to connect to SQL Server.

Creating Our Declarations

Let's first add class-level variables so that we can get or set our data. Add the following code:

As you can see from the code above, we added a region that holds our two class-level variables. These two variables match our two columns from the database table.

Creating Our Method

From our default.aspx.cs file, it would be nice if we could create a generic container that could call a method in our class file, which in turn would call a stored procedure, return the data and then bind to our dropdown list. We can do this by creating a static method in our severity class that implements ICollection, which supports methods for dealing with collections of data -- exactly what we have. Let's add this code:

namespace HelpDesk
{
    public class Severity
    {
        #region Declarations
        public int ID { get; set; }
        public string severity { get; set; }
        #endregion
        #region Methods
        public static ICollection<Severity> GetSeverities()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mwd"].ConnectionString);
            SqlCommand cmd = new SqlCommand("spHelpDeskGetSeverities", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            List<Severity> list = new List<Severity>();
            try
            {
                Severity s = new Severity();
                s.id = 0;
                s.severity = "--select one--";
                list.Add(s);
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Severity severity = new Severity();
                    severity.id = Convert.ToInt32(rdr["ID"]);
                    severity.severity = rdr["Severity"].ToString();
                    list.Add(severity);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return list;
        }
        #endregion
    }

As you can see from the code above, we did the following:

Created a public static method, which is referencing ICollection, with a generic type of our severity class, followed by our method name
Created a connection object and passed in our configuration key from our web.config
Created a command object and passed in the stored procedure we created earlier and a connection object
Set our command object to accept a stored procedure
Created a generic list object with a type of our severity class

Before moving forward, let's briefly discuss generics. A generic list object simply holds the contents of data that isn't type specific. Generics are great for dealing with collections of data. Instead of having to worry about casting to a specific type, such as an array list or others, you can make life much easier by leaving the data structure generic to a list object.

Moving forward, if for any reason we can't open a connection to the database or read through our data reader, we wrap it inside a try/catch block that handles exceptions. Inside our try block, we do the following:

Create a new instance of our severity class and set its property, ID and severity to a constant value, before adding it to our list object.
If our connection is successful, we execute the result set to our reader object, and while inside the loop we do the following:
- Create a new istance of the severity object.
- Set each of its properties to the values from our database.
When we finish with the loop, we dispose of our command object and close out connection.
Return our list object with its data.

If you haven't saved your file(s), now would be a good time to do so.

Creating the Load Combo Boxes Method

Even though we have created the class and stored procedure to retrieve our data, we haven't "hooked up" the data to any control. Let's use our severity class as our example. From the Solution Explorer, left click the plus sign (+) next to default.aspx and then double click default.aspx.cs and insert the code as shown below:

As you can see from the code above, we created a method named LoadCombos and then did the following:

Created a generic reference to ICollection and typed it as our severity class
Called our GetSeverities method from the severity class
Set the DataTextField of our dropdown list to our class property, severity
Set the DataValueField of our dropdown list to our class property, ID
Set the DataSource of our dropdown list to our severity list object
Called the DataBind method of our dropdown list

In the page load event, let's add the following:

As you can see from the code above, if the data in the list hasn't changed, we don't need to "post back" to refresh the changes to the data. As a result, we wrap our LoadCombos method in this check to minize server hits.

Creating the Other Classes

We need to repeat the headings "create the stored procedure through creating the load combo boxes method" for the rest of our dropdown list(s):

Departments
Employee
Status

The best approach would be to create each stored procedure, then the class, and then repeat the code in LoadCombos, replacing reference names as appropriate.

Creating the Help Desk Class

Now that we have our data coming in, we need to be able to record a help desk ticket submission. We need to create an event handler in a class to handle it. Let's first create a help desk class by doing the following:

Right click the project solution.
Choose Add>New Item.
In the Add New Item window, select Class.cs.
In the name text field, type "HelpDesk" and then click Add.

Double click HelpDesk.cs from the Solution Explorer, which will show the empty class as shown below:

We need to import three libraries as shown below:

The first library (System.Data) allows us to work with stored procedures in ADO.NET, the second (System.Configuration) allows us to reference a connection key from our configuration file and the last (System.Data.SqlClient) one allows us to connect to SQL Server.

Creating Our Declarations

Let's first add our class-level variables so that we can get or set our data. Add the following code:

As you can see from the code above, we added a region that holds our class-level variables. These variables match our columns from the database table.

Creating Our Method

In order to insert our help desk ticket, we need one method that will accept our parameters and insert the record. We do this by adding the following code:

#region Methods
        public void Save()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mwd"].ConnectionString);
            SqlCommand cmd = new SqlCommand("spInsertHelpDeskTicket", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter parameterFName = new SqlParameter("@FName", SqlDbType.VarChar, 50);
            parameterFName.Value = fname;
            cmd.Parameters.Add(parameterFName);
            SqlParameter parameterLName = new SqlParameter("@LName", SqlDbType.VarChar, 50);
            parameterLName.Value = lname;
            cmd.Parameters.Add(parameterLName);
            SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.VarChar, 50);
            parameterEmail.Value = email;
            cmd.Parameters.Add(parameterEmail);
            SqlParameter parameterSeverity = new SqlParameter("@SeverityID", SqlDbType.Int);
            parameterSeverity.Value = severity;
            cmd.Parameters.Add(parameterSeverity);
            SqlParameter parameterStatus = new SqlParameter("@StatusID", SqlDbType.Int);
            parameterStatus.Value = status;
            cmd.Parameters.Add(parameterStatus);
            SqlParameter parameterDepartment = new SqlParameter("@DepartmentID", SqlDbType.Int);
            parameterDepartment.Value = department;
            cmd.Parameters.Add(parameterDepartment);
            SqlParameter parameterComments = new SqlParameter("@Comments", SqlDbType.VarChar, 250);
            parameterComments.Value = comments;
            cmd.Parameters.Add(parameterComments);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }
        #endregion

As you can see from the code above, we did the following:

Created a connection object and passed in our configuration key
Created a command object and passed in our stored procedure
Set our command object to use a stored procedure
Created a SQL parameter for each class-level variable:
- Specified the data type for each
Added each SQL parameter to our command object

If for any reason we can't open or execute our query, we wrap it inside a try/catch block and then open the connection to our database and execute the stored procedure. After the stored procedure is executed, we dispose of the command object and close our connection.

Create the Help Desk Object

From the Solution Explorer, double click default.aspx.cs and type the following method:

As you can see from the code above, we simply create a reference to the help desk object.

Creating the Submit Method

Now that we have the reference to our help desk object, let's add the following method:

As you can see from the code above, we set each of our help desk object properties to the appropriate values of our controls from our default.aspx page. Once we have that information, we call the save method, and then we toggle the visibility of our controls.

Test Drive the Application

From Visual Studio, click the green arrow button (F5) to run the project. Type data into the text fields, pick optons from the dropdown lists, and press submit. If everything works correctly, you should have a new record in the help desk table.

Summary

In this article you learned how to build a relatively simple help desk application in ASP.NET and along the way learned how to do the following:

Create a database:
- Create corresponding tables
- Create and understand stored procedures
- Understand relationships with data
- Create a database diagram to enforce referential integrity
Create a Visual Studio project:
- Create a Web form with basic controls
- Create JavaScript reset functionality
- Create corresponding C# classes and understand the basics of generics
- Create server- or client-side event handlers and understand the differences between the two

Take the knowledge you gained from this article and expand your help desk application to meet any requirement you may need.

Code Download

ASP.NET HelpDesk 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. 15, 2010

[prev]

Create a Help Desk Web Application Using ASP.NET | 2

Create a Help Desk Web Application Using ASP.NET [con't]

Creating the JavaScript File

Reference the JavaScript File

Writing the JavaScript Reset Functionality

Run the Project

Filling the Dropdown Lists

Stored Procedures

Create the Stored Procedure for Severities

Creating the Severity Class

Creating Our Declarations

Creating Our Method

Creating the Load Combo Boxes Method

Creating the Other Classes

Creating the Help Desk Class

Creating Our Declarations

Creating Our Method

Create the Help Desk Object

Creating the Submit Method

Test Drive the Application

Summary

Code Download

About the Author

Find a programming school near you