A 21 KB Datalayer For All Your Data Transfer Needs

• Download source - 22.82 KB

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 relationships
  • ADatalayer - 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 ADatalayer
  • Form1 - 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, 2^nd 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:

1. A class-level variable in the abstract class for returning an instance of itself
2. The New() method in the abstract class is declared Protected to prevent an outside class from instantiating it
3. 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 used
• pComandText - This can be an SQL statement or the name of a stored procedure
• pCommandType - Specifies the type of command
• pParameters - The collection of name/value pairs to be used by a parameterized SQL statement or a stored procedure
• pOutputValues - 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()

        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        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:

1. Define the table relationsips
2. Define the table names
3. Set the parameters
4. Make the call

'Step 1: Define the table relationships
'EmailAddress table contains a foreign key PersonID in table Person
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)

'Step 2: Define the table names
lst = New List(Of String)
lst.Add("Person")
lst.Add("EmailAddress")

'Step 3: Set the parameters
pc.Clear()
pc.Add("PersonID", 1)

'Step 4: Make the call
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

• 31^st December, 2010: Initial post