WEB460 Lab 3 of 7: Adding Data Layer Functionality

Availability: In stock

Regular Price: $12.00

Special Price: $10.00

OR

WEB460 Lab 3 of 7 Adding Data Layer Functionality Find Last Name

Double click on above image to view full picture

Zoom Out
Zoom In

More Views

Quick Overview

WEB460 Lab 3 of 7: Adding Data Layer Functionality


Scenario/Summary
For our Lab this week, you will connect to a Microsoft Access database to store, update, and retrieve customer information. Here is an overview of the lab:
Step A: Create a New Web Site Project
Copy files from last week's Lab.
Step B: Add the ClearForm Functionality to pgCheckOut
Add a button and code to clear form fields.
Step C: Create a DataSet and Link It to an Access Database
This step also creates a TableAdapter that can be used in our code.
Step D: Create the clsDataLayer Class to Represent Our Application's Data Layer
Add a data field for the connection and modify the constructor.
Step E: Implement the FindCustomer Functionailty
This and the following two steps require editing three different files and then testing your changes.
Be careful to add the lab code to the correct file each time.
Generally, the parts of these steps are as follows:
– Add a method to the clsDataLayer class.
– Add a click method to pgCheckOut.aspx.cs that calls the method in clsDataLayer.
– Add a button to pgCheckOut.aspx that calls the click method.
Step F: Implement the UpdateCustomer Functionailty
Step G: Implement the InsertCustomer Functionality
Step H: Test and Finalize the Lab
When you have completed and tested the lab, the web form pgCheckOut should look similar to this image:


Deliverables
A zip archive of the ASP.NET Web Application directory. It should contain the following files in
addition to your database and dataset files:
– pgCheckOut.aspx **
– pgCheckOut.aspx.cs **
– pgConfirm.aspx
– pgConfirm.aspx.cs
– Web460Store.master
– Web460Store.master.cs
– clsDataLayer.cs **
The files with ** are the ones that should have been modified for this lab.


Lab Steps
STEP A: Create a New Web Site Project
1. Create a new Empty Web Site project.
2. Copy the six files from last week's Lab into the folder for this new project. Be careful not to move the files. We want to work on a copy of last week's lab and leave the original untouched. The website folder should have the following files:
pgCheckOut.aspx
pgCheckOut.aspx.cs
pgConfirm.aspx
pgConfirm.aspx.cs
Web460Store.master
Web460Store.master.cs
web.config
web.Debug.config ( optional: depends on the version of Visual Studio you are using)
3. Set pgCheckOut.aspx as the start page and test your application. It should perform just as it did last week.


STEP B: Add the ClearForm Functionality to pgCheckOut
Because we will be adding, retrieving, and updating customer information, we should give the user the ability to easily clear the form fields so that information from one customer is not mixed with that of another.
1. Add a private method to pgCheckOut.aspx.cs that examines each control on the page. If the control is a Textbox, DropDown list, or RadioButton, the control is cleared. If it is a panel or other container, the method calls itself recursively, passing the controls on that container so that they may be cleared.
ClearInputs Method in pgCheckOut.aspx.cs
private void ClearInputs(ControlCollection ctrls)
{
foreach (Control ctrl in ctrls)
{
if (ctrl is TextBox)
((TextBox)ctrl).Text = string.Empty;
else if (ctrl is DropDownList)
((DropDownList)ctrl).ClearSelection();
else if (ctrl is RadioButton)
((RadioButton)ctrl).Checked = false;
else if (ctrl is RadioButtonList)
((RadioButtonList)ctrl).ClearSelection();
else
ClearInputs(ctrl.Controls);
}
}
2. Next, we add a click method that will be called by a form button. The form button cannot call the recursive method directly because click methods require a different set of parameters. Because of this, we need the private helper method ClearInputs.
btnClearForm_Click in pgCheckOut.aspx.cs
public void btnClearForm_Click(object sender, EventArgs e)
{
ClearInputs(Page.Controls);
}
3. Add a button to pgCheckOut.aspx that calls the Clear Form click handler above. The button should have the name and ID btnClearForm and the text "Clear Form". You can use the image in the Lab Summary section above for guidance on placing the button.
4. Test your work. You should be able to enter data in the form fields on the left and when the Clear Form button is clicked, all form fields should be cleared.


