EHSDataCompliance Framework

• Download DataEntry.zip - 2.3 MB

Introduction    

A new methodology. This is my first article that I put forward in order to minimize the coding system in programming regarding the software development I suggest the following new methodology for easy reference and quick results. The content of the framework that I developed reduces the number of codes. Accordingly

• Pass the data object and stored procedure name
• Send the business entity object and stored procedure name through a method in the framework
• Take the parameter names of the stored procedure from the INFORMATION_SCHEMA in SQL Server and map them with the business Entity object properties.

Background

While working I found there are some common patterns in the data access layer of the framework we use. So I thought of my own concept to put all the common patterns together to reduce the product development time. As this framework is still in the beta stage I am ready to accept any suggestion and comments in this regard.

How the Framework can be Applied

Creating the Code Structure

Note : In this sample we will take the relationship between a 'Person','Car' and a 'Engin'

To use the framework we must arrange the code in the following order

• A layer for the Business Entities
• This layer contains the classes that represent the tables in the data base
• Application Layer
• A layer for the Services (where the main logic of the project will be written
• A Data Access Layer to communicate with the data base

Business Entities

Application Layer

Service Layer
or the Middle Tier

Data Access Layer

Business Entities

Person

Engin

Car

As shown all three BE's are inherited from the Base class.This class holds all the common properties such as 'Date Created','CreatedBy' etc.

Base Class

Contents of the Base Class

• IsReferenceData
  • As you can see the Car Business Entity above it has a foreign key named 'Owner' which of the Person type.So we have to tell the framework engin that 'Owner' is actually a reference of the Person.
    You will understand this better when you see the sample project.


• OutParameterValues
  • This type of property is Parameter which is class in the framework. This proeprty will give all output values returned from the executed Stored Procedure.

Shown above is the Base class which is in side the framework

You can add your own common properties but you must name the new class as 'Base' and inherit if from the 'Base' class in the framework.

Like this :

EHS.CommonDataHelpers.DataHelper.Data Is the name space where the Base class is in.

Now you can inherit your business entities from the new Base class you created.
Same goes to the BaseCollection class, which also is in the framework.

Now the business entities are created, lets create our database using Microsoft SQL Server.
Our database name will be PeopleAndCars

Table Structure

Stored Procedures we will be using for this sample

• Scope_Identity()

• Scope_Identity returned the last identity value in the same scope. Scope is a module of a stored procedure, trigger, function, or batch.

Now let's go back to C#

Data Access Layer : (First Section)

It is better to implement all the classes in Data Access Layer with the Interface IDataAccessLayer, which is in the framework.

This is not a must, but it will help the programmer to get all the basic methods of the Data Access Layer.

First let's have a look at the IDataAccessLayer

IDataAccessLayer

Contents of the interface

• As you can see there are two generic types defined :
  • S : This means that it takes a single object such as a Person
  • C : This means that it takes a collection object such as People


• TransactionStatus
  • This class is in the framework, which gives the status of the transaction you just did.


• ref
  • The ref keyword causes an argument to be passed by reference, not by value. The effect of passing by reference is that any change to the parameter in the method is reflected in the underlying argument variable in the calling method.
    The value of a reference parameter is always the same as the value of the underlying argument variable.

Now let's look at a class in the Data Access Layer. For this sample I have used the PersonDAL

PersonDAL

Contents of PersonDAL

• As you can see this, how a basic Data Access Layer class will be represendted in this framework.

Before we touch any method inside the PersonDAL, let's have a look at the StoredProcedure Class. Another class which is in the freamwork.

Stored Procedure Class

Contents of the Stored Procedure Class

• SqlDatabaseHelper
  • This class is in the framework. This class communicates with SQL Server and gives us the necessary results.


• ProcedureName
  • This proeprty will contain the name of the Stored Procedure that we will execute


• DataObject
  • This proeprty will contain the generic type data object that will be used to both get or set data.


• ConditionData
  • This proeprty will contain the parameters that needs to be passed to the stored procedure.


• SqlConnectionInfo
  • This is a class in this framework, which contains the Connection information such as :
    • User name and the password
    • The index of the connection string which is to be used.(There could be many connection strings in the Connection.config (This file 'Connection.config' will be explained later, for now it is the file that contains the connection string) which contains the connection strings.


• Activator.CreateInstance<t>() (In the property DataObject)
  • Creates an instance of the type designated by the specified generic type parameter, using the parameterless constructor.


• ExecuteNonQuery()
  • This method will set the data to the database and will return the status of the transaction.


• ExecuteReader()
  • This method will get the data from the database with the status of the transaction.


• In detail the two public methods the ExecuteNonQuery() and ExecuteReader() will:
  • Get the parameter names of the stored procedure using INFORMATION_SCHEMA
  • Check if the parameter names of the stored procedure and the Business Entity object property names are the same.
  • If they are same then add the data from the Business entity object to a SqlParameter.
  • And set or get data from the data base through the Stored Procedure

Before we go futhure down on the Data Access Layer let's have a look at the Service Layer

Service Layer (Middle Tier) : (First Section)

• Also known as application server tier or layer. The logical layer between a user interface or Web client and the database. This is typically where the Web server resides, and where business objects are instantiated. The middle tier is a collection of business rules and functions that generate and operate upon information. They accomplish this through business rules, which can change frequently, and are thus encapsulated into components that are physically separate from the application logic itself.

• The service layer improves performance, flexibility, maintainability, reusability, and scalability by centralizing process logic. Centralized process logic makes administration and change management easier by localizing system functionality so that changes must only be written once and placed on the middle tier server to be available throughout the systems. With other architectural designs, a change to a function (service) would need to be written into every application.

• In addition, it controls transactions and asynchronous queuing to ensure reliable completion of transactions. The middle tier manages distributed database integrity by the two phase commit process. It provides access to resources based on names instead of locations, and thereby improves scalability and flexibility as system components are added or moved.

Let's take a look at the CommonService class which is in the Service Layer (Middle Tier).

CommonService class

Details about the CommonService class

• Note : For this sample I haven't used any security functions, just wanted to explain the functionalities of my Framework and how it will effect the Data Access Layer. In a real time project this layer will contain a security model (of your choice), business logic and much more.



• The Service Layer(can use any other name of your choice) is the bridge between the Application Layer and the Data Access Layer. In other words the Application Layer will never know what kind of Database it communicates with. What it does is whenever it needs to communicate with the database it will call the appropriate method in the Service Layer and the Service Layer will communicate with the database.


• Person_Insert :
  • As you can see we pass the person object that have been passd from the Application Layer to the Insert method of the PersonDAL which is a class in the Data Access Layer.


• person.OutParameterValues.Values.Data[0] :
  • As you can see above in the SpPerson_Insert stored procedure ID is a OUTPUT parameter. So in order to get the out put parameters from the stored procedures we use OutParameterValues which is a property of the Base class which is in the framework.

Now you are ready to go Second section of the Data Access Layer.

Data Access Layer : (Second Section)

Now let's take a look inside the PersonDAL class.

Let's take a look at the Insert method first.

Note : Make sure all the tables and Stored Procedures (That I have shown above) are created before coming to section.

Person Insert Method

Details of the Person Insert Method

• ProcedureName
  • Set the SQL Stored Procedure name that needs to be executed.
    Note : That the stored procedure SpPerson_Insert must be created before doing this.


• Note : I will come to the objSpInsert.SqlConnectionInfo.ConnectionType = (int)ConnectionType.NormalLogin later (in the Second Section of the Service Layer).

Let's insert a new Person to the database

Application Layer

Data Access Layer(Debug Mode in Microsoft Visual Studio)

Let's take a look at the person table in the database to check if the new Person has been inserted.
(In SQL Server).

Remeber, the SpPerson_Insert stored procedure has a parameter Id which is a OUTPUT parameter so it returns the value of the Id.So lets see if we have the correct output value

(Debug Mode in Microsoft Visual Studio)

So we do infact have the correct output value, which the SpPerson_Insert stroed procedure returns.

That's how the insert is done

Now let's have a look inside the GetById Method. When it comes to GetBy,Delete we have 2 options

Option One

Let's take a look at the Person_GetById method which we will be using to get a person by id.

Person_GetById Method Details of Person_GetById Method

• ProcedureName
  • Set the SQL Stored Procedure name that needs to be executed.
    Note : That the stored procedure Sp_PersonGetById must be created before doing this.

Now let's see how we pass in the parameters to get the person we want.

This is how the person object is passd from the Application Layer to the Service Layer method Person_GetById, the service layer will call the GetById in the PersonDAL.We can pass the person object filled with only the ID property that will be used to retrive the data from the data base through the stord procedure.

In the below two figures we can see the data of the person after retreval from the database.

Data about the person :

Base data about the person :

As you can see after the 'ExecuteReader()' Method gets called the dataObject we pass, will be filled with the data about the person we need.(Recomended only for GetById and DeleteById)

Option Two

In order to use the second option we will have to create a new Business Entity containing the properties that we are going to pass as parameters to the stored procedure

Business Entity Layer

Details about the PersonGetByIdParams class

• As you can see, according to the parameters that have to be passed to the stored procedure Sp_PersonGetById. We have properties defined in our new class.

If one is using this option we can't use the implemented method GetById.
So we have to creat a new method like this :

• ConditionData
  • This proeprty will contain the parameters that needs to be passed to the stored procedure.Which is a property in the StoredProcedure class.

I have created an overloaded method.
Note : This is not a must. It doesn't have to be an overloaded method

Now let's see how we pass in the parameters to get the person we want.

This is how the person object is passed from the Application Layer to the Service Layer method Person_GetById, the service layer will call the GetById in the PersonDAL.We can pass the PersonGetByIdParams object filled with the necessary properties that will be used to retrive the data from the data base through the stord procedure.

Now let's take a look at the newly created method GetById(PersonGetByIdParams personGetByIdParams), thats in the Data Access Layer.

As you can see, we can get the data as same as Option 1, this is the recomended method for any kind of GetBy's that takes parameters to retreave data. The update method will work the same as insert.

Service Layer : (Second Section)

The main thing I want to talk in this section is about the Connetion.config (Note: The developer has to create this file and the format is be given below) file which contains the connection string to the data base.

• Id

  • From the Id tag the system will identify the connection string that the user wants to use, remember the objSpInsert.SqlConnectionInfo.ConnectionType = (int)ConnectionType.NormalLogin line that I mentioned above when we were at the Data Access Layer, that how we tell to the system what connection string that we need to use.



• SecureConnection

  • From the SecureConnection tag we can tell the system if we are using a connection string with password or a connection string without password.



• ConnectionString

  • This tag will contain the connection string that we need to connect to the database and make sure that you encrypt the connection string before writing it to the file.
    Note : The enryption and decryption software will provide in the sample code

Let us go through the objSpInsert.SqlConnectionInfo.ConnectionType = (int)ConnectionType.NormalLogin line in more detail

I have created Enum class called ConnectionType in order to provide the connection string type to the system, This is not a must but it will be esay to read the code.

As you can see we are using the connection string in the Id 0 tag to connect to the data base.

NOTE : If you are using a connection string with user name and password make sure that you use the commented format provided in the tag Id 1

How to use the Text Encryption Software

Note : You must encrypt the connection string with this software it is a must.

Let's see how to use the software

	First put the the connection string to the Text To Enctypt TextBox
	Click Encypt button And copy the text in the 'Encrypted Text' text box and past it int eh ConnectionString tag in the Connection.config file.

I hope you will enjoy using this framework as much as I enjoyed developing this framework.

If you want the catalog of the all the namespaces and the method details that I have used in this article and if you have any suggestion and comments in this regard, please do not hesitate to send me an email. My email is listed below.

This article is about using this framework, I am hopping to write another article with details of how this framework functions.

History

This is the beta version more releases of this framework will be coming soon.

Thanks To

• Mr.Clyde Casiechitty
  • Head of Business Projects at Converse Solutions Limited


• Mr.Chaminda Chandrasekara
  • Former Technical Lead at Converse Solutions Limited.
    Current Technical Lead at Navantis.


• Mr.Neluka Wickramaratne
  • Senior Software Engineer at Converse Solutions Limited and my current team lead.


• Mr.Eshanka Kodituwakku
  • Software Engineer at Converse Solutions Limited and my former team lead.


• Heshan Lokukarawita
  • My batch mate and the person who taught me proper coding structures in VB.Net