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
-
ConnectionString
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