STEP C: Create a DataSet and Link it to Microsoft Access Database
These steps create and add a DataSet called dsAccounts to your project. Carefully read through these instructions as you step through the creation of the DataSet. If you cannot connect to the database when these steps are complete, delete the DataSet and recreate it from scratch to make sure all parts are initialized properly.
1. Download and copy the Microsoft Access database, Acounts.mdb, found in the Files section of the Course Menu to your website folder. Note that you can have the database at any location on your computer, but your code should be adjusted accordingly. It is best to have it in the top level of your website or in the App_Data folder. In Steps E, F, and G below, you will need to adjust your code to accurately reflect the path to where your database is stored.
If you are using the DeVry FTP site websol, you must place the database into the FPDB folder in your account top-level directory. It is recommended to use your local computer instead of the websol FTP server.
2. From the Solution Explorer pane, right click on the App_Code folder and select the Add New Item menu option. In the Add New Item dialog box, select DataSet and type the dsAccounts as the name of the DataSet. When prompted, allow Visual Studio to store the DataSet in the App_Code folder.
3. After creating dsAccounts, double-click TableAdapter in the Dataset Toolbox to configure the connection to the database using the TableAdapter Configuration wizard. The Dataset Toolbox panel is displayed by selecting dsAccounts.xsd in the Solution Explorer panel and then clicking the Toolbox link in the main window. You may also select Toolbox from the View menu.
4. On the first window, click the New Connection button.
5. In the Choose Data Source window, select the file Microsoft Access Database and click Continue.
6. In the Add Connection window, click the Browse button and select the Microsoft Access database that you downloaded from the Files section of the Course Menu named Accounts.mdb.
7. Click Test Connection to ensure that Visual Studio can access and connect to the database.
8. Click OK, and then click Next on the TableAdapter Wizard. If you expand the connection string + symbol, you can view the connection string used to access the database. This should closely match what we will use in our application.
9. Click Next again to save the connection string to a file.
10. On the Wizard's Choose a Command Type screen, select SQL Statements and then click Next.
11. We now enter the default SQL query for this DataSet connection. In the textbox on this screen, enter the following SQL SELECT statement whose result will be used to populate the DataSet:
SELECT * FROM tblCustomers
12. Click Finish to exit the TableAdapter wizard.


STEP D: Create the clsDataLayer Class to Represent Our Application's Data Layer
1. Right-click on the project name in the Solution Explorer pane and select Add. From the submenu, select New Item. From the Add Dialog Box, add a Class called clsDataLayer.
The code file (clsDataLayer.cs) will automatically be placed in the App_Code folder in your website directory.
2. Add a data field to our class that represents the database connection and adjust the constructor to initialize it.
clsDataLayer Data Field and Constructor
OleDbConnection dbConnection;
Be sure to place this code in the correct location in the class file. Verify that your code does not have any syntax errors before continuing.
public clsDataLayer(string Path)
{
dbConnection = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path);
}


STEP E: Implement the FindCustomer Functionality Editing Multiple Files
This step, Step F, and Step G require you to add code to three different files. Carefully follow the directions to ensure that you add code to the correct files. Test your code after each step before moving to the next. You may want to make a copy of the following files before starting this step so that if you make a significant error, you can return to this point:
clsDataLayer.cs
pgCheckOut.aspx.cs
pgCheckOut.aspx
1. In the clsDataLayer class, create a method, called FindCustomer, that accepts LastName as a parameter. This method finds the all occurrences of customers with LastName in the database and places the results in the DataSet object mystoreDataSet, which is an instance of our dsAccounts DataSet.
You can add the following code to the class clsDataLayer. Be sure to replace the lines in the code below that read "Add your comments here with your own comments
explaining what the code does.
FindCustomer Method in clsDataLayer.cs
// Add your comments here
public dsAccounts FindCustomer(string LastName)
{
//Add your comments here
string sqlStmt = "select * from tblCustomers where LastName like '" + LastName + "'";
OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter(sqlStmt, dbConnection);
//Add your comments here
dsAccounts myStoreDataSet = new dsAccounts();
sqlDataAdapter.Fill(myStoreDataSet.tblCustomers);
// Add your comments here
return myStoreDataSet;
}
When adding this database code to clsDataLayer, do not forget to add the two Using directives in C# that must be placed at the start of your code that ensure that the compiler has the definition of the Data and OleDataAdapter objects necessary for the data layer. (Your challenge is to research and discover what they are.)
2. In pgCheckOut.aspx.cs, create the click method that calls the Data Layer method FindCustomer. This click method is invoked when the user clicks the Find Last Name button we add next. The following method calls FindCustomer and then fills the form fields with results from the Data Set. Be sure that the tempPath variable accurately reflects the path to where you have saved the Access database Accounts.mdb.
btnFindLastName_Click Method in pgCheckOut.aspx.cs
// Add your comments here
protected void btnFindLastName_Click(object sender, EventArgs e)
{
// Add your comments here
dsAccounts dsFindLastName;
// Add your comments here
// Depending on where you placed your Access database,
// one of the following lines may work better:
// tempPath = Server.MapPath("Accounts.mdb")
// tempPath = Server.MapPath("~/FPDB/Accounts.mdb")
string tempPath = Server.MapPath("~/App_Data/Accounts.mdb");
clsDataLayer dataLayerObj = new clsDataLayer(tempPath);
try
{
// Add your comments here
dsFindLastName = dataLayerObj.FindCustomer(txtLastName.Text);
// Add your comments here
if (dsFindLastName.tblCustomers.Rows.Count > 0)
{
// Add your comments here
txtFirstName.Text = dsFindLastName.tblCustomers[0].FirstName;
txtLastName.Text = dsFindLastName.tblCustomers[0].LastName;
txtStreet.Text = dsFindLastName.tblCustomers[0].Street;
txtCity.Text = dsFindLastName.tblCustomers[0].City;
txtState.Text = dsFindLastName.tblCustomers[0].State;
txtPhone.Text = dsFindLastName.tblCustomers[0].PhoneNumber;
customerID.Text = dsFindLastName.tblCustomers[0].CustomerID.ToString();
Master.UserFeedBack.Text = "Record Found";
}
else
{
// Add your comments here
Master.UserFeedBack.Text = "No records were found!";
}
}
catch (Exception error)
{
// Add your comments here
string message = "Something went wrong - ";
Master.UserFeedBack.Text = message + error.Message;
}
}
3. Add a button to pgCheckOut.aspx that calls btnFindLastName_Click for its click event. The button should have the name and ID btnFindLastName and the text Find Last Name. You can use the image in the Lab Summary section above for guidance on placing the button.
4. In Step F, when we update customer information, we must ensure that we update the correct customer. To do this, we use a field that uniquely identifies each row or customer in the table: the CustomerID field. We need to add this to pgCheckOut.aspx.
Add two labels to pgCheckOut.aspx with the names and IDs, lblCustID and customerID.
The text for lblCustID should be "Customer ID:".
The text for the label customerID can be left blank as the application will fill in that text.
5. Test your work. You should be able to enter a name in the Last Name field on the form and when the Find Last Name button is clicked, other form fields should be filled in with data on that customer. If the customer is not found, the appropriate message should be displayed to the user. The Access database comes with data already entered on four customers for testing: Smith, Doe, Rice, and Sue.


