Data Access in Vulcan.Net

Introduction

Since a while I am a member of the VOPS program from Grafx Software. With this membership you have access to the Beta Version of Vulcan.Net. In the previous period I have developed a demo application that is fully operational with database access to a SQL server database. In a number of articles I will describe my experiences and the solutions I have developed. In this first article I will give a description of the application and the desired functionality. In later articles I will add functionality and describe the reason for it.

With every article a demo application is available from my website (www.dla-os.nl) Although the website is in Dutch, the articles are written in English and should be easy to find and follow.

Application

The application is an example that we use for our layered architecture framework. It is a fun application named Spam-maker in which people can subscribe or unsubscribe to various types of spam. We use only the member part which is sufficient for the Vulcan.Net demo. In the figure below you will get an idea about the structure of the application. It consists of a number of forms in which you can alter data retrieved from a SQL-server database. In the sample file a create script for the table is added.

As you can see the structure of the application is based on two technologies: inheritance and delegation. For a sample application this will give a good idea about the possibilities of the application. There are a number of entry forms that fetch data from the database.

In the application we have two layers: the data access layer and a domain layer with only one class (the DLAMember class). Delegation is applied to make communication possible between the forms and the domain classes and for communication between the two layers. Furthermore the application is based on the .NET Framework only, not on extra DLL- or COM-functionality.

Data Access Class

In the data access class we handle all the statements sent to the SQL server database. It is a class in the lowest layer, handling the SQL statement in a generic way. In the source code you see a number of methods that handle the SQL connection based on the SQLclient namespace of the .NET framework.

CLASS DlaDatabase
PROTECT strConnectie AS STRING
PROTECT strErrorMessage AS STRING
PROTECT objTrans AS SqlClient.SqlTransaction
PROTECT objCon AS SqlClient.SqlConnection   
PROTECT strStatement AS STRING
PROTECT blnTransaction AS Boolean
PROTECT blnDebug := FALSE AS boolean 

CONSTRUCTOR() CLASS DlaDatabase
SELF:strConnectie := "Data Source=.;”+;
  “Initial Catalog=SPAMMAKER;Integrated Security=TRUE"
SELF:blnTransaction := TRUE
SELF:objCon := ;
  SqlClient.SqlConnection{SELF:strConnectie}
SELF:ClearError()
RETURN

In the constructor method of the class we first define the name of the connection string. Of course in a production application we should use an INI or XML config file to handle these application settings. Please take care of a correct connection to your own sample database, if you try this sample yourself.

The next step is to create a connection object that handles opening and closing the connection to the database. Because we will use a datareader for retrieving data from the database opening and closing a connection is quite error prone.

As an extra the handling of transactions can be turned on or off based on a Boolean variable (default is true). Error handling is simple in this class. When an error occurs, it is added to the strErrorMessage string and can be retrieved from the class when necessary.

METHOD ProcessModify(strSql AS STRING, objCol AS ;
  DLAParameterColl) AS LOGIC CLASS DlaDatabase

strSql := objCol:ProcessSql(strSql)
RETURN SELF:ExecuteModify(strSql)

METHOD ExecuteModify(strSql AS STRING) AS LOGIC ;
  CLASS DlaDatabase
LOCAL objCommand AS SqlClient.SqlCommand
LOCAL blnOk AS LOGIC
LOCAL e AS USUAL

blnOk := FALSE
IF blnDebug = TRUE
   MessageBox.Show(strSql)
ENDIF
SELF:OpenConnection(TRUE)
BEGIN SEQUENCE
  objCommand := SqlClient.SqlCommand{}
  SELF:strStatement := strSql
  objCommand:CommandText := strSql
  objCommand:Connection := SELF:objCon
  IF SELF:blnTransaction := TRUE
    objCommand:Transaction := SELF:objTrans
  END IF
  objCommand:ExecuteNonQuery()
  blnOk := TRUE
RECOVER USING e
  SELF:AddError(((System.Exception)e):Message + strSql)
END SEQUENCE
SELF:CloseConnection(blnOk)
RETURN blnOk

