Three-tier code generator for ASP.NET

Stevan Rodrigues

The 3-tier code generator is a wonder tool that helps developers to code within minutes a simple module to add and update records in a table. At the same time, it keeps the best practices and industry standards in mind. It exploits the object-oriented features of .NET. Following are some notable features of this tool:

  • Generates all the code required for 3-tier architecture to be directly used in Visual Studio .NET;
  • Makes use of Web Template pattern and Error pattern;
  • Generates SQL procedures and scripts to create, add, update and show records of the table;
  • Generates code for both C# as well as VB.NET;
  • Can do DataGrid editing or separate update form;
  • Makes use of cascading style sheets;
  • Uses a standard Microsoft recommended Microsoft Data Access Application Block for .NET; and
  • User controls are used for header and footer.

Files generated

Here, we shall get to know the files that are generated. However, to really appreciate the work let's dig into a few concepts.

A 3-tier application is a program that is organized into three major disjunctive tiers. These tiers are:

  • Presentation tier (Front end);
  • Logical tier (Middleware); and
  • Data tier (Backend).

Figure 1 provides a detailed description of all the components involved in a 3-tier application.

Data tier

This tier is responsible for retrieving, storing and updating information. Therefore, the data tier can ideally be represented by a commercial database. Stored procedures are considered to be a part of Data tier. Usage of stored procedures increases the performance and code transparency of an application.

Logical tier

This is the core of the system, the linking pin between the other two tiers. In most applications, programmers often have a single tier between the presentation logic and the actual back-end database. They tend to lump both the business logic and data access into the same logical tier. Though this is a bad idea, it will work. It is better to separate the code that enforces business rules and performs multi-step business processes from the database access code.

Business tier

This sub-tier contains classes to calculate aggregated values like total revenue, cash flow, etc. It does not know anything about GUI controls and how to access databases. The classes of Data Access tier will supply the required information from the databases to this sub-tier.

Data access tier

This tier, which acts as an interface to the Data tier, knows how to (from which database) retrieve and store information.

Presentation tier

This tier is responsible for communication with the users and web service consumers. It uses objects from the business layer to respond to GUI raised events.

Advantages

  • With the right approach, 3-tier architecture saves hours of development time. Here we code each bit only once with powerful re-usage.
  • Divide and conquer strategy: Each tier is rather easy to develop. It is better to have three simple parts instead of a complex single one.
  • Quality: For each layer, a specialist can contribute his/her specific expertise, e.g. a GUI designer for the presentation tier, a .NET programmer for the Logical tier and a database designer for the tables.

Common files

According to the code design, we will have a single base class for every object in a given tier. What this essentially means is that every data access object will inherit from a common data access object. In addition, every business class will inherit from a common business class. Typically, when designing a data services tier, there are two main ideas that people adopt — building a single helper class that performs all the data access on behalf of all the components in the data tier or building a data object for every type of entity that needs access to the database. For our purpose, we're going to use both the methods.

We have Microsoft Data Access Application Block for .NET, the helper class that performs the database tasks. Also, all the data objects will be inherited from DataObject where you can have a set of customized functions that will help you in data access and are not provided by the application block. Refer figure 2.

Business folder has classes that inherit from BusinessObject where we can define business rules while the Data folder has a User class that inherits from DataObject where we can define rules for the data tier. SQLHelper class is the standard data access helper class of Microsoft Data Access Application Block for .NET, which does all the standard data function calls.

All the ‘ASPX’ pages inherit from SitePage, which is used to catch errors and define a template. Also, all pages have a common header and footer control.

AppException class

Several times, in many different programming languages, error-handling routines become enormous, cumbersome and difficult to maintain. Even in modern languages that support the throwing of exceptions, one problem remains: how do we make sure that there is a persistent record of every exception that the system throws? This is an absolute necessity for a website on the Internet where  users may not see the problem (it could be something internal that causes subtle failures, such as rounding problems or bad numbers). Even if the user sees the problem, they will either log off the website or will hit the back button and move on to some other feature of the website. We cannot rely on users to detect our errors.