STEP F: Implement UpdateCustomer Functionality
1. In the clsDataLayer class, create a method called UpdateCustomer that accepts the customer's name and address as parameters. This method finds the customer whose CustomerID field matches the parameter customerID and updates the field values to match the arguments sent when it was called.
You can add the following code to the class clsDataLayer. Be sure to replace the lines in the code below that read "Add your comments here with your own comments explaining what the code does.
UpdateCustomer Method in clsDataLayer.cs
// Add your comments here
public void UpdateCustomer(string firstName, string lastName,
string street, string city,
string state, string phoneNumber, int customerID)
{
// Add your comments here
dbConnection.Open();
// Add your comments here
string sqlStmt = "UPDATE tblCustomers SET FirstName = @first, " +
"LastName = @last, " +
"Street = @street, " +
"City = @city, " +
"State = @state, " +
"PhoneNumber = @phone " +
"WHERE (tblCustomers.CustomerID = @id)";
// Add your comments here
OleDbCommand dbCommand = new OleDbCommand(sqlStmt, dbConnection);
// Add your comments here
OleDbParameter param = new OleDbParameter("@first", firstName);
dbCommand.Parameters.Add(param);
dbCommand.Parameters.Add(new OleDbParameter("@last", lastName));
dbCommand.Parameters.Add(new OleDbParameter("@street", street));
dbCommand.Parameters.Add(new OleDbParameter("@city", city));
dbCommand.Parameters.Add(new OleDbParameter("@state", state));
dbCommand.Parameters.Add(new OleDbParameter("@phone", phoneNumber));
dbCommand.Parameters.Add(new OleDbParameter("@id", customerID));
//Add your comments here
dbCommand.ExecuteNonQuery();
//Add your comments here
dbConnection.Close();
}
2. In pgCheckOut.aspx.cs, create the click method that calls the Data Layer method UpdateCustomer. This click method is invoked when the user clicks the Update
Customer button we add next. The following method calls UpdateCustomer, passing the form field values as arguments.
Be sure that the tempPath variable accurately reflects the path to where you have saved the Access database Accounts.mdb.
btnUpdate_Click Method in pgCheckOut.aspx.cs
protected void btnUpdate_Click(object sender, EventArgs e)
{
// Add your comments here
bool customerUpdateError = false;
// Add your comments here
// Depending on where you placed your Access database,
// one of the following lines may work better:
// tempPath = Server.MapPath("Accounts.mdb")
// tempPath = Server.MapPath("~/FPDB/Accounts.mdb")
string tempPath = Server.MapPath("~/App_Data/Accounts.mdb");
clsDataLayer myDataLayer = new clsDataLayer(tempPath);
// Add your comments here
try
{
myDataLayer.UpdateCustomer(txtFirstName.Text, txtLastName.Text,
txtStreet.Text, txtCity.Text,
txtState.Text, txtPhone.Text, Convert.ToInt32(customerID.Text));
}
catch (Exception error)
{
customerUpdateError = true;
string message = "Error updating customer, please check form data. ";
Master.UserFeedBack.Text = message + error.Message;
}
if (!customerUpdateError)
{
ClearInputs(Page.Controls);
Master.UserFeedBack.Text = "Customer Updated Successfully.";
}
}
3. Add a button to pgCheckOut.aspx that calls btnUpdateCustomer_Click for its click event. The button should have the name and ID btnUpdateCustomer and the text
Update Customer. You can use the image in the Lab Summary section above for guidance on placing the button.
4. Test your work. You should be able to enter a name in the Last Name field on the form and when the Find Last Name button is clicked, other form fields should be filled in with data on that customer. If the customer is not found, the appropriate message should be displayed to the user.
Once a customer has been retrieved (found) in the database, you can update the customer's information and click the Update Customer button. If the application is working correctly, the updated information on the customer will be retrieved from the database the next time you find the customer.