In the code snippet above you can see how an Insert/Update/Delete or Stored Procedure call is handled. The ProcessModify method takes care of converting the generic SQLl statement to a specific one (it is later explained how). After this the ExecuteModify method is called, which opens the database connection and executes a non-query statement against this object. When this fails and the transaction is set, the transaction will be rolled back in the CloseConnection method.

METHOD ExecuteSupplyDataReader(strSql AS STRING, ;
  objCol AS DLAParameterColl) AS
  SqlClient.SqlDataReader CLASS DlaDatabase

LOCAL objCommand AS SqlClient.SqlCommand
LOCAL objReader AS SqlClient.SqlDataReader

strSql := objCol:ProcessSql(strSql)
SELF:OpenConnection(FALSE)
objCommand := SqlClient.SqlCommand{}
objCommand:CommandText := strSql
objCommand:Connection := SELF:objCon
objReader := objCommand:ExecuteReader()
RETURN objReader

Retrieving data from the database is possible with a datareader or a dataset in combination with a data-adapter. Both are applied in the sample application; in the code snippet you get an idea about how a datareader is instantiated. First a command object is created and associated with the SQL statement. It is then executed as a reader returning a SqlDatareader object which can be read in a do-while loop afterwards. Finally the connection is closed. An example will be given in a paragraph below.

DLAMember Class for a Domain Entity

Now that we have implemented the database layer we can continue with the class that takes case of the specific behavior for a domain entity. In our sample we have a DLAMember class that contains the properties of a member of our spam service. The class has a number of methods that handle the status of the specific entity and make it possible to retrieve data from the database and pass it to a presentation layer. In the code below two methods are described.

CLASS DLAMember INHERIT DlaDatabase

METHOD Subscribe(objCol AS DLAParameterColl) ;
  AS LOGIC CLASS DLAMember
LOCAL blnOk AS LOGIC
LOCAL strStatement AS STRING
strStatement := "insert into [lid] ( [voornaam] , "+;
  "[tussenvoegsel],[achternaam],[adres],[postcode], "+;
  [woonplaats],[telefoon],[functie],[emailadres] , "+;
  "[wachtwoord], status ) "+;
  "values ('#voornaam#',’#tussenvoegsel#', "+;
  "’#achternaam#','#adres#','#postcode#', "+;
  "'#woonplaats#','#telefoon#','#functie#', "+;
  "'#emailadres#','#wachtwoord#','lid_aanmaken' ) "
blnOk := SUPER:ProcessModify(strStatement, objCol)
RETURN blnOk

METHOD Member_Detail(objCol AS DLAParameterColl) ;
  AS SqlClient.SqlDataReader CLASS DLAMember
LOCAL objDS AS SqlClient.SqlDataReader
LOCAL strStatement AS STRING
strStatement := "SELECT [LID].[Achternaam], "+;
  "[LID].[Adres],[LID].[emailadres],[LID].[Functie], "+;
  "[LID].[Lid_id],[LID].[Postcode],[LID].[Telefoon], "+;
  "[LID].[Tussenvoegsel],[LID].[Voornaam], "+;
  "[LID].[Wachtwoord],[LID].[Woonplaats] "+;
  "FROM  [LID]  WHERE [LID].[Lid_id]= #lid_id# "
objDS := ;
  SELF:ExecuteSupplyDataReader(strStatement, objCol)
RETURN objDS
END CLASS

The first method creates a new member entity. It handles a collection and connects to the relevant SQL insert statement. The second method retrieves the data from the database and passes it to a datareader. The form in which this is done will be described in the next paragraph.

Presentation in Windows Forms

The data is presented in a number of Windows forms.

This sample has a listbox control on the left hand side; when you click on an item in the list the controls at the right hand side are filled. When pressing Save a ModifyService-message is sent to the DLAMember class (see the code sample below).

METHOD OpslaanButtonClick(o AS OBJECT ,;
  e AS EventArgs) AS VOID CLASS LidMuterenForm
LOCAL oMember AS DLAMember
LOCAL oPara AS DLAParameterColl
LOCAL oCtrl2Para AS DLAControl2Parameter
oMember := DLAMember{}              
oPara := DLAParameterColl{}
oCtrl2Para := DLAControl2Parameter{SELF:Controls,;
  oPara }
