CIS407a ilabs week 3 to 7

ilab 3

 

iLab 3 of 7: User Activity Monitoring (30 points)

In this lab, we will demonstrate how to save user activity data in a database. We will be creating a new form to display the user activity data, a new dataset to contain the data, a data access class to structure the code, and a function within the data access class to save users’ activity data when users visit the Personnel form page (frmPersonnel.aspx). We will also be adding server side validation to the frmPersonnel form you added in the previous lab and update the main menu for the new functionality.

 

Please watch the tutorial before beginning the iLab.

 

Table of Contents

 

Tutorial: Part 1

 

Tutorial: Part 2

 

Tutorial: Part 3

 

Tutorial: Part 4

 

Play00:00MuteFullscreen

 

Transcript

 

Deliverables

 

NOTE

 

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step instructions.

 

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

 

All files are located in the subdirectory of the project. The project should function as specified: When you visit the Personnel form page (frmPersonnel.aspx), a record should be saved in the tblUserActivity table with the IP address, form name accessed (frmPersonnel), and the date accessed. When you click the View Activity button, you should see at least one record with this information. When the user goes to the frmPersonnel web form and enters data, the following business rules are to be enforced.

 

Fields may not be empty or filled with spaces. If any field is empty, turn that field background color to yellow and add to/create an error message to be shown in the error label.

 

The end date must be greater than the start date. If the end date is less than the start date turn both date fields yellow and add to/create an error message to be shown in the error label.

 

If all fields validate properly, then the session state items should be set properly and the user should see the frmPersonnelVerified form with all the values displayed. You will also add a new item to frmMain that will take the user to the new frmUserActivity form you added. Add the proper link and a hyperlinked image to allow the user to select this new option. Once you have verified that everything works, save your website, zip up all files, and submit it to the Dropbox.

 

Required Software

 

Microsoft Visual Studio.Net

 

Access the software at https://lab.devry.edu.

 

Steps: 1, 2, and 3

 

Lab Steps

 

STEP 1: Step Title

 

1. Open Microsoft Visual Studio.NET.

 

2. Open the PayrollSystem website by clicking on it in the Recent Projects list, or by pulling down the File menu, selecting Open Website, navigating to the folder where you previously saved the PayrollSystem, and clicking Open.

 

3. Download the PayrollSystem_DB.accdb file from Doc Sharing and save it on your local computer. (Note: your operating system may lock or block the file. Once you have copied it locally, right click on the file and select Properties and then Unblock if available). Then add it to the PayrollSystem website as follows: In Visual Studio, in the Solution Explorer click Website, Add Existing Item, then navigate to the PayrollSystem_DB.accdb file you downloaded, and click the Add button.

 

Make sure you select file types, which include *.accdb, *.accdb, etc. Otherwise, you will not be able to see the database file to select.

 

4. Now we need to create a new connection to the PayrollSystem_DB.accdb. To begin, click View Server Explorer.

 

5. When the Server Explorer toolbox appears, click the Connect to Database button.

 

6. When the Add Connection dialog appears, click the Change button. In the Change Data Source dialog, select MS Access Database File; Uncheck Always use this Selection; then click OK.

 

Press Continue to get the following screen.

 

7. Click the Browse button to navigate to the PayrollSystem_DB.accdb file in your website folder, then click Open. (NOTE: Be sure you select the PayrollSystem_DB.accdb file in your PayrollSystem website folder, not the one you originally downloaded from Doc Sharing!) Click Test Connection. You should receive a message that the test connection succeeded. Click OK to acknowledge the message, then click OK again to close the Add Connection dialog.

 

8. The PayrollSystemDB.accdb should be added to the Server Explorer. Expand the database, then expand the Tables entry under the database until you see tblUserActivity. Leave the Server Explorer window open for now as you will be returning to it in a moment.

 

9. Create a new dataset by selecting Website-> Add New Item. Under Templates, select the Dataset item. Enter dsUserActivity.xsd for the name. Click Add.

 

10. If the following message appears, select Yes. You want to make this dataset available to your entire website.

 