To get around this, we will create our own custom derivative of System.ApplicationException. This custom exception class will place an entry in the NT/2000 Application Event Log every time an exception is thrown or will write to the error log file. I have commented the section for event log. You can modify this file to suit your application. In this way, the website administrator and programmers can find out the details and the time when every error occurs.

Files created

Figure 3 lists the created files.

Business/User.cs has a User class that inherits from BusinessObject where we can define business rules while Data/User.cs has a User class that inherits from DataObject where we define rules for the data tier.

To ensure that both the User Business class and User Data class generated adhere to the rules, both these class files are inherited from Interface/IUser.cs.

The presentation tier consists of ASPX pages. Even the code behind the pages is generated.

The SQL files constitute the Data tier. These are a set of SQL procedures to add, update and display records. It also consists of a script for table creation.

How to generate the files?

Figure 1 is a screenshot of generate pages.

Prerequisites

  • IIS with support for ASP 3.0 (needs to be activated for Windows 2003) and ASP.NET, i.e. .NET Framework needs to be installed;
  • Visual Studio .NET;
  • SQL Server 7.0 and later; and
  • Good knowledge of ASP.NET, Visual Studio .NET and SQL procedures to integrate the pages.

Initial Setup for the application

Database

1. Create a Database e.g. ThreeTierDemo_db.

2. Create a table containing the field username and identity field, which is a primary key. E.g. Table Admin_tb with identity field AdminId. The script is also present in the file SourceCode\Scripts\Admin_Tb.sql.

3. if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Admin_Tb]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Admin_Tb]
GO

4. CREATE TABLE [dbo].[Admin_Tb] (
[AdminId] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL ,   
[Username] [varchar] (25) NOT NULL ,
[Password] [varchar] (20) NOT NULL ,
[AddedDate] [datetime] NOT NULL ,
[UpdatedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

5. Run the following script. The script is also present in the file SourceCode\Scripts\IntialScripts.sql.

6. if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[ProcGetReader]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProcGetReader]
GO

7. if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[ProcGetRecords]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProcGetRecords]
GO

8. if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[ProcAddRecordAction_Tb]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ProcAddRecordAction_Tb]
GO

9.if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[RecordAction_Tb]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RecordAction_Tb]
GO