oCtrl2Para:Controls2Parameters()
IF !oMember:Change(oPara)
  MessageBox.Show(oMember:GetError())
ELSE
  SELF:Close()
ENDIF 
RETURN

In the method above you can see that the DLAMember class is called and that an object with a name-value collection is used to pass the data to the domain and database layer. The advantage is that you can handle this in a delegated class named DLAControl2Parameter.

Next you can see how the DLAMember class is used to return a reader with specific information from the database. Notice that this is encapsulation to the max, because the SQL statement or the database structure is invisible in the presentation layer. This makes it possible to change the presentation or the database structure without changing the other layer implementation.

METHOD lid_idClick(o AS OBJECT, e AS EventArgs);
AS VOID CLASS LidMuterenForm

LOCAL oPara AS DLAParameterColl
LOCAL oCtrl2Para AS DLAControl2Parameter
LOCAL oMember AS DLAMember

oPara := DLAParameterColl{}
oPara:AddParameter("lid_id", ;
  oLid_Id:SelectedValue:ToString())
oMember := DLAMember{}
oPara:AddParameterFromReader( ;
  oMember:Member_Detail(oPara))
oMember:CloseConnection(TRUE)

oCtrl2Para := DLAControl2Parameter{SELF:Controls,;
  oPara }
oCtrl2Para:Parameters2Controls()
// Add event handling code here
RETURN

In the form load method we take care of handling the listbox data. This is also done by a delegated class where a datareader is changed to listboxitems in an arraylist. Please see the sample application how this is handled. Since .NET is object orientation in everything, the ListboxItems are classes as well.

Delegate Control2Parameter and DLAParameterColl

To transform the data in the Windows form to a SQL statement in the Member class two delegates have been implemented in the sample application. In the first class we transform the Parameter collection to control values and the other way around. In the code below a sample is given of transferring the data from the controls arraylist of a Windows form to the collection class.

METHOD Controls2Parameters AS VOID CLASS ;
  DLAControl2Parameter

LOCAL nTel AS WORD
LOCAL oControl AS Control
LOCAL strType AS STRING
LOCAL strName AS STRING
LOCAL oLB AS ListBox

FOR nTel := 0 TO SELF:objControls:Count-1
  oControl := SELF:objControls:Item[nTel]      
  strType := Upper(oControl:GetType():ToString()):
    Replace("SYSTEM.WINDOWS.FORMS.", "")   
  strName := Lower(oControl:Name)
  DO CASE
    CASE strType = "TEXTBOX"
      SELF:objParameters:AddParameter(strName, ;
        oControl:Text)
    CASE strType = "MASKEDTEXTBOX"              
      SELF:objParameters:AddParameter(strName,;
       oControl:Text)
    CASE strType = "LISTBOX"
      oLB := (ListBox)oControl
      SELF:objParameters:AddParameter(strName, ;
        oLB:SelectedValue:ToString())
  ENDCASE
NEXT  

RETURN

The data is retrieved from the specific controls based on the control type and added to the parameter-collection class. The AddParameter-method and the class definition are given in the source code below.

CLASS DLAParameterColl PROTECT objCol AS ;
  Collections.Specialized.NameValueCollection

CONSTRUCTOR() CLASS DLAParameterColl
  SELF:objCol := ;
    Collections.Specialized.NameValueCollection{}

METHOD AddParameter(;
  strName AS STRING,strValue AS STRING) AS VOID ;
  CLASS DLAParameterColl
  SELF:objCol:Add(strName, strValue)
RETURN

These are the basics of the sample application, which shows how the other delegates handle Listbox and collection functionality. The sample is fully functional and the SQL statement for the SQL database is included.

Summary

In this article I describe my first experiences with the beta version of the Vulcan.Net language and the Vide application. Both tools make easy access to the .NET framework classes really easy. I described how a number of Windows forms are used to retrieve and update data from/in a SQL Server database. This functionality is used in a layered application where delegates are used to do the communication between the layers.