2. Part 2

2.1. Introduction

We will begin by reviewing what was done in Part 1, particularly the three-tier architecture [web, domain, dao] used. In the proposed solution, the [dao] layer was a test layer: the data source was implemented using an [ArrayList] object. In this article, we will focus on the [DAO] layer, presenting various possible implementations of it when the data is in a DBMS.

Tools used:

the Firebird DBMS—see Appendix, Section 3.5.
the MSDE (Microsoft Data Engine) DBMS—see Appendix, Section 3.12.
IBExpert, Personal Edition, for graphical administration of the Firebird DBMS—see Appendix, Section 3.6.
EMS MS SQL Manager for graphical administration of the MSDE DBMS—see Appendix, Section 3.14.
Ibatis SqlMap for the DBMS data access layer—see section 2.5.6.2.

On a beginner-intermediate-advanced scale, this document falls into the [intermediate-advanced] category. Understanding it requires various prerequisites. Some of these can be found in documents I have written. In such cases, I cite them. It goes without saying that this is merely a suggestion and that the reader is free to use their preferred resources.

VB.NET language: [Introduction to VB.NET through Examples]
Web programming in VB.NET: [Web Development with ASP.NET 1.1]
Using Spring's IoC: [Spring IoC for .NET]
iBatis SqlMap documentation: [http://prdownloads.sourceforge.net/ibatisnet/DevGuide.pdf?download]
Firebird documentation: [http://firebird.sourceforge.net/pdfmanual/Firebird-1.5-QuickStart.pdf]
Spring.net documentation: [http://www.springframework.net/documentation.html]

2.2. The webarticles Application - Review

Here we present the components of the simplified e-commerce web application discussed in Part 1. This application allows web users to:

view a list of items from a database
add some of them to an electronic shopping cart
to confirm the cart. This confirmation simply updates the inventory levels of the purchased items in the database.

2.2.1. Application Views

The different views presented to the user are as follows:

the [ERRORS] view, which reports any application errors

2.2.2. General application architecture

The application built in Part 1 has a three-tier architecture:

The three layers have been made independent through the use of interfaces
The integration of the different layers was implemented using Spring
Each layer has its own namespace: web (UI layer), domain (business layer), and dao (data access layer).

The application follows an MVC (Model-View-Controller) architecture. If we refer back to the layered diagram above, the MVC architecture fits into it as follows:

The processing of a client request follows these steps:

The client sends a request to the controller. This controller is, in this case, an .aspx page that plays a specific role. It handles all client requests. It is the application’s entry point. It is the C in MVC.
The controller processes this request. To do so, it may need assistance from the business layer, known as the M in the MVC structure.
The controller receives a response from the business layer. The client’s request has been processed. This can trigger several possible responses. A classic example is
- an error page if the request could not be processed correctly
- a confirmation page otherwise
The controller chooses the response (= view) to send to the client. This is most often a page containing dynamic elements. The controller provides these to the view.
The view is sent to the client. This is the V in MVC.

2.2.3. The Model

The M in MVC consists of the following elements:

business classes
data access classes
the database

2.2.3.1. The database

The database contains only one table named ARTICLES, generated using the following SQL commands:

CREATE TABLE ARTICLES (
    ID            INTEGER NOT NULL,
    NAME           VARCHAR(20) NOT NULL,
    PRICE          NUMERIC(15,2) NOT NULL,
    CURRENTSTOCK   INTEGER NOT NULL,
    MINIMUM_STOCK  INTEGER NOT NULL
);
/* constraints */
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_ID check (ID>0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_PRICE check (PRICE >= 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKACTUEL check (STOCKACTUEL >= 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_STOCKMINIMUM check (STOCKMINIMUM >= 0);
ALTER TABLE ARTICLES ADD CONSTRAINT CHK_NAME check (NAME <> '');
ALTER TABLE ARTICLES ADD CONSTRAINT UNQ_NAME UNIQUE (NAME);
/* primary key */
ALTER TABLE ARTICLES ADD CONSTRAINT PK_ARTICLES PRIMARY KEY (ID);

`id`	primary key uniquely identifying an item
`name`	item name
`price`	its price
`current stock`	current stock
`minimum stock`	the stock level below which a reorder must be placed

2.2.3.2. The model's namespaces

Model M is provided in the form of two namespaces:

istia.st.articles.dao: contains the data access classes of the [dao] layer
istia.st.articles.domain: contains the business classes of the [domain] layer

Each of these namespaces is contained within its own "assembly" file:

assembly	content	role
webarticles-dao	- [IArticlesDao]: the interface for accessing the [dao] layer. This is the only interface visible to the [domain] layer. It sees no others. - [Article]: class defining an article - [ArticlesDaoArrayList]: implementation class of the [IArticlesDao] interface using an [ArrayList] class	data access layer - located entirely within the [dao] layer of the web application's 3-tier architecture
webarticles-domain	- [IArticlesDomain]: the interface for accessing the [domain] layer. It is the only interface visible to the web layer. It sees no others. - [ArticlePurchases]: a class implementing [IArticlesDomain] - [Purchase]: a class representing a customer's purchase - [Cart]: a class representing a customer's total purchases	represents the web purchase model - resides entirely in the [domain] layer of the web application's 3-tier architecture

2.2.4. Deployment and testing of the [webarticles] application

2.2.4.1. Deployment

We deploy the application developed in Part 1 of this article into a folder named [runtime]:

Comments:

The [runtime] folder contains three files and two subfolders:

the controllers [global.asax] and [main.aspx]
the configuration file [web.config]
the [bin] folder, which contains:
- the DLLs for the three layers [webarticles-dao.dll], [webarticles-domain.dll], [webarticles-web.dll]
- the files required by Spring [Spring-Core.*], [log4net.dll]
the [views] folder, which contains the presentation code for the various views.
The presence of .vb code files is unnecessary since their compiled versions are in the DLLs.

2.2.4.2. Tests

We configure the [Cassini] web server as follows:

with:

Physical Path: D:\data\serge\work\2004-2005\aspnet\webarticles-010405\runtime\

Virtual Path: /webarticles

Using a browser, we request the URL [http://localhost/webarticles/main.aspx]

Recall that the [dao] layer is implemented by a class that stores the articles in an [ArrayList] object. This class creates an initial list of four articles. From the view above, we use the menu links to perform operations. Here are a few of them. The left column represents the client’s request, and the right column represents the response sent to the client.

2.2.5. The [DAO] layer revisited

In our first implementation of the [dao] layer, the [IArticlesDao] data access interface was implemented by a class storing the items in an [ArrayList] object. This allowed us to avoid overcomplicating this layer and to demonstrate that only its interface mattered, not its implementation. We were thus able to build a working web application. This application has three layers: [web], [domain], and [dao]. Here, we will propose different implementations of the [dao] layer. Each of them can replace the current [dao] layer without any modification to the [domain] and [web] layers. This flexibility is achieved because:

the [domain] layer does not address a concrete class but an interface [IArticlesDao]
thanks to Spring, we were able to hide the name of the class implementing the [IArticlesDao] interface from the [domain] layer.

2.2.5.1. Elements of the [dao] layer

Let’s review some of the elements of the [dao] layer that will be retained in the new implementations:

- [IArticlesDao]: the interface for accessing the [dao] layer
- [Article]: the class defining an article

2.2.5.2. The [Article] class

The class defining an article is as follows:

Imports System

Namespace istia.st.articles.dao

    Public Class Article

        ' private fields
        Private _id As Integer
        Private _name As String
        Private _price As Double
        Private _currentStock As Integer
        Private _currentStock As Integer

        ' item ID
        Public Property id() As Integer
            Get
                Return _id
            End Get
            Set(ByVal Value As Integer)
                If Value <= 0 Then
                    Throw New Exception("The id field [" + Value.ToString + "] is invalid")
                End If
                Me._id = Value
            End Set
        End Property

        ' item name
        Public Property name() As String
            Get
                Return _name
            End Get
            Set(ByVal Value As String)
                If Value Is Nothing OrElse Value.Trim.Equals("") Then
                    Throw New Exception("The name field [" + Value + "] is invalid")
                End If
                Me._name = Value
            End Set
        End Property

        ' item price
        Public Property price() As Double
            Get
                Return _price
            End Get
            Set(ByVal Value As Double)
                If Value < 0 Then
                    Throw New Exception("The price field [" + Value.ToString + "] is invalid")
                End If
                Me._price = Value
            End Set
        End Property

        ' current item stock
        Public Property currentStock() As Integer
            Get
                Return _currentStock
            End Get
            Set(ByVal Value As Integer)
                If Value < 0 Then
                    Throw New Exception("The currentStock field [" + Value.ToString + "] is invalid")
                End If
                Me._currentStock = Value
            End Set
        End Property

        ' minimum stock for item
        Public Property stockminimum() As Integer
            Get
                Return _stockminimum
            End Get
            Set(ByVal Value As Integer)
                If Value < 0 Then
                    Throw New Exception("The stockMinimum field [" + Value.ToString + "] is invalid")
                End If
                Me._stockminimum = Value
            End Set
        End Property

        ' default constructor
        Public Sub New()
        End Sub

        ' constructor with properties
        Public Sub New(ByVal id As Integer, ByVal name As String, ByVal price As Double, ByVal currentStock As Integer, ByVal minimumStock As Integer)
            Me.id = id
            Me.name = name
            Me.price = price
            Me.currentStock = currentStock
            Me.minStock = minStock
        End Sub

        ' item identification method
        Public Overrides Function ToString() As String
            Return "[" + id.ToString + "," + name + "," + price.ToString + "," + currentStock.ToString + "," + minimumStock.ToString + "]"
        End Function
    End Class
End Namespace

This class provides:

a constructor for setting the 5 pieces of information for an item: [id, name, price, currentStock, minimumStock]
public properties for reading and writing the 5 pieces of information.
a validation of the data entered for the item. If the data is invalid, an exception is thrown.
a toString method that returns the value of an item as a string. This is often useful for debugging an application.

2.2.5.3. The [IArticlesDao] interface

The [IArticlesDao] interface is defined as follows:

Imports System
Imports System.Collections

Namespace istia.st.articles.dao

    Public Interface IArticlesDao
        ' list of all articles
        Function getAllArticles() As IList
        ' adds an article
        Function addArticle(ByVal anArticle As Article) As Integer
        ' deletes an article
        Function deleteArticle(ByVal articleId As Integer) As Integer
        ' modifies an article
        Function modifyArticle(ByVal anArticle As Article) As Integer
        ' retrieves an item
        Function getArticleById(ByVal idArticle As Integer) As Article
        ' deletes all articles
        Sub clearAllArticles()
        ' changes the stock of an item
        Function changeArticleStock(ByVal idArticle As Integer, ByVal movement As Integer) As Integer
    End Interface
End Namespace

The roles of the various methods in the interface are as follows:

`getAllArticles`	returns all articles from the data source
`clearAllArticles`	clears the data source
`getArticleById`	returns the [Article] object identified by its number
`addArticle`	allows you to add an article to the data source
`modifyArticle`	allows you to modify an article in the data source
`deleteItem`	allows you to delete an item from the data source
`updateItemStock`	allows you to modify the stock of an item in the data source

The interface provides client programs with a number of methods defined solely by their signatures. It does not concern itself with how these methods will actually be implemented. This brings flexibility to an application. The client program makes calls on an interface rather than on a specific implementation of it.

The choice of a specific implementation is made via a Spring configuration file.

2.3. The [ArticlesDaoPlainODBC] implementation class

We propose a new implementation of the [dao] layer that assumes the data is in an ODBC source. We know that on Windows, virtually all DBMSs on the market have an ODBC driver. The advantage of this solution is that you can switch DBMSs transparently to the application. The drawback is that an ODBC driver that only exploits features common to all DBMSs is generally less efficient than a driver specifically written to exploit the full potential of a particular DBMS. See Section 3.7 for an example of creating an ODBC source.

2.3.1. The code

2.3.1.1. The skeleton

The [ArticlesDaoPlainODBC] class implements the [IArticlesDao] interface as follows:

Imports System
Imports System.Collections
Imports System.Data.Odbc

Namespace istia.st.articles.dao

    Public Class ArticlesDaoPlainODBC
        Implements istia.st.articles.dao.IArticlesDao

        ' private fields
        Private connection As OdbcConnection = Nothing
        Private DSN As String
        Private insertCommand As OdbcCommand
        Private updatecommand As OdbcCommand
        Private deleteSomeCommand As OdbcCommand
        Private selectSomeCommand As OdbcCommand
        Private updateStockCommand As OdbcCommand
        Private deleteAllCommand As OdbcCommand
        Private selectAllCommand As OdbcCommand

        ' constructor
        Public Sub New(ByVal DSN As String, ByVal uid As String, ByVal password As String)
            ' DSN: name of the ODBC source
            ' uid: user ID
            ' password: user's password
...
        End Sub

        Public Function addArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.addArticle
...
        End Function

        Public Function updateItemStock(ByVal itemId As Integer, ByVal movement As Integer) As Integer Implements IArticlesDao.updateItemStock
...
        End Function

        Public Sub clearAllArticles() Implements IArticlesDao.clearAllArticles
...
        End Sub

        Public Function getAllArticles() As System.Collections.IList Implements IArticlesDao.getAllArticles
...
        End Function

        Public Function getArticleById(ByVal idArticle As Integer) As Article Implements IArticlesDao.getArticleById
...
        End Function

        Public Function modifyArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.modifyArticle
...
        End Function

        Public Function deleteItem(ByVal itemId As Integer) As Integer Implements IArticlesDao.deleteItem
....
        End Function

        Private Function executeQuery(ByVal query As OdbcCommand) As IList
            ' execute a SELECT query 
....
        End Function

        Private Function executeUpdate(ByVal sqlCommand As OdbcCommand) As Integer
....
    End Class

End Namespace

Comments:

Line 3: imports the namespace containing the .NET classes for accessing ODBC sources
Line 11 - will store the connection to the ODBC source
Line 12 - stores the DSN name of the data source
Lines 13–19: private variables of type [OdbcCommand] defining the SQL queries used by the class’s various methods
Lines 22–27: the constructor. It receives the elements needed to construct the [OdbcConnection] object that will connect the code to the ODBC data source
lines 29–31 – the method for adding an item
lines 33–35 – the method for changing an item’s stock
lines 37–39 – the method that deletes all items from the ODBC data source
lines 41-43 - the method that retrieves the list of all items from the ODBC source
lines 45–47 – the method that retrieves a specific item
lines 49-51 - the method that allows you to modify certain fields of an item for which you have the number
lines 53-55 - the method that allows you to delete an item by its number
lines 57-60 - utility method to execute a [SELECT] on the data source and return the result
lines 62-64 - utility method to execute an [INSERT, UPDATE, DELETE] on the data source and return the result

2.3.1.2. The

        ' constructor
        Public Sub New(ByVal DSN As String, ByVal uid As String, ByVal password As String)
            ' DSN: name of the ODBC source
            ' uid: user ID
            ' password: user's password

            'retrieve the database name passed as an argument
            Me.DSN = DSN
            Dim connectString As String = String.Format("DSN={0};UID={1};PASSWORD={2}", DSN, uid, password)
            'instantiate the connection
            connection = New OdbcConnection(connectString)
            ' Prepare the SQL queries
            insertCommand = New OdbcCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (?,?,?,?,?)", connection)
            updateCommand = New OdbcCommand("update ARTICLES set name=?, price=?, currentStock=?, minimumStock=? where id=?", connection)
            deleteSomeCommand = New OdbcCommand("delete from ARTICLES where id=?", connection)
            selectSomeCommand = New OdbcCommand("select id, name, price, currentStock, minimumStock from ARTICLES where id=?", connection)
            updateStockCommand = New OdbcCommand("update ARTICLES set currentStock=currentStock+? where id=? and (currentStock+?)>=0", connection)
            selectAllCommand = New OdbcCommand("select id, name, price, currentStock, minimumStock from ARTICLES", connection)
            deleteAllCommand = New OdbcCommand("delete from ARTICLES", connection)
        End Sub

Comments:

Line 2 - The constructor receives the three pieces of information it needs to connect to an ODBC source: the source's DSN name, the username to connect with, and the associated password.
line 8 - the source's DSN name is stored so it can be included in error messages.
Line 9 - The [OdbcConnection] object is instantiated. An instantiated connection is not an open connection. The [open] method is used to open the connection.
Lines 12–19 – We prepare the SQL queries in [OdbcCommand] objects. This will save us from having to rebuild them every time we need them. The formal parameters ? in the queries will be replaced with actual values when the query is executed.

2.3.1.3. The executeQuery method

        Private Function executeQuery(ByVal query As OdbcCommand) As IList
            ' execution of a SELECT query 
            ' Declaration of the object that provides access to all rows in the result table
            Dim myReader As OdbcDataReader = Nothing
            Try
                'Create a connection to the database
                connection.Open()
                'Execute the query
                myReader = query.ExecuteReader()
                'declare a list of items to return later
                Dim articles As IList = New ArrayList
                Dim anItem As Item
                While myReader.Read()
                    'We create an article using the values from the reader
                    anItem = New Item
                    unArticle.id = myReader.GetInt32(0)
                    anArticle.name = myReader.GetString(1)
                    anArticle.price = myReader.GetDouble(2)
                    anArticle.currentStock = myReader.GetInt32(3)
                    anItem.minStock = myReader.GetInt32(4)
                    'Add the item to the list
                    items.Add(anItem)
                End While
                'return the result
                Return items
            Finally
                ' release resources
                If Not myReader Is Nothing And Not myReader.IsClosed Then myReader.Close()
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function

Comments:

The [executeQuery] method is a utility method that:
- executes a query [SELECT id, name, price, currentStock, minimumStock from ARTICLES ...] on the data source
- returns the result as a list of [Article] objects
Line 1 - The method's only parameter is the [OdbcCommand] object containing the [Select] query to be executed.
Line 7 - The connection is opened. It will be closed on line 29 regardless of whether an error occurred or not.
line 9 - the [OdbcDataReader] object needed to process the result of the [Select] is instantiated
Lines 13–23 – Each row resulting from the [Select] query is placed in an [Article] object, which is then added to the other articles in an [ArrayList]
The list of items is returned on line 25
No exceptions are handled. They must be handled by the code calling this method.

2.3.1.4. The executeUpdate method

        Private Function executeUpdate(ByVal sqlCommand As OdbcCommand) As Integer
            ' Execution of an update query
            Try
                ' create a connection to the database
                connection.Open()
                'execute the query
                Return sqlCommand.ExecuteNonQuery()
            Finally
                ' Release resources
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function
    End Class

Comments:

The method receives an [OdbcCommand] object that contains an SQL query of type [Insert, Update, Delete].
The connection is opened on line 5. It will be closed on line 10 regardless of whether an exception occurred or not.
The update query is executed on line 7. The result—the number of rows in the ARTICLES table modified by the query—is immediately returned.

2.3.1.5. The addArticle method

        Public Function addArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.addArticle
            ' exclusive section
            SyncLock Me
                ' prepare the insert query
                With insertCommand.Parameters
                    .Clear()
                    .Add(New OdbcParameter("id", anItem.id))
                    .Add(New OdbcParameter("name", unArticle.name))
                    .Add(New OdbcParameter("price", anItem.price))
                    .Add(New OdbcParameter("currentStock", anItem.currentStock))
                    .Add(New OdbcParameter("minStock", anItem.minStock))
                End With
                Try
                    'execute it
                    Return executeUpdate(insertCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error adding item [{0}]: {1}", unArticle.ToString, ex.Message))
                End Try
            End SyncLock
        End Function

Comments:

Line 1 - The method receives the item to be added to the ODBC data source. It returns the number of rows affected by this operation, i.e., 1 or 0
Lines 3 and 20 - The method is synchronized. This will be the case for all data access methods. This means that only one thread at a time can work on the data source. This is probably too conservative. There are better alternatives, notably including these operations within transactions. In this case, the DBMS manages concurrent access. We did not want to introduce the concept of transactions at this stage. Spring offers us the option of introducing them in the [domain] layer. We may have the opportunity to revisit this in another article.
Lines 5–12 assign values to the formal parameters of the query for the [insertCommand] object initialized by the constructor. Here is the query again:

insertCommand = New OdbcCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (?,?,?,?,?)", connection)

The 5 values required for the query are provided by lines 7–11.

Lines 13–19: The query is executed. If it succeeds, the result is returned. Otherwise, a generic exception is thrown with an explicit error message

2.3.1.6. The modifieArticle method

        Public Function modifyArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.modifyArticle
            ' exclusive section
            SyncLock Me
                ' prepare the update query
                With updatecommand.Parameters
                    .Clear()
                    .Add(New OdbcParameter("name", anItem.name))
                    .Add(New OdbcParameter("price", anItem.price))
                    .Add(New OdbcParameter("currentStock", anItem.currentStock))
                    .Add(New OdbcParameter("minStock", anItem.minStock))
                    .Add(New OdbcParameter("id", anItem.id))
                End With
                ' Run it
                Try
                    'Execute the insert query
                    Return executeUpdate(updatecommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception("Error updating item [" + anItem.ToString + "]", ex)
                End Try
            End SyncLock
        End Function

Comments:

Line 1 - The method receives the item to be modified from the ODBC data source. It returns the number of rows affected by this operation, i.e., 1 or 0
The comments from the [addItem] method can be included here

2.3.1.7. The deleteArticle method

        Public Function deleteItem(ByVal itemId As Integer) As Integer Implements IArticlesDao.deleteItem
            ' exclusive section
            SyncLock Me
                ' prepare the delete query
                With deleteSomeCommand.Parameters
                    .Clear()
                    .Add(New OdbcParameter("id", idArticle))
                End With
                'execute it
                Try
                    'execute the delete query
                    Return executeUpdate(deleteSomeCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting item [id={0}]: {1}", idArticle, ex.Message))
                End Try
            End SyncLock
        End Function

Comments:

Line 1 - The method receives the ID of the item to be deleted from the ODBC data source. It returns the number of rows affected by this operation, i.e., 1 or 0
The comments for the [addItem] method can be included here

2.3.1.8. The getAllArticles method

            Public Function getAllArticles() As System.Collections.IList Implements IArticlesDao.getAllArticles
            ' Exclusive section
            SyncLock Me
                Try
                    'execute the select query
                    Dim items As IList = executeQuery(selectAllCommand)
                    'return the list
                    Return items
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error retrieving items [select id, name, price, currentStock, minimumStock from items]: {0}", ex.Message))
                End Try
            End SyncLock
        End Function

Comments:

Line 1 - The method takes no parameters. It returns a list of all items from the ODBC data source
The [Select] query retrieving all records is passed to the [executeQuery] method - line 6
The resulting list is returned on line 8
Lines 9–12 handle any exceptions

2.3.1.9. The getArticleById method

        Public Function getArticleById(ByVal idArticle As Integer) As Article Implements IArticlesDao.getArticleById
            ' exclusive section
            SyncLock Me
                ' prepare the SELECT query
                With selectSomeCommand.Parameters
                    .Clear()
                    .Add(New OdbcParameter("id", idArticle))
                End With
                'execute it
                Try
                    'execute the query
                    Dim articles As IList = executeQuery(selectSomeCommand)
                    'check if the item was found
                    If items.Count = 0 Then Return Nothing
                    'return the item
                    Return CType(articles.Item(0), Article)
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error retrieving item [{0}: {1}", idItem, ex.Message))
                End Try
            End SyncLock
        End Function

Comments:

Line 1 - The method receives the ID of the desired item as a parameter. It returns the item if it is found in the ODBC source; otherwise, it returns the reference [nothing].
The [Select] query requesting the item is initialized in lines 5–8
it is executed line 12 - a list of items is obtained
if this list is empty, the reference [nothing] is returned on line 14
Otherwise, the only item in the list is rendered on line 16
Lines 17–20 handle any exceptions

2.3.1.10. The clearAllArticles method

        Public Sub clearAllArticles() Implements IArticlesDao.clearAllArticles
            ' exclusive section
            SyncLock Me
                Try
                    'execute the delete query
                    executeUpdate(deleteAllCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting items: {0}", ex.Message))
                End Try
            End SyncLock
        End Sub

Comments:

Line 1 - The method takes no parameters and returns nothing
Line 6 - The query to delete all items is executed
Lines 7–10: Handle any exceptions

2.3.1.11. The changerStockArticle method

        Public Function changeItemStock(ByVal itemId As Integer, ByVal movement As Integer) As Integer Implements IArticlesDao.changeItemStock
            ' exclusive section
            SyncLock Me
                ' Prepare the stock update request
                With updateStockCommand.Parameters
                    .Clear()
                    .Add(New OdbcParameter("mvt1", transaction))
                    .Add(New OdbcParameter("id", itemId))
                    .Add(New OdbcParameter("mvt2", transaction))
                End With
                'run it
                Try
                    Return executeUpdate(updateStockCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error updating stock [itemId={0}, movement={1}]: [{2}]", itemId, movement, ex.Message))
                End Try
            End SyncLock
        End Function

Comments:

Line 1 - The method receives as parameters the item number for which the stock needs to be modified, as well as the stock increment (positive or negative). It returns the number of rows modified by the operation, i.e., 0 or 1.
Lines 5–10: The [updateStockCommand] query is initialized. Here is the SQL query text:

            updateStockCommand = New OdbcCommand("update ARTICLES set currentStock=currentStock+? where id=? and (currentStock+?)>=0", connection)

Note that the stock is only modified if, after the change, it remains >=0.

The query to update the item's stock is executed on line 13, and the result is returned
lines 14–18, where we handle any exceptions

2.3.2. Generating the [DAO] layer assembly

The Visual Studio project for this new version of the [dao] layer has the following structure:

The project is configured to generate a DLL named [webarticles-dao.dll]:

2.3.3. NUnit tests for the [dao] layer

2.3.3.1. Creating an ODBC-Firebird Data Source in

To test our new [DAO] layer, we need an ODBC data source and therefore a database. We are using the Firebird DBMS (Section 3.5). Using IBExpert (Section 3.6), we create the following article database:

The administrator of this database will be the [SYSDBA] user with the password [masterkey]. We create a few records:

We now create the following Firebird ODBC source (see section 3.7):

The created ODBC source has the following characteristics:

DSN name: odbc-firebird-articles
connection ID: SYSDBA
associated password: masterkey

2.3.3.2. The NUnit test class for the [dao] layer

We have already written a test class for the [dao] layer initially built. If the reader recalls, this class tested not a specific class but the [IArticlesDao] interface:

Imports System
Imports System.Collections
Imports NUnit.Framework
Imports istia.st.articles.dao
Imports System.Threading
Imports Spring.Objects.Factory.Xml
Imports System.IO

Namespace istia.st.articles.tests

    <TestFixture()> _
    Public Class NunitTestArticlesArrayList
        ' the object under test
        Private articlesDao As IArticlesDao

        <SetUp()> _
        Public Sub init()
            ' Retrieve an instance of the Spring object factory
            Dim factory As XmlObjectFactory = New XmlObjectFactory(New FileStream("spring-config.xml", FileMode.Open))
            ' Request instantiation of the articlesdao object
            articlesDao = CType(factory.GetObject("articlesdao"), IArticlesDao)
        End Sub

....

We can see that in the <Setup()> method, we ask Spring for a reference to the singleton named [articlesdao] of type [IArticlesDao], i.e., of the interface type. The [articlesdao] singleton was defined by the following [spring-config.xml] configuration file:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!DOCTYPE objects PUBLIC "-//SPRING//DTD OBJECT//EN"
"http://www.springframework.net/dtd/spring-objects.dtd">

<objects>
    <object id="articlesdao" type="istia.st.articles.dao.ArticlesDaoArrayList, webarticles-dao"/>
</objects>

Let’s demonstrate that the initial test class allows us to test our new [dao] layer without modification or recompilation.

In the Visual Studio folder for our new [dao] layer, create the [tests] folder (shown on the right below) by copying the [bin] folder from the test project of the initial [dao] layer (shown on the left below). If necessary, the reader is invited to review the test project for the first version of the [dao] layer in the first part of the article.

In the [tests] folder, replace the [webarticles-dao.dll] DLL from the old [dao] layer with the [webarticles-dao.dll] DLL from the new [dao] layer
Modify the configuration file [spring-config.xml] to instantiate the new class [ArticlesDaoPlainODBC]:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!DOCTYPE objects PUBLIC "-//SPRING//DTD OBJECT//EN"
"http://www.springframework.net/dtd/spring-objects.dtd">

<objects>
    <object id="articlesdao" type="istia.st.articles.dao.ArticlesDaoPlainODBC, webarticles-dao">
        <constructor-arg index="0">
            <value>odbc-firebird-articles</value>
        </constructor-arg>
        <constructor-arg index="1">
            <value>SYSDBA</value>
        </constructor-arg>
        <constructor-arg index="2">
            <value>masterkey</value>
        </constructor-arg>
    </object>
</objects>

Comments:

line 6, the [articlesdao] object is now associated with an instance of the [ArticlesDaoPlainODBC] class
This class has a constructor with three arguments:
the DSN source name - line 8
the identity used to access the database - line 11
the password associated with this identity - line 14

Here, we are using the information from the ODBC-Firebird source we created earlier.

2.3.3.3. Testing

We are now ready to run the tests. Using the [Nunit-Gui] application, we load the [test-webarticles-dao.dll] DLL from the [tests] folder above and run the [testGetAllArticles] test:

Looking at the screenshot above, we might regret the name [NUnitTestArticlesDaoArrayList] initially given to the test class. It is confusing. It is indeed the [ArticlesDaoPlainODBC] class that is being tested here. The screenshot shows that we have correctly retrieved the articles we placed in the [ARTICLES] table. Now, let’s run all the tests:

In the left window, we see the list of tested methods. The color of the dot preceding each method’s name indicates whether the method passed (green) or failed (red). Readers viewing this document on screen will see that all tests were successful.

2.3.3.4. Conclusion

We have just demonstrated that:

because the NUnit test class referenced not a class but an interface;
because the exact name of the class instantiating the interface was provided in a configuration file and not in the code;
because Spring handled instantiating the class and providing a reference to it in the test code;

therefore, the test code written for the initial [dao] layer remained valid for a new implementation of that same layer. We did not need access to the test class code. We used only its compiled version, the one generated during testing of the initial [dao] layer. We will draw similar conclusions when it comes time to integrate the new [dao] layer into the [webarticles] application.

2.3.4. Integration of the new [dao] layer into the [webarticles] application

2.3.4.1. Integration tests

Recall that the initial version of the [webarticles] application was deployed in the following [runtime] folder:

Readers are encouraged to review Section 2.2.4, which details the deployment procedures for the [webarticles] application. We make the following changes to the contents of the [runtime] folder:

In the [bin] folder, the DLL for the old [dao] layer is replaced by the DLL for the new [dao] layer
in [runtime], the configuration file [web.config] is replaced by a file that accounts for the new implementation class of the [dao] layer:

The new [web.config] configuration file is as follows:

<?xml version="1.0" encoding="iso-8859-1" ?>
<configuration>
    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
        </sectionGroup>
    </configSections>
    <spring>
        <context type="Spring.Context.Support.XmlApplicationContext, Spring.Core">
            <resource uri="config://spring/objects" />
        </context>
        <objects>
            <object id="articlesDao" type="istia.st.articles.dao.ArticlesDaoPlainODBC, webarticles-dao">
                <constructor-arg index="0">
                    <value>odbc-firebird-articles</value>
                </constructor-arg>
                <constructor-arg index="1">
                    <value>SYSDBA</value>
                </constructor-arg>
                <constructor-arg index="2">
                    <value>masterkey</value>
                </constructor-arg>
            </object>
            <object id="articlesDomain" type="istia.st.articles.domain.AchatsArticles, webarticles-domain">
                <constructor-arg index="0">
                    <ref object="articlesDao" />
                </constructor-arg>
            </object>
        </objects>
    </spring>
    <appSettings>
        <add key="urlMain" value="/webarticles/main.aspx" />
        <add key="urlInfos" value="views/infos.aspx" />
        <add key="urlErrors" value="views/errors.aspx" />
        <add key="urlList" value="views/list.aspx" />
        <add key="urlCart" value="views/cart.aspx" />
        <add key="urlEmptyCart" value="views/emptycart.aspx" />
    </appSettings>
</configuration>

Comments:

Lines 14–24 associate the [articlesDao] singleton with an instance of the new [ArticlesDaoPlainODBC] class. This is the only change. We have already encountered this during testing of the new [dao] layer.

We are ready for testing. We configure the [Cassini] web server in the same way as in section 2.2.4. We initialize the [Firebird] product table with the following values:

Make sure that the Cassini web server and the [Firebird] DBMS are running. Using a browser, we request the URL [http://localhost/webarticles/main.aspx]:

Now let’s check the contents of the [ARTICLES] table in the [Firebird] database:

The items [umbrella] and [boots] were purchased, and their stock levels were reduced by the purchased quantity. The item [hat] could not be purchased because the requested quantity exceeded the quantity in stock. We invite the reader to perform additional tests.

2.3.4.2. Conclusion

What did we do?

We reverted to the deployment version of the previous version;
we replaced the [dao] layer DLL with a new version. The [web] and [domain] layer DLLs remained unchanged;
we modified the [web.config] configuration file so that it accounts for the new implementation class of the [dao] layer

All of this is clean and makes it very easy to evolve the web application. These important features are provided by two architectural choices:

access to the layers via interfaces
integration and configuration of the layers by Spring.

We are now proposing a new implementation of the [dao] layer.

2.4. The implementation class [ArticlesDaoSqlServer]

The second implementation of the [dao] layer assumes that the data is in an SQL Server database. Microsoft provides a DBMS called MSDE, which is a limited version of SQL Server. See the appendix for instructions on how to obtain and install it, section 3.12.

2.4.1. The code

The [ArticlesDaoSqlServer] class is very similar to the [ArticlesDaoPlainODBC] class discussed previously. Therefore, we will only highlight the changes made to the previous version:

the required classes are in the [System.Data.SqlClient] namespace instead of the [System.Data.Odbc] namespace
the [OdbcConnection] connection is now of type [SqlConnection]
[OdbcCommand] objects are now of type [SqlCommand]
the syntax of parameterized SQL queries has changed. The insert query now looks like this:

insertCommand = New SqlCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (@id,@name,@price,@currentStock,@minimumStock)", connection)

whereas it was previously:

insertCommand = New OdbcCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (?,?,?,?,?)", connection)

The [addItem] method then becomes the following:

        Public Function addItem(ByVal anItem As Item) As Integer Implements IArticlesDao.addItem
            ' exclusive section
            SyncLock Me
                ' prepare the insert query
                With insertCommand.Parameters
                    .Clear()
                    .Add(New SqlParameter("@id", unArticle.id))
                    .Add(New SqlParameter("@name", unArticle.name))
                    .Add(New SqlParameter("@price", unArticle.price))
                    .Add(New SqlParameter("@sa", unArticle.currentStock))
                    .Add(New SqlParameter("@minStock", unArticle.minStock))
                End With
                Try
                    'execute it
                    Return executeUpdate(insertCommand)
                Catch ex As Exception
                    'Request error'
                    Throw New Exception(String.Format("Error adding item [{0}]: {1}", unArticle.ToString, ex.Message))
                End Try
            End SyncLock
        End Function

The constructor is also modified:

        Public Sub New(ByVal server As String, ByVal databaseName As String, ByVal uid As String, ByVal password As String)
            ' server: name of the SQL Server instance to connect to
            ' databaseName: name of the database to connect to
            ' uid: user ID
            ' password: the user's password

            'retrieve the database name passed as an argument
            Me.databaseName = databaseName
            'we instantiate the connection
            Dim connectString As String = String.Format("Data Source={0};Initial Catalog={1};UID={2};PASSWORD={3}", server, databaseName, uid, password)
            connection = New SqlConnection(connectString)
            ' Prepare the SQL queries
            insertCommand = New SqlCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (@id,@name,@price,@currentStock,@minimumStock)", connection)
...
        End Sub

The constructor now accepts four parameters:

            ' server: name of the SQL Server instance to connect to
            ' databaseName: name of the database to connect to
            ' uid: user ID
            ' password: the user's password

The complete code for the [ArticlesDaoSqlServer] class is as follows:

Imports System
Imports System.Collections
Imports System.Data.SqlClient

Namespace istia.st.articles.dao

    Public Class ArticlesDaoSqlServer
        Implements istia.st.articles.dao.IArticlesDao

        ' private fields
        Private connection As SqlConnection = Nothing
        Private databaseName As String
        Private insertCommand As SqlCommand
        Private updatecommand As SqlCommand
        Private deleteSomeCommand As SqlCommand
        Private selectSomeCommand As SqlCommand
        Private updateStockCommand As SqlCommand
        Private deleteAllCommand As SqlCommand
        Private selectAllCommand As SqlCommand

        ' constructor
        Public Sub New(ByVal server As String, ByVal databaseName As String, ByVal uid As String, ByVal password As String)
            ' server: name of the SQL Server instance to connect to
            ' databaseName: name of the database to connect to
            ' uid: user ID
            ' password: the user's password

            'retrieve the database name passed as an argument
            Me.databaseName = databaseName
            'we instantiate the connection
            Dim connectString As String = String.Format("Data Source={0};Initial Catalog={1};UID={2};PASSWORD={3}", server, databaseName, uid, password)
            connection = New SqlConnection(connectString)
            ' Prepare the SQL queries
            insertCommand = New SqlCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (@id,@name,@price,@currentStock,@minimumStock)", connection)
            updateCommand = New SqlCommand("update ARTICLES set name=@name, price=@price, currentStock=@sa, minimumStock=@sm where id=@id", connection)
            deleteSomeCommand = New SqlCommand("delete from ARTICLES where id=@id", connection)
            selectSomeCommand = New SqlCommand("select id, name, price, currentStock, minimumStock from ARTICLES where id=@id", connection)
            updateStockCommand = New SqlCommand("update ARTICLES set currentStock=currentStock+@mvt where id=@id and (currentStock+@mvt)>=0", connection)
            selectAllCommand = New SqlCommand("select id, name, price, currentStock, minimumStock from ARTICLES", connection)
            deleteAllCommand = New SqlCommand("delete from ARTICLES", connection)
        End Sub

        Public Function addItem(ByVal anItem As Item) As Integer Implements IArticlesDao.addItem
            ' exclusive section
            SyncLock Me
                ' prepare the insert query
                With insertCommand.Parameters
                    .Clear()
                    .Add(New SqlParameter("@id", unArticle.id))
                    .Add(New SqlParameter("@name", unArticle.name))
                    .Add(New SqlParameter("@price", unArticle.price))
                    .Add(New SqlParameter("@sa", unArticle.currentStock))
                    .Add(New SqlParameter("@minStock", unArticle.minStock))
                End With
                Try
                    'execute it
                    Return executeUpdate(insertCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error adding item [{0}]: {1}", unArticle.ToString, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function updateItemStock(ByVal itemId As Integer, ByVal movement As Integer) As Integer Implements IArticlesDao.updateItemStock
            ' exclusive section
            SyncLock Me
                ' prepare the stock update request
                With updateStockCommand.Parameters
                    .Clear()
                    .Add(New SqlParameter("@mvt", movement))
                    .Add(New SqlParameter("@mvt", movement))
                End With
                'execute it
                Try
                    Return executeUpdate(updateStockCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error updating stock [itemId={0}, movement={1}]: [{2}]", itemId, movement, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Sub clearAllArticles() Implements IArticlesDao.clearAllArticles
            ' exclusive section
            SyncLock Me
                Try
                    'execute the insert query
                    executeUpdate(deleteAllCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting items: {0}", ex.Message))
                End Try
            End SyncLock
        End Sub

        Public Function getAllArticles() As System.Collections.IList Implements IArticlesDao.getAllArticles
            ' exclusive section
            SyncLock Me
                Try
                    'execute the SELECT query
                    Dim articles As IList = executeQuery(selectAllCommand)
                    'return the list
                    Return items
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error retrieving items [select id, name, price, currentStock, minimumStock from items]: {0}", ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function getArticleById(ByVal idArticle As Integer) As Article Implements IArticlesDao.getArticleById
            ' exclusive section
            SyncLock Me
                ' prepare the SELECT query
                With selectSomeCommand.Parameters
                    .Clear()
                    .Add(New SqlParameter("@id", idArticle))
                End With
                'execute it
                Try
                    'execute the query
                    Dim articles As IList = executeQuery(selectSomeCommand)
                    'check if the item was found
                    If items.Count = 0 Then Return Nothing
                    'return the item
                    Return CType(articles.Item(0), Article)
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error retrieving item [{0}: {1}", idItem, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function modifyArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.modifyArticle
            ' exclusive section
            SyncLock Me
                ' prepare the update query
                With updatecommand.Parameters
                    .Clear()
                    .Add(New SqlParameter("@name", anItem.name))
                    .Add(New SqlParameter("@price", anItem.price))
                    .Add(New SqlParameter("@quantity", unArticle.currentStock))
                    .Add(New SqlParameter("@minStock", unArticle.minStock))
                    .Add(New SqlParameter("@id", anItem.id))
                End With
                ' execute it
                Try
                    'execute the insert query
                    Return executeUpdate(updatecommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception("Error updating item [" + anItem.ToString + "]", ex)
                End Try
            End SyncLock
        End Function

        Public Function deleteArticle(ByVal articleId As Integer) As Integer Implements IArticlesDao.deleteArticle
            ' exclusive section
            SyncLock Me
                ' prepare the delete query
                With deleteSomeCommand.Parameters
                    .Clear()
                    .Add(New SqlParameter("@id", idArticle))
                End With
                'execute it
                Try
                    'Execute the delete query
                    Return executeUpdate(deleteSomeCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting item [id={0}]: {1}", idArticle, ex.Message))
                End Try
            End SyncLock
        End Function

        Private Function executeQuery(ByVal query As SqlCommand) As IList
            ' execution of a SELECT query 
            ' declare the object that allows access to all rows in the result table
            Dim myReader As SqlDataReader = Nothing
            Try
                ' Create a connection to the database
                connection.Open()
                'Execute the query
                myReader = query.ExecuteReader()
                'declare a list of items to return later
                Dim articles As IList = New ArrayList
                Dim anItem As Item
                While myReader.Read()
                    'We create an article using the values from the reader
                    anItem = New Item
                    unArticle.id = myReader.GetInt32(0)
                    anArticle.name = myReader.GetString(1)
                    anArticle.price = myReader.GetDouble(2)
                    anArticle.currentStock = myReader.GetInt32(3)
                    anItem.minStock = myReader.GetInt32(4)
                    'Add the item to the list
                    items.Add(anItem)
                End While
                'return the result
                Return items
            Finally
                ' release resources
                If Not myReader Is Nothing And Not myReader.IsClosed Then myReader.Close()
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function

        Private Function executeUpdate(ByVal updateCommand As SqlCommand) As Integer
            ' Execute an update query
            Try
                ' Create a connection to the database
                connection.Open()
                'execute the query
                Return updateCommand.ExecuteNonQuery()
            Finally
                ' Release resources
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function
    End Class

End Namespace

The reader is encouraged to review this code in light of the comments on the [ArticlesDaoPlainODBC] class provided earlier.

2.4.2. Generating the [dao] layer assembly

The new Visual Studio project has the following structure:

The project is configured to generate a DLL named [webarticles-dao.dll]:

2.4.3. NUnit tests for the [dao] layer

2.4.3.1. Creating a SQL Server data source

To test our new [dao] layer, we need a SQL Server data source and therefore the SQL Server DBMS. We will actually use the MSDE (Microsoft Data Engine) DBMS (Section 3.12), which is a version of SQL Server limited only by the number of concurrent users it supports. Using [EMS MS SQL Manager] (section 3.14), we create the following product database in an MSDE instance named [portable1_tahe\msde140405]:

The database is owned by the user [mdparticles] with password [admarticles]. The Transact-SQL command to create the [ARTICLES] table is as follows:

CREATE TABLE [ARTICLES] (
  [id] int NOT NULL,
  [name] varchar(20) COLLATE French_CI_AS NOT NULL,
  [price] float(53) NOT NULL,
  [currentStock] int NOT NULL,
  [minStock] int NOT NULL,
  CONSTRAINT [ARTICLES_uq] UNIQUE ([name]),
  PRIMARY KEY ([id]),
  CONSTRAINT [ARTICLES_ck_id] CHECK ([id] > 0),
  CONSTRAINT [ARTICLES_ck_name] CHECK ([name] <> ''),
  CONSTRAINT [ARTICLES_ck_price] CHECK ([price] >= 0),
  CONSTRAINT [ARTICLES_ck_current_stock] CHECK ([current_stock] >= 0),
  CONSTRAINT [ARTICLES_ck_minimum_stock] CHECK ([minimum_stock] >= 0)
)
ON [PRIMARY]
GO

We create a few items:

2.4.3.2. The NUnit test class

The NUnit test class for the implementation class [ArticlesDaoSqlServer] is the same as that for the class [ArticlesDaoPlainODBC] (see section 2.3.3.2). We follow a similar approach to prepare the NUnit test for the class:

we create the [tests] folder (on the right) in the Visual Studio folder of the [dao-sqlserver] project by copying the [tests] folder from the [dao-odbc] project (on the left):

in the [tests] folder of the [dao-sqlserver] project, we replace the [webarticles-dao.dll] DLL with the [webarticles-dao.dll] DLL generated by the [dao-sqlserver] project
We modify the configuration file [spring-config.xml] to instantiate the new class [ArticlesDaoSqlServer]:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!--
<!DOCTYPE objects PUBLIC "-//SPRING//DTD OBJECT//EN"
"http://www.springframework.net/dtd/spring-objects.dtd">
-->
<objects>
    <object id="articlesdao" type="istia.st.articles.dao.ArticlesDaoSqlServer, webarticles-dao">
        <constructor-arg index="0">
            <value>portable1_tahe\msde140405</value>
        </constructor-arg>
        <constructor-arg index="1">
            <value>dbarticles</value>
        </constructor-arg>
        <constructor-arg index="2">
            <value>admarticles</value>
        </constructor-arg>
        <constructor-arg index="3">
            <value>mdparticles</value>
        </constructor-arg>
    </object>
</objects>

Comments:

line 7, the [articlesdao] object is now associated with an instance of the [ArticlesDaoSqlServer] class
This class has a constructor with four arguments:
the name of the MSDE instance used - line 9
the name of the database - line 12
the identity used to access the database - line 15
the password associated with this identity - line 18

Here we are using the information from the MSDE source we created earlier.

2.4.3.3. Testing

We are ready to run the tests. Using the [Nunit-Gui] application, we load the [test-webarticles-dao.dll] DLL from the [tests] folder above and run the [testGetAllArticles] test:

Although the test class was initially named [NUnitTestArticlesDaoArrayList]—a name retained because we are using the [tests-webarticles-dao.dll] DLL derived from this class—it is indeed the [ArticlesDaoSqlserver] class that is being tested here. The screenshot shows that we have correctly retrieved the articles we placed in the [ARTICLES] table. Now, let’s run all the tests:

In the left-hand window, we see the list of tested methods. The color of the dot preceding each method’s name indicates whether the method passed (green) or failed (red). Readers viewing this document on screen will see that all tests were successful.

2.4.4. Integrating the new [dao] layer into the [webarticles] application

We follow the procedure explained in section 2.3.4. We make the following changes to the contents of the [runtime] folder:

In the [bin] folder, the DLL for the old [dao] layer is replaced by the DLL for the new [dao] layer implemented by the [ArticlesDaoSqlServer] class
In [runtime], the configuration file [web.config] is replaced by a file that accounts for the new implementation class:

<?xml version="1.0" encoding="iso-8859-1" ?>
<configuration>
    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
        </sectionGroup>
    </configSections>
    <spring>
        <context type="Spring.Context.Support.XmlApplicationContext, Spring.Core">
            <resource uri="config://spring/objects" />
        </context>
        <objects>
<objects>
    <object id="articlesdao" type="istia.st.articles.dao.ArticlesDaoSqlServer, webarticles-dao">
        <constructor-arg index="0">
            <value>portable1_tahe\msde140405</value>
        </constructor-arg>
        <constructor-arg index="1">
            <value>dbarticles</value>
        </constructor-arg>
        <constructor-arg index="2">
            <value>adarticles</value>
        </constructor-arg>
        <constructor-arg index="3">
            <value>mdparticles</value>
        </constructor-arg>
    </object>
            <object id="articlesDomain" type="istia.st.articles.domain.AchatsArticles, webarticles-domain">
                <constructor-arg index="0">
                    <ref object="articlesDao" />
                </constructor-arg>
            </object>
        </objects>
    </spring>
    <appSettings>
        <add key="urlMain" value="/webarticles/main.aspx" />
        <add key="urlInfos" value="views/infos.aspx" />
        <add key="urlErrors" value="views/errors.aspx" />
        <add key="urlList" value="views/list.aspx" />
        <add key="urlCart" value="views/cart.aspx" />
        <add key="urlEmptyCart" value="views/emptycart.aspx" />
    </appSettings>
</configuration>

Comments:

Lines 15–33 associate the [articlesDao] singleton with an instance of the new [ArticlesDaoSqlServer] class. This is the only change. We have already encountered this during testing of the new [dao] layer

We are ready for testing. We keep the same [Cassini] web server configuration as before. We initialize the [MSDE] product table with the following values:

Make sure that the Cassini web server and the MSDE DBMS (here, the instance portable1_tahe\msde140405) are running. Using a browser, we request the URL [http://localhost/webarticles/main.aspx]:

Now let’s check the contents of the [ARTICLES] table in the [MSDE] database:

The items [soccer ball] and [tennis racket] were purchased, and their stock levels were reduced by the purchased quantity. The item [roller skates] could not be purchased because the requested quantity exceeded the quantity in stock. We invite the reader to perform additional tests.

2.4.5. The implementation class [ArticlesDaoOleDb]

2.4.5.1. OleDb data source

The third implementation of the [dao] layer assumes that the data is in a database accessible via an OleDb driver. The principle behind OleDb sources is analogous to that of ODBC sources. A program using an OleDb source does so via a standard interface common to all OleDb sources. Changing the OleDb source simply involves changing the OleDb driver. The code itself remains unchanged.

You can find out which OleDb drivers are available on your machine using Visual Studio:

display the Server Explorer by selecting [View/Server Explorer]:

To add a new connection, right-click on [Data Connection] and select the [Add Connection] option. This opens a wizard where you can define the connection settings:

The [Provider] pane lists the available OLEDB drivers. For the new [DAO] layer, we will use the [Microsoft Jet 4.0 OLE DB Provider] driver, which provides access to Access databases.
Let’s temporarily exit Visual Studio to create the ACCESS database [articles.mdb] with the following single table:

The table structure is as follows:

`id`	numeric - integer - primary key
`name`	text - 20 characters -
`price`	numeric - double
`current stock`	numeric - integer
`minimum stock`	numeric - integer

Let's go back to Visual Studio and create a new connection as explained earlier:

We select the [Microsoft Jet 4.0] driver and go to the [Connection] panel:

Using the [1] button, select the ACCESS database that was just created, then complete the connection setup by clicking the [Finish] button. The connection you created now appears in the list of available connections:

Double-clicking the [ARTICLES] table gives us access to its contents:

You can then add, modify, or delete rows in the table.
In the Server Explorer, select the new connection to access its Properties sheet:

It is useful to know the connection string. We will use it to connect to the database:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\data\serge\databases\access\articles\articles.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

From this string, we will retain only the following elements:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data\serge\databases\access\articles\articles.mdb;

2.4.5.2. The code for the [ArticlesDaoOleDb] class

The [ArticlesDaoOleDb] class is very similar to the [ArticlesDaoPlainODBC] class discussed earlier. Therefore, we will only list the changes made to the previous version:

the required classes are in the [System.Data.OleDb] namespace instead of the [System.Data.Odbc] namespace
the [OdbcConnection] connection is now of type [OleDbConnection]
[OdbcCommand] objects are now of type [OleDbCommand]

The class constructor accepts a single parameter: the database connection string:

        ' constructor
        Public Sub New(ByVal connectString As String)
            ' connectString: connection string to the OleDb source
            'instantiate the connection
            connection = New OleDbConnection(connectString)
            ' SQL queries are prepared
...
        End Sub

The complete code for the [ArticlesDaoOleDb] class is as follows:

Imports System
Imports System.Collections
Imports System.Data.OleDb

Namespace istia.st.articles.dao

    Public Class ArticlesDaoOleDb
        Implements istia.st.articles.dao.IArticlesDao

        ' private fields
        Private connection As OleDbConnection = Nothing
        Private insertCommand As OleDbCommand
        Private updatecommand As OleDbCommand
        Private deleteSomeCommand As OleDbCommand
        Private selectSomeCommand As OleDbCommand
        Private updateStockCommand As OleDbCommand
        Private deleteAllCommand As OleDbCommand
        Private selectAllCommand As OleDbCommand

        ' constructor
        Public Sub New(ByVal connectString As String)
            ' connectString: connection string to the OleDb source
            'instantiate the connection
            connection = New OleDbConnection(connectString)
            ' prepare the SQL queries
            insertCommand = New OleDbCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (?,?,?,?,?)", connection)
            updateCommand = New OleDbCommand("update ARTICLES set name=?, price=?, currentStock=?, minimumStock=? where id=?", connection)
            deleteSomeCommand = New OleDbCommand("delete from ARTICLES where id=?", connection)
            selectSomeCommand = New OleDbCommand("select id, name, price, currentStock, minimumStock from ARTICLES where id=?", connection)
            updateStockCommand = New OleDbCommand("update ARTICLES set currentStock=currentStock+? where id=? and (currentStock+?)>=0", connection)
            selectAllCommand = New OleDbCommand("select id, name, price, currentStock, minimumStock from ARTICLES", connection)
            deleteAllCommand = New OleDbCommand("delete from ARTICLES", connection)
        End Sub

        Public Function addItem(ByVal anItem As Item) As Integer Implements IArticlesDao.addItem
            ' exclusive section
            SyncLock Me
                ' prepare the insert query
                With insertCommand.Parameters
                    .Clear()
                    .Add(New OleDbParameter("id", anArticle.id))
                    .Add(New OleDbParameter("name", unArticle.name))
                    .Add(New OleDbParameter("price", anItem.price))
                    .Add(New OleDbParameter("currentStock", anItem.currentStock))
                    .Add(New OleDbParameter("minStock", anItem.minStock))
                End With
                Try
                    'execute it
                    Return executeUpdate(insertCommand)
                Catch ex As Exception
                    'request error
                    Throw New Exception(String.Format("Error adding item [{0}]: {1}", unArticle.ToString, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function updateItemStock(ByVal itemId As Integer, ByVal movement As Integer) As Integer Implements IArticlesDao.updateItemStock
            ' exclusive section
            SyncLock Me
                ' prepare the stock update request
                With updateStockCommand.Parameters
                    .Clear()
                    .Add(New OleDbParameter("mvt1", transaction))
                    .Add(New OleDbParameter("id", itemId))
                    .Add(New OleDbParameter("mvt2", transaction))
                End With
                'run it
                Try
                    Return executeUpdate(updateStockCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error updating stock [itemId={0}, movement={1}]: [{2}]", itemId, movement, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Sub clearAllArticles() Implements IArticlesDao.clearAllArticles
            ' exclusive section
            SyncLock Me
                Try
                    'execute the insert query
                    executeUpdate(deleteAllCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting items: {0}", ex.Message))
                End Try
            End SyncLock
        End Sub

        Public Function getAllArticles() As System.Collections.IList Implements IArticlesDao.getAllArticles
            ' exclusive section
            SyncLock Me
                Try
                    'execute the SELECT query
                    Dim articles As IList = executeQuery(selectAllCommand)
                    'returns the list
                    Return articles
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error retrieving items [select id, name, price, currentStock, minimumStock from items]: {0}", ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function getArticleById(ByVal idArticle As Integer) As Article Implements IArticlesDao.getArticleById
            ' exclusive section
            SyncLock Me
                ' prepare the SELECT query
                With selectSomeCommand.Parameters
                    .Clear()
                    .Add(New OleDbParameter("id", idArticle))
                End With
                'execute it
                Try
                    'execute the query
                    Dim articles As IList = executeQuery(selectSomeCommand)
                    'check if the item was found
                    If items.Count = 0 Then Return Nothing
                    'return the item
                    Return CType(articles.Item(0), Article)
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error retrieving item [{0}: {1}", idItem, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function modifyArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.modifyArticle
            ' exclusive section
            SyncLock Me
                ' prepare the update query
                With updatecommand.Parameters
                    .Clear()
                    .Add(New OleDbParameter("name", anItem.name))
                    .Add(New OleDbParameter("price", anItem.price))
                    .Add(New OleDbParameter("currentStock", anItem.currentStock))
                    .Add(New OleDbParameter("minStock", anItem.currentStock))
                    .Add(New OleDbParameter("id", unArticle.id))
                End With
                ' Run it
                Try
                    'Execute the insert query
                    Return executeUpdate(updatecommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception("Error updating item [" + anItem.ToString + "]", ex)
                End Try
            End SyncLock
        End Function

        Public Function deleteArticle(ByVal articleId As Integer) As Integer Implements IArticlesDao.deleteArticle
            ' exclusive section
            SyncLock Me
                ' prepare the delete query
                With deleteSomeCommand.Parameters
                    .Clear()
                    .Add(New OleDbParameter("id", idArticle))
                End With
                'execute it
                Try
                    'execute the delete query
                    Return executeUpdate(deleteSomeCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting item [id={0}]: {1}", idArticle, ex.Message))
                End Try
            End SyncLock
        End Function

        Private Function executeQuery(ByVal query As OleDbCommand) As IList
            ' execution of a SELECT query 
            ' declare the object that allows access to all rows in the result table
            Dim myReader As OleDbDataReader = Nothing
            Try
                ' Create a connection to the database
                connection.Open()
                'Execute the query
                myReader = query.ExecuteReader()
                'declare a list of items to return later
                Dim articles As IList = New ArrayList
                Dim anItem As Item
                While myReader.Read()
                    we prepare an article with the reader's values
                    anArticle = New Article
                    article.id = myReader.GetInt32(0)
                    article.name = myReader.GetString(1)
                    anArticle.price = myReader.GetDouble(2)
                    anArticle.currentStock = myReader.GetInt32(3)
                    anItem.minStock = myReader.GetInt32(4)
                    'Add the item to the list
                    items.Add(anItem)
                End While
                'return the result
                Return items
            Finally
                ' release resources
                If Not myReader Is Nothing And Not myReader.IsClosed Then myReader.Close()
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function

        Private Function executeUpdate(ByVal sqlCommand As OleDbCommand) As Integer
            ' Execute an update query
            Try
                ' Create a connection to the database
                connection.Open()
                'execute the query
                Return sqlCommand.ExecuteNonQuery()
            Finally
                ' Release resources
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function
    End Class

End Namespace

The reader is encouraged to review this code in light of the comments on the [ArticlesDaoPlainODBC] class provided earlier.

2.4.5.3. Generating the [dao] layer assembly

The new Visual Studio project has the following structure:

The project is configured to generate a DLL named [webarticles-dao.dll]:

2.4.5.4. NUnit tests for the [dao] layer

2.4.5.4.1. The NUnit test class

The NUnit test class for the implementation class [ArticlesDaoOleDb] is the same as that for the class [ArticlesDaoPlainODBC] (see section 2.3.3.2). We follow a similar approach to prepare the NUnit test for the class:

we create the [tests] folder (on the right) in the Visual Studio folder of the [dao-oledb] project by copying the [tests] folder from the [dao-odbc] project (on the left):

in the [tests] folder of the [dao-oledb] project, we replace the [webarticles-dao.dll] DLL with the [webarticles-dao.dll] DLL generated by the [dao-oledb] project
We modify the configuration file [spring-config.xml] to instantiate the new class [ArticlesDaoOleDb]:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!--
<!DOCTYPE objects PUBLIC "-//SPRING//DTD OBJECT//EN"
"http://www.springframework.net/dtd/spring-objects.dtd">
-->
<objects>
    <object id="articlesdao" type="istia.st.articles.dao.ArticlesDaoOleDb, webarticles-dao">
        <constructor-arg index="0">
            <value>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data\serge\databases\access\articles\articles.mdb;</value>
        </constructor-arg>
    </object>
</objects>

Comments:

line 7, the [articlesdao] object is now associated with an instance of the [ArticlesDaoOleDb] class
This class has a constructor with one argument: the connection string to the OleDb ACCESS database - line 9

2.4.5.4.2. Tests

We are ready for testing. Using the [Nunit-Gui] application, we load the [test-webarticles-dao.dll] DLL from the [tests] folder above and run the [testGetAllArticles] test:

Despite the name [NUnitTestArticlesDaoArrayList] initially given to the test class, it is indeed the [ArticlesDaoOleDb] class that is being tested here. The screenshot shows that we have correctly retrieved the articles we placed in the [ARTICLES] table. Now, let’s run all the tests:

Readers viewing this document on screen will see that all tests have passed (green color).

2.4.5.5. Integrating the new [dao] layer into the [webarticles] application

We follow the procedure explained in section 2.3.4. We make the following changes to the contents of the [runtime] folder:

In the [bin] folder, the DLL for the old [dao] layer is replaced by the DLL for the new [dao] layer implemented by the [ArticlesDaoOleDb] class
In [runtime], the configuration file [web.config] is replaced by a file that accounts for the new implementation class:

<?xml version="1.0" encoding="iso-8859-1" ?>
<configuration>
    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
        </sectionGroup>
    </configSections>
    <spring>
        <context type="Spring.Context.Support.XmlApplicationContext, Spring.Core">
            <resource uri="config://spring/objects" />
        </context>
        <objects>
            <object id="articlesDao" type="istia.st.articles.dao.ArticlesDaoOleDb, webarticles-dao">
                <constructor-arg index="0">
                    <value>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data\serge\databases\access\articles\articles.mdb;</value>
                </constructor-arg>
            </object>
            <object id="articlesDomain" type="istia.st.articles.domain.AchatsArticles, webarticles-domain">
                <constructor-arg index="0">
                    <ref object="articlesDao" />
                </constructor-arg>
            </object>
        </objects>
    </spring>
    <appSettings>
        <add key="urlMain" value="/webarticles/main.aspx" />
        <add key="urlInfos" value="views/infos.aspx" />
        <add key="urlErrors" value="views/errors.aspx" />
        <add key="urlList" value="views/list.aspx" />
        <add key="urlCart" value="views/cart.aspx" />
        <add key="urlEmptyCart" value="views/emptycart.aspx" />
    </appSettings>
</configuration>

Comments:

Lines 14–18 associate the [articlesDao] singleton with an instance of the new [ArticlesDaoOleDb] class. This is the only change.

We keep the same [Cassini] web server configuration as before. We initialize the product table with the following values:

Make sure the articles database is not being used by a program such as Visual Studio or Access. Using a browser, we request the URL [http://localhost/webarticles/main.aspx]:

Now let's check the contents of the [ARTICLES] table using Access:

The items [pants] and [skirt] were purchased, and their stock levels were reduced by the purchased quantity. The item [coat] could not be purchased because the requested quantity exceeded the quantity in stock. We invite the reader to perform additional tests.

2.5. The implementation class [ArticlesDaoFirebirdProvider]

2.5.1. The Firebird-net-provider

We have previously used a [Firebird] data source via an ODBC driver. While ODBC drivers offer high reusability for the code that uses them, they are less efficient than drivers written specifically for the target DBMS. The [Firebird] DBMS can be used via a library of specific classes that can be downloaded from the Firebird website [http://firebird.sourceforge.net/]. The downloads page offers the following links (April 2005):

The [firebird-net-provider] link is the one to use to download the .NET classes for accessing the Firebird DBMS. Installing the package creates a folder similar to the following:

Two items are of interest to us:

[FirebirdSql.Data.Firebird.dll]: the assembly containing the .NET classes for accessing the Firebird DBMS
[FirebirdNETProviderSDK.chm]: the documentation for these classes

Next, to enable a Visual Studio project to use these classes, we will do two things:

We will place the [FirebirdSql.Data.Firebird.dll] assembly in the project’s [bin] folder
add this same assembly to the project's references

2.5.2. The code for the [ArticlesDaoFirebirdProvider] class

The [ArticlesDaoFirebirdProvider] class is very similar to the [ArticlesDaoSqlServer] class discussed earlier. Therefore, we will only highlight the changes made compared to that version:

The required classes are in the [FirebirdSql.Data.Firebird] namespace instead of the [System.Data.SqlClient] namespace
the [SqlConnection] connection is now of type [FbConnection]
[SqlCommand] objects are now of type [FbCommand]
[SqlParameter] objects are now of type [FbParameter]

The class constructor accepts four parameters, which it uses to construct the connection string to the database:

        ' constructor
        Public Sub New(ByVal server As String, ByVal databaseName As String, ByVal uid As String, ByVal password As String)
            ' server: name of the DBMS host machine
            ' databaseName: path to the database
            ' uid: ID of the user logging in
            ' password: their password
...
        End Sub

The complete code for the [ArticlesDaoFirebirdProvider] class is as follows:

Imports System
Imports System.Collections
Imports FirebirdSql.Data.Firebird

Namespace istia.st.articles.dao

    Public Class ArticlesDaoFirebirdProvider
        Implements istia.st.articles.dao.IArticlesDao

        ' private fields
        Private connection As FbConnection = Nothing
        Private databasePath As String
        Private insertCommand As FbCommand
        Private updatecommand As FbCommand
        Private deleteSomeCommand As FbCommand
        Private selectSomeCommand As FbCommand
        Private updateStockCommand As FbCommand
        Private deleteAllCommand As FbCommand
        Private selectAllCommand As FbCommand

        ' constructor
        Public Sub New(ByVal server As String, ByVal databasePath As String, ByVal uid As String, ByVal password As String)
            ' server: name of the Firebird DBMS host machine
            ' databaseName: path to the database to be used
            ' uid: ID of the user connecting to the database
            ' password: the user's password

            'retrieve the database name passed as an argument
            Me.databasePath = databasePath
            'we instantiate the connection
            Dim connectString As String = String.Format("DataSource={0};Database={1};User={2};Password={3}", server, databasePath, uid, password)
            connection = New FbConnection(connectString)
            ' Preparing SQL queries
            insertCommand = New FbCommand("insert into ARTICLES(id, name, price, currentStock, minimumStock) values (@id,@name,@price,@currentStock,@minimumStock)", connection)
            updatecommand = New FbCommand("update ARTICLES set name=@name, price=@price, currentStock=@sa, minimumStock=@sm where id=@id", connection)
            deleteSomeCommand = New FbCommand("delete from ARTICLES where id=@id", connection)
            selectSomeCommand = New FbCommand("select id, name, price, currentStock, minimumStock from ARTICLES where id=@id", connection)
            updateStockCommand = New FbCommand("update ARTICLES set currentStock=currentStock+@mvt where id=@id and (currentStock+@mvt)>=0", connection)
            selectAllCommand = New FbCommand("select id, name, price, currentStock, minimumStock from ARTICLES", connection)
            deleteAllCommand = New FbCommand("delete from ARTICLES", connection)
        End Sub

        Public Function addItem(ByVal anItem As Item) As Integer Implements IArticlesDao.addItem
            ' exclusive section
            SyncLock Me
                ' prepare the insert query
                With insertCommand.Parameters
                    .Clear()
                    .Add(New FbParameter("@id", anArticle.id))
                    .Add(New FbParameter("@name", unArticle.name))
                    .Add(New FbParameter("@price", anItem.price))
                    .Add(New FbParameter("@sa", unArticle.currentStock))
                    .Add(New FbParameter("@minStock", anItem.minStock))
                End With
                Try
                    'execute it
                    Return executeUpdate(insertCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error adding item [{0}]: {1}", unArticle.ToString, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function updateItemStock(ByVal itemId As Integer, ByVal movement As Integer) As Integer Implements IArticlesDao.updateItemStock
            ' exclusive section
            SyncLock Me
                ' prepare the stock update request
                With updateStockCommand.Parameters
                    .Clear()
                    .Add(New FbParameter("@mvt", movement))
                    .Add(New FbParameter("@id", idArticle))
                End With
                'execute it
                Try
                    Return executeUpdate(updateStockCommand)
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error updating inventory [itemId={0}, movement={1}]: [{2}]", itemId, movement, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Sub clearAllArticles() Implements IArticlesDao.clearAllArticles
            ' exclusive section
            SyncLock Me
                Try
                    'execute the insert query
                    executeUpdate(deleteAllCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting items: {0}", ex.Message))
                End Try
            End SyncLock
        End Sub

        Public Function getAllArticles() As System.Collections.IList Implements IArticlesDao.getAllArticles
            ' exclusive section
            SyncLock Me
                Try
                    'execute the SELECT query
                    Dim articles As IList = executeQuery(selectAllCommand)
                    'return the list
                    Return items
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error retrieving items [select id, name, price, currentStock, minimumStock from items]: {0}", ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function getArticleById(ByVal idArticle As Integer) As Article Implements IArticlesDao.getArticleById
            ' exclusive section
            SyncLock Me
                ' prepare the SELECT query
                With selectSomeCommand.Parameters
                    .Clear()
                    .Add(New FbParameter("@id", idArticle))
                End With
                'execute it
                Try
                    'the query is executed
                    Dim articles As IList = executeQuery(selectSomeCommand)
                    'Check if the item was found
                    If articles.Count = 0 Then Return Nothing
                    'return the item
                    Return CType(articles.Item(0), Article)
                Catch ex As Exception
                    'Query error
                    Throw New Exception(String.Format("Error retrieving item [{0}: {1}", idItem, ex.Message))
                End Try
            End SyncLock
        End Function

        Public Function modifyArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.modifyArticle
            ' exclusive section
            SyncLock Me
                ' prepare the update query
                With updatecommand.Parameters
                    .Clear()
                    .Add(New FbParameter("@name", anItem.name))
                    .Add(New FbParameter("@price", anItem.price))
                    .Add(New FbParameter("@quantity", anItem.currentStock))
                    .Add(New FbParameter("@minStock", anItem.minStock))
                    .Add(New FbParameter("@id", anItem.id))
                End With
                ' execute it
                Try
                    'execute the insert query
                    Return executeUpdate(updatecommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception("Error updating item [" + anItem.ToString + "]", ex)
                End Try
            End SyncLock
        End Function

        Public Function deleteArticle(ByVal articleId As Integer) As Integer Implements IArticlesDao.deleteArticle
            ' exclusive section
            SyncLock Me
                ' prepare the delete query
                With deleteSomeCommand.Parameters
                    .Clear()
                    .Add(New FbParameter("@id", idArticle))
                End With
                'when it is executed
                Try
                    'execute the delete query
                    Return executeUpdate(deleteSomeCommand)
                Catch ex As Exception
                    'query error
                    Throw New Exception(String.Format("Error deleting item [id={0}]: {1}", idArticle, ex.Message))
                End Try
            End SyncLock
        End Function

        Private Function executeQuery(ByVal query As FbCommand) As IList
            ' execution of a SELECT query 
            ' declare the object that allows access to all rows in the result table
            Dim myReader As FbDataReader = Nothing
            Try
                ' Create a connection to the database
                connection.Open()
                'Execute the query
                myReader = query.ExecuteReader()
                'declare a list of items to return later
                Dim articles As IList = New ArrayList
                Dim anItem As Item
                While myReader.Read()
                    'we prepare an article with the values from the reader
                    anItem = New Item
                    unArticle.id = myReader.GetInt32(0)
                    anArticle.name = myReader.GetString(1)
                    anArticle.price = myReader.GetDouble(2)
                    anArticle.currentStock = myReader.GetInt32(3)
                    anItem.minStock = myReader.GetInt32(4)
                    'Add the item to the list
                    items.Add(anItem)
                End While
                'return the result
                Return items
            Finally
                ' release resources
                If Not myReader Is Nothing And Not myReader.IsClosed Then myReader.Close()
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function

        Private Function executeUpdate(ByVal updateCommand As FbCommand) As Integer
            ' Execute an update query
            Try
                ' Create a connection to the database
                connection.Open()
                'execute the query
                Return updateCommand.ExecuteNonQuery()
            Finally
                ' Release resources
                If Not connection Is Nothing Then connection.Close()
            End Try
        End Function
    End Class

End Namespace

The reader is encouraged to review this code in light of the comments on the [ArticlesDaoSqlServer] class provided earlier.

2.5.3. Generating the [dao] layer assembly

The new Visual Studio project has the following structure:

Note the presence of the [FirebirdSql.Data.Firebird.dll] assembly in the project references. This DLL has been placed in the project’s [bin] folder. The project is configured to generate a DLL named [webarticles-dao.dll]:

2.5.4. NUnit tests for the [dao] layer

2.5.4.1. The NUnit test class

The NUnit test class for the implementation class [ArticlesDaoFirebirdProvider] is the same as that for the class [ArticlesDaoPlainODBC] (see section 2.3.3.2). We follow a similar approach to prepare the NUnit test for the class [ArticlesDaoFirebirdProvider]:

we create the [tests] folder (on the right) in the Visual Studio folder of the [dao-firebird-provider] project by copying the [bin] folder from the [dao-odbc] layer’s test project (on the left):

In the [tests] folder, we replace the [webarticles-dao.dll] DLL with the [webarticles-dao.dll] DLL generated from the [dao-firebird-provider] project
We modify the configuration file [spring-config.xml] to instantiate the new class [ArticlesDaoFirebirdProvider]:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!--
<!DOCTYPE objects PUBLIC "-//SPRING//DTD OBJECT//EN"
"http://www.springframework.net/dtd/spring-objects.dtd">
-->
<objects>
    <object id="articlesdao" type="istia.st.articles.dao.ArticlesDaoFirebirdProvider, webarticles-dao">
        <constructor-arg index="0">
            <value>localhost</value>
        </constructor-arg>
        <constructor-arg index="1">
            <value>D:\data\serge\databases\firebird\dbarticles2.gdb</value>
        </constructor-arg>
        <constructor-arg index="2">
            <value>sysdba</value>
        </constructor-arg>
        <constructor-arg index="3">
            <value>masterkey</value>
        </constructor-arg>
    </object>
</objects>

Comments:

line 7, the [articlesdao] object is now associated with an instance of the [ArticlesDaoFirebirdProvider] class
this class has a constructor with four arguments
the DBMS host machine - line 9
the path to the Firebird database - line 12
the login of the user connecting - line 15
their password - line 18

2.5.4.2. Tests

The [ARTICLES] table in the data source is populated with the following items (use IBExpert):

We are ready to run the tests. Using the [Nunit-Gui] application, we load the [test-webarticles-dao.dll] DLL from the [tests] folder above and run the [testGetAllArticles] test:

Despite the name [NUnitTestArticlesDaoArrayList] initially given to the test class, it is indeed the [ArticlesDaoFirebirdProvider] class that is being tested here. The screenshot shows that we have correctly retrieved the articles we placed in the [ARTICLES] table. Now, let’s run all the tests:

Readers viewing this document on screen will see that all tests passed (green). What they cannot see is that the tests ran significantly faster than with the article database accessed via an ODBC driver in our first implementation.

2.5.5. Integrating the new [dao] layer into the [webarticles] application

We follow the procedure already explained twice, notably in section 2.3.4. We make the following changes to the contents of the [runtime] folder:

In the [bin] folder, the DLL for the old [dao] layer is replaced by the DLL for the new [dao] layer implemented by the [ArticlesDaoFirebirdProvider] class. We also place the DLL required by Firebird [FirebirdSql.Data.Firebird.dll] there:

In [runtime], the configuration file [web.config] is replaced with a file that accounts for the new implementation class:

<?xml version="1.0" encoding="iso-8859-1" ?>
<configuration>
    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
        </sectionGroup>
    </configSections>
    <spring>
        <context type="Spring.Context.Support.XmlApplicationContext, Spring.Core">
            <resource uri="config://spring/objects" />
        </context>
        <objects>
            <object id="articlesDao" type="istia.st.articles.dao.ArticlesDaoFirebirdProvider, webarticles-dao">
                <constructor-arg index="0">
                    <value>localhost</value>
                </constructor-arg>
                <constructor-arg index="1">
                    <value>D:\data\serge\databases\firebird\dbarticles2.gdb</value>
                </constructor-arg>
                <constructor-arg index="2">
                    <value>sysdba</value>
                </constructor-arg>
                <constructor-arg index="3">
                    <value>masterkey</value>
                </constructor-arg>
            </object>
            <object id="articlesDomain" type="istia.st.articles.domain.AchatsArticles, webarticles-domain">
                <constructor-arg index="0">
                    <ref object="articlesDao" />
                </constructor-arg>
            </object>
        </objects>
    </spring>
    <appSettings>
        <add key="urlMain" value="/webarticles/main.aspx" />
        <add key="urlInfos" value="views/infos.aspx" />
        <add key="urlErrors" value="views/errors.aspx" />
        <add key="urlList" value="views/list.aspx" />
        <add key="urlCart" value="views/cart.aspx" />
        <add key="urlEmptyCart" value="views/emptycart.aspx" />
    </appSettings>
</configuration>

Comments:

Lines 14–27 associate the [articlesDao] singleton with an instance of the new [ArticlesDaoFirebirdProvider] class. This is the only change.

We are ready for testing. We configure the [Cassini] web server as in the previous tests. We initialize the articles table with the following values:

Using a browser, we enter the URL [http://localhost/webarticles/main.aspx]:

Now let's check the contents of the [ARTICLES] table:

The items [pencil] and [50-sheet pad] were purchased, and their stock levels were reduced by the purchased quantity. The item [fountain pen] could not be purchased because the requested quantity exceeded the quantity in stock. We invite the reader to perform additional tests.

2.5.6. The implementation class [ArticlesDaoSqlMap]

2.5.6.1. The Ibatis SqlMap product

We have written four different implementations of the [dao] layer for our [webarticles] application. In each case, we were able to integrate the new [dao] layer into the [webarticles] application without recompiling the other two layers, [web] and [domain]. This was achieved, as a reminder, through two architectural choices:

accessing the layers via interfaces
integration of the layers using Spring

We want to take this a step further. Although different, our four implementations of the [dao] layer share striking similarities. Once the first implementation was written, the other three were created almost entirely by copy-pasting and substituting certain keywords with others. The logic, however, remained unchanged. One might wonder if it would be possible to have a single implementation that would free us from the various methods of data access. We have used four:

access via an ODBC driver to an ODBC data source
direct access to an SQL Server database
access via an Ole DB driver to an Ole DB data source
direct access to a Firebird database

The Ibatis SqlMap tool [[http://www.ibatis.com/]] enables the development of data access layers that are independent of the actual nature of the data source. Data access is provided through:

configuration files containing information that defines the data source and the operations to be performed on it
a class library that uses this information to access the data

The Ibatis SqlMap tool was initially developed for the Java platform. Its port to the .NET platform is recent and appears to be partially buggy (personal opinion that would require thorough verification). Nevertheless, since the tool has proven itself on the Java platform, it seems worthwhile to present the .NET version.

2.5.6.2. Where can I find the IBATIS SqlMap ?

The main Firebird website is [http://www.ibatis.com/]. The downloads page offers the following links:

Select the [Stable Binaries] link, which takes you to [SourceForge.net]. Follow the download process through to completion. You will receive a ZIP file containing the following files:

In a Visual Studio project using iBatis SqlMap, you need to do two things:

place the above files in the project’s [bin] folder
add a reference to each of these files to the project

2.5.6.3. Ibatis SqlMap configuration files

A [SqlMap] data source will be defined using the following configuration files:

providers.config: defines the class libraries to use for accessing data
sqlmap.config: defines the connection settings
Mapping files: define the operations to be performed on the data

The logic behind these files is as follows:

To access the data, we will need a connection. To represent this, we have already encountered several classes: OdbcConnection, SqlConnection, OleDbConnection, FbConnection. We will also need a [Command] object to issue SQL queries: OdbcCommand, SqlCommand, OleDbCommand, FbCommand, etc. In the [providers.config] file, we define all the classes we need.
The [sqlmap.config] file essentially defines the connection string to the database containing the data. The database connection will be opened by instantiating the [Connection] class defined in [providers.config], whose constructor will be passed the connection string defined in [sqlmap.config].
The mapping files define:
- associations between rows in data tables and .NET classes, whose instances will contain these rows
- the SQL operations to be executed. These are identified by a name. The .NET code executes these operations via their names, which eliminates all SQL code from the .NET code.

2.5.6.4. The configuration files for the [dao-sqlmap] project

Let’s examine the exact nature of SqlMap’s configuration files using an example. We will consider the case where the data source is the Firebird ODBC source from section 2.3.3.1.

2.5.6.4.1. providers.config

The [providers.config] file for an ODBC source is as follows:

<?xml version="1.0" encoding="utf-8" ?> 

<providers>
    <clear/>
    <provider 
        name="Odbc1.1" 
        enabled="true" 
        assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
        connectionClass="System.Data.Odbc.OdbcConnection" 
        commandClass="System.Data.Odbc.OdbcCommand" 
        parameterClass="System.Data.Odbc.OdbcParameter" 
        parameterDbTypeClass="System.Data.Odbc.OdbcType" 
        parameterDbTypeProperty="OdbcType" 
        dataAdapterClass="System.Data.Odbc.OdbcDataAdapter" 
        commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder" 
        usePositionalParameters = "true"
        useParameterPrefixInSql = "false"
        useParameterPrefixInParameter = "false"
        parameterPrefix = "@"
    />
</providers>

Comments:

A [providers.config] file is included with the [SqlMap] package. It offers several standard providers. The code above is taken directly from this file.
A <provider> has a name—line 6—which can be anything
A <provider> can be enabled [enabled=true] or disabled [enabled=false]. If enabled, the DLL referenced on line 8 must be accessible. A [providers.config] file can contain multiple <provider> tags.
line 8 - name of the assembly containing the classes defined on lines 9-15
line 9 - class to use to create a connection
line 10 - class to use to create a [Command] object for executing SQL commands
line 11 - class to use for managing the parameters of a parameterized SQL command
line 12 - enumeration class of possible data types for table fields
line 13 - name of the property of a [Parameter] object that contains the type of this parameter's value
line 14 - name of the [Adapter] class used to create [DataSet] objects from the data source
line 15 - name of the [CommandBuilder] class which, when associated with an [Adapter] object, automatically generates its [InsertCommand, DeleteCommand, UpdateCommand] properties from its [SelectCommand] property
lines 16–19 – define how parameterized SQL commands are handled. Depending on the situation, you might write, for example:

insert into ARTICLES(id,name,price,currentStock,minStock) values (?,?,?,?,?)

or

insert into ARTICLES(id,name,price,currentStock,minStock) values (@id,@name,@price,@currentStock,@minStock)

In the first case, these are formal positional parameters. Their actual values must be provided in the order of the formal parameters. In the second case, these are named parameters. A value is provided for such a parameter by specifying its name. The order no longer matters.

Line 16 - indicates that ODBC sources use positional parameters
Lines 17–19 – concern named parameters. There are none here.

This information allows SqlMap to know, for example, which class it must instantiate to create a connection. Here, it will be the [OdbcConnection] class (line 9).

2.5.6.4.2. sqlmap.config

The [providers.config] file defines the classes to use for accessing an ODBC source. It does not specify any ODBC sources. The [sqlmap.config] file does that:

<?xml version="1.0" encoding="utf-8" ?>
<sqlMapConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Schemas\SqlMapConfig.xsd">
    <properties resource="properties.xml"/>
    <settings>
        <setting useStatementNamespaces="false" />
        <setting cacheModelsEnabled="false" />
    </settings>
    <!-- ==== data source =========    -->
    <database>
        <provider name="${provider}"/>
        <dataSource name="sqlmaparticles"  connectionString="${connectionString}"/>
        <transactionManager type="ADO/SWC" />
    </database>
    <sqlMaps>
        <sqlMap resource="articles.xml" />
    </sqlMaps>
</sqlMapConfig>

Comments:

Line 3 - We define a properties file [properties.xml]. This file defines key-value pairs. The keys can be anything. The value associated with a key C is obtained using the notation ${C} in [sqlmap.config]. Here is the [properties.xml] file that will be associated with the previous [sqlmap.config] file:

<?xml version="1.0" encoding="utf-8" ?>
<settings>
    <add key="provider" value="Odbc1.1" />
    <add key="connectionString" value="DSN=odbc-firebird-articles;UID=SYSDBA;PASSWORD=masterkey" />
</settings>

Line 3 - the [provider] key is defined. Its value is the name of the <provider> tag to be used in [providers.config]

line 4 - the [connectionString] key is defined. Its value is the connection string to use to open a connection to the Firebird ODBC data source.

lines 4-7 - configuration parameters:
- line 5 - SQL queries will be identified by a name that may itself be part of a namespace. [useStatementNamespaces="false"] indicates that namespaces will not be used.
- line 6 - SqlMap has various caching strategies to minimize access to the data source. [cacheModelsEnabled="false"] indicates that none will be used.
Lines 9–13 – The data source properties are defined:
- line 10 - name of the <provider> from [providers.config] to use
- Line 11 - Connection string to the data source
- line 12 - transaction manager. We did not use it here, but left the line in because it was in the standard distribution file.
lines 14-16 - list of files defining the SQL operations to be performed on the data source.
line 15 - defines the mapping file [articles.xml]

2.5.6.4.3. articles.xml

This file serves two purposes:

Defining an object mapping for the data source tables. In the simplest cases, this amounts to associating a class with a row in a table.
defining parameterized SQL operations and naming them.

We will use the following [articles.xml] file:

<?xml version="1.0" encoding="iso-8859-1" ?>
<sqlMap namespace="Articles" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="SqlMap.xsd">
    <!-- the resultMap -->
    <resultMaps>
        <resultMap id="article" class="istia.st.articles.dao.Article">
            <result property="id" column="ID" />
            <result property="name" column="NAME" />
            <result property="price" column="PRICE" />
            <result property="currentStock" column="CURRENTSTOCK" />
            <result property="minStock" column="MINSTOCK" />
        </resultMap>
    </resultMaps>
    <!-- SQL queries -->
    <statements>
        <!-- retrieving all articles -->
        <select id="getAllArticles" resultMap="article">
                select ID, NAME, PRICE, CURRENTSTOCK, MINIMUMSTOCK FROM ARTICLES
            </select> 
        <!-- delete all items -->
        <delete id="clearAllArticles">
                delete from ARTICLES
            </delete> 
        <!-- insert an article -->
        <insert id="insertArticle" parameterClass="istia.st.articles.dao.Article">
                insert into ARTICLES (id, name, price,currentStock, minimumStock) values
                ( #id# , #name# , #price# , #currentStock# , #minStock# )
            </insert>
        <!-- delete an item -->
        <delete id="deleteArticle" parameterClass="int">
                delete FROM ARTICLES where ID= #value#
            </delete>
        <!-- edit an article -->
        <update id="modifyArticle" parameterClass="istia.st.articles.dao.Article">
                update ARTICLES set NAME= #name# ,PRICE= #price# ,CURRENTSTOCK= #currentstock# ,MINIMUMSTOCK= #minimumstock# where ID= #id#
            </update>
        <!-- search for a specific item -->
        <select id="getArticleById" resultMap="article" parameterClass="int">
                select ID, NAME, PRICE,    CURRENTSTOCK, MINIMUMSTOCK FROM ARTICLES where ID= #value#
            </select>
        <!-- Change an item's stock -->
        <update id="changeItemStock" parameterClass="Hashtable">
                update ARTICLES set CURRENTSTOCK=(CURRENTSTOCK + #movement#) where ID=#id# and ((CURRENTSTOCK + #movement#) >=0)
      </update>
    </statements>
</sqlMap>

Comments:

Lines 4-11 - We define a mapping between a row in the [ARTICLES] table of the data source and the [istia.st.articles.dao.Article] class. Each column of the table is associated with a property of the [Article] class. This mapping allows [SqlMap] to construct the result of an SQL SELECT operation. Each result row from the SELECT will be placed in an [Article] object according to the mapping rules.
Line 5 - The mapping is enclosed in a <resultMap> tag and is named using the [id="article"] attribute. The associated class is specified by the [class="istia.st.articles.dao.Article"] attribute.
Lines 14–44 – The required SQL operations are defined
Lines 16–18 – A SELECT operation named [getAllArticles] is defined
- line 16 - the SELECT operation is named [name="getAllArticles"] and the mapping to use is defined by the attribute [resultMap="article"]. This refers to the mapping in lines 5–11
- line 17 - text of the SQL command to be executed
Lines 20–22 – We define the SQL-Delete command [clearAllArticles] to clear the articles table.
Lines 24–27 – We define the SQL-Insert command [insertArticle] to add a new item to the items table. This is a parameterized query using the elements (#id#, #name#, #price#, #currentStock#, #minStock#). The values for these five elements will come from an [Article] object passed as a parameter: [parameterClass="istia.st.articles.dao.Article"]. The parameter object must have the properties (id, name, price, currentStock, minimumStock) referenced by the parameterized SQL command.
lines 29-31 - we define the SQL Delete command [deleteArticle] intended to delete an item whose number #value# is known. This number will be passed as a parameter: [parameterClass="int"]. This is a general rule. When the parameter is unique, it is referenced by the keyword #value# in the SQL command text.
Lines 33–35 – We define the SQL-Update command [modifyArticle] to modify an item whose number is known. As with the [insertArticle] command, the five required pieces of information will come from the properties of an [istia.st.articles.dao.Article] object.
lines 37-39 - we define the SQL-Select command [getArticleById], which retrieves the record for an item whose number is known.
Lines 41–43 - We define the SQL-Update command [changerStockArticle], which modifies the [stockactuel] field of an item whose number is known. The two required pieces of information—the item’s #id# and the stock increment #mouvement#—will be found in a dictionary: [parameterClass="Hashtable"]. This dictionary must have two keys: id and mouvement. The values associated with these two keys will be used in the SQL command.

2.5.6.4.4. Location of configuration files

We will consider two different scenarios:

In the case of a Nunit test, the [SqlMap] configuration files will be placed in the same folder as the tested binaries.
In the case of a web application, they will be placed in the application root.

2.5.6.5. The SqlMap API

The SqlMap classes are contained in DLLs that are typically placed in the application’s [bin] folder:

Applications using SqlMap classes must import the [IBatisNet.DataMapper] namespace:

Imports IBatisNet.DataMapper

All SQL operations are performed through a singleton of type [Mapper], a class in the [IBatisNet.DataMapper] namespace. The singleton is obtained as follows:

        Dim mapper As SqlMapper = Mapper.Instance

To execute the SqlMap command [getAllArticles], we write:

                    dim articles as IList = mapper.QueryForList("getAllArticles", Nothing)

The [QueryForList] method returns the result of a SELECT command as a list
The first parameter is the name of the SQL command to be executed (see articles.xml)
The second parameter is the parameter to pass to the SQL query. It must correspond to the [parameterClass] attribute of the SqlMap command. In [articles.xml], we have [parameterClass=Nothing]. Therefore, we pass a null pointer here.
The result is of type IList. The objects in this list are specified by the [resultMap] attribute of the SQL-select command: [resultMap="article"]. "article" is a mapping name:

<resultMap id="article" class="istia.st.articles.dao.Article">

The class associated with this mapping is [istia.st.articles.dao.Article]. Ultimately, the [articles] variable defined above is a list of [istia.st.articles.dao.Article] objects. We have thus retrieved the entire [ARTICLES] table in a single statement. If the [ARTICLES] table is empty, we get an [IList] object with 0 elements.

To execute the SqlMap command [getArticleById], we write:

dim anArticle as Article = CType(mapper.QueryForObject("getArticleById", idArticle), Article)

The [QueryForObject] method retrieves the result of a SELECT command that returns only one row
The first parameter is the name of the SqlMap command to execute
The second parameter is the parameter to pass to the SQL query. It must correspond to the [parameterClass] attribute of the SqlMap command. In [articles.xml], we have [parameterClass="int"]. Therefore, we pass an integer here representing the ID of the article being searched for.
The result is of type Object. If the SELECT returned no rows, the result is a null pointer (nothing).

To execute the SqlMap [insertArticle] command, we write:

                    mapper.Insert("insertArticle", anArticle)

The [Insert] method allows you to execute SQL INSERT commands
The first parameter is the name of the SqlMap command to execute
The second parameter is the parameter to be passed to it. It must correspond to the [parameterClass] attribute of the SqlMap command. In [articles.xml], we have [parameterClass="istia.st.articles.dao.Article"]. Therefore, we pass an object of type [istia.st.articles.dao.Article] here.

To execute the SqlMap command [deleteArticle], we write:

                    dim nbArticles as Integer = mapper.Delete("deleteArticle", idArticle)

The [Delete] method allows you to execute SQL DELETE commands
The first parameter is the name of the SQL command to execute
The second parameter is the parameter to be passed to it. It must correspond to the [parameterClass] attribute of the SqlMap command. In [articles.xml], we have [parameterClass="int"]. Therefore, we pass the ID of the article to be deleted here.
The result of the [Delete] method is the number of rows deleted

Similarly, to execute the SqlMap command [clearAllArticles], we write:

                    dim nbArticles as Integer = mapper.Delete("clearAllArticles", nothing)

To execute the SqlMap command [modifyArticle], we write:

                    dim nbArticles as Integer = mapper.Update("modifyArticle", anArticle)

The [Update] method allows you to execute SQL UPDATE commands
The first parameter is the name of the SqlMap command to execute
The second parameter is the parameter to be passed to it. It must correspond to the [parameterClass] attribute of the SqlMap command. In [articles.xml], we have [parameterClass="istia.st.articles.dao.Article"]. Therefore, we pass an object of type [istia.st.articles.dao.Article] here.
The result of the [Update] method is the number of rows modified.

Similarly, to execute the SqlMap command [changerStockArticle], we would write:

                    Dim parameters As New Hashtable(2)
                    parameters("id") = itemId
                    parameters("movement") = movement
                    ' update
                    dim nbLines as Integer = mapper.Update("changeItemStock", parameters)

The second parameter corresponds to the [parameterClass] attribute of the SqlMap command. In [articles.xml], we have [parameterClass="Hashtable"]. The parameterized SQL command [changeItemStock] uses the parameters [id, movement]. Therefore, we pass a dictionary with these two keys here.

2.5.6.6. The code for the [ArticlesDaoSqlMap] class

Following the previous explanations, we are now able to write the following new implementation class [ArticlesDaoSqlMap]:

Option Explicit On 
Option Strict On

Imports System
Imports IBatisNet.DataMapper
Imports System.Collections

Namespace istia.st.articles.dao

    Public Class ArticlesDaoSqlMap
        Implements IArticlesDao

        ' private fields
        Dim mapper As SqlMapper = Mapper.Instance

        ' list of all articles
        Public Function getAllArticles() As IList Implements IArticlesDao.getAllArticles
            SyncLock Me
                Try
                    Return mapper.QueryForList("getAllArticles", Nothing)
                Catch ex As Exception
                    Throw New Exception("Failed to retrieve all articles: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Function

        ' Add an item
        Public Function addItem(ByVal anItem As Item) As Integer Implements IArticlesDao.addItem
            SyncLock Me
                Try
                    ' unArticle: article to add
                    ' insertion
                    mapper.Insert("insertArticle", unArticle)
                    Return 1
                Catch ex As Exception
                    Throw New Exception("Failed to add article [" + anArticle.ToString + "]: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Function

        ' Deletes an item
        Public Function deleteItem(ByVal itemId As Integer) As Integer Implements IArticlesDao.deleteItem
            SyncLock Me
                Try
                    ' id: ID of the item to be deleted
                    ' deletion
                    Return mapper.Delete("deleteArticle", idArticle)
                Catch ex As Exception
                    Throw New Exception("Error deleting item with ID [" + idArticle.ToString + "]: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Function

        ' Modifies an item
        Public Function modifyArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.modifyArticle
            SyncLock Me
                Try
                    ' update
                    Return mapper.Update("modifyArticle", anArticle)
                Catch ex As Exception
                    Throw New Exception("Error updating article [" + anArticle.ToString + "]: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Function

        ' Search for an article
        Public Function getArticleById(ByVal idArticle As Integer) As Article Implements IArticlesDao.getArticleById
            SyncLock Me
                Try
                    ' id: ID of the article being searched for
                    Return CType(mapper.QueryForObject("getArticleById", idArticle), Article)
                Catch ex As Exception
                    Throw New Exception("Error retrieving the article with id [" + idArticle.ToString + "]: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Function

        ' Delete all items
        Public Sub clearAllArticles() Implements IArticlesDao.clearAllArticles
            SyncLock Me
                Try
                    mapper.Delete("clearAllArticles", Nothing)
                Catch ex As Exception
                    Throw New Exception("Error clearing the items table: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Sub

        ' Change the stock of an item
        Public Function changeItemStock(ByVal itemId As Integer, ByVal movement As Integer) As Integer Implements IArticlesDao.changeItemStock
            SyncLock Me
                Try
                    ' id: ID of the item whose stock is being changed
                    ' movement: stock movement
                    Dim parameters As New Hashtable(2)
                    parameters("id") = articleId
                    parameters("movement") = movement
                    ' update
                    Return mapper.Update("changeItemStock", parameters)
                Catch ex As Exception
                    Throw New Exception(String.Format("Error updating stock [{0},{1}]: {2}", itemId, movement, ex.ToString))
                End Try
            End SyncLock
        End Function
    End Class
End Namespace

Readers are encouraged to review this code in light of the explanations provided for the SqlMap API. It is worth noting that using [SqlMap] has significantly reduced the amount of code required.

2.5.6.7. Generating the [dao] layer assembly

The new Visual Studio project has the following structure:

Note the presence of the "assemblies" required by SqlMap in the project references. These DLLs have been placed in the project's [bin] folder. The project is configured to generate a DLL named [webarticles-dao.dll]:

2.5.6.8. NUnit tests for the [dao] layer

2.5.6.8.1. The NUnit test class

The NUnit test class for the implementation class [ArticlesDaoSqlMap] is the same as that for the class [ArticlesDaoPlainODBC] (see section 2.3.3.2). We follow a similar approach to prepare the NUnit test for the class [ArticlesDaoSqlMap]:

we create the [test1] folder (on the right) in the Visual Studio folder of the [dao-sqlmap] project by copying the [tests] folder from the [dao-odbc] project (on the left):

In the [tests] folder, we replace the [webarticles-dao.dll] DLL with the [webarticles-dao.dll] DLL generated from the [dao-sqlmap] project.
We add the DLLs required by SqlMap as well as the configuration files discussed [providers.config, sqlmap.config, properties.xml, articles.xml].
We modify the configuration file [spring-config.xml] to instantiate the new class [ArticlesDaoSqlMap]:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!--
<!DOCTYPE objects PUBLIC "-//SPRING//DTD OBJECT//EN"
"http://www.springframework.net/dtd/spring-objects.dtd">
-->
<objects>
    <object id="articlesdao" type="istia.st.articles.dao.ArticlesDaoSqlMap, webarticles-dao"/>
</objects>

Comments:

Line 7: The [articlesdao] object is now associated with an instance of the [ArticlesDaoSqlMap] class
This class has no constructor. The default constructor will be used.

2.5.6.8.2. Tests

The [ARTICLES] table in the Firebird data source is populated with the following articles:

We are ready for testing. Using the [Nunit-Gui] application, we load the [test-webarticles-dao.dll] DLL from the [test1] folder above and run the [testGetAllArticles] test:

Despite the name [NUnitTestArticlesDaoArrayList] initially given to the test class, it is indeed the [ArticlesDaoSqlMap] class that is being tested here. The screenshot shows that we have correctly retrieved the articles we placed in the [ARTICLES] table. Now, let’s run all the tests:

Readers viewing this document on screen will see that some tests passed (green) but others failed (red). The tests that failed are [testArticleAbsent] and [testChangerStockArticle]. After extensive investigation, it appears that the causes of these failures are as follows:

In [testArticleAbsent], we attempt to modify an item that does not exist. We use the [modifieArticle] method for this, which returns the number of modified rows as 0 or 1. Here, we should get 0. Instead, we get an exception of type [IBatisNet.Common.Exceptions.ConcurrentException].
In [changerStockArticle], there is another operation of type [update]. This involves decrementing stock by an amount greater than the current stock. To do this, the [changerStockArticle] method is used, which returns the number of modified rows as 0 or 1. The SQL command was written to prevent an update (see the "changerStockArticle" SQL command in articles.xml) that would result in a negative stock level. Here, we expect to get 0 as the result of the [changerStockArticle] method. Again, we get an exception of type [IBatisNet.Common.Exceptions.ConcurrentException].

There are many possible sources of error:

the code in the [ArticlesDaoSqlMap] class is incorrect. This is possible. However, it comes from a port of a Java class that had worked correctly with the Java version of SqlMap.
the .NET version of SqlMap is buggy
the Firebird ODBC driver is buggy
...

In the absence of certainty, we will work around the issue by catching the [IBatisNet.Common.Exceptions.ConcurrentException]. The new code for the [ArticlesDaoSqlMap] class becomes the following:

....
Namespace istia.st.articles.dao

    Public Class ArticlesDaoSqlMap
        Implements IArticlesDao

        ' private fields
        Dim mapper As SqlMapper = Mapper.Instance

        ' list of all articles
        Public Function getAllArticles() As IList Implements IArticlesDao.getAllArticles
...
        End Function

        ' Add an item
        Public Function addArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.addArticle
...
        End Function

        ' Deletes an item
        Public Function deleteItem(ByVal itemId As Integer) As Integer Implements IArticlesDao.deleteItem
            SyncLock Me
                Try
                    ' id: ID of the item to be deleted
                    ' deletion
                    Return mapper.Delete("deleteArticle", idArticle)
                Catch ex As Exception
                    If ex.GetType.Equals(GetType(IBatisNet.Common.Exceptions.ConcurrentException)) Then Return 0
                    Throw New Exception("Error deleting article with id [" + idArticle.ToString + "]: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Function

        ' Modifies an item
        Public Function modifyArticle(ByVal anArticle As Article) As Integer Implements IArticlesDao.modifyArticle
            SyncLock Me
                Try
                    ' update
                    Return mapper.Update("modifyArticle", anArticle)
                Catch ex As Exception
                    If ex.GetType.Equals(GetType(IBatisNet.Common.Exceptions.ConcurrentException)) Then Return 0
                    Throw New Exception("Error updating article [" + anArticle.ToString + "]: [" + ex.ToString + "]")
                End Try
            End SyncLock
        End Function

        ' Search for an article
        Public Function getArticleById(ByVal idArticle As Integer) As Article Implements IArticlesDao.getArticleById
...
        End Function

        ' Delete all articles
        Public Sub clearAllArticles() Implements IArticlesDao.clearAllArticles
....
        End Sub

        ' Change the stock of an item
        Public Function changeItemStock(ByVal itemId As Integer, ByVal movement As Integer) As Integer Implements IArticlesDao.changeItemStock
            SyncLock Me
                Try
                    ' id: ID of the item whose stock is being changed
                    ' movement: stock movement
                    Dim parameters As New Hashtable(2)
                    parameters("id") = itemId
                    parameters("movement") = movement
                    ' update
                    Return mapper.Update("changeItemStock", parameters)
                Catch ex As Exception
                    If ex.GetType.Equals(GetType(IBatisNet.Common.Exceptions.ConcurrentException)) Then Return 0
                    Throw New Exception(String.Format("Error changing stock [{0},{1}]: {2}", itemId, movement, ex.ToString))
                End Try
            End SyncLock
        End Function
    End Class
End Namespace

The changes are on lines: 28, 41, 69. For SQL operations of type [UPDATE, DELETE], if an exception of type [IBatisNet.Common.Exceptions.ConcurrentException] occurs, 0 is returned as the result, thereby indicating that no rows were modified or deleted. Once this is done, the project’s DLL is regenerated, placed in the [test1] folder, and the NUnit tests are rerun:

This time it works. We will now work with this DLL.

2.5.6.9. Integrating the new [dao] layer into the [webarticles] application

2.5.6.9.1. ODBC data source

Here we test the ODBC data source discussed in section 2.3.3.1. It is used here via SqlMap.

We follow the procedure described in section 2.3.4. We make the following changes to the contents of the [runtime] folder:

In the [bin] folder, the DLL for the old [dao] layer is replaced by the DLL for the new [dao] layer implemented by the [ArticlesDaoSqlMap] class. We add the DLLs required for Firebird and SqlMap:

in [runtime], we place the SqlMap configuration files [providers.config, sqlmap.config, properties.xml, articles.xml]:

In [runtime], the configuration file [web.config] is replaced by a file that accounts for the new implementation class:

<?xml version="1.0" encoding="iso-8859-1" ?>
<configuration>
    <configSections>
        <sectionGroup name="spring">
            <section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
            <section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
        </sectionGroup>
    </configSections>
    <spring>
        <context type="Spring.Context.Support.XmlApplicationContext, Spring.Core">
            <resource uri="config://spring/objects" />
        </context>
        <objects>
            <object id="articlesDao" type="istia.st.articles.dao.ArticlesDaoSqlMap, webarticles-dao"/>
            <object id="articlesDomain" type="istia.st.articles.domain.AchatsArticles, webarticles-domain">
                <constructor-arg index="0">
                    <ref object="articlesDao" />
                </constructor-arg>
            </object>
        </objects>
    </spring>
    <appSettings>
        <add key="urlMain" value="/webarticles/main.aspx" />
        <add key="urlInfos" value="views/infos.aspx" />
        <add key="urlErrors" value="views/errors.aspx" />
        <add key="urlList" value="views/list.aspx" />
        <add key="urlCart" value="views/cart.aspx" />
        <add key="urlEmptyCart" value="views/emptycart.aspx" />
    </appSettings>
</configuration>

Comments:

Lines 14 associate the [articlesDao] singleton with an instance of the new [ArticlesDaoSqlMap] class. This is the only change.

We're ready to run the tests. We'll configure the [Cassini] web server as we did in the previous tests. We'll populate the articles table with the following values:

Using a browser, we request the URL [http://localhost/webarticles/main.aspx]:

Now let’s check the contents of the [ARTICLES] table:

The items [knife] and [spoon] were purchased, and their stock levels were reduced by the purchased quantity. The item [fork] could not be purchased because the requested quantity exceeded the quantity in stock. We invite the reader to perform additional tests.

2.5.6.9.2. MSDE data source

Here we are testing the MSDE data source discussed in section 2.4.3.1. It is used here via SqlMap. We follow the same procedure as before We make the following changes to the contents of the [runtime] folder:

the contents of the [bin] folder remain unchanged
In [runtime], the SqlMap configuration files [providers.config, properties.xml] change. The configuration files [sqlmap.config, articles.xml] remain unchanged.
The [providers.config] file configures a new <provider>:

<?xml version="1.0" encoding="utf-8" ?> 

<providers>
    <clear/>
    <provider
        name="sqlServer1.1"
        assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.SqlClient.SqlConnection"
        commandClass="System.Data.SqlClient.SqlCommand"
        parameterClass="System.Data.SqlClient.SqlParameter"
        parameterDbTypeClass="System.Data.SqlDbType"
        parameterDbTypeProperty="SqlDbType"
        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
        commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"    
        usePositionalParameters = "false"    
        useParameterPrefixInSql = "true"
        useParameterPrefixInParameter = "true"                
        parameterPrefix="@"
    />        
</providers>

This <provider> uses the .NET classes for accessing SQL Server data sources. It is included by default in the [providers.config] template file distributed with SqlMap.

The [properties.xml] file defines the <provider> for the MSDE source as well as its connection string:

<?xml version="1.0" encoding="utf-8" ?> 
<settings>
    <add key="provider" value="sqlServer1.1" />
    <add 
        key="connectionString" 
        value="Data Source=portable1_tahe\msde140405;Initial Catalog=dbarticles;UID=admarticles;PASSWORD=mdparticles;"/>
</settings>

In [runtime], the [web.config] configuration file remains unchanged.

We are ready for testing. The [Cassini] web server retains its usual configuration. We initialize the articles table in the MSDE source using [EMS MS SQL Manager]:

Using a browser, we request the URL [http://localhost/webarticles/main.aspx]:

Now let’s check the contents of the [ARTICLES] table using [EMS MS SQL Manager]:

The items [soccer ball] and [tennis racket] were purchased, and their stock levels were reduced by the purchased quantity. The item [roller skates] could not be purchased because the requested quantity exceeded the quantity in stock. We invite the reader to perform additional tests.

2.5.6.9.3. OleDb data source

Here we are testing the ACCESS data source presented in section 2.4.5.1. It is used here via SqlMap. We follow the same procedure as before We make the following changes to the contents of the [runtime] folder:

the contents of the [bin] folder remain unchanged
In [runtime], the SqlMap configuration files [providers.config, properties.xml] change. The configuration files [sqlmap.config, articles.xml] remain unchanged.
The [providers.config] file configures a new <provider>:

<?xml version="1.0" encoding="utf-8" ?> 

<providers>
    <clear/>
    <provider 
        name="OleDb1.1" 
        enabled="true" 
        assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
        connectionClass="System.Data.OleDb.OleDbConnection" 
        commandClass="System.Data.OleDb.OleDbCommand" 
        parameterClass="System.Data.OleDb.OleDbParameter" 
        parameterDbTypeClass="System.Data.OleDb.OleDbType" 
        parameterDbTypeProperty="OleDbType" 
        dataAdapterClass="System.Data.OleDb.OleDbDataAdapter" 
        commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder" 
        usePositionalParameters = "true"
        useParameterPrefixInSql = "false"
        useParameterPrefixInParameter = "false"
        parameterPrefix = ""
    />
</providers>

This <provider> uses the .NET classes for accessing OleDb data sources. It is included by default in the [providers.config] template file distributed with SqlMap.

The [properties.xml] file defines the OleDb source <provider> and its connection string:

<?xml version="1.0" encoding="utf-8" ?> 
<settings>
    <add key="provider" value="OleDb1.1" />
    <add 
        key="connectionString" 
        value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data\serge\databases\access\articles\articles.mdb;"/>
</settings>

In [runtime], the [web.config] configuration file remains unchanged.

We are ready for testing. The [Cassini] web server retains its usual configuration. We initialize the articles table from the ACCESS source as follows:

Using a browser, we request the URL [http://localhost/webarticles/main.aspx]:

Now let’s check the contents of the [ARTICLES] table with:

The items [pants] and [skirt] were purchased, and their stock levels were reduced by the purchased quantity. The item [coat] could not be purchased because the requested quantity exceeded the quantity in stock. We invite the reader to perform additional tests.

2.5.7. Conclusion

We conclude this long tutorial article here. What have we done?

We have implemented the [DAO] layer of a three-tier web application in four different ways:
1. by using .NET access classes to ODBC sources
2. by using .NET access classes for SQL Server sources
3. by using .NET access classes for OleDb sources
4. by using third-party access classes to access a Firebird database
Each time, we integrated the new [DAO] layer into the three-tier [webarticles] application [web, domain, DAO] without recompiling any of the [web, domain] layers
We finally introduced the [SqlMap] tool, which allowed us to create a [DAO] layer capable of adapting to different data sources transparently to the code. Thus, with this new layer, we were able to successively use the data sources from the previous implementations 1 through 3. This was done transparently using configuration files.
We demonstrated the great flexibility that the Spring and SqlMap tools bring to three-tier web applications.