|
Part 2 - Adding the Missing Functionality
Introduction
The Dataset Generator in Visual Studio 2005/8 generates a data entity structure that meets the
requirements of a good object relation mapper (ORM), except for a couple small areas. This
article shows the code that needs to be added to provide tier separation and how it can
easily be generated automatically.
Review
Part 1 of this article covered the basics of the Typed DataSet in Visual Studio 2005/8,
including a quick tutorial on how to use the visual editor /wizards and a review of the
functionality of the code it generates. The DataSet Visual Editor provides a great ORM
tool to build business entities with their data access methods and compose them together
with relationships into business objects.
The missing parts are the methods on the entity to wrap the table adapter methods to
store, retrieve and update to provide tier separation and a way to interact with the
table adapters to allow editing of the SQL at runtime.
N-Tiered Architecture Fundamentals
The Data Access Layer (DAL) in an N-tiered architecture provides the link between the Business Entity Layer
and the physical Data Storage Layer. The DAL bridges the tier separation between the business layer and the
data layer which in most cases is a physical separation between the application server and the database
server. Some architectures use stored procedures heavily, which puts much of the DAL on the database
server but the trend is to move the DAL logic into the application server to have database provider
independence. The DAL also provides a service interface with any external systems such as Workflow
or Business Rules engines.
On the other end, the Business Entities interact with the DAL to store, retrieve, and update data
needed by the business layer. Business Entities are the in-memory domain model components that
allow related data to be organized and readily available for processing. Business Entitles are
used as building blocks to compose business objects.
Business Objects
Business objects are a critical part of an N-tiered architecture, building the foundation of the
Business layer. Business objects provide all the components necessary to accomplish a set of
tightly related specific business functions. Business objects interact with the data access
layer to persist the data and also interact with the service layer to make the data and functions
available to the presentation layer or other applications. Business objects provide the business
logic which may include working with Business Workflow and Business Rules and abstracts the
complexity from the presentation layer.
A business object is made up of three parts: a domain model with data entity structures and
relationships; the CRUD methods necessary to persist to a data store; and the methods to
implement business logic. The dataset visual editor is a great tool to construct business
objects. The resulting typed dataset covers the first part very well, the second partially,
and provides a great environment to hand craft the business logic.
The main point of this discussion is to be careful not to skip layers in the architectural model.
Specifically, the presentation layer should not have access to the data access layer. The
presentation layer should instantiate the business object and call methods on it to do data
access. These methods are not generated automatically and must be added by the developer.
The code added through this article will fill in the missing piece to complete the second
part of the business object to make the typed dataset editor an excellent and complete ORM tool.
Wrapping the Table Adapter Methods
Options for Retrieving and Updating Data
The table adapters created by the dataset generator have two ways of retrieving data
from the database. The Fill method takes as a parameter an existing data table and
fills it with the selected data whereas the GetData method returns a data table object
already filled. Many Web application designers prefer the GetData method, but my opinion,
that bypasses the business objects and makes the application more client-server than
N-tiered in nature. I prefer to have the business object instantiate the entity and
then call the Fill method. This gives the business object a chance to perform business
logic and business rules on the data before it is passed on to the presentation layer.
Another difference in this architecture is table adapter providing a single Update
method that covers inserting, editing, and deleting records whereas other data access
tools would provide a separate method for each. The dataset can easily separate out
which records have been added or edited or deleted, and call the update method for
each group of records if this separation is needed.
Creating the Entity Methods
Methods need to be added to each entity in the dataset so they can fill themselves
and update changed records. In the dataset, the entity is the data table that was
generated, but we cannot open the designer code and make edits there since they will
be overwritten the next time the dataset is generated. How can we add the methods to
the data table?
Partial Classes
Fortunately, both the data table and the table adapter classes have been marked as
"Partial" classes. The Partial Class is a new feature in .NET 2.0 and
is a very valuable tool. By creating another partial class with additional methods,
the compiler will put them all together into the same class at compile time and the
custom methods will be available right next to the generated properties and methods.
In the design of the dataset, Microsoft has created a specific place for developers
to add their own code to the dataset functionality. When viewing the dataset designer,
right click on the background and select "View Code". Visual Studio will add
another sub file to the dataset. In Solution Explorer, click the "Show All Files"
button in the toolbar at the top of the pane, and the dataset can be expanded to show
the sub files. In addition to the master "XSD" file, there is a
".Designer.vb" (or they could be ".cs" for C# projects)
that contains all the generated code, ".XSC" and ".XSS"
files used for storing the diagram layout information. When you click
"View Code", another code file is added. In this file is the shell of a
partial class for the dataset. (Note: this code assumes the dataset is created in
a class library project named "Biz".)
Partial Class CustomersDataSet
End Class
Methods and properties can be added to this partial class and they will be compiled
together with the generated code to expand the functionality. The entity data table
is a subclass of the dataset so to add methods to it, insert a sub partial class
inside the dataset.
Partial Class CustomersDataSet
Partial Class CustomersDataTable
Public Sub Fill()
Dim taCustomers As New CustomersDataSetTableAdapters.CustomersTableAdapter
taCustomers.Fill(Me)
End Sub
End Class
End Class
This code instantiates the table adapter and then calls the fill method of it to load
the data into the current instance.
In the simplest form, this is all that is needed. For a Web app where the business
object is instantiated and released in a short time, this will suffice, but it probably
should be converted to a function that returns the data table to the caller. But in a
Windows app, the dataset remains instantiated for a long time and therefore the table
adapter should be made Shared (Static in C#).
Exceptions thrown by the table adapter contain limited information, so this is a good
opportunity to intercept the exception and add content to the message. With these changes,
here is the resulting code that is needed for each data table.
Partial Class CustomersDataSet
Shared taCustomers As New CustomersDataSetTableAdapters.CustomersTableAdapter
Partial Public Class CustomersDataTable
Public Sub Fill()
Try
taCustomers.Fill(Me)
Catch ex As Exception
Throw New ApplicationException("Customers: Fill - " & ex.Message)
End Try
End Sub
Public Sub Update()
Try
taCustomers.Update(Me)
Catch ex As Exception
Throw New ApplicationException("Customers: Update - " & ex.Message)
End Try
End Sub
End Class
End Class
Polymorphism
If every data table needs to implements these same methods, building an interface would help make sure
they were implemented consistently. Here is an example of an interface called ITableUpdate in a class
called _Interface in the same project.
Public Class _Interface
''' <summary>
''' Interface for Data table in a dataset to standardize fill and update methods
''' </summary>
Public Interface ITableUpdate
Sub Fill()
Sub Update()
End Interface
End Class
Then the data table partial class would implement the interface as follows:
Partial Class CustomersDataSet
Shared taCustomers As New CustomersDataSetTableAdapters.CustomersTableAdapter
Shared taOrders As New CustomersDataSetTableAdapters.OrdersTableAdapter
Partial Public Class CustomersDataTable
Implements _Interface.ITableUpdate
Public Sub Fill() Implements _Interface.ITableUpdate.Fill
Try
taCustomers.Fill(Me)
Catch ex As Exception
Throw New ApplicationException("Customers: Fill - " & ex.Message)
End Try
End Sub
Public Sub Update() Implements _Interface.ITableUpdate.Update
Try
taCustomers.Update(Me)
Catch ex As Exception
Throw New ApplicationException("Customers: Update - " & ex.Message)
End Try
End Sub
End Class
End Class
Implementing an interface will also allow the methods to be called in a generic function that could
apply to any data table even though each data table has a different name. This is an example of the
object oriented principle of polymorphism where the data table can be cast as the interface and then
the method called generically. Here is an example of a subroutine that will call the update method
on any data table that implements the interface.
Public Shared Function GenericUpdate(ByVal dt As DataTable) As Boolean
'Does the data table implement the interface?
If TryCast(dt, _Interface.ITableUpdate) Is Nothing Then
Return False 'update failed
Else
CType(dt, _Interface.ITableUpdate).Update()
Return True 'Update successful
End If
End Function
There are many ways the data access methods can be expanded. I found a situation once where I needed
to call the Fill and Update methods but ignore any errors that are returned. (When merging data from
two tables, the adapter would throw an error if the record already existed, so I just ignored the
error and ended up with all the records I needed.) So now I add another overload of the methods to
ignore errors as follows:
Public Function Fill(ByVal IgnoreErrors As Boolean) As String
Try
Return "Customers=" & taCustomers.Fill(Me)
Catch ex As Exception
If IgnoreErrors Then
Return "Customers: Fill - " & ex.Message
Else
Throw New ApplicationException("Customers: Fill - " & ex.Message)
End If
End Try
End Function
There are many other possibilities that may be added, but they should follow the same pattern.
For example, any additional queries need to be wrapped as well.
Audit Trail
Most database designs keep an audit trail of when records have been created or changed and
who they were edited by. I have two fields that I add to every table: ModifiedDate (date
time data type) and ModifiedBy (string data type for the username of the person). Rather
than remembering to update the fields from the presentation layer, they can be consistently
set in the data access layer.
Filling in the time is easy enough, but if your application is distributed across time zones,
you might consider using the server date/time or GMT.
Getting the current username is not quite as easy. In an active directory environment, the
Thread.CurrentPrincipal.Identity.Name property will retrieve the current user. But the best
practice is to let the presentation layer identify the user. So, add a property to every
dataset to identify the current user and have the presentation layer set the property when
the dataset is instantiated. In the outer data set partial class, add the following code:
Private _CurrentUser As String
'String value for the current username to put in the ModifiedBy field
Public Property CurrentUser() As String
Get
Return _CurrentUser
End Get
Set(ByVal value As String)
_CurrentUser = value
End Set
End Property
In the presentation layer, capture the username when the application starts and store it as
a session variable. As each form loads, set the property on the dataset.
Now add a method to each dataset to set the audit values before updating the records.
Public Shared Sub UpdateModifiedFields(ByRef dt As DataTable)
Dim oRow As DataRow 'row in original table
If dt.GetChanges(DataRowState.Added + DataRowState.Modified) IsNot Nothing Then
For Each row As DataRow In dt.GetChanges(DataRowState.Added _
+ DataRowState.Modified).Rows
oRow = dt.Rows.Find(row(0)) 'make changes in the original table
If oRow IsNot Nothing Then
oRow.BeginEdit()
oRow("ModifiedBy") = Me.CurrentUser
oRow("ModifiedDate") = Now
oRow.EndEdit()
Else
Throw New ApplicationException("row not found for " _
& dt.TableName & "Key=" & row(0))
End If
Next
End If
End Sub
This code uses the GetChanges method of the data table to return a set of rows that have been added
or modified. Changing fields on records marked for deletion is not allowed, so this combination will
exclude them. The method returns a copy of the rows, so use the Find method of the data row to get
the original row of the data table. (It is also important to pass the data table parameter by reference
rather than by value.) Then reference the property on the current dataset we are in as
"Me.CurrentUser" (use "this." in C#).
Actually, this code is fairly generic and can be called from a general utility module, but polymorphism
will need to be used again since each dataset has a different name and type. First create another
interface called ITypedDataSet and give it a property named CurrentUser. Then, instead of referring
to the dataset as "Me", cast the parent dataset of the data table as the interface and
reference the property as follows:
oRow("ModifiedBy") = CType(dt.DataSet, _Interface.ITypedDataSet).CurrentUser
Modifying the SQL Queries
Another area where dataset architecture can be improved is in allowing the SQL to be modified at runtime.
Again, it is safest to add queries to the table adapter at design time, but there are some situations
where it may be beneficial to create ad hoc SQL at runtime.
There is a lot of danger in modifying the compiled select SQL, so Microsoft has made it almost
impossible by not exposing any of the properties of the table adapter, but marking them as Private
or Protected. The only property exposed (marked as Friend in VB) is the connection object. But
being able to access the Adapter property would open up significant functionality.
What got me started on this is the idea of creating a generic method to fill tables by relation.
If the Customers table gets filled with 5 customers, then fill the Orders table with just the
orders for those 5 customers. Many off-the-shelf DAL tools offer this functionality, so I thought
I would give it a try in the datasets.
The way to get around Microsoft's security (be sure to consider the risks involved) is to add
a public method to the table adapter that will return the Adapter property. The Table adapters
use partial classes as well, so it is possible, the structure of the class is a little different.
If you look at the designer code for the dataset, you will see that the table adapters are added
as a separate class in a separate namespace but in the same physical file. In our partial class
file, after the end of the dataset class, add another class in its own namespace as follows:
Partial Class CustomersDataSet
...
End Class
Namespace CustomersDataSetTableAdapters
Partial Public Class CustomersTableAdapter
End Class
End NameSpace
Notice that the namespace is uniquely named according to the dataset. Inside the namespace,
add a class for each table adapter, again uniquely named according to the data table. With
these unique names, another interface needs to be added to allow the table adapters to be
use generically. It contains only the GetAdapter method that we will be implementing.
Add this code to the _Interface class:
Public Interface ITableAdapter
Function GetAdapter() As Global.System.Data.SqlClient.SqlDataAdapter
End Interface
It would be nice to add to this interface all of the common elements of the table adapter such as the CommandCollection, the Fill and Update methods and others, but this would require editing the designer code to add the "implements" clause. It would be possible to add overloaded versions of the methods with a different signature and implement the interface. For example, the overloads with the "IgnoreErrors" parameter could be implemented through the interface and then the implementation added to the table adapter class.
Public Interface ITableAdapter
Function GetAdapter() As Global.System.Data.SqlClient.SqlDataAdapter
Public Function Fill(ByVal dt As DataTable, ByVal IgnoreErrors As Boolean) As String
Public Function Update(ByVal dt As DataTable, ByVal IgnoreErrors As Boolean) As String
End Interface
I choose not to pursue this because the methods would have to be wrapped in the datatable anyway,
so it did not save a lot of code.
To implement the GetAdapter method, add the method to the table adapter class.
Partial Public Class CustomersTableAdapter
Implements _Interface.ITableAdapter
Public Function GetAdapter() As Global.System.Data.SqlClient.SqlDataAdapter _
Implements _Interface.ITableAdapter.GetAdapter
If (Me._adapter Is Nothing) Then
Me.InitAdapter()
End If
Me.Adapter.SelectCommand = Me.CommandCollection(0)
Return Me._adapter
End Function
End Class
Make sure the SelectCommand property of the adapter is seeded with the base SQL so it can be manipulated later.
The last step is to create a generic method in the dataset to wrap the table adapter method to allow
access to the adapter by table name. Since each table adapter is uniquely named, a case statement is
used to select the right adapter. This method is added to the dataset class.
Public Function GetAdapter(ByVal TableName As String) As SqlClient.SqlDataAdapter _
Implements _Interface.ITypedDataSet.GetAdapter
Select Case TableName
Case "Customers"
Return taCustomers.GetAdapter()
Case "Orders"
Return taOrders.GetAdapter()
Case Else
Return Nothing
End Select
End Function
One danger here is that table adapters deal with provider specific code. Defining the return
type as SqlClient.SqlDataAdapter means it will need to be modified if the type of database ever
changes.
Notice the use of another interface. The ITypedDataSet interface specifies the CurrentUser
property and the GetAdapter method as follows (added to the _Interfaces class):
Public Interface ITypedDataSet
Property CurrentUser() As String
Function GetAdapter(ByVal TableName As String) As SqlClient.SqlDataAdapter
End Interface
Conclusion
In case you got lost as the classes evolved, the entire code for the two table dataset
is listed below. The code allows the data access methods to be called from the business
object or data entity level, preserving the tier separation. Providing access to the data
adapter inside the table adapter allows the developer to manipulate the SQL to fill the
tables at runtime, with special security precautions.
This added functionality does require writing a significant amount of code, but the repetitive
nature of the code is conducive to code generation. In the next part of the article (Part 3),
I will show how this code can be easily generated.
About The Author
|
|
David Catherman
Email: David (dot) Catherman (at) Hotmail (dot) com
David Catherman has 20+ years designing and developing database applications with
specific concentration for the last 5-6 years on Microsoft .NET and SQL Server.
He is currently working as an Application Architect and Senior Developer using
Visual Studio 2005 and SQL Server 2005. He has several MCPs in .NET and is
pursuing MCSD.
|
Appendix
The following final code is added to the custom code section of the CustomersDataSet.
It uses multiple partial classes to separate the different sections.
Partial Class CustomersDataSet
Shared taCustomers As New CustomersDataSetTableAdapters.CustomersTableAdapter
Shared taOrders As New CustomersDataSetTableAdapters.OrdersTableAdapter
#Region "Table Sub Classes"
Partial Public Class CustomersDataTable
Implements _Interface.ITableUpdate
Public Sub Fill() Implements _Interface.ITableUpdate.Fill
Try
taCustomers.Fill(Me)
Catch ex As Exception
Throw New ApplicationException("Customers: Fill - " & ex.Message)
End Try
End Sub
Public Function Fill(ByVal IgnoreErrors As Boolean) As String
Try
Return "Customers=" & taCustomers.Fill(Me)
Catch ex As Exception
If IgnoreErrors Then
Return "Customers: Fill - " & ex.Message
Else
Throw New ApplicationException("Customers: Fill - " & ex.Message)
End If
End Try
End Function
Public Sub Update() Implements _Interface.ITableUpdate.Update
Biz.Utility.UpdateModifiedFields(Me)
Try
taCustomers.Update(Me)
Catch ex As Exception
Throw New ApplicationException("Customers: Update - " & ex.Message)
End Try
End Sub
End Class
Partial Public Class OrdersDataTable
Implements _Interface.ITableUpdate
Public Sub Fill() Implements _Interface.ITableUpdate.Fill
Try
taOrders.Fill(Me)
Catch ex As Exception
Throw New ApplicationException("Orders: Fill - " & ex.Message)
End Try
End Sub
Public Function Fill(ByVal IgnoreErrors As Boolean) As String
Try
Return "Orders=" & taOrders.Fill(Me)
Catch ex As Exception
If IgnoreErrors Then
Return "Orders: Fill - " & ex.Message
Else
Throw New ApplicationException("Orders: Fill - " & ex.Message)
End If
End Try
End Function
Public Sub Update() Implements _Interface.ITableUpdate.Update
Biz.Utility.UpdateModifiedFields(Me)
Try
taOrders.Update(Me)
Catch ex As Exception
Throw New ApplicationException("Orders: Update - " & ex.Message)
End Try
End Sub
End Class
#End Region
Public Sub FillAll()
Try
Customers.Fill()
Orders.Fill()
Catch ex As Exception
Throw New ApplicationException("CustomersDataSet: FillAll - " & ex.Message)
End Try
End Sub
End Class
'Implement Table DataSet Extentions
Partial Class CustomersDataSet
Implements _Interface.ITypedDataSet
Private _CurrentUser As String
'String value for the current username to put in the ModifiedBy field
Public Property CurrentUser() As String _
Implements _Interface.ITypedDataSet.CurrentUser
Get
Return _CurrentUser
End Get
Set(ByVal value As String)
_CurrentUser = value
End Set
End Property
Public Function GetAdapter(ByVal TableName As String) As SqlClient.SqlDataAdapter _
Implements _Interface.ITypedDataSet.GetAdapter
Select Case TableName
Case "Customers"
Return taCustomers.GetAdapter()
Case "Orders"
Return taOrders.GetAdapter()
Case Else
Return Nothing
End Select
End Function
End Class
'Table Adapter extentions to expose the Adapter as public
Namespace CustomersDataSetTableAdapters
Partial Public Class CustomersTableAdapter
Public Function GetAdapter() As Global.System.Data.SqlClient.SqlDataAdapter
If (Me._adapter Is Nothing) Then
Me.InitAdapter()
End If
Me.Adapter.SelectCommand = Me.CommandCollection(0)
Return Me._adapter
End Function
End Class
Partial Public Class OrdersTableAdapter
Public Function GetAdapter() As Global.System.Data.SqlClient.SqlDataAdapter
If (Me._adapter Is Nothing) Then
Me.InitAdapter()
End If
Me.Adapter.SelectCommand = Me.CommandCollection(0)
Return Me._adapter
End Function
End Class
End Namespace
|
