Sample Database Code

From a small sample project for training a client

Altered to protect confidential information

 

Summary

This document illustrates the Database and Business layers and their interaction in an N-Tier project. A full project consists of the following layers:

Data Layer

Data Access Layer

Business Layer

Workflow Layer

Presentation Layer

Five-Tier Architecture

 

The sample project combines the workflow and presentation layers.

Data Layer

The data layer consists of the database with its table definitions. For an example of a database design for a larger SQL Server project please visit http://www.highermath.com/ps/dbdesc.htm.

 

Data Access Layer

The data access layer consists of the objects used to connect to the database, access it and manage records. It is a set of objects written by the programming team to encapsulate SQL queries to the database and to handle parsing the returned result sets.

 

The sample program uses an OleDbConnection object to connect to the database. This flexible object contains a connection string that can be configured to open either an MS Access database or a SQL Server database, among others. For each table in the database, the project uses OleDbDataAdapters. Objects for each of the tables are provided to use the data adapters to access data in various required ways.

 

Examples classes:

 

Public Class daCrawlQueue

 

      Private mbIsNewRecord As Boolean = False

      Private mobjDataAdabpter As Data.Common.DbDataAdapter

      Private mobjDataSet As New Data.DataSet

      Private mobjRow As DataRow

 

      Public ReadOnly Property IsNewRecord() As Boolean

            Get

                  Return mbIsNewRecord

            End Get

      End Property

 

      Public Property ReferenceCount() As Integer

            Get

                  Return IIf(mobjRow Is Nothing, "", mobjRow.Item("ReferenceCount"))

            End Get

            Set(ByVal Value As Integer)

                  If Not mobjRow Is Nothing Then

                        mobjRow.Item("ReferenceCount") = Value

                  End If

            End Set

      End Property

 

      Public Sub New(ByVal objDataAdabpter As Data.Common.DbDataAdapter)

 

            mobjDataAdabpter = objDataAdabpter

 

            With mobjDataAdabpter

                  .FillSchema(mobjDataSet, SchemaType.Source, "CrawlQueue")

            End With

 

      End Sub

 

      'Will add/update the site to the CrawlQueue table. Will set the ReferenceCount

      'only on a new record.

 

      'Returns CrawlQueueIndex

 

      'NOTE: This is set up for OLE DB. It can be easily set up for SQL or ODBC, as well.

      'For now, it will throw an exception if the data adapter is not OLE DB.

 

      Public Function AddSite(ByVal iSiteIndex As Integer, ByVal iReferenceCount As Integer) As Integer

 

            If TypeOf mobjDataAdabpter Is OleDb.OleDbDataAdapter Then

                  Dim objOleDbDa As OleDb.OleDbDataAdapter = mobjDataAdabpter

                  Dim objCommand As New OleDb.OleDbCommand("SELECT * FROM CrawlQueue WHERE SiteIndex=" & iSiteIndex, objOleDbDa.SelectCommand.Connection)

 

                  With objOleDbDa

                        .SelectCommand = objCommand

                  End With

            Else

                  Throw New System.Exception("daCrawlQueue class should be upgraded to handle more data adapter types.")

            End If

 

            With mobjDataAdabpter

                  mobjDataSet.Clear()

                  .Fill(mobjDataSet, "CrawlQueue")

            End With

 

            With mobjDataSet.Tables(0)

                  If .Rows.Count > 0 Then

                        mbIsNewRecord = False

                        mobjRow = .Rows(0)

                  Else

                        mbIsNewRecord = True

                        mobjRow = .NewRow

 

                        With mobjRow

                              .Item("SiteIndex") = iSiteIndex

                              .Item("ReferenceCount") = iReferenceCount

                        End With

 

                        .Rows.Add(mobjRow)

 

                        mobjDataAdabpter.Update(mobjDataSet)

 

                        With mobjDataAdabpter

                              mobjDataSet.Clear()

                              .Fill(mobjDataSet, "CrawlQueue")

                        End With

 

                        mobjRow = .Rows(0)

                  End If

            End With

 

            Return mobjRow.Item("CrawlQueueIndex")

 

      End Function

 

      Public Sub Update()

 

            mobjDataAdabpter.Update(mobjDataSet)

 

      End Sub

 

End Class

 