STEP G: Implement InsertCustomer Functionality
1. In the clsDataLayer class, create a method called InsertCustomer that accepts the customer's name and address as parameters. This method creates a new customer in the database with the data passed as arguments to the method.
You can add the following code to the class clsDataLayer. Be sure to replace the lines in the code below that read Add your comments here with your own comments explaining what the code does.
InsertCustomer Method in clsDataLayer.cs
// Add your comments here
public void InsertCustomer(string firstName, string lastName,
string street, string city,
string state, string phoneNumber)
{
// Add your comments here
dbConnection.Open();
// Add your comments here
string sqlStmt = "INSERT INTO tblCustomers (FirstName, LastName, Street, City,
State, PhoneNumber) ";
sqlStmt += "VALUES (@first, @last, @street, @city, @state, @phone)";
// Add your comments here
OleDbCommand dbCommand = new OleDbCommand(sqlStmt, dbConnection);
// Add your comments here
OleDbParameter param = new OleDbParameter("@first", firstName);
dbCommand.Parameters.Add(param);
dbCommand.Parameters.Add(new OleDbParameter("@last", lastName));
dbCommand.Parameters.Add(new OleDbParameter("@street", street));
dbCommand.Parameters.Add(new OleDbParameter("@city", city));
dbCommand.Parameters.Add(new OleDbParameter("@state", state));
dbCommand.Parameters.Add(new OleDbParameter("@phone", phoneNumber));
//Add your comments here
dbCommand.ExecuteNonQuery();
//Add your comments here
dbConnection.Close();
}
2. In pgCheckOut.aspx.cs, create the click method that calls the Data Layer method InsertCustomer. This click method is invoked when the user clicks the Add Customer button we add next. The following method calls AddCustomer, passing the form field values as arguments, and then clears the form to await information the user enters on the next customer. Be sure that the tempPath variable accurately reflects the path to where you have saved the Access database Accounts.mdb.
btnAdd_Click Method in pgCheckOut.aspx.cs
// Add your comments here
protected void btnAdd_Click(object sender, EventArgs e)
{
// Add your comments here
bool customerAddError = false;
// Add your comments here
// Depending on where you placed your Access database,
// one of the following lines may work better:
// tempPath = Server.MapPath("Accounts.mdb")
// tempPath = Server.MapPath("~/FPDB/Accounts.mdb")
string tempPath = Server.MapPath("~/App_Data/Accounts.mdb");
clsDataLayer myDataLayer = new clsDataLayer(tempPath);
// Add your comments here
try
{
// Add your comments here
myDataLayer.InsertCustomer(txtFirstName.Text, txtLastName.Text,
txtStreet.Text, txtCity.Text,
txtState.Text, txtPhone.Text);
}
catch (Exception error)
{
// Add your comments here
customerAddError = true;
string message = "Error adding customer, please check form data. ";
Master.UserFeedBack.Text = message + error.Message;
}
// Add your comments here
if (!customerAddError)
{
ClearInputs(Page.Controls);
Master.UserFeedBack.Text = "Customer Added Successfully.";
}
}
3. Add a button to pgCheckOut.aspx that calls btnAddCustomer_Click for its click event. The button should have the name and ID btnAddCustomer and the text Add Customer. You can use the image in the Lab Summary section above for guidance on placing the button.
4. Test your work You should be able to enter a customer's name and address in the appropriate fields on the form and when the Add Customer button is clicked, a record should be added to the database for the customer.
You should be able to view the record directly in the database or by finding the customer using the Find Last Name functionality of the application.


STEP H: Test and Finalize the Lab
1. Save your work!
2. Test it! Check the Clear, Find, Update, and Add capabilities. Until you add more of your own, the last names in the database that you can search for in this lab are Smith, Doe, Rice, and Sue.
3. Make changes as appropriate until it works.
4. Remember to add comments for each step being performed.
5. Please zip and submit the entire web project folder.

