|
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 architectures 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 1 - Visual Studio .NET 2003
In Visual Studio .NET 2003, there are some wizards and code generation tools to build a database application quickly but this tutorial will demonstrate how to keep the code separated into different modules or classes to provide the tiers of an application. For the examples, I will be using the Northwind database and building a form to allow viewing and editing of the Orders and Order Details tables.
Getting Started
Start off by using the Solution Explorer to build a new project. In this example, we will be building a Windows form, but a Web form works in much the same way. If you wish to create a true 3-tiered application, you should create a separate class object to contain the data access and business logic layers, but you can easily move the modules later.
Data Object Layer
Applying Object Oriented Programming (OOP) principles, the first step is to build a data object that contains the needed information (usually from the database) you will be working with. In .NET, the best (or easiest) tool is to use is the typed dataset which provides a class wrapper to a general dataset to allow object oriented access to data and strong typing of properties (fields). While there are other structures like collections and array lists that can represent database table objects, most of the wizards in VS use the typed dataset.
To create an new dataset, right click on your project and select Add - Add New Item. From the wizard, choose a Dataset object and give the new data object a proper name such as OrdersDO. The wizard will create the XSD and the code behind for the typed dataset and bring up a design surface for you to work with. You can then drag tables from the Server Explorer add to the schema of the dataset visually and even add relations and constraints through other wizards. If you would like to exclude some of the fields in a table, delete them from the schema. For our example, drag the Orders and the Order Details tables from the server explorer onto the design surface. In the representation of the Orders table, I deleted all but the first 4 fields since I don't need all the shipping detail. From the XML Schema section of the toolbox, select a data relation and drag and drop it on the Orders object. In the Relationship wizard, select Order Details as the child table. When you are finished, it should look like Figure 1.
Figure 1 - Typed Dataset as a data object
What Code Is Generated?
The typed dataset generates hundred of lines of code to abstract the data tables and rows, and provide strong typing of data fields as properties. This allows for an object oriented style of reference to the data objects with design-time verification of syntax and intellisense. To see the code that was generated, select the project and click the icon at the top of the Solution explorer to "Show all files". Then expand the "+" next to the dataset to see two files. The file with the .vb (or .cs) extension contains the code. Do not edit this code since it will be re-generated each time the dataset is saved.
Here is a summary of what is generated. Assume for this example that a typed dataset was generated with the name "MyDS" which contains a table of the name "MyTab".
The typed DataSet accomplishes strong typing by generating a class called MyDS, which derives from (inherits) DataSet. The name of the subclass of the DataSet class is equal to DataSet.DataSetName in the original DataSet that produced the XML schema. Four public nested classes are exposed:
- MyDS.MyTabDataTable which inherits DataTable and implements IEnumerable
- MyDS.MyTabRow which inherits DataRow
- MyDS.MyTabRowChangeEvent which inherits EventArgs
- MyDS.MyTabRowChangeEventHandler
where
- MyDS is the DataSet.DataSetName
- MyTab is the DataTable.TableName
- MyTabRow is DataTable.TableName + Row
MyDS.MyTabDataTable has a series of private DataColumn members; one data member per column is the table or resultset (from query or stored procedure). There are getters for these, but they are marked internal because you are not allowed to add or delete DataColumns at runtime. There are also four typed delegates for Changing, Changed, Deleting, and Deleted rows.
The typed DataTable has the following methods:
- An Indexer for Rows and a GetEnumerator method.
- Two add methods, both called AddMyTabRow but each used a little differently. AddMyTabRow(row), which takes a Row, is used with NewMyTabRow, an empty typed row. AddMyTabRow(n1,n2,n3)takes N parms, where N is the number of columns in the table and MyTab is the name of the table.
- RemoveMyTabRow, a delete method.
The DataColumns are created and added to the DataTable in the DataTable's InitClass method. If metadata is available, it is also filled in at that time. If there is a primary key or unique column, there is a method called FindBykeycolname that uses the primary key as input.
The MyTabRow class exposes columns as public properties. If the column is nullable, there are two predefined helper functions--IsColumnnameNull and SetColumnnameNull--where Columnname is a placeholder for the name of the column.
A strongly typed DataSet can also contain more than one table. If you have tables with parent-child relationships--specified by the existence of a DataRelation in the DataSet's Relations collection--some additional information and methods are generated. When the DataSet contains a Relation, the following things happen:
- The PrimaryKey property is added to DataColumn properties for the parent table, a ForeignKeyConstraint is added for the child table, and DataRelation is added. If the DataSet's Nested property was set in the original schema, it is preserved in the typed DataSet.
- ChildTabRow has a property of type ParentTabRow. A property's get method calls GetParentRow, and the setter calls SetParent.
- ParentRow has a method, GetChildTabRow, which returns an array of typed child rows by calling GetChildRows.
where
- ParentTab is the DataTable.TableName of the parent table.
- ChildTab is the DataTable.TableName of the child table.
Finally, the strongly typed DataSet has certain methods and a property that are related to XML persistence. These override the DataSet's methods.
- A protected constructor takes a SerializationInfo info and a StreamingContext context. This constructor calls InitClass before calling GetSerializationData. This is a requirement when you implement ISerializable.
- ReadXmlSerializable simply calls the base class's ReadXml method.
- GetSchemaSerializable calls WriteXmlSchema to write the schema to an XmlTextWriter. Then it reads it back into a System.Xml.Schema.XmlSchema. This is similar to the code in the base class (DataSet).
- The properties ShouldSerializeTables and ShouldSerialize-Relations, and an additional property called ShouldSerialize[MyTable], return false.
For our example where we have two related tables, about 720 lines of code were generated for us to build a Data Object Layer.
Data Access Layer
Another place where there is sometimes a need for an isolation layer is between the Data Access and the Business Logic to provide a single place where changes need to be made if the backend data storage engine ever changes. The Data Access Layer allows interaction of the data object with the data store and provides the methods necessary to fill and update the dataset from the database. This layer also adds a place to interface with stored procedures needed for business logic. To build this layer, add a new item to your project and select the "Component Class" object from the list and name it something like OrdersDA. This gives you a design surface where you can visually add more objects.
From the data section of the toolbox, drag a Data Adapter object to the surface (use SqlDataAdapter for SQL Server or OleDataAdapter for other databases). Normally, the wizard starts up automatically but if it does not, right click on the data adapter and choose to "Configure Data Adapter".
The first screen asks you to identify a connection where you may choose and existing connection to the database or create a new one. The second screen allows the choice of generating SQL, stored procedures or using existing stored procedures. If you are generating new SQL statements or stored procedures, the next screen allows you to build the query desired and if you are generating stored procedures, allows you to enter names for them according to your naming conventions. (If you are using existing stored procedures, the wizard allows you to identify the stored procedure names.)
If you are using SQL statements, the third screen allows you to enter the Select statement that will return the desired data from the database or you can use the Query Builder button to visually select the table and fields and even test it. The Advanced button allows the option of generating update, insert, and deleted commands from the given select statement. When you click next, he final screen summarizes what was just generated.
Back to the design surface, select the data adapter that was just created and rename the object in the name field of the properties list to conform to your naming scheme, for example, "daOrders". Next, we need to clean up the data connection. Select the connection object that was created and in the properties, change the name to "con". Rather than leaving the connection string hard-coded, we will move it to the app configuration file. Under the Configurations section, expand the DynamicProperties entry and select the ConnectionString property under it (not the one under the Data section). Click on the builder button (ellipse) to the right of the property value, check the box for "map to a key in configuration file", and fill in the desired name (or except the default). This will create the proper entry in the App.Config file and generate the code needed to retrieve it at runtime.
Figure 2 - Data Access Layer
Next we need to add another data adapter for the Order Details table using the same procedure as above (making sure that it uses the same connection) and rename it "daOrderDetails".
Right-click on each data adapter and choose "Generate dataset". Choose the existing dataset that we
created in step one called "ProjectName.OrdersDO" and make sure the proper table is checked. When you click OK, the wizard will create all the code necessary to link the data adapters to the dataset.
The last part of this step is to right click on the design surface and select "View Code" to switch to the code window. Change the class declaration to inherit from your typed dataset rather than Component. This will give your business object access to all the dataset objects.
Public Class OrdersDA
Inherits OrdersDO 'System.ComponentModel.Component
All of the code that was generated is behind the collapsed region titled:
+Component Designer generated code
After the generated code, we need to manually add two more methods to fill and update the dataset. Add the following code:
Public Function FillDataSet() As DataSet
con.Open()
Me.daOrders.Fill(Me.Orders)
Me.daOrderDetails.Fill(Me.Order_Details)
con.Close()
Return Me
End Function
Public Function UpdateDataSet(Optional ByVal TableName As String = "All") As DataSet
con.Open()
If TableName = "Orders" Or TableName = "All" Then _
Me.daOrders.Update(Me.Tables("Orders"))
If TableName = "Order Details" Or TableName = "All" Then _
Me.daOrders.Update(Me.Tables("Order Details"))
con.Close()
End Function
'Add any other business object functionality here
End Class
Finally, you need to save all and build your project. You will get an error about not having a Sub Main, but we will solve that in the next step.
What Code Was Generated?
When you expanded the "+Component Designer generated code" section, you can see the code that was generated. This includes the following:
- Connection object with connection string isolated in the configuration file
- Data Adapters for each of the tables with associated table mappings and command objects.
- Four Command objects for each data adapter to implement the CRUD functionality
- Parameter objects for each of the commands that require them.
- Instantiation code for the dataset (if included)
For these two tables, this amount to about 150 lines of code.
Business Layer
The next step is to create Business Object class where all the validation and other business logic will be contained. In the Biz project, create a new Class item called OrdersBO. In the code window make the new class inherited from the OrdersDA class. Later we can come back to this class and add logic as needed.
Note: if you do not have a lot of business logic, you could put it in the Data Access class, but that does not provide the isolation required by OOP.
Presentation Layer
Visual Studio offers a Data Form Wizard to build the User Interface portion of your application with a master detail relationship view.
In Solution Explorer, right click on the project (use the same project for now so the wizard can find the business object--you can move it later) and click Add new item. From the list, choose the Data Form wizard and give the form a name like "OrdersWin".
Figure 3 - Data Form Wizard
On the next screen, the wizard will search for existing datasets in the project to use or give you the option of creating one on the fly. (The wizard does a great job of creating the dataset for you, but mixes the data layer with the presentation layer, violating good programming practice for n-tiered applications--which is why we went to the trouble to create the data and business objects separately).
Figure 4 - Data Form Wizard, Screen 2
After choosing the OrdersBO (business object, not data object), the wizard will then allow you to choose the methods to fill and update the dataset from the functions we created. You will see on the list, the fill and update methods we have created in our business object.
Figure 5 - Data Form Wizard, Screen 3
If you have multiple tables in the DataSet, the wizard has another screen that allows the user to choose the master and detail tables and also allows the user to pick which fields will be added to the form.
Figure 6 - Data Form Wizard, Screen 4
Finally, the wizard offers the choice of showing one parent in a form view or multiple parents in a grid view. The child records are shown in a linked grid at the bottom.
Figure 7 shows the resulting form in the designer. You can run this project and have a working form that allows manipulation of the Orders and Order Detail information.
Figure 7 - Form view of Data Form Wizard
Changing the last option in the wizard to show only one parent results in a form that shows a one-to-many relationship and adds controls to navigate through Orders records and edit them as necessary as shown in Figure 7.
If you build and run the application (set the project properties to start the OrdersWin form) you will see a good start on a master detail form. Looking at how the linking was accomplished you will see that the Data Source for the detail grid was set to objOrdersBO (the instantiation of our business object) and the Data Member was set to Orders.OrdersOrder_x0020_Details which is the name of the relation on the dataset.
In summary, this application was easily created with only actually writing very few lines of code. This allows novice developers to get a quick start on building applications and yet maintain separated layers of a 3-tier architecture.
What Code Is Generated?
The wizard generated the following code as if each control were dragged onto the design surface and given the proper properties.
- For each of the fields in the table, the wizard generates a label and textbox control, properly named with binding to the field in the data row.
- Buttons are created to load data, update, add new and delete records
- Buttons are created to handle the navigation between records
- A grid is created and linked to show the child records in the relationship.
- Table styles are created for each grid column
- A method is create to handle each of the button click events
- A method is created to load the data (calls the base method)
- A method is created update the dataset using a merge method that works with data binding more efficiently.
Most of this code would have been generated by the visual tools, but this wizard has saved 20-30 minutes of time for us and added several patterns for good coding. Total lines of code generated are about 190.
On the down side, this wizard is one of the most finicky I have seen. You have to have just the right properties in the business object class in order for the wizard to find it (I am still not sure of the right combination) and the fill and update methods must have the right signature. Even then, the grid does not always generate correctly.
Separating the Tiers
Best practice would be to create the separate tiers before starting the application. If you have developed all items in the Windows project, you can now create a new Class Library project called NorthwindBiz and drag and drop the OrdersDO and OrdersBO objects to the new project. You will then have to search and replace in the OrdersWin.vb file, each instance of "NorthwindWin." to "NorthwindBiz.". You will also need to add a reference to the NorthwindWin project pointing to the NorthwindBiz project. With these minor changes, the form should run as before. But now you could also add a project to add a Web interface using the same business logic. (Unfortunately, the Data Form wizard does not exist for Web apps in this version, so you will have a little more work.)
Conclusion
There are many good frameworks and code generators on the market that do a better job of generating applications, but most require an investment of time and money to master well. I like the productivity tools that come with Visual Studio, but don't like the poor programming practices they promote. This procedure helps correct that problem.
Further code can be added to the OrdersBO class to do validation and implement other business logic.
Bottom line, in about 20 minutes you have a well architected working module for an application with over 1000 lines of code, of which only 10 were written manually.
More Info
[1] http://www.awprofessional.com/articles/printerfriendly.asp?p=29901
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 is an
MCP in .NET and is pursuing MCSD.
Using Visual Studio .NET Wizards to Create an N-Tiered Application
|
