|
Table of Contents
Introduction
Microsoft is known for providing good tools for Rapid Application Development (RAD) tools for developers. MS Access, Visual Studio 6, Visual Interdev and others have provided an environment to visually combine controls, properties, and generated code for quick applications. Visual Studio .NET has combined the best of the previous tools into a great development environment with several different coding languages.
There are many wizards in Visual Studio .NET (VS) that assist new users in creating applications. Unfortunately, they tend not to promote good programming habits--specifically there is not a separation of code into layers and they do not adhere to OOP standards isolating business logic. The default model of VS is to drag and drop data objects onto the form we are building. VS generates the code necessary, but mixes the data access code and the business logic code directly in the user interface form object. This limits the reusability of the code and tends to duplicate code where the same table data is used on more than one form.
Most architects recommend more of an n-tiered approach where the code is separated into different layers. There should at least be a data access layer, business logic layer, and a presentation layer. When working with distributed applications (different parts of the application running on different servers) more layers may need to be added.
There are two primary advantages for separating the layers: code reusability and de-coupling code from the database. By separating out the data access and business logic layers, many different forms can be used to collect and display the information without rewriting or duplicating the code to access and use the information. The de-coupling helps keep your code from breaking when the database schema changes and makes it easier to even change data storage providers without much code change.
Microsoft .NET has made great strides in supporting and encouraging developers to move toward Object Oriented Programming (OOP) standards. While OOP is a large (and deep) subject, one facet that should interest developers of database applications is the concept of a business object. While database tables and fields do a good job of implementing entities and attributes, business objects bring together both the properties and behavior of a particular entity. When the application instantiates a business object class to represent each entity (table), the attributes can be accessed through properties and the behavior of the entity can be called through methods. This concept also brings all the code for an entity together into one place that can be referenced easily from other parts of the application. Through inheritance and polymorphism, the business objects can be quite flexible and have substantial power to accomplish much with little code.
Part 2 - Using Visual Studio 2005
The new version of .NET and Visual Studio offers several enhancements that change the way we develop database centric applications. In the context of this article, we will be looking specifically at Data Sources and Partial Classes.
The Binding Context Manager has been expanded and greatly simplified into Binding Sources component that allows forms to provide better data binding to datasets, Web services, and business objects.
Partial Classes provides a way to extend classes across multiple files which will be combined together at compile time. The Typed Dataset class has been extended to include partial classes, separating generated code from hand crafted code, allowing us to add business logic to the dataset class without the complications of inheritance.
Getting Started
After opening VS2005, create a new project (File / New Project) and let's start with a VB Windows project. In the dialog at the bottom, name your project NorthwindWin and the Solution Northwind.
Figure 1 - New Project in VS2005
The Data Object
The first step in creating a data application is to create a Typed DataSet object to define the data object to be used. You can add a new DataSet object to you project from the Solution Explorer window by right-clicking on the project and selecting Add / New Item. The design surface of the DataSet has been enhanced to automatically include relations from the database when more than one table is added to the dataset (by dragging from the Server Explorer or Database Explorer (Express Version) window). The dataset has been expanded to include one or more data adapters to handle the "Fill" and "Update" methods so we don't need to create an inherited class to implement them. The Configuration Wizard has been changed slightly, but accomplished the same effect.
A better way to create a DataSet is to use the Data Sources wizard. With the proper project selected, click the top line Data menu and select "Add New Data Source". The Data Source Configuration Wizard will take over and step you through creating a DataSet with the proper connection. Just click Next on the first screen. The next screen allows you to choose the type of Data Source.
Figure 2 - Create DataSource Wizard, Screen 2
This time we will choose the Database type, but take note of the other options to use a Web Service or existing Business Object, because we will come back to them.
The second screen allows you to choose an existing connection or create a new one.
Figure 3 - Data Source Wizard, Screen 3
If you do not have a connection to the Northwind database, use the New Connection button to create one now. If you have not previously set up a Project Setting for this connection, the Wizard will do it for you now.
Figure 4 - Data Source Wizard, Screen 4
In the next screen of the Wizard, you will be shown a tree list of all the metadata in the database. Expand the Tables node and select the tables needed in this DataSet (Orders, Order Detail, and Products)
Figure 5 - Data Source Wizard, Screen 5
Before leaving this screen, be sure to correctly name your DataSet. (Some of the earlier Betas had problems renaming DataSets correctly.) In this case, we need to change the name of the dataset from NorthwindDataSet to OrdersDataSet.
After finishing the Wizard, you will have a dataset added to your project with 3 tables in it.
Figure 6 - Resulting DataSet Visual representation
With this design surface, you can continue to drag more tables from the Server Explorer (Database Explorer in Standard version) to add to the dataset. Looking ahead, we know we will need to add the Customers and Employees tables for combo box lookups.
You can edit the tables by right-clicking on the header and selecting Configure... from the drop down menu (or use top line Data menu, Configure).
Figure 7 - Configuration Wizard page 1
Click the Query Builder button to visually edit the SQL for the Table Adapter. In this case, the combo box will need a Full Name field that combines the last and first names.
You can also click the Advanced Options button to deselect the option to generate Update and Delete methods since this table is for lookup only and will not be edited. Click the Next button for more options on the Fill, GetData and Update methods and finish the Wizard. Repeat this configuration for the Customers table.
Presentation Layer
Data Sources RAD Tool
Once the dataset is created, you can open the Data Sources pane from the top line Data menu. This pane will already show the dataset created, but you may also add other sources from a database server, local database file (such as Access or FoxPro), a Web Service, or a predefined Business Object class. The wizard will proceed through each step required for adding data sources. You may also expand the table node to see all the fields available in the data table.
When you have a form open in the IDE, the Data Sources pane exposes some additional functionality. As each node is selected, a dropdown (combo) box allows the user to choose how the object should be rendered when dragged to the form. For tables, the choices are GridView or Details view. The GridView will create a DataGridView control that implements each field in a default form, but can be edited to render different embedded controls. The Detail view creates a label and textbox (or other component) for each field showing a single record at a time.
Other choices for field controls are: Textbox, NumericUpDown, Combobox, Label, Linked Label, and Listbox. You can also create custom user controls and add them to the list.
The Data Sources tool fosters a Rapid Application Development (RAD) environment where developers can quickly create a User Interface (UI) based on database schema. Rather than binding controls directly to dataset fields, VS2005 adds another layer call the Binding Source which correlates to the data object stored in the dataset. The controls on the UI are bound to the Binding Source fields. This is a new component that is a combination of the Binding Context and DataView objects.
The Controls have simplified binding by reducing the number of properties available for binding to a couple of the more popular (tag, text, selected value, etc.). You can still bind to the other myriad of properties through the Advanced settings if needed, but the common bindings are prominent and defaulted in most cases by the Data Sources wizard.
Building the Orders View/Edit screen
Now it is time to build a form to edit the Orders data. Right-click on the project in Solution Explorer / Add / New Item and select the Windows Form icon, giving it a name of OrdersForm.vb. VS will present a blank form to work with.
If your Data Sources window is not open, click the top line Data menu and select Show Data Sources. In your tree, you should see the dataset and data tables. To the right of each table is a dropdown that allows you to choose between implementing it as a DataGridView or free form Details controls. You can further expand the table to see the data fields and similarly select which control to use to implement the field in free form controls. (Note: In the Beta, you could configure how the field would be rendered in the DataGridView as well, but this was removed in the final version so that only Textboxes are rendered, but they can be changed by editing the columns later.) This Wizard can be modified through the Tools / Options / Windows Forms Designer / Data UI Customization to allow custom controls to be listed as well.
Most presentations usually prefer to have parent screens shown in Details format so in Data Sources, change the format for the Orders table to Details. Now comes the exciting part--drag the Orders table from the Data Sources window and drop it in the upper left corner of the blank form in the design surface of Visual Studio. The Data Sources Wizard will create each of the controls in the table, according to the default selected. You may also drag the fields one at a time from Data Sources pane, but it is much faster to just have them all created at once. You will notice that each control is properly named and bound to the correct field in the table. The wizard also creates a label for each control, using the description property if available and otherwise, the name of the field, inserting spaces where needed.
More than just the controls, the Wizard has added functionality to our form by adding a Data Navigation strip that allows the user to switch between records using VCR style buttons. The Wizard also added the components necessary to provide connectivity and binding with the dataset and database. If you open the code window, you will see that the Wizard has even added a line of code to call the Table Adapter component to fill the DataSet component. If you set the project to be the Startup project and the properties of the project to show this form at startup, you can build and run the solution now and see the form fully functional, showing all the records in the Orders table. There is a little work to do to get the Combo Box controls working correctly, but the form is basically functional.
Figure 8 - Orders Form Master
To get the Combo Boxes functional, we can open the DataSet and add the Customer and Employee tables (or create separate datasets for them). After building the project, you can refresh the Data Sources window to see the new tables or add new data sources if you put them in a different DataSet.
Back on the form, drag the Employee table from the Data Source and drop it on the combo box. The first thing you notice is another Binding Source component is added to for the Employee table. If you select the employee combo box control, you will notice that the proper settings for the Data Source, Display Member and Value Member have been set and the binding has been changed from Text to Selected Value. This is what Microsoft calls "Connect the dots"--you can drag and drop a table to any list driven control. The wizard takes the primary key for the Value Member and the first character field for the Display Member property.
You can drag the Customer table to the Customer combo box in the same way and a Binding Source component will be added for it also. If you open the code behind the form, you will also notice that VS has added code to the OrdersForm_Load class to fill the tables with data from the dataset for each binding source added to the form.
After a little rearrangement of controls, here is what the form should look like:
Figure 9 - Order Form Master final
Master Detail Presentation
The heart of a good relational application is the ability to display parent-child relationships. Visual Studio 2005 offers a fairly flexible tool to help rapid development of this functionality. In the Data Sources pane if you expand the tables, you will see below the fields, an entry for each child relationship of the table. In our example, expanding the Orders table node, you will see an entry for the relationship to the Order Details table. If you select the DataGridView representation and drag the relation node to the form, VS assumes you want a master-detail arrangement and will created the needed links for you.
VS will add a new BindingSource component to the form for the table and a table adapter to fill the table on form load. The Smart Data tag will expand (or you can click on the arrow in the upper right of the grid control) to show several properties and actions for the control. The "Choose Data Source" should be set to the new binding source based on the relation between the tables. This is all that is necessary to set the detail grid to link with the master record. If you look at the properties, you will see that the Data Source for the newly added Binding Source is the OrdersBindingSource and the Data Member is the relationship.
Figure 10 - Adding Details to the Order Form
If you compile and run the project now, you will see the linking between the master and detail parts. But the grid needs some editing to make it show productive data.
You will need to replace the ProductID with a combo box to display the name of the product instead. This will require another binding source to be added to the form. Drag a Binding Source component from the Data section of the Toolbox, name it ProductsBindingSource and set the Data Source property to OrdersDataSet and the Data Member property to the Products table.
Now expand the Smart Tag on the DataGridView control again (or look at the Properties pane) and click the "Edit Columns" action. This allows you to edit width and style of columns, reorder the columns, and add / delete columns. You can remove any columns that are not necessary and hide (Visible property equals False) the Primary Key and Foreign Key fields. You should also convert the ProductID column from a Text Box control to a Combo Box control to display value of Product Name. Set the Data Source property to ProductsBindingSource, the Display Member to ProductName and the Value Member to ProductID.
When the Order Detail Binding Source was added, VS also added the following lines of code to the OrdersForm_Load event to fill the Order Detail table:
Dim OrdersDetailTableAdapter As New Order_DetailsTableAdapter
OrdersDetailTableAdapter.Fill(Me.OrdersDataSet.Order_Details)
That is all we need to do. We can build and run our application by pressing F5 (or selecting "Start Debugging from the Debug Menu.) Here is an example of the final form running. Notice that the Navigation Bar allows us to move between records and the Order Detail information is linked to the Orders record selected. Also in the navigation bar is a yellow asterisk button to create a new record, a red X button to delete the current record, and a diskette button to save changes.
Figure 11 - Final Application Running
Whereas VS2003 provided one inflexible wizard for RAD construction of master-detail form, VS2005 has an infrastructure to allow RAD construction of a wide variety of form styles. The Data Sources tool is a welcome improvement to Visual Studio.
Separating the Tiers
Once again Visual Studio has fostered bad habits in promoting RAD. To get the functionality described above, the datasets need to be in the same project as the User Interface. But in the real world, it is better to separate your business layer from your UI so if you decide to implement a Web interface instead of Windows, much of the application will not need to change.
We can correct this easily enough. Create a new Class Library project in your solution for your business objects. It is best to start out with this project and create the original DataSet there instead of in the UI project.
Note: If you have all ready created the application as in the above tutorial, you can move the DataSet we just created to the new project, but you need to also move the app.config file to keep the connection strings.
Create a new Class Library project in your solution named NorthwindBiz and move the dataset there. VS will actually make a new copy of the dataset, leaving the original alone. After copying the dataset, you will need to remove the dataset in the Windows project. Your will also need to search and replace the hard coded Namespace references, changing "NorthwindWin" to "NorthwindBiz".
After the data set has been created, go back to the form and open the Data Sources pane. This time when you add a new data source, instead of using the Database tool, select the Object tool. On the next screen, you will see a list of referenced projects you could use. If the reference is not shown, you will need to click the "Add Reference" button and select the NorthwindBiz project. Expand the NorthwindBiz project and select the OrdersDataSet object. Click Next and Finish and you will see the OrdersDataSet listed in the Data Sources Pane.
If you actually create the business project before creating the windows project, you will notice a couple of differences. The Wizard no longer automatically instantiates the table adapter in the form load event. Since we are using an Object Data Source which may not even be a dataset, VS leaves the Data Access logic up to us. We must write that code ourselves now, but rather than write it in the Presentation layer, we need to write it in the Business layer.
Business Logic Code
If you wish to add business logic to your application, VS 2005 adds a Partial Class to the DataSet to facilitate this. Right-click on the OrdersDataSet and select View Code to generate another file under the DataSet with code stubs for the Partial Public Class where you can add code that will not be overwritten when the dataset is rebuilt. Partial Classes are a great tool in .NET 2.0 where a class may be separated into different physical files. When you build the project, the code will be merged and compiled together. This is valuable where we have generated code and developer code in the same class.
You can add partial classes for the DataTable, DataRow, and TableAdapter as follows:
Partial Public Class OrdersDataSet
Partial Public Class OrdersDataTable
End Class
Partial Public Class OrdersRow
End Class
End Class
Partial Public Class OrdersDataAdapter
End Class
If your business logic has to do with the whole table, add your code to the DataTable class. If your logic has to do with validating a row, add the code to the DataRow class. If you wish to add logic to the Data layer of your application, add the code in the Data Adapter class (note how it is outside the DataSet class.)
When you build the dataset in the Windows project, VS automatically adds code to fill the dataset and update back to the database. When you build the dataset in a separate business project, VS assumes that logic will be built into the business object. This is wise because you want to separate any data access logic away from the presentation layer. To build these functions, we need to add a couple functions to the partial class for the dataset as follows.
Imports ta = NorthwindDataSetTableAdapters
Partial Public Class NorthwindDataSet
Private taOrders As New ta.OrdersTableAdapter
Private taOrderDetail As New ta.Order_DetailsTableAdapter
Private taCustomer As New ta.CustomersTableAdapter
Private taEmployee As New ta.EmployeesTableAdapter
Private taProduct As New ta.ProductsTableAdapter
Public Sub FillDataSetAll()
Me.taOrders.Fill(Me.Orders)
Me.taOrderDetail.Fill(Me.Order_Details)
Me.taCustomer.Fill(Me.Customers)
Me.taEmployee.Fill(Me.Employees)
Me.taProduct.Fill(Me.Products)
End Sub
First, we need to instantiate an object for each of the table adapters. Then we add a method that will call the Fill method for each of the table adapters and fill dataset.
Other methods need to be added to update the tables. In this dataset, only the Orders and Order Details tables need to be updated since all the others are for lookup only.
Public Sub UpdateOrders()
Me.taOrders.Update(Me.Orders)
End Sub
Public Sub UpdateOrderDetails()
Me.taOrderDetails.Update(Me.Order_Details)
End Sub
End Class
These few lines of code will provide access to the Data Access layer through the Business Logic layer, providing the necessary separation from the presentation layer.
Now, back in the presentation layer, in the code behind the form, we need to call these methods of the business object. You can create the code stub for the load even by double-clicking in the title bar of the form and calling the method just created in the Business Layer of our application.
Partial Public Class OrderForm
Private Sub OrderForm_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Me.NorthwindDataSet1.FillDataSetAll()
End Sub
Another piece of code needs to be added to save updates back to the database. There is a an icon in the Navigation Bar for this for which code is written automatically when the dataset was in the Windows project, but when using Object Data Sources, the Wizard leaves that for us to write. You may need to change the enabled property for the button and then you can build a code stub for the save event by double-clicking on the Save Item button in the navigation bar. The following code needs to be added. Note that you must call the EndEdit method of the binding source to force the form to update the dataset.
Private Sub bindingNavigatorSaveItem_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles bindingNavigatorSaveItem.Click
Me.Validate()
Me.Order_DetailsBindingSource.EndEdit()
Me.OrdersBindingSource.EndEdit()
Me.NorthwindDataSet1.UpdateOrders()
Me.NorthwindDataSet1.UpdateOrderDetails()
End Sub
End Class
You can once again press F5 to run the application to see that the data is filled in and all the same functions exist. If any changes to the data are made, the user must click the save button to write the changes back to the database.
Summary
The new version of Visual Studio 2005 offers significant improvements in the flexibility of creating multi-tiered application using RAD tools. The Typed Dataset generation tool allows data abstraction between the database and the UI/Business Logic and also provides Object Oriented access to the entities and attributes of a database. The Dataset generator also provides the Data Access code virtually separated into a separate layer. The Partial Classes feature gives us a nice place to implement Business Logic layer with complete access to the Dataset component.
The Binding Sources component isolates the UI from the Business Logic layer and provides a smooth data binding tool, transforming our application into more of a 4-tiered application. Finally, the Data Sources window allows rapid generation of the Presentation layer and preserving the n-tiered environment.
While we do have to write a few lines of code to fill the dataset--in a few minutes, we have generated a good multi-tier application and the only code we have to write is to implement our unique business logic.
What's Next?
In Part 3 of the series, I will look more closely at what can be done in the Business Logic layer to enhance our application. I will also show some more code generation tools to help write some of the Data Access code and also show how to make the Data Access code provider independent so the same application can work with different database servers.
Links
About the Author
David Catherman - CMI Solutions
Email: DCatherman (at) CMiSolutions (dot) com
David Catherman has 20+ years designing and developing
database applications with specific concentration for the last 4-5 years on
Microsoft .NET and SQL Server. He is currently Application Architect and Senior
Developer at CMI Solutions using Visual Studio and SQL Server 2005. He has 3 MCP
certifications in .NET and is pursuing MCSD.
Using Visual Studio .NET Wizards to Create an N-Tiered Application
|