Regular Price: $12.00

Special Price: $10.00

Details

WEB460 Lab 3 of 7: Adding Data Layer Functionality

Scenario/Summary
For our Lab this week, you will connect to a Microsoft Access database to store, update, and retrieve customer information. Here is an overview of the lab:
Step A: Create a New Web Site Project
Copy files from last week's Lab.
Step B: Add the ClearForm Functionality to pgCheckOut
Add a button and code to clear form fields.
Step C: Create a DataSet and Link It to an Access Database
This step also creates a TableAdapter that can be used in our code.
Step D: Create the clsDataLayer Class to Represent Our Application's Data Layer
Add a data field for the connection and modify the constructor.
Step E: Implement the FindCustomer Functionailty
This and the following two steps require editing three different files and then testing your changes.
Be careful to add the lab code to the correct file each time.
Generally, the parts of these steps are as follows:
– Add a method to the clsDataLayer class.
– Add a click method to pgCheckOut.aspx.cs that calls the method in clsDataLayer.
– Add a button to pgCheckOut.aspx that calls the click method.
Step F: Implement the UpdateCustomer Functionailty
Step G: Implement the InsertCustomer Functionality
Step H: Test and Finalize the Lab
When you have completed and tested the lab, the web form pgCheckOut should look similar to this image:

Deliverables
A zip archive of the ASP.NET Web Application directory. It should contain the following files in
addition to your database and dataset files:
– pgCheckOut.aspx **
– pgCheckOut.aspx.cs **
– pgConfirm.aspx
– pgConfirm.aspx.cs
– Web460Store.master
– Web460Store.master.cs
– clsDataLayer.cs **
The files with ** are the ones that should have been modified for this lab.

Lab Steps
STEP A: Create a New Web Site Project
1. Create a new Empty Web Site project.
2. Copy the six files from last week's Lab into the folder for this new project. Be careful not to move the files. We want to work on a copy of last week's lab and leave the original untouched. The website folder should have the following files:
pgCheckOut.aspx
pgCheckOut.aspx.cs
pgConfirm.aspx
pgConfirm.aspx.cs
Web460Store.master
Web460Store.master.cs
web.config
web.Debug.config ( optional: depends on the version of Visual Studio you are using)
3. Set pgCheckOut.aspx as the start page and test your application. It should perform just as it did last week.

STEP B: Add the ClearForm Functionality to pgCheckOut
Because we will be adding, retrieving, and updating customer information, we should give the user the ability to easily clear the form fields so that information from one customer is not mixed with that of another.
1. Add a private method to pgCheckOut.aspx.cs that examines each control on the page. If the control is a Textbox, DropDown list, or RadioButton, the control is cleared. If it is a panel or other container, the method calls itself recursively, passing the controls on that container so that they may be cleared.
ClearInputs Method in pgCheckOut.aspx.cs
private void ClearInputs(ControlCollection ctrls)
{
foreach (Control ctrl in ctrls)
{
if (ctrl is TextBox)
((TextBox)ctrl).Text = string.Empty;
else if (ctrl is DropDownList)
((DropDownList)ctrl).ClearSelection();
else if (ctrl is RadioButton)
((RadioButton)ctrl).Checked = false;
else if (ctrl is RadioButtonList)
((RadioButtonList)ctrl).ClearSelection();
else
ClearInputs(ctrl.Controls);
}
}
2. Next, we add a click method that will be called by a form button. The form button cannot call the recursive method directly because click methods require a different set of parameters. Because of this, we need the private helper method ClearInputs.
btnClearForm_Click in pgCheckOut.aspx.cs
public void btnClearForm_Click(object sender, EventArgs e)
{
ClearInputs(Page.Controls);
}
3. Add a button to pgCheckOut.aspx that calls the Clear Form click handler above. The button should have the name and ID btnClearForm and the text "Clear Form". You can use the image in the Lab Summary section above for guidance on placing the button.
4. Test your work. You should be able to enter data in the form fields on the left and when the Clear Form button is clicked, all form fields should be cleared.

