Introduction
In this article, I will show you how to build a datalayer that can handle all data transfer needs, including multiple connections, all in 21 kilobytes.
Overview
The code in the download file contains a solution with two projects. The classes I will cover in this article are:
Datalayer - The project that compiles to the 21 KB DLL
TableRelationItem - A class for storing relationship information between two tables TableRelationList - A class for storing a collection of relationshipsADatalayer - The class that handles database connectivity and data transfers
DatalayerUI - A project that shows how to use the Datalayer.dll
- config.xml - A file that contains the connection strings
DatalayerRT - A class that inherits ADatalayerForm1 - A class to illustrate instantiating the datalayer
The classes in the Datalayer project are taken from three projects in the BaseClasses solution presented in my book "Hands-On Design Patterns for Visual Basic, 2nd Edition."
The Code
Datalayer.TableRelationItem
The purpose of this class is to store the relationship information for two tables. This class is used by a method in ADatalayer that returns a DataSet. In the name of brevity, the code below shows only the class-level variable declarations and a validation method. The full code available in the download contains a Property method for each variable.
Public Class TableRelationItem
Protected mRelationName As String = ""
Protected mPrimaryTable As String = ""
Protected mPrimaryField As String = ""
Protected mForeignTable As String = ""
Protected mForeignField As String = ""
Public Function IsValid() As Boolean
Try
If mRelationName = "" Then Return False
If mPrimaryTable = "" Then Return False
If mPrimaryField = "" Then Return False
If mForeignTable = "" Then Return False
If mForeignField = "" Then Return False
Return True
Catch ex As Exception
Throw
Finally
End Try
End Function
End Class
Datalayer.TableRelationList
The purpose of this class is to provide a collection of TableRelationItem, and is used by a method in ADatalayer that returns a DataSet. I tend to use BindingList in the event I ever need to bind it to a visible control. A List(Of ) would work just as well in this case.
Notice that the Add() method checks whether or not the relation is valid.
Imports System.ComponentModel
Public Class TableRelationList
Inherits BindingList(Of TableRelationItem)
Public Sub New()
MyBase.AllowNew = True
End Sub
Public Shadows Function Add(ByVal pTableRelation As TableRelationItem) As Boolean
Try
If pTableRelation.IsValid Then
MyBase.Items.Add(pTableRelation)
Else
Throw New Exception("Invalid table relation")
End If
Return True
Catch ex As Exception
Throw
Finally
End Try
End Function
End Class
Datalayer.ADatalayer
This is the most complex of the classes. I will discuss only selected methods so that you have an idea of the scope and abilities. This class is declared abstract because it encapsulates all work necessary for data transfer, but does not contain the actual connection strings. The application using this class will have a class that inherits from ADatalayer and provides the connection strings needed for that application.
I will be using the Singleton design pattern for this class and its child. Only one datalayer per person is ever needed. The following conditions are required to implement the Singleton design pattern:
- A class-level variable in the
abstract class for returning an instance of itself - The
New() method in the abstract class is declared Protected to prevent an outside class from instantiating it - A shared method in the runtime child class for returning an instance of itself. I use a method called
GetInstance()
The class signature and class-level variables are shown below. It inherits from IDatalayer, which is not presented in the article, but exists in the download. The class-level mDatalayer variable is used to meet condition one above.
Public MustInherit Class ADatalayer
Implements IDatalayer
Protected Shared mDataLayer As ADatalayer = Nothing
Protected Shared mConnections As PropertyCollection = Nothing
End Class
The New() method is declared Protected so that it can be accessed by a child class but not by an external class. This meets condition two above.
This method accepts a list of the connection strings, opens all connections and adds them to a PropertyCollection. I used a DataTable as a convenience because an XML file can easily be imported into a DataSet, and the connections table passed in from that.
I've seen a lot of code that "opens late and closes early" so that the time a connection remains open is minimized so that it remains available for others to use. I've discovered, however, that closing a connection does not always immediately release it for the next user. "In use" connections start to pile up. Also, opening a connection is the most expensive part of the process. Why close it? Open one connection for each user for the life of the application, and close it in the Finalize() method.
Protected Sub New(ByVal pConnectionStrings As DataTable)
Dim row As DataRow
Dim con As SqlClient.SqlConnection
Try
mConnections = New PropertyCollection
For Each row In pConnectionStrings.Rows
con = New SqlClient.SqlConnection(row("Value"))
con.Open()
mConnections.Add(row("Name"), con)
Next
Catch ex As Exception
Throw
Finally
End Try
End Sub
The GetConnection() method takes the name of the connection desired and returns that one from the PropertyCollection.
Protected Function GetConnection(ByVal ConnectionName As String) _
As SqlClient.SqlConnection Implements IDatalayer.GetConnection
Try
Return mConnections(ConnectionName)
Catch ex As Exception
Throw
Finally
End Try
End Function
The GetCommand() method prepares and returns an SqlCommand object using the parameters supplied:
pConnectionName - The name of the connection to be usedpComandText - This can be an SQL statement or the name of a stored procedurepCommandType - Specifies the type of commandpParameters - The collection of name/value pairs to be used by a parameterized SQL statement or a stored procedurepOutputValues - An optional list of parameter names that are designated as output parameters
Once the SqlCommand object is instantiated, the method iterates through each item in the pParameters PropertyCollection and creates an SqlParameter. If the parameter name is in the pOutputValues list, then the SqlParameter direction is set accordingly.
Private Function GetCommand( _
ByVal pConnectionName As String, _
ByVal pComandText As String, _
ByVal pCommandType As System.Data.CommandType, _
ByRef pParameters As PropertyCollection, _
Optional ByVal pOutputValues As List(Of String) = Nothing _
) As SqlClient.SqlCommand Implements IDatalayer.GetCommand
Dim cmd As SqlClient.SqlCommand
Dim prm As SqlClient.SqlParameter
Try
cmd = New SqlClient.SqlCommand
cmd.Connection = GetConnection(pConnectionName)
cmd.CommandTimeout = 300
cmd.CommandType = pCommandType
cmd.CommandText = pComandText
If Not pParameters Is Nothing Then
For Each prmKey In pParameters.Keys
prm = New SqlClient.SqlParameter()
prm.ParameterName = prmKey
prm.Value = pParameters(prmKey)
If Not pOutputValues Is Nothing Then
If pOutputValues.Contains(prmKey) Then
prm.Direction = ParameterDirection.InputOutput
End If
End If
cmd.Parameters.Add(prm)
Next
End If
Return cmd
Catch ex As Exception
Throw
Finally
End Try
End Function
There are two ExecuteSelect() methods. The first one returns a DataTable, and is fairly straightforward. Since it returns a DataTable, the pOutputValues list is not required. The parameters used by this method are the same as those described above.
Public Function ExecuteSelect( _
ByVal pConnectionName As String, _
ByVal pComandText As String, _
ByVal pCommandType As System.Data.CommandType, _
ByVal pProperties As PropertyCollection _
) As DataTable Implements IDatalayer.ExecuteSelect
Dim cmd As SqlClient.SqlCommand
Dim adp As SqlClient.SqlDataAdapter
Dim tbl As DataTable
Try
cmd = GetCommand(pConnectionName, pComandText, pCommandType, pProperties, Nothing)
adp = New SqlClient.SqlDataAdapter(cmd)
tbl = New DataTable
adp.Fill(tbl)
Return tbl
Catch ex As Exception
Throw
Finally
cmd = Nothing
adp = Nothing
tbl = Nothing
End Try
End Function
The second ExecuteSelect() method returns a DataSet, so it has two additional parameters:
pTableNames - A list of the names for the tables being returned pRelations - A TableRelationList containing a collection of TableRelationItem objects
The first few lines of this method are similar to the first ExecuteSelect() method, except that a DataSet is filled instead of a DataTable. The pComandText parameter should contain multiple SQL Select statements or call a stored procedure that returns multiple recordsets. Next, the pTableNames parameter is checked and sets the names of the DataTable in the DataSet. The pRelations are then checked and adds the DataRelations to the DataSet. Finally, the DataSet is returned.
Public Function ExecuteSelect( _
ByVal pConnectionName As String, _
ByVal pComandText As String, _
ByVal pCommandType As System.Data.CommandType, _
ByVal pProperties As PropertyCollection, _
ByVal pTableNames As List(Of String), _
ByVal pRelations As TableRelationList _
) As DataSet Implements IDatalayer.ExecuteSelect
Dim cmd As SqlClient.SqlCommand
Dim adp As SqlClient.SqlDataAdapter
Dim dst As DataSet
Dim ndx As Integer
Dim pTableRelation As TableRelationItem
Dim dataRel As DataRelation
Dim dcPrimary(0) As DataColumn
Dim dcForeign(0) As DataColumn
Try
cmd = GetCommand(pConnectionName, pComandText, pCommandType, pProperties, Nothing)
adp = New SqlClient.SqlDataAdapter(cmd)
dst = New DataSet
adp.Fill(dst)
If Not pTableNames Is Nothing Then
For ndx = 0 To pTableNames.Count - 1
dst.Tables(ndx).TableName = pTableNames(ndx)
Next
End If
If Not pRelations Is Nothing Then
For Each pTableRelation In pRelations
dcPrimary(0) = dst.Tables(pTableRelation.PrimaryTable)._
Columns(pTableRelation.PrimaryField)
dcForeign(0) = dst.Tables(pTableRelation.ForeignTable)._
Columns(pTableRelation.ForeignField)
dataRel = New DataRelation(pTableRelation.RelationName, _
dcForeign, dcPrimary, False)
dataRel.Nested = True
dst.Relations.Add(dataRel)
Next
End If
Return dst
Catch ex As Exception
Throw
Finally
cmd = Nothing
adp = Nothing
dst = Nothing
pTableRelation = Nothing
dataRel = Nothing
dcPrimary = Nothing
dcForeign = Nothing
End Try
End Function
The ExecuteNonQuery() shows how to use output parameters. The pProperties parameter is passed ByRef so that the caller can retrieve the values, which are updated using the UpdateParameters() method.
Public Function ExecuteNonQuery( _
ByVal pConnectionName As String, _
ByVal pComandText As String, _
ByVal pCommandType As System.Data.CommandType, _
ByRef pProperties As PropertyCollection, _
ByVal pOutputValues As List(Of String) _
) As Integer Implements IDatalayer.ExecuteNonQuery
Dim cmd As SqlClient.SqlCommand
Dim rtn As Integer
Try
cmd = GetCommand(pConnectionName, pComandText, _
pCommandType, pProperties, pOutputValues)
rtn = cmd.ExecuteNonQuery
UpdateParameters(pProperties, pOutputValues, cmd.Parameters)
Return rtn
Catch ex As Exception
Throw
Finally
cmd = Nothing
End Try
End Function
The UpdateParameters() method updates the pProperties PropertyCollection based on the pOutputValues List.
Private Sub UpdateParameters( _
ByRef pProperties As PropertyCollection, _
ByVal pOutputValues As List(Of String), _
ByVal pParameters As SqlClient.SqlParameterCollection _
) Implements IDatalayer.UpdateParameters
Dim prmKey As String
Try
If Not pProperties Is Nothing AndAlso Not pOutputValues Is Nothing Then
For Each prmKey In pOutputValues
pProperties.Remove(prmKey)
pProperties.Add(prmKey, pParameters(prmKey).Value)
Next
End If
Catch ex As Exception
Throw
Finally
End Try
End Sub
A DefaultConnectionName() method is added for use by the runtime child class. This will be explained later.
MustOverride Function DefaultConnectionName( _
) As String Implements IDatalayer.DefaultConnectionName
The following methods are included in ADatalayer, though not addressed in this article:
ExecuteReader() ExecuteScalar() ExecuteXmlReader()
These methods are straightforward and should not require explanation.
config.xml
I use an XML configuration file rather than the app.config file to simplify the retrieval of configuration information. The app.config file is used to provide such extensive configuration information for the application environment that it becomes difficult to extract just the information need by the user. This file contains the connection strings and is placed in the appropriate bin directory based on the solution configuration.
<?xml version="1.0" encoding="utf-8"?>
<Configuration>
<Connection Name="Development" Value="Data Source=DevDB;
Initial Catalog=ContactList;Integrated Security=True" />
<Connection Name="Production" Value="Data Source=ProdDB;
Initial Catalog=ContactList;Integrated Security=True" />
</Configuration>
DatalayerUI.DatalayerRT
The purpose of this class is to provide a runtime datalayer for use by a specific application. The class signature and New() method are shown below:
Public Class DatalayerRT
Inherits DataLayer.ADatalayer
Protected Sub New(ByVal pConnectionStrings As DataTable)
MyBase.New(pConnectionStrings)
End Sub
End Class
Since this class inherits ADatalayer, it must override the DefaultConnectionName() method. I use conditional compilation to determine which database I will connect to based on the current solution configuration. I've done it this way because most of the time I'm either connecting to a development database or a production database. It is rare that I have an application that connects to more than one database, but the architecture of the datalayer does allow for that.
Public Overrides Function DefaultConnectionName() As String
Try
#If DEBUG Then
Return "Development"
#Else
Return "Production"
#End If
Catch ex As Exception
Throw
Finally
End Try
End Function
Finally, to satisfy the third condition for implementing the Singleton design pattern, I provide a shared method for returning an instance of the class:
Public Shared Function GetInstance( _
ByVal pConnectionStrings As DataTable _
) As DataLayer.IDatalayer
Try
If mDataLayer Is Nothing Then
mDataLayer = New DatalayerRT(pConnectionStrings)
End If
Return mDataLayer
Catch ex As Exception
Throw
Finally
End Try
End Function
Form1
The purpose of this form is to show how to use the datalayer. The code below shows the New() method in the Form1 class, along with the variables I'll be using. I'll cover actual examples separately.
Public Class Form1
Protected mDataLayer As DatalayerRT
Public Sub New()
InitializeComponent()
Dim ds As DataSet = Nothing
Dim pc As PropertyCollection = Nothing
Dim dt As DataTable = Nothing
Dim count As Int32 = 0
Dim tri As Datalayer.TableRelationItem = Nothing
Dim trl As Datalayer.TableRelationList = Nothing
Dim lst As List(Of String) = Nothing
Try
Catch ex As Exception
Throw
Finally
lst = Nothing
trl = Nothing
tri = Nothing
dt = Nothing
pc = Nothing
ds = Nothing
End Try
End Sub
End Class
Example 1: Instantiating the DatalayerRT
Add the following code to the Try clause:
ds = New DataSet
ds.ReadXml("config.xml")
mDataLayer = DatalayerRT.GetInstance(ds.Tables("Connection"))
This code loads the config.xml file into a DataSet and passed the "Connection" table to the GetInstance() method of DatalayerRT.
Example 2: Using ExecuteScalar() with a Paramaterized SQL Statement
pc = New PropertyCollection
pc.Add("EmailAddressTypeID", 1)
count = mDataLayer.ExecuteScalar( _
mDataLayer.DefaultConnectionName, _
"SELECT COUNT(*) FROM EmailAddress WHERE (EmailAddressTypeID = @EmailAddressTypeID)", _
CommandType.Text, _
pc, Nothing)
Example 3: Using ExecuteSelect() to Return a DataTable from a Stored Procedure
dt = mDataLayer.ExecuteSelect( _
mDataLayer.DefaultConnectionName, _
"ReadEmailAddress", _
CommandType.StoredProcedure, _
pc)
Example 4: Using ExecuteSelect() to Return a DataSet from a Two Parameterized SQL Select Statements
There are four steps required for this:
- Define the table relationsips
- Define the table names
- Set the parameters
- Make the call
tri = New Datalayer.TableRelationItem
tri.RelationName = "PersonEmailAddress"
tri.PrimaryTable = "EmailAddress"
tri.PrimaryField = "PersonID"
tri.ForeignTable = "Person"
tri.ForeignField = "PersonID"
trl = New Datalayer.TableRelationList
trl.Add(tri)
lst = New List(Of String)
lst.Add("Person")
lst.Add("EmailAddress")
pc.Clear()
pc.Add("PersonID", 1)
ds = mDataLayer.ExecuteSelect( _
mDataLayer.DefaultConnectionName, _
"SELECT * FROM Person WHERE (PersonID = @PersonID);" _
& "SELECT * FROM EmailAddress WHERE (PersonID = @PersonID)", _
CommandType.StoredProcedure, _
pc, lst, trl)
Summary
It is possible to create one Datalayer DLL that can serve all data transfer needs and be used by any application that requires it. This DLL compiled to 21 KB on my machine, and I can use it simply by adding it as a reference to my application and creating the runtime DatalayerRT class.
History
- 31st December, 2010: Initial post