10. CREATE TABLE [dbo].[RecordAction_Tb] (
[ActionId] [int] IDENTITY (1, 1) NOT NULL ,
[ResourceId] [int] NULL ,
[TableName] [varchar] (50) NOT NULL ,
[PrimaryId] [int] NOT NULL ,
[Action] [varchar] (15) NOT NULL ,
[ActionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

11. SET QUOTED_IDENTIFIER OFF
GO

12. SET ANSI_NULLS ON
GO

13. create procedure ProcGetReader
@proc_DataText varchar(200),
@proc_DataValue varchar(75),
@proc_OrderBy varchar(75),
@proc_Tablename varchar(200)

as
set nocount on
declare @error_number int,       
@query nvarchar(1000)

Select @query ='SELECT  ' + @proc_DataText + ' , ' +
@proc_DataValue + '  FROM  ' + @proc_Tablename +
' Order By ' + @proc_OrderBy

exec (@query)

-- error checking
select @error_number = @@error

if ( @error_number <> 0 )
begin
set nocount off
return 1
end

set nocount off
return 0
GO

14. SET QUOTED_IDENTIFIER OFF
GO

15. SET ANSI_NULLS ON
GO

16. SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

17. create procedure ProcGetRecords
@DataText varchar(200),
@DataValue varchar(75),
@OrderBy varchar(75),
@Tablename varchar(200)

as

set nocount on
declare @error_number int,       
@query nvarchar(1000)

Select @query ='SELECT  ' + @DataText + ' , ' + @DataValue +
'  FROM  ' + @Tablename + ' Order By ' + @OrderBy

exec (@query)

-- error checking
select @error_number = @@error
if ( @error_number <> 0 )
begin
set nocount off
return 1
end
set nocount off
return 0
GO

18. SET QUOTED_IDENTIFIER OFF
GO

19. SET ANSI_NULLS ON
GO

20. SET QUOTED_IDENTIFIER OFF
GO

21. SET ANSI_NULLS ON
GO

22. create procedure ProcAddRecordAction_Tb
@proc_ResourceId int,
@proc_TableName varchar(50),
@proc_PrimaryId int,
@proc_Action varchar(15),
@proc_ActionDate datetime
as
set nocount on
/*set ansi_defaults on*/
declare @error_number int,
@row_count int
insert into RecordAction_Tb(
ResourceId,
TableName,
PrimaryId,
Action,
ActionDate
)
values (
@proc_ResourceId,
@proc_TableName,
@proc_PrimaryId,
@proc_Action,
@proc_ActionDate
)
-- error checking
select @error_number = @@error,
@row_count = @@rowcount

if ( @error_number <> 0 ) or ( @row_count <> 1 )
begin
set nocount off
return 1
end
set nocount off
return 0
GO

23. SET QUOTED_IDENTIFIER OFF
GO

24. SET ANSI_NULLS ON
GO

Website

  • Create a new project in Visual Studio .NET e.g. http://localhost/ThreeTierDemo
  • In the web.config add the following:
    <appSettings>
    <!-- User application and configured property settings go here.-->
    <!-- Example: <add key="settingName" value="settingValue"/> -->
    <add key="ConnectionString" value="Server=(local);UID=sa;PWD=password;Database=ThreeTierDemo_db" />
    <add key="ErrorLogFile" value="~/Errors/ErrorLog.txt" />
    </appSettings>
  • Copy the folder components, CSS, UserControls, Images, Errors from SourceCode\Csharp or SourceCode\VB and place it in the root of the website.

Include the entire folders in the application.

Before including files

The list of files will be as shown in figure 2 before the files are included.

Note: You need to toggle the  button of the Solution Explorer to see the files generated.

After including files

The list of files will be as shown in figure 3 after the files have been included.

4.         For VB projects, you need to set the RootNamespace in the properties to blank. Refer figure 4.

5.         For VB projects, you need to exclude Global.asax or in Global.asax directive for the ‘inherits’ attribute remove the namespace prefix.

Code generator

1. Copy the folder SourceCode\ThreeTierGenerator and paste it in C:\Inetpub\wwwroot\.

2. ThreeTierGenerator folder contains CommonSettings.asp where you can change AuthorCompany and Author. This will ensure that your company name and your name appear in the comments.

3. Browse to http://localhost/ThreeTierGenerator/GenerateAllPages.asp. Fields that are displayed in the page are as follows:

Pages folder
Pages folder is the name of the folder where the ASPX pages are located. E.g. Users.

Object name
Object name is the name of the class that will be linked to the table. It is used to generate the business class and data class, e.g. User.

Form name
Form name is the name of the forms generated, e.g. Users.

Action
Action is a part of the ASPX page name. Suppose we type Users in the Action field, the name of the pages will be AddUsers.aspx, UpdateUsers.aspx, etc. E.g. Users.

Title
Title is the title of all the pages. E.g. User.

Table
Table is the name of the table in the database. E.g. Users_Tb.

Field ID
Field ID is the identity field auto generated for the tables, which is a must. This is used for hyperlinks, etc. E.g. UserId.

Primary ID
Primary ID is the primary key for the table. Primary key can be multiple, separated by commas. E.g. Email.

Site folder
Site folder is the physical location of the folder. E.g. C:\Inetpub\wwwroot\ThreeTierDemo.

Class folder
Name of the folder where the class files are created. E.g. Components.

Namespace
Namespace is the root namespace of the website. E.g. Company.ThreeTierDemo.

Admin table
Name of the table where the ID of the user is stored. This is a must if you want to audit the trail. E.g. Admin_Tb.

Admin table primary key
Name of the primary key of the table where the user ID of the user is stored. This is a must if you want to audit the trail. E.g. AdminId.

CSS file path
CSS file path relative to the pages folder. E.g. .../CSS/Site.css.

Fields
Name of the fields that are used both in the table as well as in the class files, e.g. FirstName, LastName, Email, Comments…

SQL field type
SQL data type of the fields. Provide ';' the data type contains ',' e.g. Decimal(14;2), INT, VARCHAR(75),VARCHAR(75),VARCHAR(100),VARCHAR(500)

NET field type
.NET data type of the fields, e.g. string, string, string, string.

Null/Not Null
E.g. NOT NULL, NOT NULL, NOT NULL, NOT NULL.

Fields display names
Display names used in the pages, e.g. First Name, Last Name, Email, Comments.

Input type
The different input types are text, email, password, textarea, select, selectdynamic, date and checkbox. Post fixing ‘req’ input field types ensures that the required field validator controls are added for validation. Email field is validated using regular expressions and select dynamic generates the code to bind dropdown list to the database. You will need to edit the code if you are using select dynamic. E.g. textreq, textreq, emailreq, textarea.

Input max length
This is used to validate the field length. E.g. 75, 75, 100, 500…

4. The check boxes related to Class, Default, Add, Update, Table, SQL Add, SQL Update, SQL Delete, SQL Show, SQL Select can be checked to write the corresponding files to the pages folder location. You need to check the Write Files check box to write the files. Refer figure 5.

5. Appropriate permissions need to be set in the C:\Inetpub\wwwroot\ThreeTierDemo folder.

6. Include the files that are generated.

 

Note: You need to toggle the  button of the Solution Explorer to see the files generated.

Before including files

Figure 6 depicts the files listed before inclusion.

After including files

Figure 7 depicts the files listed before inclusion.

7. Run the SQL scripts generated starting from Users_tb.sql.

8. View Default.aspx in the browser.

9. If you check Append Docs, the tool adds information about various files to Documentation.txt file, which you can use for documentation purposes.

10. If you check Append Requests, the tool adds information to a <Namespace>.txt, i.e. in our case Company.ThreeTierDemo.txt, which contains all the data that you have typed in. This you can cut, paste and copy later when you want to regenerate the pages.

11. To ensure that you have added proper content, a screen is generated that helps you compare the information (figure 8).

Forms authentication

To implement forms authentication, the following steps must be taken:

  1. Copy the folder SourceCode/Login and include it in the project.
  2. Copy web.config file from the login folder and place it in the subfolders, e.g. users folder, that you want to protect.
  3. Add or modify the authentication tag of the web.config present in the root of the application.
  4. <authentication mode="Forms">
    <forms name=".MyCookie" loginUrl="login/login.aspx"
    protection="All" timeout="80" path="/" />
    </authentication>
  5. Run the script. Change the name of Admin_tb, AdminId if you have used some other name for them:
    if exists (select * from dbo.sysobjects
    where id = object_id(N'[dbo].[ProcAuthenticate]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[ProcAuthenticate]
    GO
  6. SET QUOTED_IDENTIFIER OFF
    GO
  7. SET ANSI_NULLS ON
    GO
  8. CREATE Procedure ProcAuthenticate
    @proc_username Varchar( 25 ),
    @proc_password Varchar( 20 )
    As
    DECLARE @AdminId INT
    DECLARE @actualPassword Varchar( 25 )
    SELECT
    @AdminId = AdminId,
    @actualPassword = password
    FROM Admin_Tb
    WHERE username = @proc_username
    IF @AdminId IS NOT NULL
    IF @proc_password = @actualPassword
    RETURN @AdminId
    ELSE
    RETURN – 2
    ELSE
    RETURN – 1
    GO
  9. SET QUOTED_IDENTIFIER OFF
    GO
  10. SET ANSI_NULLS ON
    GO

You can download the complete code from the following URL:
http://www.developeriq.com/downloads/ThreeTierCode.zip

The author is a postgraduate of Science, majoring in Mathematics, serving the IT industry in Dubai, U.A.E. He can be reached at: stevanin@hotmail.com.








}