STEP C: Create a DataSet and Link it to Microsoft Access Database
These steps create and add a DataSet called dsAccounts to your project. Carefully read through these instructions as you step through the creation of the DataSet. If you cannot connect to the database when these steps are complete, delete the DataSet and recreate it from scratch to make sure all parts are initialized properly.
1. Download and copy the Microsoft Access database, Acounts.mdb, found in the Files section of the Course Menu to your website folder. Note that you can have the database at any location on your computer, but your code should be adjusted accordingly. It is best to have it in the top level of your website or in the App_Data folder. In Steps E, F, and G below, you will need to adjust your code to accurately reflect the path to where your database is stored.
If you are using the DeVry FTP site websol, you must place the database into the FPDB folder in your account top-level directory. It is recommended to use your local computer instead of the websol FTP server.
2. From the Solution Explorer pane, right click on the App_Code folder and select the Add New Item menu option. In the Add New Item dialog box, select DataSet and type the dsAccounts as the name of the DataSet. When prompted, allow Visual Studio to store the DataSet in the App_Code folder.
3. After creating dsAccounts, double-click TableAdapter in the Dataset Toolbox to configure the connection to the database using the TableAdapter Configuration wizard. The Dataset Toolbox panel is displayed by selecting dsAccounts.xsd in the Solution Explorer panel and then clicking the Toolbox link in the main window. You may also select Toolbox from the View menu.
4. On the first window, click the New Connection button.
5. In the Choose Data Source window, select the file Microsoft Access Database and click Continue.
6. In the Add Connection window, click the Browse button and select the Microsoft Access database that you downloaded from the Files section of the Course Menu named Accounts.mdb.
7. Click Test Connection to ensure that Visual Studio can access and connect to the database.
8. Click OK, and then click Next on the TableAdapter Wizard. If you expand the connection string + symbol, you can view the connection string used to access the database. This should closely match what we will use in our application.
9. Click Next again to save the connection string to a file.
10. On the Wizard's Choose a Command Type screen, select SQL Statements and then click Next.
11. We now enter the default SQL query for this DataSet connection. In the textbox on this screen, enter the following SQL SELECT statement whose result will be used to populate the DataSet:
SELECT * FROM tblCustomers
12. Click Finish to exit the TableAdapter wizard.

STEP D: Create the clsDataLayer Class to Represent Our Application's Data Layer
1. Right-click on the project name in the Solution Explorer pane and select Add. From the submenu, select New Item. From the Add Dialog Box, add a Class called clsDataLayer.
The code file (clsDataLayer.cs) will automatically be placed in the App_Code folder in your website directory.
2. Add a data field to our class that represents the database connection and adjust the constructor to initialize it.
clsDataLayer Data Field and Constructor
OleDbConnection dbConnection;
Be sure to place this code in the correct location in the class file. Verify that your code does not have any syntax errors before continuing.
public clsDataLayer(string Path)
{
dbConnection = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path);
}

STEP E: Implement the FindCustomer Functionality Editing Multiple Files
This step, Step F, and Step G require you to add code to three different files. Carefully follow the directions to ensure that you add code to the correct files. Test your code after each step before moving to the next. You may want to make a copy of the following files before starting this step so that if you make a significant error, you can return to this point:
clsDataLayer.cs
pgCheckOut.aspx.cs
pgCheckOut.aspx
1. In the clsDataLayer class, create a method, called FindCustomer, that accepts LastName as a parameter. This method finds the all occurrences of customers with LastName in the database and places the results in the DataSet object mystoreDataSet, which is an instance of our dsAccounts DataSet.
You can add the following code to the class clsDataLayer. Be sure to replace the lines in the code below that read "Add your comments here with your own comments
explaining what the code does.
FindCustomer Method in clsDataLayer.cs
// Add your comments here
public dsAccounts FindCustomer(string LastName)
{
//Add your comments here
string sqlStmt = "select * from tblCustomers where LastName like '" + LastName + "'";
OleDbDataAdapter sqlDataAdapter = new OleDbDataAdapter(sqlStmt, dbConnection);
//Add your comments here
dsAccounts myStoreDataSet = new dsAccounts();
sqlDataAdapter.Fill(myStoreDataSet.tblCustomers);
// Add your comments here
return myStoreDataSet;
}
When adding this database code to clsDataLayer, do not forget to add the two Using directives in C# that must be placed at the start of your code that ensure that the compiler has the definition of the Data and OleDataAdapter objects necessary for the data layer. (Your challenge is to research and discover what they are.)
2. In pgCheckOut.aspx.cs, create the click method that calls the Data Layer method FindCustomer. This click method is invoked when the user clicks the Find Last Name button we add next. The following method calls FindCustomer and then fills the form fields with results from the Data Set. Be sure that the tempPath variable accurately reflects the path to where you have saved the Access database Accounts.mdb.
btnFindLastName_Click Method in pgCheckOut.aspx.cs
// Add your comments here
protected void btnFindLastName_Click(object sender, EventArgs e)
{
// Add your comments here
dsAccounts dsFindLastName;
// Add your comments here
// Depending on where you placed your Access database,
// one of the following lines may work better:
// tempPath = Server.MapPath("Accounts.mdb")
// tempPath = Server.MapPath("~/FPDB/Accounts.mdb")
string tempPath = Server.MapPath("~/App_Data/Accounts.mdb");
clsDataLayer dataLayerObj = new clsDataLayer(tempPath);
try
{
// Add your comments here
dsFindLastName = dataLayerObj.FindCustomer(txtLastName.Text);
// Add your comments here
if (dsFindLastName.tblCustomers.Rows.Count > 0)
{
// Add your comments here
txtFirstName.Text = dsFindLastName.tblCustomers[0].FirstName;
txtLastName.Text = dsFindLastName.tblCustomers[0].LastName;
txtStreet.Text = dsFindLastName.tblCustomers[0].Street;
txtCity.Text = dsFindLastName.tblCustomers[0].City;
txtState.Text = dsFindLastName.tblCustomers[0].State;
txtPhone.Text = dsFindLastName.tblCustomers[0].PhoneNumber;
customerID.Text = dsFindLastName.tblCustomers[0].CustomerID.ToString();
Master.UserFeedBack.Text = "Record Found";
}
else
{
// Add your comments here
Master.UserFeedBack.Text = "No records were found!";
}
}
catch (Exception error)
{
// Add your comments here
string message = "Something went wrong - ";
Master.UserFeedBack.Text = message + error.Message;
}
}
3. Add a button to pgCheckOut.aspx that calls btnFindLastName_Click for its click event. The button should have the name and ID btnFindLastName and the text Find Last Name. You can use the image in the Lab Summary section above for guidance on placing the button.
4. In Step F, when we update customer information, we must ensure that we update the correct customer. To do this, we use a field that uniquely identifies each row or customer in the table: the CustomerID field. We need to add this to pgCheckOut.aspx.
Add two labels to pgCheckOut.aspx with the names and IDs, lblCustID and customerID.
The text for lblCustID should be "Customer ID:".
The text for the label customerID can be left blank as the application will fill in that text.
5. Test your work. You should be able to enter a name in the Last Name field on the form and when the Find Last Name button is clicked, other form fields should be filled in with data on that customer. If the customer is not found, the appropriate message should be displayed to the user. The Access database comes with data already entered on four customers for testing: Smith, Doe, Rice, and Sue.

