The HelpDesk Application
| The HelpDesk application can be used to support your products online. This application lets users search through problems that have been posted and read the resolutions to the problems. Users also can post new problems, for which you can find and post resolutions. This application can save your company a tremendous amount of money because if your support staff is competent and conscientious, the online Help Desk will reduce your phone support costs immensely. The HelpDesk application can be separated into two distinct sections. The first section is what all users see, including the screens with which they can search for problems and their resolutions. The second distinct part of this program is the administrative section, where a person with administrative rights can come to administer various activities related to the program, such as adding administrative accounts and entering the resolution for problems.
A number of source code modules make up the HelpDesk application. Table 4.3 shows the important methods comprising the application, the listing the methods are in, the source code modules in which they can be found, and a description. Not every source code module is shown in this chapter because many of them are similar and need not be explained separately. All the pages that are similar fall into the administrative section of the application. I have shown, in Listings 4.19, 4.20, and 4.21, the Admin-Administrators source code module, which is almost identical to the other administrative modules. Listing 4.12 contains three of the methods from Default.aspx.cs that are important. The Page_Load() method simply takes information from two session variables, UserName and LastOn, and displays them on the screen so it is easy to see who has logged on and what the last date/time of logon is. The LogIn_Click() method uses the FormsAuthentication class to log the user in. This application uses FormAuthentication, and the SignOut() method is used to actually log a user out. Once the user has been signed out, he is redirected to the login page. The SearchID_Click() method is fired when a user clicks on a search ID button. The first thing this method does is take a look at the editable text field with the identifier of LookupID. If no data is in this field, then no search can be carried out. When that is the case, an error message is displayed in the page, and the return is performed. Later in this method, the data that is found in the LookUpID field is converted into an integer. If the data in the text field isn't numeric (for instance, if the user typed in her name or something else like that) then an exception will be thrown, caught, and no action will be taken. However, if the LookUpID field is successfully converted to an integer value, the user will be redirected to the ShowIncident page, and the converted IncidentID will be carried as a parameter, which will be retrieved with the QueryString() method.
RECOMMENDED PRACTICE: You might normally expect a user to enter a numeric value when that is called for in the user interface, but to take it as a given that he will is not a good idea. If the user types in something that is not numeric, and you try to evaluate the data with one of the Convert methods, doing so could very well and probably will throw an exception. You can protect yourself from this in two ways. The first is to use a validator that contains a regular expression that matches only numerics. This validator will not let users proceed with non-numerics in the field. Additionally, you should catch any exceptions that might be thrown when they're being converted from a text field to numeric data. You can see the HelpDesk application's main screen in Figure 4.1. It is clear and easy to understand. Listing 4.12 Methods in Default.aspx.cs private void Page_Load(object sender, System.EventArgs e) { UserInfo.Text = "User Name:" + Convert.ToString( Session["UserName"] ) + ", Last On:" + Convert.ToDateTime( Session["LastOn"] ).ToString(); } private void Logout_Click(object sender, System.EventArgs e) { FormsAuthentication.SignOut(); Response.Redirect( "Login.aspx" ); } private void SearchID_Click(object sender, System.EventArgs e) { if( LookupID.Text.Trim().Length == 0 ) { ErrorMessage.Text = "There is nothing in the ID field.<br>"; return; } try { int nIncidentID = Convert.ToInt32( LookupID.Text.Trim() ); Response.Redirect( "ShowIncident.aspx?Incident<br>"; } } Figure 4.1. The Main Screen Is Compact and Offers the Choices for Normal Users.
The code in Listing 4.13 contains the rest of the methods from Default.aspx.cs. The ViewID_Click() method first makes sure that the ID field contains data. If it does not, then the program displays an error message and simply returns. A SqlConnection object is created, and the connection string is obtained from the Application("DBConnectString") object. The try/catch block is set up so that exceptions resulting from numeric parse errors and database errors will be caught and errors displayed. The first thing inside the try block is a conversion from the OpenID field into an integer, which represents the ID of the incident that is to be viewed. This code includes a chunk of database-related code. The SqlConnection object is opened, a command is created (which is a select for the incident table), and a SqlDataReader is obtained from the SqlCommand object's ExecuteReader() method. Then the SqlDataReader is used to see whether any matching records exist, and if so, the user ID is obtained from the SqlDataReader object. This piece is important because the user who is logged on must match the user ID which matches the incident object. This requirement is so that users who do not create this particular incident cannot edit it. Once the query has been performed, the SqlDataReader and the SqlConnection objects are closed. If no record was found, an error message is displayed for the page and a return is performed. The session ID that contains the user type is then evaluated using Convert.ToInt32() method. If the user type is 1, the user that is logged on has administrative rights. If the user type is 0, this user has no administrative rights, and the incident creator ID must match the logged user's ID. If the person logged on does not have administrative rights, and the incident ID does not match the user ID, then an error message is displayed to the user, and a return is performed. If everything matches up, though, the user is redirected to the submit problem page, or the incident will be displayed and offer the user the capability to edit. The Search_Click() method simply redirects the user to the ViewProblemList page. If the search criteria is carried to the ViewProblemList page as a parameter, and then it is used in the query. Listing 4.13 Methods in Default.aspx.cs private void ViewID_Click(object sender, System.EventArgs e) { if( OpenID.Text.Trim().Length == 0 ) { ErrorMessage.Text = "There is nothing in the ID field.<br>"; return; } SqlConnection objConnection = new SqlConnection( Convert.ToString( Application["DBConnectString"] ) ); try { int nIncidentID = Convert.ToInt32( OpenID.Text.Trim() ); objConnection.Open(); SqlCommand objCommand = new SqlCommand( "SELECT UserID FROM Incident WHERE UserID"] ); reader.Close(); objConnection.Close(); if( !bFound ) { ErrorMessage.Text = "No match was found for the entered ID.<br>"; return; } if( Convert.ToInt32( Session["UserType"] ) == 1 ) { Response.Redirect( "SubmitProblem.aspx?IncidentUserID"] ) != nUserID ) { ErrorMessage.Text = "The current user cannot edit that problem.<br>"; return; } Response.Redirect( "SubmitProblem.aspx?Incident<br>"; } } private void Search_Click(object sender, System.EventArgs e) { Response.Redirect( "ViewProblemList.aspx?SearchCriteria=" + SearchSpec.Text ); } Listing 4.14 has code that can be found in a Login.aspx.cs file. It is important to know that this program uses forms authentication, and that the user must log in, as shown in Figure 4.2. Figure 4.2. Before Using the HelpDesk Application, You Must Log In.
There are two AddParameter() methods. These methods make it easy to add parameters to SqlCommand objects. Using these two methods reduces the amount of code in this source module. Less code makes not only for a more efficient assembly because smaller chunks of code execute faster but also makes understanding and maintaining the code easier. The first AddParameter() method lets you specify the size of the parameter type. Doing this is important for types that have variable lengths, such as varchar and nvarchar (as opposed to integers, which are 4 bytes). The second AddParameter() method does not let you specify the size, and it works great when you are using integers and things that always have a constant size. Both of these methods set the parameter direction for output. This setting is perfectly fine for the code found in the Login_Click() method because it doesn't need to add parameters for any other direction except output. Next, we will take a look at the Login_Click() method. This method creates a SqlConnection object and gets the connect string from the application variable that we have already created in this application. A try/catch block makes sure that any exceptions that are thrown are caught, and that means the error messages are displayed to users. First, the connection is opened. Next, a SQL string is created based on the user name found in the UserName editable text field. The SqlCommand object is created, and then six parameters are added. These parameters are @Password, @ID, @UserType, @LastOn, @Name, and @Email. After adding these parameters, the SqlCommand object ExecuteNonQuery() method is called. Some local variables I declared are used in this method. They will contain the user ID, the user type, the date that the user was last on, the user name, and the user e-mail address. If the user has checked the new user checkbox in the user interface, then we execute code that creates a new record. This is the first code that is seen after the local variables are declared. The first thing that is done is to check the ID field that was returned from the query. If the ID is found, then this indicates the user name already exists in the database. If that is the case, the connection is closed, and we bail out of the method with a return. If, however, the user name is not found in the database, a new record is created using a SQL Insert command. The ID that was retrieved is then stored in the local ID variable for use later in the code, and the user name is stored in the Session["UserName""] variable. If the user is trying to log on and has not checked the new user box, a chunk of code is executed that checks the user's credentials against the database information. If the ID field is null, then a matching record was not found. When this is the case, an error message is shown to the user and a return is performed. The password is retrieved from the query that was performed earlier in this method, and if the password does not match the password the user typed in, the user is alerted to the mismatch, the connection object is closed, and we bail out of the method with a return. If the user name has been found and the password matches, then all the information needed is retrieved from the parameters of the Command object. The last piece of database code that is performed is to update the date when the user has been logged on. This update is done so that we always know the last time of day that the user was logged on. The last piece of code performed in this method is to set session variables so that they contain the relevant information. This information includes the user ID, the user type, the date the user was last on, the user name, and the user e-mail address. Finally, the user is redirected to the Default.aspx page. Because this application uses forms authentication, this redirect is done using the forms authentication class. Listing 4.14 Methods in Login.aspx.cs[View full width] private void AddParameter( ref SqlCommand objCommand, string strName, SqlDbType Type, int The code show in Listing 4.15 is from the ShowIncident.aspx.cs file. In the Page_Load() method, the first thing that happens is that the value contained in the query string IncidentID is converted to an integer. This integer will represent the database ID that will uniquely identify this particular incident. A hidden text field named problem ID will maintain this value for later use. You can see this text field being assigned the value that the incident ID contains. A SqlConnection is created and a try/catch block follows. The SqlConnection is opened with the Open() method, and a string that contains the query is informed. Essentially, the query will select user ID location, phone, title, description, and resolution from the database incident corresponding to this ID. A SqlCommand object is created with the query string. And, finally, a SqlDataReader is obtained by executing the SqlCommand object's ExecuteReader() method.
If a record has been found, then the SqlDataReader.read() method will return True and code will execute that populates the user interface object with the information found in the database. For instance, the problem title, the description, and the resolution are retrieved from the database and placed in user-interface elements. The location and phone information are also retrieved and placed into a string variable that will be used shortly. A new query is made to retrieve information about the particular user who submitted this problem. The user information is queried from the UserInfo table, and if a match is found, this information, along with the location and phone information, forms the information in the form that will identify the submitter and all pertinent information. The catch block closes the SqlConnection object and shows the user the error message. Listing 4.15 Methods in ShowIncident.aspx.cs private void Page_Load(object sender, System.EventArgs e) { int nIncidentID = Convert.ToInt32( Request.QueryString["IncidentID"] ); ProblemID.Text = Convert.ToString( nIncidentID ); SqlConnection objConnection = new SqlConnection( Convert.ToString( Application["DBConnectString"])); try { objConnection.Open(); string strSql = "SELECT UserID,Location,Phone,Title,Description,Resolution" + "FROM Incident WHERE Title"] ); Description.Text = Convert.ToString( reader["Description"] ); string strResolution = Convert.ToString( reader["Title"] ); if( strResolution.Length == 0 ) { strResolution = "None yet..."; } Resolution.Text = strResolution; string strLocation = Convert.ToString( reader["Location"] ); string strPhone = Convert.ToString( reader["Phone"] ); int nUserID = Convert.ToInt32( reader["UserID"] ); reader.Close(); string strUserName = ""; string strEmail = ""; strSql = "SELECT Name,Email FROM UserInfo WHERE Name"] ); strEmail = Convert.ToString( reader["Email"] ); } reader.Close(); Submitter.Text = strUserName + ", Email:" + strEmail + ", Location:" + strLocation + ", Phone:" + strPhone; } else { reader.Close(); ProblemTitle.ForeColor = Color.Red; ProblemTitle.Text = "No records found!"; } objConnection.Close(); } catch( Exception ex ) { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } ProblemTitle.ForeColor = Color.Red; ProblemTitle.Text = ex.Message.ToString(); } } Listing 4.16 shows the code that is called when the SubmitProblem page is loaded. The code is actually performed only the first time through and not for any subsequent post backs. The first thing the code does is retrieve an ID from the query string that was passed into the page. Then it retrieves the user name and e-mail addresses from two session variables that were previously set. The database code is fairly simple. A SqlConnection object is created and then opened. A SqlCommand object is created with a simple query that retrieves the ID and name columns from the Category table. The record set that is returned will be contained in a SqlDataReader object, and this DataReader object will then be bound to the category list box that is part of the user interface. Another SqlCommand object is created that retrieves all the departments from the database. The record set contained in the departments is then bound to the department list box that is also part of the user interface. If the variable IncidentId is greater than 0, this means that there was an IncidentId passed in the query string. An entire block of code is performed if that is the case. This block of code retrieves the information related to this incident from the database and then populates the user interface objects with the information. A SqlCommand object is created in which the query that retrieves the correct indicant from the database is created. The reader's Execute Reader() method is called, and a SqlDataReader object is returned. If the data reader's Read() method returns True, this means a record was returned and is part of a record set. If that is the case, then the fields from the record set will be used to populate the user interface object for the page. Two small details must be attended to for the user interface to correctly reflect the retrieved information. The Category drop-down list must be set to the correct category in which this incident belongs, and the Department drop-down list must be set to correctly reflect the department to which this incident belongs. You can see two for loops, each of which sets the correct item in a category list and a department list, respectively. You can see in Figure 4.3 the submission screen with which users can submit or edit problems. Listing 4.16 Methods in SubmitProblem.aspx.cs private void Page_Load(object sender, System.EventArgs e) { if( !IsPostBack ) { int nIncidentID = Convert.ToInt32( Request.QueryString["IncidentID"] ); UserName.Text = Convert.ToString( Session["UserName"] ); Email.Text = Convert.ToString( Session["Email"] ); SqlConnection objConnection = new SqlConnection(Convert.ToString(Application["DBConnectString"])); try { objConnection.Open(); SqlCommand objCommand = new SqlCommand( "SELECT ID,Name FROM Category",objConnection); SqlDataReader reader = objCommand.ExecuteReader(); Category.DataTextField = "Name"; Category.DataValueField = "ID"; Category.DataSource = reader; Category.DataBind(); reader.Close(); objCommand = new SqlCommand("SELECT ID,Name FROM Department",objConnection); reader = objCommand.ExecuteReader(); Department.DataTextField = "Name"; Department.DataValueField = "ID"; Department.DataSource = reader; Department.DataBind(); reader.Close(); if( nIncidentID > 0 ) { ScreenTitle.Text = "Edit Problem"; Button1.Text = "Update"; ForUpdate.Text = "1"; IncidentID.Text = Convert.ToString( nIncidentID ); objCommand = new SqlCommand( "SELECT Title,Location,Resolution,Phone, " + "CatID,DeptID,Description FROM Incident WHERE Title"] ); Location.Text = Convert.ToString( reader["Location"] ); Phone.Text = Convert.ToString( reader["Phone"] ); Description.Text = Convert.ToString( reader["Description"] ); Resolution.Text = Convert.ToString( reader["Resolution"] ); int nCatID = Convert.ToInt32( reader["CatID"] ); int DeptID = Convert.ToInt32( reader["DeptID"] ); for( int i=0; i<Category.Items.Count; i++ ) { if(Convert.ToInt32(Category.Items[i].Value)==nCatID) { Category.SelectedIndex = i; break; } } for( int i=0; i<Department.Items.Count; i++ ) { if(Convert.ToInt32(Department.Items[i].Value)==nCatID) { Department.SelectedIndex = i; break; } } } reader.Close(); } objConnection.Close(); } catch { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } } } } Figure 4.3. Users Can Submit Problems for Resolution.
The code in Listing 4.17 is used to respond to user events from the SubmitProblem page. The first message you see is from the MainMenu_ Click() method. This message simply redirects to the main menu page. The UpdateEmail() method takes a look at the e-mail address that the user has entered. It saves this e-mail address to the database for this particular user. It is important to note, though, that the UpdateEmail() method is not called unless the e-mail address is blank or the user has made a change in his e-mail address. The Save_Click() method is the one in which a great deal of the work is done. First, it takes a look at the query string named NE. This query string will indicate whether or not the page is read-only. Actually, "NE" stands for "no edit." If this particular page has been called and is set for no edit, at this point the user is redirected to the main page. A SqlConnection object is created and then opened. Right below where you see the SqlConnection object being opened, you will see that the e-mail address is examined to see whether it is either blank or has changed. If it has changed, then the UpdateEmail() method is called. A hidden text field named ForUpdate indicates whether or not this particular incident record can be updated. If it can't be updated, then a new record must be created. You can see in the code when a new record is created because the SQL statement uses the insert command. After the query string has been created, a SqlCommand object is created, and an ID parameter is added so that the newly created identity field, which represents the ID value, can be returned after the SQL command has been executed. Moving down a few lines, you can see where an update statement has been created. This code updates an incident record rather than creating a new incident record. You can see that the SQL string is created, a SqlCommand object is created, and the Command object's ExecuteNonQuery() method is called. The user is then redirected to a page ThanksForNew. This page acknowledges the user's interaction and thanks him. Listing 4.17 Methods in SubmitProblem.aspx.cs private void MainMenu_Click(object sender, System.EventArgs e) { Response.Redirect( "Default.aspx" ); } private void UpdateEmail( SqlConnection objConnection, string strEmail ) { Session["Email"] = strEmail; string strSql = "Update UserInfo set Email='" + strEmail + "' WHERE UserID"] ); SqlCommand objCommand = new SqlCommand( strSql, objConnection ); objCommand.ExecuteNonQuery(); } private void Save_Click(object sender, System.EventArgs e) { if( Convert.ToInt32( Request.QueryString["NE"] ) == 1 ) { Response.Redirect( "default.aspx" ); } ErrorMessage.Text = ""; SqlConnection objConnection = new SqlConnection( Convert.ToString( Application["DBConnectString"] ) ); try { objConnection.Open(); if( Convert.ToString( Session["Email"] ).Length == 0 && Email.Text.Trim().Length > 0 ) { UpdateEmail( objConnection, Email.Text.Trim() ); } int nIncidentID; if( ForUpdate.Text == "0" ) { string strSql = "INSERT INTO Incident (UserID,CatID,DeptID, " + "Location,Phone,Title,Description) VALUES (" + Convert.ToString( Session["UserID"] ) + "," + Category.SelectedItem.Value + "," + Department.SelectedItem.Value + ",'" + Location.Text.Replace( "'", "`" ) + "','" + Phone.Text + "','" + Title.Text.Replace( "'", "`" ) + "','" + Description.Text.Replace( "'", "`" ) + "') SELECT @ID=@@IDENTITY"; SqlCommand objCommand = new SqlCommand( strSql, objConnection ); objCommand.Parameters.Add( "@ID", SqlDbType.Int ); objCommand.Parameters["@ID"].Direction = ParameterDirection.Output; objCommand.ExecuteNonQuery(); nIncidentID = Convert.ToInt32( objCommand.Parameters["@ID"].Value ); } else { string strSql = "Update Incident Set Cat,Dept,Location='" + Location.Text.Replace( "'", "`" ) + "',Phone='" + Phone.Text + "',Title='" + Title.Text.Replace( "'", "`" ) + "',Description='" + Description.Text.Replace( "'", "`" ) + "',Resolution='"+ Resolution.Text.Replace( "'", "`" ) + "')"; SqlCommand objCommand = new SqlCommand( strSql, objConnection ); objCommand.ExecuteNonQuery(); nIncidentID = Convert.ToInt32( IncidentID.Text ); } objConnection.Close(); Response.Redirect( "ThanksForNew.aspx?Incident<br>"; } } Listing 4.18 contains the DisplayList() method. This method is used in the ViewProblemList page to display a list of all problems that are currently in the database. A SqlConnection object is created and then opened. A query string is placed into a variable named strSql. If a search string was passed in (and this is from a query string named SearchCriteria), then this will become part of the SQL string with which the query will be made. This allows the same code to be used with or without a given search string. The SqlCommand object is created, and then its ExecuteReader() method is called. A SqlDataReader contains the data set that was returned, and this is used to iteratively create the list from which the user can make a selection. Listing 4.18 Methods in ViewProblemList.aspx.cs public void DisplayList() { SqlConnection objConnection = new SqlConnection(Convert.ToString(Application["DBConnectString"])); try { objConnection.Open(); string strSql = "SELECT Title,ID FROM Incident ORDER BY Title"; string strSearch = Request.QueryString["SearchCriteria"].Trim().Replace("'","`"); if( strSearch.Length > 0 ) { strSql = "SELECT Title,ID FROM Incident where(Description like '%"+ strSearch + "%' or Resolution like '%" + strSearch + "%' or Title like '%" + strSearch + "%') ORDER BY Title"; } SqlCommand objCommand = new SqlCommand( strSql, objConnection ); SqlDataReader reader = objCommand.ExecuteReader(); bool bResultsFound = false; while( reader.Read() ) { bResultsFound = true; Response.Write( "<tr><td>" + reader["Title"].ToString() + "</td><td><a href=\"ShowIncident.aspx?IncidentID"].ToString() + "\">View</a></td></td>\r\n" ); } if( !bResultsFound ) { Response.Write( "<tr><td>There were no " + "results found</td><td> </td></td>\r\n" ); } objConnection.Close(); } catch( Exception ex ) { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } Response.Write( "<tr><td>" + ex.Message.ToString() + "</td></tr>" ); } } Users who are logged on with administrative rights are brought to a different screen by default once they log on. As you can see in Figure 4.4, this menu offers four buttons plus the opportunity to go back to the main page. This screen offers users four choices for administering the HelpDesk application. They can add, edit, and delete administrators; add categories; add departments; and edit incidences. Figure 4.4. If You Log In As an Administrator, You Are Brought to a Different Screen than Normal Users.
The four pages with which administrators can administer the application are similar, if not practically identical, to each other. For this reason, I am showing you only the code in the AdminAdministrators page. The first thing that happens when the AdminAdministrators page is loaded is that a session variable that contains the user type is checked to make sure that the user has administrative rights. If she doesn't, then she is redirected to a page called Unauthorized.aspx. If she does have the right, and this is not a post back, then the PopulateAdministratorList() method is called. This method populates a DataList that contains all current administrators for the application. The PopulateAdministratorList() method is fairly simple. This method creates and opens a SqlConnection object. It creates a SqlCommand object to retrieve all the information about users who have administrative rights. The returned SqlDataReader object is then bound to the data list named AdministratorList. You can see the AdminAdministrator screen in Figure 4.5. Listing 4.19 Methods in AdminAdministrators.aspx.cs private void Page_Load(object sender, System.EventArgs e) { if( Convert.ToInt32( Session["UserType"] ) != 1 ) { Response.Redirect( "Unauthorized.aspx" ); } if( !IsPostBack ) { PopulateAdministratorList(); } } private void PopulateAdministratorList() { SqlConnection objConnection = new SqlConnection(Convert.ToString(Application["DBConnectString"])); try { objConnection.Open(); SqlCommand objCommand = new SqlCommand( "SELECT Name,ID FROM UserInfo WHERE " + "UserType=1 ORDER BY Name", objConnection ); SqlDataReader reader = objCommand.ExecuteReader(); AdministratorList.DataTextField = "Name"; AdministratorList.DataValueField = "ID"; AdministratorList.DataSource = reader; AdministratorList.DataBind(); reader.Close(); objConnection.Close(); } catch( Exception ex ) { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } ErrorMessage.Text = ex.Message.ToString(); } } private void MainMenu_Click(object sender, System.EventArgs e) { Response.Redirect( "Default.aspx" ); } private void AdminMenu_Click(object sender, System.EventArgs e) { Response.Redirect( "Admin.aspx" ); } Figure 4.5. The Administrative Screens Are All Similar, and Each Offers Maintenance for Different Application Sets of Information.
Listing 4.20 contains the methods that fire when the user clicks any of the Update, the Delete, or the New buttons. The Update_Click() method updates a record in the database. It retrieves the information from the user interface (and this includes the administrator name and password) and then simply updates the database with the newly edited information. The Delete_Click() method deletes the currently selected administrator from the database. It does this by creating a SqlConnection object and opening it. It then creates a SqlCommand object and forms a query based on the current ID. After the method performs the SQL command, the user interface objects are cleared out to indicate that the user is no longer there. The New_Click() method simply sets all the user interface objects so that they are empty and can receive the information for a new administrator. The method also sets the AdministratorListSelectedIndex to 1 so that when the page is displayed none of the items in the administrator list will be selected. Listing 4.20 Methods in AdminAdministrators.aspx.cs private void Update_Click(object sender, System.EventArgs e) { ErrorMessage.Text = ""; int nID = Convert.ToInt32( AdministratorList.SelectedItem.Value ); string strSql; if( nID <= 0 ) { strSql = "INSERT INTO UserInfo (Name,Password,UserType) VALUES ('" + UserName.Text + "','" + Password.Text + "',1)"; } else { strSql = "Update UserInfo set Name='" + UserName.Text + "',Password='" + Password.Text + "' WHERE DBConnectString"])); try { objConnection.Open(); SqlCommand objCommand = new SqlCommand( strSql, objConnection ); objCommand.ExecuteNonQuery(); objConnection.Close(); if( nID <= 0 ) { PopulateAdministratorList(); UserName.Text = ""; Password.Text = ""; AdminID.Text = "0"; } ErrorMessage.Text = "Administrative user has been updated/added"; } catch( Exception ex ) { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } ErrorMessage.Text = ex.Message.ToString(); } } private void Delete_Click(object sender, System.EventArgs e) { ErrorMessage.Text = ""; int nID = Convert.ToInt32( AdministratorList.SelectedItem.Value ); if( nID <= 0 ) { ErrorMessage.Text = "There is no currently selected user"; return; } SqlConnection objConnection = new SqlConnection(Convert.ToString(Application["DBConnectString"])); try { objConnection.Open(); SqlCommand objCommand = new SqlCommand( "DELETE FROM UserInfo WHERE "; Password.Text = ""; AdminID.Text = "0"; ErrorMessage.Text = "Administrative user has been deleted"; } catch( Exception ex ) { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } ErrorMessage.Text = ex.Message.ToString(); } } private void New_Click(object sender, System.EventArgs e) { AdministratorList.SelectedIndex = -1; UserName.Text = ""; Password.Text = ""; AdminID.Text = "0"; ErrorMessage.Text = "Enter information for new administrative user and click Update"; } Listing 4.21 contains a single method named Administrator_ SelectedIndexChanged(). This method gets the ID and user name from the currently selected item in the administrator list object. Using this information, it retrieves the password from the database that corresponds to this particular user. It then populates all the user interface objects with the information for the selected administrator, which then gives the user the opportunity to edit all this information. Listing 4.21 Methods in AdminAdministrators.aspx.cs private void Administrators_SelectedIndexChanged(object sender, System.EventArgs e) { ErrorMessage.Text = ""; AdminID.Text = Convert.ToString( AdministratorList.SelectedItem.Value ); UserName.Text = Convert.ToString( AdministratorList.SelectedItem.Text ); SqlConnection objConnection = new SqlConnection(Convert.ToString(Application["DBConnectString"])); try { objConnection.Open(); SqlCommand objCommand = new SqlCommand( "SELECT Password FROM UserInfo WHERE Password"] ); } else { Password.Text = ""; } reader.Close(); objConnection.Close(); } catch( Exception ex ) { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } ErrorMessage.Text = ex.Message.ToString(); } } |
nSize ) { objCommand.Parameters.Add( strName, Type, nSize ); objCommand.Parameters[strName].Direction = ParameterDirection.Output; } private void AddParameter( ref SqlCommand objCommand, string strName, SqlDbType Type ) { objCommand.Parameters.Add( strName, Type ); objCommand.Parameters[strName].Direction = ParameterDirection.Output; } private void Login_Click(object sender, System.EventArgs e) { SqlConnection objConnection = new SqlConnection( Convert.ToString( Application["DBConnectString"] ) ); try { objConnection.Open(); string strSql = "SELECT @ID=ID,@UserType=UserType,@Password=Password, " + "@LastOn=DateLastOn,@Name=Name,@Email=Email " + "FROM UserInfo WHERE Name='" + UserName.Text.Trim() + "'"; SqlCommand objCommand = new SqlCommand( strSql, objConnection ); AddParameter( ref objCommand, "@Password", SqlDbType.VarChar, 254 ); AddParameter( ref objCommand, "@ID", SqlDbType.Int ); AddParameter( ref objCommand, "@UserType", SqlDbType.Int ); AddParameter( ref objCommand, "@LastOn", SqlDbType.DateTime ); AddParameter( ref objCommand, "@Name", SqlDbType.VarChar, 254 ); AddParameter( ref objCommand, "@Email", SqlDbType.VarChar, 254 ); objCommand.ExecuteNonQuery(); int nID, nUserType = 0; DateTime objLastOn = DateTime.Now; string strUserName = ""; string strEmail = ""; if( NewUser.Checked ) { if( !Convert.IsDBNull( objCommand.Parameters["@ID"].Value )) { ErrorMessage.Text = "The user name you entered already exists."; objConnection.Close(); return; } strSql = "INSERT INTO UserInfo (Name,Password) VALUES('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "') SELECT @ID=@@IDENTITY"; objCommand = new SqlCommand( strSql, objConnection ); AddParameter( ref objCommand, "@ID", SqlDbType.Int ); objCommand.ExecuteNonQuery(); nID = Convert.ToInt32( objCommand.Parameters["@ID"].Value ); strUserName = UserName.Text; } else { if( Convert.IsDBNull( objCommand.Parameters["@ID"].Value ) ) { ErrorMessage.Text = "The user name you entered does not exist."; objConnection.Close(); return; } string strPassword = Convert.ToString( objCommand.Parameters["@Password"].Value); if( strPassword.ToUpper() != Password.Text.Trim().ToUpper()) { ErrorMessage.Text = "The password you entered is incorrect."; objConnection.Close(); return; } nID = Convert.ToInt32( objCommand.Parameters["@ID"].Value ); nUserType = Convert.ToInt32( objCommand.Parameters["@UserType"].Value); objLastOn = Convert.ToDateTime( objCommand.Parameters["@LastOn"].Value); strUserName = Convert.ToString( objCommand.Parameters["@Name"].Value ); strEmail = Convert.ToString( objCommand.Parameters["@Email"].Value ); objCommand = new SqlCommand( "update UserInfo set DateLastOn='" + DateTime.Now.ToShortDateString() + "'", objConnection ); objCommand.ExecuteNonQuery(); } objConnection.Close(); Session["UserID"] = nID; Session["UserType"] = nUserType; Session["LastOn"] = objLastOn; Session["UserName"] = strUserName; Session["Email"] = strEmail; FormsAuthentication.RedirectFromLoginPage( UserName.Text,false); } catch( Exception ex ) { ErrorMessage.Text = ex.Message.ToString(); } finally { if( objConnection.State == ConnectionState.Open ) { objConnection.Close(); } } } 
EAN: N/A
Pages: 175