11. If the TableAdapter Configuration Wizard dialog appears, click Cancel. (We will be configuring a Data Adapter for this dataset later in C# code, so we do not need to run this wizard.)

 

12. Drag-and-drop the tblUserActivity table from the Server Explorer window into the dsUserActivity dataset in the editor window.

 

NOTE: If you see a message that says your connection uses a local data file that is not in the current project, that indicates you did not select the correct PayrollSystem_DB.accdb file when you created your data connection. To fix this problem, click No, then right-click on PayrollSystemDB.accdb in the Server Explorer window and choose Modify Connection. Click the Browse button, navigate to the PayrollSystemDB.accdb file that is in your PayrollSystem website folder, and click Open. Test the connection, then click OK.

 

Click the Save icon on the toolbar to save the dsUserActivity.xsd dataset.

 

(You can now close the Server Explorer window if you wish.)

 

13. Create a new class to contain the C# code that will access this dataset. To do so, click Website, Add New Item. In the Add New Item dialog, select the Class template, and enter clsDataLayer for the name. Make sure the Language is set to Visual C#. Click Add.

 

14. If the following message appears, select Yes. You want to make this class available to everything in your solution.

 

15. Add the following to the top of your class, below any other using statements created for you by Visual Studio.

 

Add to top of class

 

// Add your comments here

 

using System.Data.OleDb;

 

using System.Net;

 

using System.Data;

 

16. Add the following three functions inside the squiggly braces for the public class clsDataLayer class, above the beginning of the public clsDataLayer() constructor and save the class.

 

Class

 

// This function gets the user activity from the tblUserActivity

 

public static dsUserActivity GetUserActivity(string Database)

 

{

 

// Add your comments here

 

dsUserActivity DS;

 

OleDbConnection sqlConn;

 

OleDbDataAdapter sqlDA;

 

// Add your comments here

 

sqlConn = new OleDbConnection(“PROVIDER=Microsoft.ACE.OLEDB.12.0;” + “Data Source=” + Database);

 

// Add your comments here

 

sqlDA = new OleDbDataAdapter(“select * from tblUserActivity”, sqlConn);

 

// Add your comments here

 

DS = new dsUserActivity();

 

// Add your comments here

 

sqlDA.Fill(DS.tblUserActivity);

 

// Add your comments here

 

return DS;

 

}

 

// This function saves the user activity

 

public static void SaveUserActivity(string Database, string FormAccessed)

 

{

 

// Add your comments here

 

OleDbConnection conn = new OleDbConnection(“PROVIDER=Microsoft.ACE.OLEDB.12.0;” +

 

“Data Source=” + Database);

 

conn.Open();

 

OleDbCommand command = conn.CreateCommand();

 

string strSQL;

 

strSQL = “Insert into tblUserActivity (UserIP, FormAccessed) values (‘” +

 

GetIP4Address() + “‘, ‘” + FormAccessed + “‘)”;

 

command.CommandType = CommandType.Text;

 

command.CommandText = strSQL;

 

command.ExecuteNonQuery();

 

conn.Close();

 

}

 

// This function gets the IP Address

 

public static string GetIP4Address()

 

{

 

string IP4Address = string.Empty ;

 

foreach (IPAddress IPA in

 

Dns.GetHostAddresses(HttpContext.Current.Request.UserHostAddress)) {

 

if (IPA.AddressFamily.ToString() == “InterNetwork”) {

 

IP4Address = IPA.ToString();

 

break;

 

}

 

}

 

if (IP4Address != string.Empty) {

 

return IP4Address;

 

}

 

foreach (IPAddress IPA in Dns.GetHostAddresses(Dns.GetHostName())) {

 

if (IPA.AddressFamily.ToString() == “InterNetwork”) {

 

IP4Address = IPA.ToString();

 

break;

 

}

 

}

 

return IP4Address;

 

}

 

STEP 2: frmUserActivity, frmPersonnel, frmMain

 

17. Create a new web form called frmUserActivity. Switch to Design Mode and add the ACIT logo to the page as an ImageButton and link it back to frmMain. Below the image button add a panel. To the panel, add a Label and GridView (found under the Toolbox, Data tab) having the following properties.

 

Property Value

 

Label – Text User Activity

 

GridView – (ID) grdUserActivity

 

18. Go to the Page_Load method by double clicking an empty space on the page and add the following code.

 

Page_Load method for frmUserActivity.aspx

 

if (!Page.IsPostBack) {

 

// Declares the DataSet

 

dsUserActivity myDataSet = new dsUserActivity();

 

// Fill the dataset with what is returned from the function

 

myDataSet = clsDataLayer.GetUserActivity(Server.MapPath(“PayrollSystem_DB.accdb”));

 

// Sets the DataGrid to the DataSource based on the table

 

grdUserActivity.DataSource = myDataSet.Tables[“tblUserActivity”];

 

// Binds the DataGrid

 

grdUserActivity.DataBind();

 

}

 

19. Open the frmMain form, add a new link button and image button to point to the new frmUserActivity. Find an image to use for the image button and add the new option as View User Activity.

 

20. Go to the frmMain Page_Load and add the following code.

 

frmMain.aspx Page_Load code

 

// Add your comments here

 

clsDataLayer.SaveUserActivity(Server.MapPath(“PayrollSystem_DB.accdb”), “frmPersonnel”);

 

21. In the Solution Explorer, right click on the frmMain.aspx form and select Set As Start Page. Run your project. When you open the project, a record should be saved in the tblUserActivity table with the IP address, form name accessed (frmPersonnel), and the date accessed. When you click the View Activity button, you should see at least one record with this information.

 

23. You will now add server side validation code to the frmPersonnel page. Currently, when the Submit button is pressed, the frmPersonnelVerified page is displayed. This is because the frmPersonnelVerified page is set as the Submit button’s PostBackUrl property. Instead of having the page go directly to the frmPersonnelVerified page when the Submit button is pressed, we want to do some server side validation. If any of the validation rules fail, we will redisplay the frmPersonnel page with the fields in question highlighted in yellow with an error message displayed.

 

First, it is important to understand what is currently happening when the submit button is pressed. This is causing a postback of the form to the frmPersonnelVerified form. When this postback happens, all of the data in the fields on the frmPersonnel form are sent to the frmPersonnelVerified form as name value pairs. In the Page_Load code of frmPersonnelVerified these values are picked up from the Request object and displayed. Each name value pair will be in the Request object as the ID of the control containing the value and the value itself. We can pass data between pages by using Session state instead. In order to do validation on the values but still have the values visible on the frmPersonnelVerified page, we will need to change not only the PostBack URL of the frmPersonnel page but also how the frmPersonnelVerified form is getting the data—it will need to get it from Session state rather than from the Request object.

 

In order to do this, we will make the following changes.

 

Clear the Submit button PostBackURL Property on the frmPersonnel form. Remove the value in the PostBackUrl that is highlighted.

 

In the btnSubmit_Click event handler get each value from the data entry fields and set Session state items for each. (instructions below)

 

Change the frmPersonnelVerified code behind to get the values from the Session state items you created in the previous step. (instructions below)

 

When you are done with these steps, you should be able to enter data on the frmPersonnel data entry form and then click the Submit button. The frmPersonnelVerified page should then be displayed with the values that were in the data entry fields on frmPersonnel.

 

23. Add a label to the frmPersonnel form with an ID of lblError. Do not place the label to the right or left of any of the controls on the form. Add it below the controls or above the controls. The text property of this label should be set to an empty string.

 

24. Add code to perform server side validation in response to the submit button being clicked. Here are the business rules we want to enforce (remember this will be server C# code in the frmPersonnel code behind): Fields may not be empty or filled with spaces. If any field is empty, turn that field background color to yellow and add to/create an error message to be shown in the error label. The end date must be greater than the start date. If the end date is less than the start date, turn both date fields yellow and add to/create an error message to be shown in the error label. If all fields validate properly then the session state items should be set properly and the user should see the frmPersonnelVerified form with all the values displayed.

 

frmPersonnel.aspx Lab Hints

 

1. The server side validation should be in the Submit button’s event handler. There is a Trim method on the string object that will automatically remove spaces from the beginning and end of a string. To test if txtFirstName is empty or filled with spaces, use the following code.

 

if (Request[“txtFirstName”].ToString().Trim() == “”)

 

2. To set the background color of the txtFirstName field, use the following code.

 

txtFirstName.BackColor = System.Drawing.Color.Yellow;

 

3. To set a value in session state and redirect the response to the frmPersonnelVerified.aspx do the following. txtFirstName is the key and txtFirstName.Text is the value.

 

Session[“txtFirstName”] = txtFirstName.Text;

 

//Need to set session variables for all text boxes

 

Response.Redirect(“frmPersonnelVerified.aspx”);

 

4. You may want to create variables to work with for validation rather than using the Request item objects directly.

 

To turn a string into a DateTime object you can use the DateTime method Parse. If you had a date value stored in a string called strDate, you could turn it into a DateTime object like this.

 

DateTime myDateTimeObject = DateTime.Parse(strDate);

 

You can compare two DateTime objects by using the DateTime.Compare method. If you had two DateTime objects called dt1 and dt2 you can check to see if dt1 is greater than dt2 by doing this.

 

if (DateTime.Compare(dt1,dt2) > 0)

 

DateTime.Compare will return a 0 if the two dates are equal, a 1 if dt1 is greater than dt2, and a -1 if dt1 is less than dt2.

 

If you put in an invalid date for either of the date fields, you will get an exception/server error when trying to parse the values. We will address this in a later lab—for now make sure you enter valid dates (valid meaning a date in the form of mm/dd/yyyy).

 

5. An example of the code you might want to use to test if the end date is after the start date follows.

 

DateTime startDate = DateTime.Parse(Request[“txtStartDate”]);

 

DateTime endDate = DateTime.Parse(Request[“txtEndDate”]);

 

if (DateTime.Compare(startDate, endDate) > 0)

 

{

 

txtStartDate.BackColor = System.Drawing.Color.Yellow;

 

txtEndDate.BackColor = System.Drawing.Color.Yellow;

 

Msg = Msg + “The end date must be a later date than the start date.”;

 

//The Msg text will be displayed in lblError.Text after all the error messages are concatenated

 

validatedState= false;

 

//Boolean value – test each textbox to see if the data entered is valid, if not set validState=false.

 

//If after testing each validation rule, the validatedState value is true, then submit to frmPersonnelVerified.aspx, if not, then display error message

 

}

 

else

 

{

 

txtStartDate.BackColor = System.Drawing.Color.White;

 

txtEndDate.BackColor = System.Drawing.Color.White;

 

}

 

Remember to clear the PostBackURL property of the Submit button!

 

frmPersonnelVerified.aspx Lab Hints

 

When using the Session state in frmPersonnel.aspx for txtFirstName, you used the following code: Session[“txtFirstName”] = txtFirstName.Text;

 

To get this same value back from the session we use the key and the Session object in the Page_Load of frmPersonnellVerified.aspx (instead of using Request, use Session) as follows.

 

Session[“txtLastName”].ToString()

 

STEP 3: Verify and Submit

 

23. View the video above on what functions your lab should have so far.

 

24. Run your project. When you open the project and go to the main menu form a record should be saved in the tblUserActivity table with the IP address, form name accessed (frmPersonnel), and the date accessed. When you click the View Activity button you should see at least one record with this information. The validation and error display should work for entering data. All navigation and hyperlinks should work.

 

Once you have verified that it works, save your project, zip up all files, and submit in the Dropbox.

 

image showing computer screen: Please enter first name

 

image showing computer screen: Please enter last name

 

image showing computer screen: end date must be greater than start date

 

NOTE: Make sure you include comments in the code provided where specified (where the ” //Your comments here” is mentioned) and for any code you write, or else a five-point deduction per item (form, class, function) will be made. You basically put two forward slashes, which start the comment; anything after the // on that line is disregarded by the compiler. Then type a brief statement describing what is happening in the following code. Comments show professionalism and are a must in systems. As a professional developer, comments will set you apart from others and make your life much easier if maintenance and debugging are needed.

 

ilab 4

 

iLab 4 of 7: Web Forms with Database Interaction (30 points)

iLab Overview

 

In this lab, we will start with the form that we created in Week 2 (frmPersonnel) and add functionality to INSERT records into a database table and SELECT records for display to the user. We will create a typed dataset, a Data Layer class, several functions to access the data, and a connection to a database. We also will add a search form to allow the user to search records in the database and display the results of that search.

 

Please watch the tutorial before beginning the iLab.

 

Lab Tutorial Video

 

Play00:00MuteFullscreen

 

Transcript

 

Deliverables

 

NOTE

 

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step instructions.

 

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

 

All files are located in the subdirectory of the project. The project should function as specified: When you press the Submit button in frmPersonnel, a record should be saved in the tblPersonnel table having the FirstName, LastName, PayRate, StartDate, and EndDate that you entered on the form. Add a search feature to the project. Update your main navigation page with the new options. Once you have verified that it works, save your website, zip up all files, and submit it in the Dropbox.

 

Required Software

 

Microsoft Visual Studio.Net

 

Access the software at https://lab.devry.edu.

 

Steps: 1, 2, and 3

 

Lab Steps

 

STEP 1: Data Layer

 

Open Microsoft Visual Studio.NET.

 

Click the ASP.NET project called PayrollSystem to open it.

 

Open the clsDataLayer class and add the following function:

 

// This function saves the personnel data

 

public static bool SavePersonnel(string Database, string FirstName, string LastName,

 

string PayRate, string StartDate, string EndDate)

 

{

 

bool recordSaved;

 

try {

 

// Add your comments here

 

OleDbConnection conn = new OleDbConnection(“PROVIDER=Microsoft.ACE.OLEDB.12.0;” +

 

“Data Source=” + Database);

 

conn.Open();

 

OleDbCommand command = conn.CreateCommand();

 

string strSQL;

 

// Add your comments here

 

strSQL = “Insert into tblPersonnel ” +

 

“(FirstName, LastName, PayRate, StartDate, EndDate) values (‘” +

 

FirstName + “‘, ‘” + LastName + “‘, ” + PayRate + “, ‘” + StartDate +

 

“‘, ‘” + EndDate + “‘)”;

 

// Add your comments here

 

command.CommandType = CommandType.Text;

 

command.CommandText = strSQL;

 

// Add your comments here

 

command.ExecuteNonQuery();

 

// Add your comments here

 

conn.Close();

 

recordSaved = true;

 

} catch (Exception ex) {

 

recordSaved = false;

 

}

 

return recordSaved;

 

}

 

4. In the frmPersonnelVerified form, go to the Page_Load() event and add the following code after the existing code (but still in the Page_Load event handler):

 

// Add your comments here

 

if (clsDataLayer.SavePersonnel(Server.MapPath(“PayrollSystem_DB.accdb”),

 

Session[“txtFirstName”].ToString(),

 

Session [“txtLastName”].ToString(),

 

Session [“txtPayRate”].ToString(),

 

Session [“txtStartDate”].ToString(),

 

Session [“txtEndDate”].ToString()))

 

{

 

txtVerifiedInfo.Text = txtVerifiedInfo.Text +

 

” The information was successfully saved!”;

 

}

 

else

 

{

 

txtVerifiedInfo.Text = txtVerifiedInfo.Text +

 

” The information was NOT saved.”;

 

}

 

5. Add comments for all code containing // Add your comments here.

 

6. Test your work to make sure that no errors occur! (Make sure to put in valid date values for the date data entry fields).

 

STEP 2: Data Display and Search

 

7. Using the skills that you learned in Week 3, create a new DataSet for the tblPersonnel table (call the DataSet dsPersonnel).

 

8. Using the skills that you learned in Week 3, create a new function called GetPersonnel in the clsDataLayer class. This function should retrieve all data from the tblPersonnel table and return it in the form of a dsPersonnel DataSet. Use the GetUserActivity function as an example.

 

9. Create a new Web form called frmViewPersonnel.

 

10. Using the skills that you learned in Week 3, add a GridView control (called grdViewPersonnel) to the form. This GridView control will be used to display data from the tblPersonnel table. Add the ACIT logo at the top of the page and make sure it links back to frmMain.

 

11. Add the following code to the Page_Load() function in frmViewPersonnel.

 

if (!Page.IsPostBack)

 

{

 

//Declare the Dataset

 

dsPersonnel myDataSet = new dsPersonnel();

 

//Fill the dataset with shat is returned from the method.

 

myDataSet = clsDataLayer.GetPersonnel(Server.MapPath(“PayrollSystem_DB.accdb”));

 

//Set the DataGrid to the DataSource based on the table

 

grdViewPersonnel.DataSource = myDataSet.Tables[“tblPersonnel”];

 

//Bind the DataGrid

 

grdViewPersonnel.DataBind();

 

}

 

12. Return to the frmPersonnel Web form and add a button ((ID) = btnViewPersonnel, Text = View Personnel) which, when clicked, will display form frmViewPersonnel.

 

13. Open the frmPersonnelVerified form and add a button ((ID) = btnViewPersonnel, Text = View Personnel) which, when clicked, will display form frmViewPersonnel. NOTE: This is the same button with the same functionality that you added to form frmPersonnel in the previous step. Also, add a new link and linked image to frmMain called View Personnel that will go to the new frmViewPersonnel page you created.

 

Let’s test the View Personnel page. Start your program in Internet Explorer. Click on Add New Employee and add yourself to the database and press Submit. Once you are on the personnel verified form, click the View Personnel button. You should see the data that you just entered.

 

14. You will now add a Search feature to allow the user to find and display data. The user will enter a last name and the Web application will display the grid of employees with all employees that match that last name.

 

15. Create a new Web form called frmSearchPersonnel. Add the hyperlinked ACIT logo to this page. Also, add a new item on frmMain (with a Link button and Image button) called Search Personnel.

 

16. On the frmSearchPersonnel form, add a label that displays “Search for employee by last name:”. Next to the label, add a text box with an ID of txtSearch. Add a button with an ID of btnSearch and set the text of the button to “Search”.

 

17. When the frmSearchPersonnel Search button is pressed, the frmViewPersonnel is displayed. At this point, no searching is actually happening, but you have the forms that you need and the navigation is working. Now you can focus on the coding that you will need to do to have the grid only display matching employees.

 

18. Before calling the GetPersonnel method that you added previously in the lab, you will need to get the value that is in the Request[“txtSearch”] item. When the form posts the search page results to the frmViewPersonnel, the name value pair for the search value is passed as part of the Request object. This value will need to be assigned to a string variable. To do this task, add the following line of code in the code block below to the Page_Load function in frmViewPersonnel after the line: dsPersonnel myDataSet = new dsPersonnel();

 

string strSearch = Request[“txtSearch”];

 

Then, modify the call of the GetPersonnel function one line below to add the strSearch as one of the arguments:

 

myDataSet = clsDataLayer.GetPersonnel(Server.MapPath(“PayrollSystem_DB.accdb”), strSearch);

 

19. Modify the GetPersonnel method that you added in the clsDataLayer.cs class to include a new parameter called strSearch of type string. Add string strSearch as an argument to the function as below:

 

public static dsPersonnel GetPersonnel(string Database, string strSearch)

 

Then modify the sqlDA select statement within the GetPersonnel function to test if a value is entered for a search parameter.

 

if (strSearch == null || strSearch.Trim()==””)

 

{

 

sqlDA = new OleDbDataAdapter(“select * from tblPersonnel”, sqlConn);

 

}

 

else

 

{

 

sqlDA = new OleDbDataAdapter(“select * from tblPersonnel where LastName = ‘” + strSearch + “‘”, sqlConn);

 

}

 

20. Test the search so that when you enter a last name, employees with that last name are returned. Make sure that when you access frmViewPersonnel and you are not searching, all employees are returned.

 

STEP 3: Test and Submit

 

Run your project and test it as follows:

 

The frmMain form should be displayed first.

 

Click on the Add New Employee hyperlink to go to the frmPersonnel data entry form. Click the View Personnel button on this form. The frmViewPersonnel form should be displayed in the browser, but at this point, there should not be very many personnel listed.

 

Use the Back button in your Web browser to return to the frmPersonnel form and enter some personnel data for a few employees, similar to the following:

 

Now, click the Submit button. The frmPersonnelVerified form should be displayed, showing the data you entered, and you should get a message saying that the data were successfully saved, like this example.

 

You should be able to view the employee records by clicking the View Personnel link on the home page.

 

Test the Search feature and make sure that entering no search string returns all of the data and that typing in a last name will return all employees with the same last name.

 

NOTE: Make sure that you include comments in the code provided where specified (where the ” // Your comments here” line appears) and for any code that you write, or else a 5-point deduction per item (form, class, function) will be made.

 

ilab 5

 

iLab 5 of 7: Transaction Processing (30 points)

This week, we will use the .NET OleDbTransaction functions to either commit a set of changes to the database, if all of them were done correctly, or to roll back all of the changes if there was an error in any one of them. We will first modify the code that we created last week so that it will save personnel data in the database in two steps; first by inserting a personnel record for a new employee, and then by updating that record to fill in the start and end dates. This two-step approach is not really needed in this simple case, but we will use it to simulate a more complex database transaction that would have to be done in multiple steps, such as one involving more than one table or even more than one database. We will then see what happens when there is an error in the second operation (the update), allowing a record to be created containing incomplete information: not a good result! We will fix the problem by wrapping both operations (the insert and the update) into a single transaction that will be committed (made permanent) only if both operations succeed

 

 

 

 

iLab 6 of 7: Login and Security Levels (30 points)

 

Scenario/Summary

In this week’s lab, we will create a login form, validate a user based on their login name and password, and allow them to access the system or not. We will assign a session variable to determine the user’s level of security and allow certain functions to be displayed or not displayed in the existing frmPersonnel form depending on the assigned security level. (NOTE: In some cases, the instructions for this lab will be less specific than in earlier labs, because you are expected to apply what you have learned in earlier weeks. Refer to the detailed instructions in previous weeks’ labs if you need to do so.)

 

Please watch the tutorial before beginning the iLab.

 

 

 

Table of Contents

Tutorial: Part 1

Tutorial: Part 2

Play00:00MuteFullscreen

 

Transcript

Deliverables

NOTE

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step instructions.

 

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

 

When you try to log in, if you use User Name = Mickey and Password = Mouse, the frmMain form should open with all links visible. If you use User Name = Minnie and Password = Mouse, the frmMain form should open with only the Salary Calculator, View Personnel, and Search options available. You will have a new option called Manage Users that will allow you to add new users and remove or update existing users. Once you have verified that it works, save your website, zip up all files, and submit in the Dropbox.

 

 

Note on database connections: We are using a SQLDataSource control for the Edit employees feature that we added. You should be using the connection string stored in the web.config file for your database connection for this control. Rather than creating a new connection each time, just use this connection. If you change the folder where your website is stored (e.g., you copy each week’s work to a new location), you will need to update the web.config. The advantage of using the database connection in the web.config is that you only have to set the configuration in one location.

 

Before starting this week’s lab, make sure that everything is working and that all database connections are properly configured.

 

Required Software

Microsoft Visual Studio.NET

 

Access the software at https://lab.devry.edu.

Steps: 1, 2, and 3

Lab Steps

STEP 1: Login Form

1. In order to do this lab, we need to assign a primary key to the tblUserLogin table. This will allow us to modify the user login table from our Manage Users form that we will create later. Go to Windows Explorer and open the PayrollSystem_DB.accdb. Set the UserID as the Primary key and save the table. Close the database.

 

2. Open Microsoft Visual Studio.NET.

 

3. Click the ASP.NET website named PayrollSystem to open it.

 

4. Create a new Web form named frmLogin.

 

5. Add the ACIT logo to the top of the frmLogin page. Do not hyperlink the logo.

 

6. Under the login controls, you will see Login. Drop the Login control onto the form. Set the properties of the login control as follows:

 

Property Value

DestinationPageUrl frmMain.aspx

TitleText Please enter your UserName and Password in order to log in to the system.

 

 

7. Highlight everything in the form, then click Format, Justify, Center. Save your work.

 

8. Go to the Solution Explorer, right-click on frmLogin, and left-click on Set As Start Page.

 

Then run the website to check if the Web form appears correctly.

 

 

 

If you receive an error, add the following code to the web.config file right above the </configuration> line:

 

<appSettings>

<add key=”ValidationSettings:UnobtrusiveValidationMode” value=”None” />

</appSettings>

STEP 2: Login Check

9. Create a new DataSet called dsUser. Use the table tblUserLogin as the database table for this dataset. Do this in the same way that you added datasets in the previous labs.

 

10. Open the clsDataLayer and add the following function:

 

// This function verifies a user in the tblUser table

public static dsUser VerifyUser(string Database, string UserName, string UserPassword)

{

// Add your comments here

dsUser DS;

OleDbConnection sqlConn;

OleDbDataAdapter sqlDA;

// Add your comments here

sqlConn = new OleDbConnection(“PROVIDER=Microsoft.ACE.OLEDB.12.0;” +

“Data Source=” + Database);

// Add your comments here

sqlDA = new OleDbDataAdapter(“Select SecurityLevel from tblUserLogin ” +

“where UserName like ‘” + UserName + “‘ ” +

“and UserPassword like ‘” + UserPassword + “‘”, sqlConn);

// Add your comments here

DS = new dsUser();

// Add your comments here

sqlDA.Fill(DS.tblUserLogin);

// Add your comments here

return DS;

}

11. Double-click on the login control that you added. Add the following code to the login control Authenticate event handler:

 

// Add your comments here

dsUser dsUserLogin;

// Add your comments here

string SecurityLevel;

// Add your comments here

dsUserLogin = clsDataLayer.VerifyUser(Server.MapPath(“PayrollSystem_DB.accdb”),

Login1.UserName, Login1.Password);

// Add your comments here

if (dsUserLogin.tblUserLogin.Count < 1)

{

e.Authenticated = false;

return;

}

// Add your comments here

SecurityLevel = dsUserLogin.tblUserLogin[0].SecurityLevel.ToString();

// Add your comments here

switch (SecurityLevel)

{

case “A”:

// Add your comments here

e.Authenticated = true;

Session[“SecurityLevel”] = “A”;

break;

case “U”:

// Add your comments here

e.Authenticated = true;

Session[“SecurityLevel”] = “U”;

break;

default:

e.Authenticated = false;

break;

}

STEP 3: User Authentication, Test and Submit

12. Open the frmPersonnel form and add the following code to its Page_Load() function:

 

// Add your comments here

if (Session[“SecurityLevel”] == “A”) {

btnSubmit.Visible = true;

//Add your comments here

} else {

btnSubmit.Visible = false;

}

13. Set the start page as frmLogin.aspx. Run the website. Try to log in with both User Name = Mickey and Password = Mouse and User Name = Minnie and Password = Mouse. Any other user ID and password should not allow you to log in.

 

14. When the user logs in, we want to restrict what they can see and do based on their user role. The role is stored in the database table tblUserLogin. Mickey Mouse has all privileges, whereas Minnie Mouse has read only privileges. We want to control the visibility of the links on the frmMain page.

 

15. Initially, we did not set the ID of any of the Link Button or Image Button controls that we used on frmMain. In order to make our code more maintainable, we will change the IDs as follows:

 

Option Link Button ID Image Button ID

Annual Salary Calculator linkbtnCalculator imgbtnCalculator

Add New Employee linkbtnNewEmployee imgbtnNewEmployee

View User Activity linkbtnViewUserActivity imgbtnViewUserActivity

View Personnel linkbtnViewPersonnel imgbtnViewPersonnel

Search Personnel linkbtnSearch imgbtnSearch

Edit Employees linkbtnEditEmployees imgbtnEditEmployees

16. Modify the main form so that the following options are turned off for nonadmin users:

 

Add New Employee

View User Activity

Edit Employees

17. You now have a Web application that honors the role of the logged-in user. We don’t have a way of managing the user roles and users in the system.

 

18. Add a new form called frmManageUsers that will allow the user to add new users. The user will also need to be able to view all users and modify or delete any of the users in the database. Add a main form option called Manage Users that is only accessible to admin users. Add the link and image buttons as we have done in the past. Add the ACIT logo that is hyperlinked as you did in previous assignments.

 

For the security level of the user, use a dropdown list control to allow the user to select from A or U.

Name the controls with names that make sense.

Add code as appropriate to the code behind and clsDataLayer. Note: You will need to create a SaveUser function that is very similar to the SavePersonnel function. Use the following as a guide:

public static bool SaveUser(string Database, string UserName, string Password,

string SecurityLevel)

When creating the SaveUser function, be sure to insert the data into the tblUserLogin table with columns: userName, UserPassword, and SecurityLevel.

 

19. Hints:

 

Make sure you reestablish your database connection if you copied the files from a previous lab.

Update any DataSource controls that you added with the new Payroll database location.

You can turn a control on or off by setting its Visible property.

You can add a data entry form for new users and a grid displaying all users all on the same form.

To force a gridView to refresh, call its DataBind method in the btnAddUser_click event handler. For example, use the following code in the btnAddUser_click (be sure to include an Else condition as well if the user was not added successfully):

if (clsDataLayer.SaveUser(Server.MapPath(“PayrollSystem_DB.accdb”),

txtUserName.Text, txtPassword.Text,ddlSecurityLevel.SelectedValue))

{

lblError.Text = “The user was successfully added!”;

grdUsers.DataBind();

}

20. Test your application to make sure that you are logging in with a valid user ID. Try to log in with both Minnie and Mickey and make sure that the UI adjusts by the role properly. Make sure that you can utilize the Manage Users functionality to Add/Modify/Delete and view user information. Once you have verified that everything works, save your project, zip up all files, and submit in the Dropbox.

 

NOTE: Make sure you include comments in the code provided where specified (where the ” // Your comments here” is mentioned); also, any code you write needs to be properly commented, or else a 5-point deduction per item (form, class, function) will be made.

 

frmManageUsers

 

 

 

Mickey Mouse (Admin) Login:

 

 

 

Minnie Mouse (User) Login:

 

iLab 7 of 7: Error Notification Via E-Mail (30 points)

 

Scenario/Summary

 

In this lab, we will incorporate error handling into the login process so that a notice of each invalid login is automatically e-mailed to the technical support staff.

 

Please watch the tutorial before beginning the iLab.

 

Lab Tutorial Video

Play00:00MuteFullscreen

Transcript

Software Citation Requirements

This course uses open-source software, which must be cited when used for any student work. Citation requirements are on the Open Source Applications page.

Please review the installation instruction files to complete your assignment.

 

Deliverables

 

NOTE

Submit your assignment to the Dropbox, located at the top of this page. For instructions on how to use the Dropbox, read these step-by-step instructions.

 

(See the Syllabus section “Due Dates for Assignments & Exams” for due dates.)

 

When you try to log in, if your user name is not Mickey, Minnie, or another user that you added (that is, if the user name is not found in tblUserLogin), then an e-mail should be sent to the address recipient@recipientdomain.com. If the user attempts to bypass the login page by typing a page name in the URL, your Web application should redirect the user back to the login page. Once you have verified that it works, save your project, zip up all files, and submit in the Dropbox.

 

NOTE: E-mails may be blocked due to firewalls, antivirus software, or even Internet service providers that turned off SMTP because of some known security issues. If the code works (does not produce an error when submitting), you will get full credit for this project even if no e-mail message is actually transmitted. Consult with your instructor before submitting if an error occurs or if no e-mail is generated, to be sure.

 

Required Software

 

Microsoft Visual Studio.NET

 

Access the software at https://lab.devry.edu.

Steps: 1, 2, and 3

 

Lab Steps

 

STEP 1: Business Layer Functionality

 

1. Open Microsoft Visual Studio.NET.

 

2. Click the ASP.NET website named PayrollSystem to open it.

 

3. Create a new class called clsBusinessLayer.

 

4. Add the following code in the clsBusinessLayer class:

 

// **** Add the following at the top of the class file,

// Add your comments here

using System.Net.Mail;

//**** Add the following code inside the body of public class clsBusinessLayer ****

public static bool SendEmail(string Sender, string Recipient, string bcc, string cc,

string Subject, string Body)

{

try {

// Add your comments here

MailMessage MyMailMessage = new MailMessage();

// Add your comments here

MyMailMessage.From = new MailAddress(Sender);

// Add your comments here

MyMailMessage.To.Add(new MailAddress(Recipient));

// Add your comments here

if (bcc != null && bcc != string.Empty) {

// Add your comments here

MyMailMessage.Bcc.Add(new MailAddress(bcc));

}

// Add your comments here

if (cc != null && cc != string.Empty) {

// Add your comments here

MyMailMessage.CC.Add(new MailAddress(cc));

}

// Add your comments here

MyMailMessage.Subject = Subject;

// Add your comments here

MyMailMessage.Body = Body;

// Add your comments here

MyMailMessage.IsBodyHtml = true;

// Add your comments here

MyMailMessage.Priority = MailPriority.Normal;

// Add your comments here

SmtpClient MySmtpClient = new SmtpClient(“localhost”);

//SMTP Port = 25;

//Generic IP host = “127.0.0.1”;

// Add your comments here

MySmtpClient.Send(MyMailMessage);

// Add your comments here

return true;

} catch (Exception ex) {

// Add your comments here

return false;

}

}

STEP 2: Integration

 

5. Open the frmLogin Web form code behind the file and add the following code to the body of the if (dsUserLogin.tblUserLogin.Count < 1) statement, just above the return statement:

 

// Add your comments here

// Add your comments here

if (clsBusinessLayer.SendEmail(“youremail@yourdomain.com”,

“receiver@receiverdomain.com”, “”, “”, “Login Incorrect”,

“The login failed for UserName: ” + Login1.UserName +

” Password: ” + Login1.Password))

{

Login1.FailureText = Login1.FailureText +

” Your incorrect login information was sent to receiver@receiverdomain.com”;

}

NOTE: Change the youremail@yourdomain.com and receiver@receiverdomain.com to your e-mail and someone else’s e-mail for testing.

 

6. Optional: Perform this step only if you are doing this lab using Visual Studio installed on your own computer and you have administrative rights on your computer. If you are doing this lab using the iLab (Citrix) server, or if you do not have access to IIS, skip to Step 8.

 

7. In previous versions of Windows, the SMTP server was built into IIS. Now we will need to get a separate one. On the Microsoft Codeplex site is an SMTP server called smtp4dev, specifically designed for development environments. Pages 652–653 in the text discuss how to download and use smtp4dev. The site is http://smtp4dev.codeplex.com. Click on Downloads. Another example is Papercut, downloadable at: http://papercut.codeplex.com/ You can use either smtp server.

 

 

 

Test the e-mail by logging in as someone other than Mickey or Minnie. You should receive an email to the SMTP client.

 

 

 

8. We have a security hole in our Web application. If you start the Web application by going to the login page, you can bypass the login page by simply typing the name of a form in the URL (try it). There is some limited protection because of the check that we are doing for the user role, but it still allows a user to get to pages that we don’t want them to get to unless the role is set properly. Add a security check in the Page_Load of each sensitive page (Manage Users, Add New Employee, View User Activity, Edit Employees), check for the Session role item with a value of A, and, if the user is accessing these pages without the proper permissions, redirect back to the frmLogin.aspx page. For example:

 

if (Session[“SecurityLevel”] != “A”)

{

Response.Redirect(“frmLogin.aspx”);

}

9. This still leaves the possibility of a person bypassing the login page. We will fix that by using forms authentication. Add the following to the web.config file before the </system.web> tag.

 

<authentication mode=”Forms”>

<forms loginUrl=”frmLogin.aspx” />

</authentication>

<authorization >

<deny users=”?” />

</authorization>

10. This will redirect users to the login page if they have not yet gone through it for login. This process will use a cookie – when the user successfully logs in, a cookie is set that allows the user to go to other pages. If that cookie is not set, then the user is redirected to the login page if they try to go to any other page. Add the cookie code by adding this code in the frmLogin.aspx C# code after each place that you have e.Authenticated = true:

 

FormsAuthentication.RedirectFromLoginPage(Login1.UserName, false);

If you receive an error when you enter this in the code, right click on the line and choose Resolve->Using System.Web.Security

 

11. Hints:

 

Make sure you reestablish your database connection if you copied the files from a previous lab. Also, make sure to update the web.config file with the database connection string.

Update any DataSource controls that you added with the new payroll database location.

When you manually try to go to a second page by skipping the login page, a cookie is set specifying the name of the page you were attempting to visit. Once you log in successfully, ASP.Net will automatically attempt to navigate back to that page. You can reset the cookie so that the next page is frmMain, as expected, by typing that page in the URL for the browser before logging in.

Submit Final Lab (includes all previous lab assignments).

 

STEP 3: Test And Submit

 

12. Run your project. When you try to log in, enter a username that is not Mickey or Minnie (i.e., a username that is not found in tblUserLogin). An e-mail should be sent to the recipient@recipientdomain.com e-mail address.

 

13. Test that frmMain reconfigures properly based on user role. Make sure that the user cannot bypass the login page.

 

Once you have verified that everything works, save your website, zip up all files, and submit them in the Dropbox.

 

NOTE: E-mails may be blocked due to firewalls, antivirus software, or even Internet service providers that turned SMTP off because of some known security issues. If the code works (does not produce an error when submitting), you will get full credit for this project even if no e-mail message is actually transmitted. Consult with your instructor before submitting if an error occurs or if no e-mail is generated. It is expected that no e-mail will be sent if you are using the iLab (Citrix) server for this lab or if you were not able to download and install smtp4dev.

 

NOTE: Make sure that you include comments in the code provided where specified (where the ” // Add your comments here” is mentioned), including code you wrote, or else a 5-point deduction per item (form, class, function) will be made.