STEP F: Implement UpdateCustomer Functionality
1. In the clsDataLayer class, create a method called UpdateCustomer that accepts the customer's name and address as parameters. This method finds the customer whose CustomerID field matches the parameter customerID and updates the field values to match the arguments sent when it was called.
You can add the following code to the class clsDataLayer. Be sure to replace the lines in the code below that read "Add your comments here with your own comments explaining what the code does.
UpdateCustomer Method in clsDataLayer.cs
// Add your comments here
public void UpdateCustomer(string firstName, string lastName,
string street, string city,
string state, string phoneNumber, int customerID)
{
// Add your comments here
dbConnection.Open();
// Add your comments here
string sqlStmt = "UPDATE tblCustomers SET FirstName = @first, " +
"LastName = @last, " +
"Street = @street, " +
"City = @city, " +
"State = @state, " +
"PhoneNumber = @phone " +
"WHERE (tblCustomers.CustomerID = @id)";
// Add your comments here
OleDbCommand dbCommand = new OleDbCommand(sqlStmt, dbConnection);
// Add your comments here
OleDbParameter param = new OleDbParameter("@first", firstName);
dbCommand.Parameters.Add(param);
dbCommand.Parameters.Add(new OleDbParameter("@last", lastName));
dbCommand.Parameters.Add(new OleDbParameter("@street", street));
dbCommand.Parameters.Add(new OleDbParameter("@city", city));
dbCommand.Parameters.Add(new OleDbParameter("@state", state));
dbCommand.Parameters.Add(new OleDbParameter("@phone", phoneNumber));
dbCommand.Parameters.Add(new OleDbParameter("@id", customerID));
//Add your comments here
dbCommand.ExecuteNonQuery();
//Add your comments here
dbConnection.Close();
}
2. In pgCheckOut.aspx.cs, create the click method that calls the Data Layer method UpdateCustomer. This click method is invoked when the user clicks the Update
Customer button we add next. The following method calls UpdateCustomer, passing the form field values as arguments.
Be sure that the tempPath variable accurately reflects the path to where you have saved the Access database Accounts.mdb.
btnUpdate_Click Method in pgCheckOut.aspx.cs
protected void btnUpdate_Click(object sender, EventArgs e)
{
// Add your comments here
bool customerUpdateError = false;
// Add your comments here
// Depending on where you placed your Access database,
// one of the following lines may work better:
// tempPath = Server.MapPath("Accounts.mdb")
// tempPath = Server.MapPath("~/FPDB/Accounts.mdb")
string tempPath = Server.MapPath("~/App_Data/Accounts.mdb");
clsDataLayer myDataLayer = new clsDataLayer(tempPath);
// Add your comments here
try
{
myDataLayer.UpdateCustomer(txtFirstName.Text, txtLastName.Text,
txtStreet.Text, txtCity.Text,
txtState.Text, txtPhone.Text, Convert.ToInt32(customerID.Text));
}
catch (Exception error)
{
customerUpdateError = true;
string message = "Error updating customer, please check form data. ";
Master.UserFeedBack.Text = message + error.Message;
}
if (!customerUpdateError)
{
ClearInputs(Page.Controls);
Master.UserFeedBack.Text = "Customer Updated Successfully.";
}
}
3. Add a button to pgCheckOut.aspx that calls btnUpdateCustomer_Click for its click event. The button should have the name and ID btnUpdateCustomer and the text
Update Customer. You can use the image in the Lab Summary section above for guidance on placing the button.
4. Test your work. You should be able to enter a name in the Last Name field on the form and when the Find Last Name button is clicked, other form fields should be filled in with data on that customer. If the customer is not found, the appropriate message should be displayed to the user.
Once a customer has been retrieved (found) in the database, you can update the customer's information and click the Update Customer button. If the application is working correctly, the updated information on the customer will be retrieved from the database the next time you find the customer.