Public Class daSites

   

      Private mobjDataAdabpter As Data.Common.DbDataAdapter

      Private mobjDataSet As New Data.DataSet

      Private mobjRow As DataRow

 

      Property BaseURL() As String

            Get

                  Return IIf(mobjRow Is Nothing, "", mobjRow.Item("BaseURL").ToString)

            End Get

            Set(ByVal Value As String)

                  If Not mobjRow Is Nothing Then

                        mobjRow.Item("BaseURL") = Value

                  End If

            End Set

      End Property

 

      Property HostName() As String

            Get

                  Return IIf(mobjRow Is Nothing, "", mobjRow.Item("HostName").ToString)

            End Get

            Set(ByVal Value As String)

                  If Not mobjRow Is Nothing Then

                        mobjRow.Item("HostName") = Value

                  End If

            End Set

      End Property

 

      Public Sub New(ByVal objDataAdabpter As Data.Common.DbDataAdapter)

 

            mobjDataAdabpter = objDataAdabpter

 

            With mobjDataAdabpter

                  .FillSchema(mobjDataSet, SchemaType.Source, "Sites")

            End With

 

      End Sub

 

      'This will add a site if not already in the table.

 

      'Returns SiteIndex.

 

      'NOTE: This is set up for OLE DB. It can be easily set up for SQL or ODBC, as well.

      'For now, it will throw an exception if the data adapter is not OLE DB.

 

      Public Function AddSite(ByVal strUrl As String, ByVal strHostName As String) As Integer

 

            If TypeOf mobjDataAdabpter Is OleDb.OleDbDataAdapter Then

                  Dim objOleDbDa As OleDb.OleDbDataAdapter = mobjDataAdabpter

                  Dim objCommand As New OleDb.OleDbCommand("SELECT * FROM Sites WHERE HostName='" & strHostName & "'", objOleDbDa.SelectCommand.Connection)

 

                  With objOleDbDa

                        .SelectCommand = objCommand

                  End With

            Else

                  Throw New System.Exception("daSites class should be upgraded to handle more data adapter types.")

            End If

 

            With mobjDataAdabpter

                  mobjDataSet.Clear()

                  .Fill(mobjDataSet, "Sites")

            End With

 

            With mobjDataSet.Tables(0)

                  If .Rows.Count > 0 Then

                        mobjRow = .Rows(0)

                  Else

                        mobjRow = .NewRow

 

                        With mobjRow

                              .Item("BaseURL") = strUrl

                              .Item("HostName") = strHostName

                        End With

 

                        .Rows.Add(mobjRow)

 

                        mobjDataAdabpter.Update(mobjDataSet)

 

                        With mobjDataAdabpter

                              mobjDataSet.Clear()

                              .Fill(mobjDataSet, "Sites")

                        End With

 

                        mobjRow = .Rows(0)

                  End If

            End With

 

            Return mobjRow.Item("SiteIndex")

 

      End Function

 

      Public Sub Update()

 

            mobjDataAdabpter.Update(mobjDataSet)

 

      End Sub

 

End Class

 

 

Business Layer

This set of objects controls all access to the data and enforces all the “business rules” and logic necessary to massage the data.

 

Example:

 

Public Class busCrawlQueue

   

      'Initial Crawl Set

 

      'The initial set of sites to be crawled is obtained by querying search engines

      'such as Google using a list of search terms supplied by the administrator.

      'This set of URLs is stored in the database forming a primary queue of sites

      'to be examined.

 

      'Secondary Crawl Set

 

      'When links to other sites are obtained via data extraction from a crawled site,

      'these links are added to the queue. This forms a secondary queue of sites to be

      'crawled. Each time a reference to the same site is noted, a count is

      'incremented. This count will move the site towards the top of the queue. Sites

      'in the primary queue start with a count of 1 and secondary sites begin with 0.

      'Secondary sites that have already been noted will thus attain a higher count

      'and move into the primary queue.

 

 

      Private mobjData As daCrawlQueue

      Private mobjSites As busSites

 

      Public Sub New(ByVal objSites As busSites, ByVal objData As daCrawlQueue)

 

            mobjData = objData

            mobjSites = objSites

 

      End Sub

 

      'AddPrimarySite, AddSecondarySite:

      'Will add/update the site to the Sites and CrawlQueue tables and will set the

      'higher of any existing reference count or the suggested count.

 

      'Returns CrawlQueueIndex

 

      Public Function AddPrimarySite(ByVal strUrl As String, ByVal lblSiteCount As Label) As Integer

 

            Return AddSite(strUrl, 1, lblSiteCount)

 

      End Function

 

      Public Function AddSecondarySite(ByVal strUrl As String, ByVal lblSiteCount As Label) As Integer

 

            Return AddSite(strUrl, 0, lblSiteCount)

 

      End Function

 

      'This routine accesses the data layer to add or update the site entry in the crawl

      'queue.

 

      Private Function AddSite(ByVal strUrl As String, ByVal iReferenceCount As Integer, ByVal lblSiteCount As Label) As Integer

 

            'First, we make sure the site is in the master sites table.

 

            Dim iSiteIndex As Integer = mobjSites.AddSite(strUrl)

 

            With mobjData

                  'Now, it is added to updated in the crawl queue.

 

                  Dim iCrawlQueueIndex As Integer = .AddSite(iSiteIndex, iReferenceCount)

 

                  Dim iCurrentCount As Integer = .ReferenceCount

 

                  'An existing record may already have a bigger reference count.

                  'The larger count remains intact.

 

                  If iReferenceCount > iCurrentCount Then

                        .ReferenceCount = iReferenceCount

                        .Update()

                  End If

 

                  'This interfaces with any active label control to tell the user

                  'each time a new site is found. The effect is to see the counter

                  'incrementing as the search is running.

 

                  If .IsNewRecord Then

                        If Not lblSiteCount Is Nothing Then

                              With lblSiteCount

                                    Dim iSiteCount As Integer = .Text

 

                                    iSiteCount += 1

                                    .Text = iSiteCount

                                    .Refresh()

                              End With

                        End If

                  End If

 

                  Return iCrawlQueueIndex

            End With

 

      End Function

 

End Class