Abstract
Nowadays, most web and application programs are written based on this three-layer architecture. In this article, first this three-layer Architecture is introduced and then a program for generating application and web in an optimized and complete way is introduced.
Introduction
This architecture consists of three-layers, including presentation, business, and data. But, with some modifications, an optimal architecture can be obtained. Presentation layer refers to forms and pages which are in fact user interface (UI). Business layer includes functions for establishing relations between data and presentation layers. Data layer refers to the functions which have direct contact with the database.
Three-layer Architecture
In this article, with modification of this three-layer architecture, an architecture may be obtained which is the unit of data between these layers and is completely similar to a three-layer architecture. What follows is an explanation of the different parts of this architecture.
Explanation
Layer one (presentation): This layer includes forms and pages.
Layer two: This layer consists of functions for establishing relations. They include:
Insert function: This function adds a new entity to the table. The input of this function is an interface object and its output is a number (Integer) which shows whether this function has been successfully run or not.Getlist: This function has no input while its output is a list of all the rows of the table.Update: This function is used for editing an entity. The input of this function is an interface object and its output is a number (Integer) which shows whether this function has been successfully run or not.Delete: This function is used to delete an entity form the table. Its input is an interface object and its output is a number (Integer) which shows whether this function has been successfully run or not.Details: This function can be used to find an entity. Its input is an interface object and its output is a table consisting of only one row.DetailsByField: The input of this function is the name of one of the fields and its value. Its output is all the rows of the table in which the value of the considered field equals the requested value.
Interface object: Interface object is a class which includes all the fields of the table. Interface object is the unit which transfers data between layers. When an action (insert, update, delete) is to be performed, an object is created from this interface class and the values of its properties are given to the business layer. However, when a list (call getlist function) is to be taken, there is no need to create an object of this interface class. Also, the function of getlist from business layer can be directly called. Separating interface (interface object) from business layer makes business layer lighter and creating object in business layer faster, since large class is an antipattern.
Layer three: This layer consists of two classes, data and parent data.
- Data: This class opens the interface object and set properties for parent data class.
Data class inherits the parent data class. - Parent data: Executing database procedures, this class performs its operation.
Generating Code Program
This program creates SqlServer stored procedures for each table such as insert, update, delete, get list and generates C# code for both web (ASP.NET) and application optimally. The programmer needs only to design UI (user interface) and add a series of generated classes to his program (with dragging). Companies and programmers who do a repeated work are in fact wasting both money and time of the customers. This program generates all the required codes.
How to Use Generating Code Program
It is important that all the tables have primary key in order for the generating code program to know what the basis of delete, update, and search functions is, otherwise, the generating code program will not be able to recognize what field to write in front of where command in SqlServer.
Method
A list of the tables is displayed after running HeiltonGenerator.exe file and connecting to the database SqlServer. Then, the programmer must select the table and go to the setting tab and choose the path and click generate.
Connection String Tab
In this tab, there is a class for creating SqlConnection which has a Boolean type of variable named State for checking the openness or closeness of SqlConnection. Separating this class is useful for management of connection string.
using System;
using System.Data.SqlClient;
public class SqlCon
{
Private String StrCon;
Private Boolean State;
Private SqlConnection Con;
Public SqlConnection OpenCon()
{
if (!State)
{
StrCon ="Connection String";
Con = new SqlConnection(StrCon);
Con.Open();
State=!State;
}
return Con;
}
Public void CloseCon()
{
if (State)
{
Con.Close();
State=!State;
}
}
}
Setting Tab
In this tab, a name for output folder and a direction for the generated code is selected and then the type of the project (web, or application) is chosen. Afterwards, generate needs to be clicked. When the message "successfully generate code" is displayed, user can exit the program. Two folders are made in the output folder including Sql and Code. Sql folder includes a file named StoredProcedures.sql in which SqlServer Stored Procedure queries are generated. These queries should be opened and executed in SqlServer environment for the stored procedures to be created. Now, programmer needs to go to Microsoft Visual Studio and drag Code folder into Solution Explorer.
In this stage, classes are added to the program and ready to be used.
For every table, a class is made separately in each layer. Naming of these classes is as follows:
For example, there is a table named Tblperson whose naming is in this way:
InterfaceTblperson
BusinessTblperson
DataTblperson
ParentDataTblperson
Layer (class) name+ Table name
In other words, Layer name + Table name
Naming stored procedure in SqlServer is in this way:
spInsertTblperson
spUpdateTblperson
spDeleteTblperson
spDetailsTblperson
spGetListTblperson
spDetailsByFieldTblperson
sp+Action+TableName
sp stands for stored procedure.
A program and all its actions are attached in this article.
There is also a table named Tblperson with the following fields:
To display all the rows of this table, the following code needs to be written.
privatevoid Form1_Load(object sender, EventArgs e)
{
BusinessTblperson BTblperson = new BusinessTblperson();
dataGridView1.DataSource = BTblperson.GetList();
}
To add an entity (row) to Tblperson table, the following code must be written.
- ID field is Identity
Specification (inSqlServer). So there is no need to add value to it.
InterfaceTblperson ITblperson = new InterfaceTblperson();
ITblperson.Name = textName.Text;
ITblperson.LastName = textLastName.Text;
BusinessTblperson BTblperson = new BusinessTblperson();
Int Ireturn=BTblperson.Insert(ITblperson);
if (Ireturn == 0)
{
MessageBox.Show("Insert Failed ");
}
else
{
MessageBox.Show("Insert is successful");
}
To delete an entity (row) from Tblperson table, the following code should be written.
- ID field is Identity Specification (in SqlServer). So only this ID field should be given value.
InterfaceTblperson ITblperson = newInterfaceTblperson();
ITblperson.ID = int.Parse(textID.Text);
BusinessTblperson BTblperson = new BusinessTblperson();
Int Ireturn=BTblperson.Delete(ITblperson);
if (Ireturn == 0)
{
MessageBox.Show("Delete Failed ");
}
else
{
MessageBox.Show("Delete is successful");
}
To update an entity (row) from Tblperson table, the following code must be written.
All the fields should be given value.
InterfaceTblperson ITblperson = new InterfaceTblperson();
ITblperson.ID = int.Parse(textID.Text);
ITblperson.Name = textName.Text;
ITblperson.LastName = textLastName.Text;
BusinessTblperson BTblperson = new BusinessTblperson();
Int Ireturn=BTblperson.Update(ITblperson);
if (Ireturn == 0)
{
MessageBox.Show("Update Failed");
}
else
{
MessageBox.Show("Update is successful");
}
To find an entity (row) from the Tblperson table, the following code must be written.
- Only primary key field must be given value.
InterfaceTblperson ITblperson = new InterfaceTblperson();
ITblperson.ID = int.Parse(textID.Text);
BusinessTblperson BTblperson = new BusinessTblperson();
DataTable DTable=BTblperson.Details(ITblperson);
if (DTable.Rows.Count == 0)
{
MessageBox.Show("No Find");
}
else
{
textName.Text = DTable.Rows[0].Field<string>("Name");
textLastName.Text = DTable.Rows[0].Field<string>("LastName");
}
A view of the form is given below:
