(untagged)

Three Tier Code Generator For ASP.NET

Stevan Rodrigues

0.00/5 (No votes)

8 Jul 2005

Generates three tier code for ASP.NET.

Introduction

The 3-Tier Code Generator is a wonder tool that will help developers to code within minutes a simple module to add, update records in the 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 of the features of the 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.
User controls are used for header and footer.

Background

For the past few years I have been using my own customized tools for code generation. This tool is the outcome of one of my endeavors. This tool has greatly eased my life and given me ample amount of time for research and further studies. In fact the day I studied ASP.NET I wrote this code in ASP. I have been updating this code ever since and have added a lot of features and tried to adhere to coding standards and best practices. My desire is to recode it in .NET using code dom. Before I move on to do so I want to release this code so that it benefits the developer community.

Overview

My article is divided into two parts. One will be the files that are generated and the other how to generate the code.

Files generated

Here, I will describe the files that are generated. But to really appreciate the work let's dig into a few concepts.

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

Presentation tier (Front end)
Logical tier (Middleware)
Data tier (Backend).

Data tier

This tier is responsible for retrieving, storing and updating information therefore this tier can be ideally represented by a commercial database. We consider stored procedures as a part of the 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 the data access into the same logical tier. This will work, but this is a bad idea. It's 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... This tier doesn't know anything about the GUI controls and how to access databases. The classes of Data Access tier will supply the needy information from the databases to this sub tier.

Data Access tier

This tier acts as an interface to the Data tier. This 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 and it uses objects from the business layer to respond to the GUI raised events.

Advantages

With the right approach the 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 3 simple parts instead of a complex single one.
Quality- For each layer a specialist can contribute his specific expertise like 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 which 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 go with 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.

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 the pages have a common header and footer control.

The AppException class

Many a 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 the 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 onto 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. This way, the website administrator and the programmers can find out the details and the time of every error that occurs.

Files created

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 can define rules for the data tier.

To ensure that both the User Business class and the 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 pages are 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?

Prequistes

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..
Good knowledge of ASP.NET, Visual Studio .NET, SQL procedures to integrate the pages.

Initial Setup for the application

Database

Create a Database e.g.. ThreeTierDemo_db

Create a table containing the field username and the 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.

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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
SET QUOTED_IDENTIFIER OFF 
GO
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

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

After including files
For VB projects you need to set the RootNamespace in the properties to blank.
For VB projects you need to exclude Global.asax or in Global.asax directive for the inherits attribute remove the namespace prefix.

Code generator

Copy the folder SourceCode\ThreeTierGenerator and paste it in C:\Inetpub\wwwroot\.
ThreeTierGenerator folder contains CommonSettings.asp where you can change AuthorCompany and Author. This will ensure that your company name and your name appears in the comments.
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 the 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, then the name of the pages will be AddUsers.aspx, UpdateUsers.aspx e.g.: Users

Title

Title is the title of all the pages. e.g.: User

Table

Table is the name of the table in 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 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 user 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, checkbox. Post fixing �req� input field types ensures that 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

Example data for all the fields:
- Pages folder: Users
- Object Name: User
- Form name: Users
- Action: Users
- Title: User
- Table: Users_Tb
- Field Id: UserId
- Primary Id: Email
- C:\Inetpub\wwwroot\ThreeTierDemo
- Site folder: Components
- Namespace: Company.ThreeTierDemo
- Admin table: Admin_Tb
- Admin table primary key: AdminId
- CSS file ath: ../CSS/Site.css
- Fields: FirstName, LastName, Email, Comments
- SQL field type: VARCHAR(75),VARCHAR(75),VARCHAR(100),VARCHAR(500)
- .NET field type: string, string, string, string
- Null/Not Null: NOT NULL, NOT NULL, NOT NULL, NOT NULL
- Fields display Names: First Name, Last Name, Email, Comments
- Input type: textreq, textreq, emailreq, textarea
- Input max length: 75,75,100,500
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.
Appropriate permissions need to be set on the C:\Inetpub\wwwroot\ThreeTierDemo folder.
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

After including files
Run the SQL scripts generated starting from Users_tb.sql.
View Default.aspx in the browser.
If you check Append Docs, the tool adds information about various files to Documentation.txt file which you can use for documentation purpose.
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.
To ensure that you have added proper content, a screen is generated that helps you to compare the information.

Forms authentication

To implement forms authentication, follow the following steps:

Copy the folder SourceCode/Login and include it in the project.
Copy the web.config file from the login folder and place it in the subfolders e.g.. users folder that you want to protect.

Add or modify the authentication tag of the web.config present in the root of the application.

<authentication mode="Forms">
<forms name=".MyCookie" loginUrl="login/login.aspx" 
protection="All" timeout="80" path="/" />
</authentication>

Run the script. Change the name of the 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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Points of interest

When I moved from ASP to ASP.NET the challenge was to have a tool similar to the one I was using in ASP, a customized tool of my own which would do literally all my work. With in few days I had a tool that could generate pages in ASP.NET. It could be edited using any text editor.

After I finished my MCAD, my challenge was to ensure that the pages were compatible with Visual Studio .NET. So the tool was further enhanced to do the same.

After reading one of the books, my challenge was to ensure that the tool meets the three tier requirements, uses the Microsoft Data Access Application Block and meets the coding standards. I tried my best to be consistent wherever possible.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Three Tier Code Generator For ASP.NET

Introduction

Following are some of the features of the tool:

Background

Overview

Files generated

Data tier

Logical tier

Business tier

Data Access tier

Presentation tier

Advantages

Common files

The AppException class

Files created

How to generate the files?

Prequistes

Initial Setup for the application

Database

Website

Before including files

After including files

Code generator

Pages folder

Object name

Form name

Action

Title

Table

Field ID

Primary ID

Site folder

Class folder

Namespace

Admin table

Admin table primary key

CSS file path

Fields

SQL field type

NET field type

Null/Not Null

Fields display names

Input type

Input max length

Example data for all the fields:

Before including files

After including files

Forms authentication

Points of interest

License