STEP G: Implement InsertCustomer Functionality
1. In the clsDataLayer class, create a method called InsertCustomer that accepts the customer's name and address as parameters. This method creates a new customer in the database with the data passed as arguments to the method.
You can add the following code to the class clsDataLayer. Be sure to replace the lines in the code below that read Add your comments here with your own comments explaining what the code does.
InsertCustomer Method in clsDataLayer.cs
// Add your comments here
public void InsertCustomer(string firstName, string lastName,
string street, string city,
string state, string phoneNumber)
{
// Add your comments here
dbConnection.Open();
// Add your comments here
string sqlStmt = "INSERT INTO tblCustomers (FirstName, LastName, Street, City,
State, PhoneNumber) ";
sqlStmt += "VALUES (@first, @last, @street, @city, @state, @phone)";
// Add your comments here
OleDbCommand dbCommand = new OleDbCommand(sqlStmt, dbConnection);
// Add your comments here
OleDbParameter param = new OleDbParameter("@first", firstName);
dbCommand.Parameters.Add(param);
dbCommand.Parameters.Add(new OleDbParameter("@last", lastName));
dbCommand.Parameters.Add(new OleDbParameter("@street", street));
dbCommand.Parameters.Add(new OleDbParameter("@city", city));
dbCommand.Parameters.Add(new OleDbParameter("@state", state));
dbCommand.Parameters.Add(new OleDbParameter("@phone", phoneNumber));
//Add your comments here
dbCommand.ExecuteNonQuery();
//Add your comments here
dbConnection.Close();
}
2. In pgCheckOut.aspx.cs, create the click method that calls the Data Layer method InsertCustomer. This click method is invoked when the user clicks the Add Customer button we add next. The following method calls AddCustomer, passing the form field values as arguments, and then clears the form to await information the user enters on the next customer. Be sure that the tempPath variable accurately reflects the path to where you have saved the Access database Accounts.mdb.
btnAdd_Click Method in pgCheckOut.aspx.cs
// Add your comments here
protected void btnAdd_Click(object sender, EventArgs e)
{
// Add your comments here
bool customerAddError = false;
// Add your comments here
// Depending on where you placed your Access database,
// one of the following lines may work better:
// tempPath = Server.MapPath("Accounts.mdb")
// tempPath = Server.MapPath("~/FPDB/Accounts.mdb")
string tempPath = Server.MapPath("~/App_Data/Accounts.mdb");
clsDataLayer myDataLayer = new clsDataLayer(tempPath);
// Add your comments here
try
{
// Add your comments here
myDataLayer.InsertCustomer(txtFirstName.Text, txtLastName.Text,
txtStreet.Text, txtCity.Text,
txtState.Text, txtPhone.Text);
}
catch (Exception error)
{
// Add your comments here
customerAddError = true;
string message = "Error adding customer, please check form data. ";
Master.UserFeedBack.Text = message + error.Message;
}
// Add your comments here
if (!customerAddError)
{
ClearInputs(Page.Controls);
Master.UserFeedBack.Text = "Customer Added Successfully.";
}
}
3. Add a button to pgCheckOut.aspx that calls btnAddCustomer_Click for its click event. The button should have the name and ID btnAddCustomer and the text Add Customer. You can use the image in the Lab Summary section above for guidance on placing the button.
4. Test your work You should be able to enter a customer's name and address in the appropriate fields on the form and when the Add Customer button is clicked, a record should be added to the database for the customer.
You should be able to view the record directly in the database or by finding the customer using the Find Last Name functionality of the application.

STEP H: Test and Finalize the Lab
1. Save your work!
2. Test it! Check the Clear, Find, Update, and Add capabilities. Until you add more of your own, the last names in the database that you can search for in this lab are Smith, Doe, Rice, and Sue.
3. Make changes as appropriate until it works.
4. Remember to add comments for each step being performed.
5. Please zip and submit the entire web project folder.

Product Tags

Use spaces to separate tags. Use single quotes (') for phrases.