The following step-by-step tutorial re-creates the Employee project using a presentation tier and a data tier.
Create a New Project
STEP 1: Begin a new Windows Application project. Name the project
Ch03EmployeeTiers.
STEP 2: Name the form EmployeesForm and change the Text property to Employees.
Add a Data Source
STEP 1: From the Data Sources window, add a Pubs database data source. In- clude the emp_id, fname, lname, and hire_date fields from the em- ployee table.
STEP 2: In the DataSet Designer, add a new column called FullName to the
employee table using the expression “fname + ' ' + lname” (with- out the outer double quotes). Notice that the order is reversed from the earlier examples.
Trouble ? If you receive a message that the column could not be added, close and reopen the DataSet Designer and repeat the step, which should now work.
STEP 3: In the DataSet Designer, right-click on employeeTableAdapter and
select Configure . In the TableAdapter Configuration Wizard, select Advanced Options .
STEP 4: In the Advanced Options dialog box, deselect the check box for generat- ing the extra statements. All three check boxes should be deselected. Click OK . Click Finish in the TableAdapter Configuration Wizard.
STEP 5: In the DataSet Designer, modify the Caption property for each field to be a “user-friendly” string.
STEP 6: Save all.
Add a Class for the Data Tier
You need to add a new class for the data tier. Although you could use the Com- ponent class, which has a visual designer, for this example we will not need the visual designer.
STEP 1: From the Project menu, choose Add Class . (Alternate method: Right- click the project name in the Solution Explorer and select Add / Class .)
STEP 2: Name the new class PubsEmployeeData.vb.
Code the Component
STEP 1: Display the code for your new class. Notice that the design template generated the lines
Public Class PubsEmployeeData End Class
STEP 2: Code the class-level variables. Although these variables could be
coded as local in this program, in future programs you will need class- level variables for the DataSet and TableAdapter.
' Declare class-level variables. Private AnEmployeeTableAdapter _
As PubsDataSetTableAdapters.employeeTableAdapter Private APubsDataSet As PubsDataSet
STEP 3: Write the GetEmployeeDataset method in the data-tier class. Notice that this method returns a DataSet object.
Public Function GetEmployeeDataset() As PubsDataSet ' Fill and return the DataSet.
Try
' Instantiate the TableAdapter and DataSet. AnEmployeeTableAdapter = _
New PubsDataSetTableAdapters.employeeTableAdapter APubsDataSet = New PubsDataSet
' Fill the DataSet.
AnEmployeeTableAdapter.Fill(APubsDataSet.employee) ' Return the DataSet.
Return APubsDataSet Catch ex As Exception Throw ex
End Try End Function
STEP 4: Write comments at the top of the class.
STEP 5: Save all.
Add Controls to the Form
Although you can create the user interface by adding individual controls from the toolbox, we are going to take a shortcut. We will use the drag-and-drop feature from the Data Sources window and then delete the extra components.
STEP 1: Display the form in the designer.
STEP 2: In the Data Sources window, set the employee table to Details .
STEP 3: Change the emp_id field to a ComboBox. STEP 4: Drag the table to the form.
STEP 5: Delete all of the objects in the component tray. We cannot use these components because we will get the data from the data tier.
STEP 6: Display the form’s code and delete the automatically generated code.
All error messages should disappear.
STEP 7: Delete the text boxes and identifying labels for the first name and last name fields. (Keep the controls for Employee ID, Full Name, and Hire Date.)
STEP 8: Rearrange the controls and change the labels, if necessary, to Employee
ID, Name, and Hire Date. Figure 3.29 shows the form at this point.
F i g u r e 3 . 2 9
Rearrange the controls for the step-by-step exercise.
Code the Form
In the form, you must write code to retrieve the DataSet and bind to the data fields. You cannot do this at design time since the DataSet is declared in the data-tier component. You must declare an instance of the DataTier component and a DataSet object. Then you can call the GetEmployeeDataset method to retrieve the DataSet from the data tier and bind the data to a BindingSource object.
STEP 1: Switch to the form’s code and delete all automatically generated
procedures.
STEP 2: Write the code for the form’s Load event handler.
Private Sub EmployeeForm_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
' Retrieve the data and bind the form’s controls. Try
' Create an instance of the data-tier component. Dim EmployeeData As New PubsEmployeeData
' Retrieve the dataset from the data tier. Dim APubsDataSet As PubsDataSet
APubsDataSet = EmployeeData.GetEmployeeDataset() ' Set up the binding source.
Dim ABindingSource As New BindingSource With ABindingSource
.DataSource = APubsDataSet .DataMember = "employee" .Sort = "emp_id"
End With
' Fill the combo box. With Emp_idComboBox
.DataSource = ABindingSource .DisplayMember = "emp_id"
.DataBindings.Add("text", ABindingSource, "emp_id", _ False, DataSourceUpdateMode.Never)
End With
' Bind the other controls.
FullNameTextBox.DataBindings.Add("text", _ ABindingSource, "FullName") Hire_dateDateTimePicker.DataBindings.Add("text", _ ABindingSource, "hire_date") Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub
STEP 3: Write remarks at the top of the form class.
Test the Project
STEP 1: Test the project. The fields should fill with data, just as they did when
the data components were in the form. Select various records using the combo box.
F i g u r e 3 . 3 0
A planning sketch for the hands-on programming example. FullNameComboBox Emp_idTextBox Job_idTextBox Job_lvlTextBox Pub_idTextBox Name: Employee ID: Job ID: Job Level: Pub ID: File Hire_dateTextBox Hire Date:
➤
Feedback 3.4
1. Where do you place the binding source and TableAdapter for a multi- tier project?
2. What return type is necessary for a function in the class that fills a DataSet from a TableAdapter?
3. Write the code to bind a first name label to a DataSet called Customers- DataSet. The form uses CustomersBindingSource. Display the First- Name field from the Customer table.
Your Hands-On Programming Example
Create a multiple-tier project to obtain data from the employee table in the Pubs database using a stored procedure. Use an ORDER BY clause in the stored procedure to sort the data in order by last name. Display the full employee names (last name, first name, and middle initial) in alphabetic order in a combo box. Do not display the first and last name fields other than in the combo box. Show the remaining fields in text boxes for the selected record, with meaning- ful identifying labels. Do not include a navigation toolbar. Include a File / Exit menu item to terminate the program.
Planning the Project
Sketch a form ( Figure 3.30 ) that your users sign off as meeting their needs.
Plan the Objects, Properties, and Methods Plan the two tiers. Determine the objects and property settings for the form and its controls and for the data-tier component. Figure 3.31 shows a diagram of the components in the two tiers.
Presentation Tier
Object Property Setting
EmployeeForm Name EmployeeForm
Text Employees
FullNameComboBox Name FullNameComboBox
Text boxes for all database fields (including Hire Date)
Data Tier
Methods Actions—Pseudocode
GetEmployeeData Instantiate the DataSet and TableAdapter. Fill the DataSet.
Return the DataSet.
F i g u r e 3 . 3 1
A diagram of the components in each tier for the hands-on programming example.
Presentation Tier Data Tier
Event Handlers/Methods Actions—Pseudocode
ExitToolStripMenuItem_Click Exit the project.
Form_Load Instantiate the data tier.
Retrieve the DataSet. Bind the controls.
Format the date in its binding.
Write the Project Following the sketch in Figure 3.30 , create the form. Fig- ure 3.32 shows the completed form.
• Add a copy of Pubs.mdf to your project and set a connection to the file us- ing the Server Explorer. Then create a stored procedure to retrieve and sort the data and create the new concatenated FullName field.
• Using the Data Sources window, set up the data source and DataSet.
• Create the data-tier component, writing the method according to the pseudocode.
• Set up the user interface according to the plans.
• Write the code for the form. Working from the pseudocode, write each event handler.
• When you complete the code, test the operation several times. Compare the screen output to the data tables to make sure that you are displaying the correct information.
The Project Coding Solution The Form 'Project: Ch03HandsOn 'Programmer: Bradley/Millspaugh 'Date: June 2009 'Class: EmployeeForm
'Description: Presentation tier to display the employee information. Public Class EmployeeForm
Private Sub EmployeeForm_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load
' Retrieve data from data tier and bind controls. Try
Dim EmployeeData As New EmployeeDataTier Dim EmployeeDataSet As PubsDataSet
EmployeeDataSet = EmployeeData.GetEmployeeData() ' Set up the BindingSouce.
Dim ABindingSource As New BindingSource With ABindingSource
.DataSource = EmployeeDataSet .DataMember = "getEmployees" End With
' Bind the controls. With FullNameComboBox
.DataSource = ABindingSource .DisplayMember = "fullname"
.DataBindings.Add("text", ABindingSource, "FullName", _ False, DataSourceUpdateMode.Never) End With Emp_idTextBox.DataBindings.Add("text", _ ABindingSource, "emp_id") Job_idTextBox.DataBindings.Add("text", _ ABindingSource, "job_id") Job_lvlTextBox.DataBindings.Add("text", _ ABindingSource, "job_lvl") F i g u r e 3 . 3 2
The completed form for the hands-on programming example.
Pub_idTextBox.DataBindings.Add("text", _ ABindingSource, "pub_id")
Hire_dateTextBox.DataBindings.Add("text", _ ABindingSource, "hire_date", True, _
DataSourceUpdateMode.OnValidation, Nothing, "d") Catch ex As Exception
MessageBox.Show(ex.Message) End Try
End Sub
Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click ' End the program.
Me.Close() End Sub
End Class
The Data-Tier Component
'Project: Ch03HandsOn
'Programmer: Bradley/Millspaugh 'Date: June 2009
'Class: EmployeeDataTier
'Description: The data-tier component for the Ch03HandsOn program. ' Includes the code for the database access.
Public Class EmployeeDataTier ' Class-level declarations.
Private EmployeeDataSet As PubsDataSet
Private EmployeeTableAdapter As PubsDataSetTableAdapters.getEmployeesTableAdapter Public Function GetEmployeeData() As PubsDataSet
' Fill and return the DataSet. EmployeeDataSet = New PubsDataSet
EmployeeTableAdapter = New PubsDataSetTableAdapters.getEmployeesTableAdapter EmployeeTableAdapter.Fill(EmployeeDataSet.getEmployees)
Return EmployeeDataSet End Function
End Class
The Stored Procedure
CREATE PROCEDURE getEmployees /*
Procedure to retrieve and sort the Employee table. */
AS
Select *, lname+', '+fname+' '+minit as FullName from Employee
order by lname RETURN
S u m m a r y
1. Data are accessible from many sources including databases, files, e-mail, and spreadsheets.
2. ADO.NET is the object model for referencing data in a VB application program.
3. Database tables contain rows (records) and columns (fields).
4. A primary key uniquely identifies a record. When a primary key is in- cluded in a second table for linking purposes, it is called a foreign key in the second table.
5. A typed DataSet can contain multiple tables, as well as relationships and constraints. The data in a DataSet are a temporary copy in memory, which are disconnected from the file from which they come.
6. A DataSet object has a DataTable collection, which can hold multiple DataTable objects. Each DataTable has a DataRow collection, a DataCol- umn collection, and a Constraints collection. The DataSet also can contain DataRelation objects.
7. A TableAdapter object connects to the database and handles SQL queries.
8. The objects used for database handling include the BindingSource, TableAdapter, and DataSet components.
9. XML is an industrywide standard for storing and transferring data in a text-based format with tags that identify the data fields. An XML file also may have a schema file that defines field names, data types, and constraints.
10. The Data Sources window provides the ability to add data sources to a project and drag tables to a form to create data-bound controls.
11. SQL Server 2008 Express Edition, which allows programming against SQL Server databases, is included with Visual Studio and is considered the “native” database format for a single-user database in VB.
12. By dragging a table name from the Data Sources window to the form, you can automatically display data in a DataGridView control. Set the Header- Text property of each column to modify the column headings.
13. The DataSet Designer is a visual tool for working with a DataSet. You can add fields, tables, and relationships in the DataSet Designer, as well as preview the data.
14. In the Data Sources window, you can select the control to use for each database field and whether to display the table in a grid or details view.
15. In the DataSet Designer, you can set the caption for each field in the Data- Set, which is used for the identifying labels for detail controls. You also can add a field with an expression, which can be concatenated strings or calculated values.
16. A common way to allow the user to access data is to display a list box with a field for the user to select. You must set the list’s DataSource and Dis- playMember properties to automatically fill the list.
17. You can sort database data by using the Sort method of the TableAdapter.
18. You also can sort data by modifying the SQL SELECT statement generated by the TableAdapter to include an ORDERBY clause. The Query Builder can assist you in creating and modifying SQL queries.
19. Fields may be combined or calculated as expressions by using an Alias field in the SQL SELECT statement of the TableAdapter.
20. The TableAdapter automatically creates SQL statements for updating the data; you can choose to not create those extra statements in the TableAdapter Configuration Wizard.
21. Stored procedures provide a more secure method for accessing data than SQL queries in your application. You can create stored procedures from within the VS IDE.
22. OOP principles suggest that separating data access from the user inter- face is the preferred solution. You can create a separate data tier by adding a new class to the project. In the new class, write code to declare and instantiate the DataSet and TableAdapter and fill the DataSet. The DataSet can be passed to the user interface (presentation tier) as needed. The presentation tier must explicitly bind the fields from the data-tier component.
23. You must explicitly bind form controls to the data fields from the data tier. For a ComboBox, set the DataSource and DisplayMember properties; for TextBoxes and DateTimePickers, use the DataBindings.Add method of each control.