Three Tier Code Generator For ASP.NET

• Download latest version of source code - 121 Kb
• Download demo version - 176 Kb

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

1. Create a Database e.g.. ThreeTierDemo_db
2. 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

3. 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

1. Create a new project in Visual Studio .NET e.g. http://localhost/ThreeTierDemo
2. 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>

3. 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

   

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

   

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 appears 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 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
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.

   

5. Appropriate permissions need to be set on 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

   

   After including files

   

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 purpose.
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 to compare the information.

Forms authentication

To implement forms authentication, follow the following steps:

1. Copy the folder SourceCode/Login and include it in the project.
2. Copy the 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.

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

4. 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.