Click here to Skip to main content
14,299,406 members

Data Access Layer Auto Generation Library

Rate this:
4.97 (38 votes)
Please Sign up or sign in to vote.
4.97 (38 votes)
17 Dec 2008CPOL
The proposed solution to avoid coding of data access layer in an ASP.NET project.
Image 1


Data Access Layer (DAL) is an important part in almost all architectures. I encountered problems creating DAL for large databases — maintaining DAL for these databases was another nightmare. So I thought of a generic solution to this problem, which was to auto generate the DAL.

I made use of System.CodeDom which is a code generator to produce source code in a particular programming language.

Using the Code

In order to generate DAL in your ASP.NET project follow the below steps:

  1. Add a reference of DalProvider.dll which is a DAL Auto-Generator Library
  2. Add the following under compilation tag in the web.config
        <compilation debug="true">
                 <add extension=".dal"

    This allows the compilation of any file with the ".dal" extension added to the project with our DAL auto generation library.

  3. Add a XML file to the App_Code folder in the below mentioned format with the extension ".dal"
        <connectionString>Data Source =XXXXX; Initial Catalog = XXXXX;
                      User Id =XXXXX; Password=XXXXX

    In this XML file under the "dal" root tag we need to mention the connection string of the database as well as the namespace to access the auto generated DAL.

  4. Compile your ASP.NET project. This generates the DAL with the following features:
  • Namespace "DAL1" as specified in the XML file
  • All tables are represented as classes with its columns as member fields that can be accessed using property with name in the following pattern "Col" + Column Name. This enables the users to create objects of tables.
    DAL1.TableName table1 = new DAL1.TableName();
    tabel1.ColColumnName1 = "2";
    table2.ColColumnName2 = "Trial";
    Response.Write ("Data in column2:" +  table2.ColColumnName2);
  • All stored procedure access methods are defined under the DataProcedure class. Thus to access a stored procedure the user has to use the respective stored procedure access function ("Access"+StoredProcedureName).
        DAL2.DataProcedure dbObject = new DAL2.DataProcedure();
    DataTable dataResult=new DataTable();
        int returnStatus = dbObject.AccessStoredProcedureName(ref dataResult,
            "21", "Thomas", "Atlas", "ITDept");
        if (returnStatus>

A reference to a datatable has to be passed to this function to retrieve the records fetched using the stored procedure.


The code is big but simple. The work flow of this library is as below:

  • Extract the connection string and the proposed namespace from the XML file during compilation
  • Query the systemobjects table and systemcolumns table to list out the tables and columns respectively
       --To extract the table information from database
         Select * from sysobjects where type = 'u'
       --To extract the column information with respect to the table
    Select * from syscolumns
  • Generate classes from the table names and create coulmns as member variables under it
  • Create a class named DataProcedure to hold the functions to access stored procedures
  • Query the systemobjects table to list all the stored procedures
    Select * from sysobjects where type = 'p' and category = 0
  • Query systemcolumns to get the parameters of the respective stored procedure
  • SELECT AS [Name],
    ISNULL(, N'') AS [SystemType],
    CAST(CASE WHEN IN (N'char', N'varchar', N'binary', N'varbinary',
        N'nchar', N'nvarchar') 
    THEN param.prec ELSE param.length END AS int) AS [Length],
    CAST(param.xprec AS int) AS [NumericPrecision],
    CAST(param.xscale AS int) AS [NumericScale],
    null AS [DefaultValue],
        CASE param.isoutparam WHEN 1 THEN param.isoutparam WHEN 0 THEN 
           CASE WHEN '' 
    THEN 1 ELSE 0 END END AS bit) AS [IsOutputParameter]
    dbo.sysobjects AS sp
    INNER JOIN sysusers AS ssp ON ssp.uid = sp.uid
    INNER JOIN syscolumns AS param ON (param.number = 1) AND (
    LEFT OUTER JOIN systypes AS baset ON baset.xusertype = 
        param.xtype and baset.xusertype = baset.xtype
     (sp.xtype = N'P' OR sp.xtype = N'RF')and( ?  and'dbo')
    CAST(param.colid AS int) ASC
  • Combine all the information and generate functions to access stored procedures

Points of Interest

This solution is a good example of using System.CodeDom.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Anoop Unnikrishnan
Software Developer
India India
Anoop Unnikrishnan is associated with a CMM Level 5 Company. He has done his Bachelor of Engineering in Information Science. His certifications include OCA,IBM SOA Associate, MCAD, MCTS and MCPD. Currently persuing MBA.

He is working on .NET since first Beta versions. He has also published his book "Start Programming with C#".

Grab a copy from

Anoop can be reached :

Comments and Discussions

GeneralMy vote of 2 Pin
ANJYR6-Aug-13 0:46
memberANJYR6-Aug-13 0:46 
QuestionDAL file has not created Pin
prabhu.d16-May-13 2:46
memberprabhu.d16-May-13 2:46 
GeneralLibrary has trouble with tablenames using special chars Pin
Thomas-H.13-Jan-09 23:32
memberThomas-H.13-Jan-09 23:32 
GeneralRe: Library has trouble with tablenames using special chars Pin
Anoop Unnikrishnan11-Mar-09 1:46
memberAnoop Unnikrishnan11-Mar-09 1:46 
GeneralObject reference not set to an instance of an object. Pin
Peter de Witte (wrk)22-Dec-08 21:54
memberPeter de Witte (wrk)22-Dec-08 21:54 
GeneralRe: Object reference not set to an instance of an object. Pin
Anoop Unnikrishnan25-Dec-08 23:10
memberAnoop Unnikrishnan25-Dec-08 23:10 
GeneralExcellent Article Pin
Thomas C.P20-Dec-08 0:55
memberThomas C.P20-Dec-08 0:55 
GeneralRe: Excellent Article Pin
Anoop Unnikrishnan20-Dec-08 19:41
memberAnoop Unnikrishnan20-Dec-08 19:41 
GeneralMy vote of 1 Pin
Ruchit S.18-Dec-08 18:51
memberRuchit S.18-Dec-08 18:51 
GeneralRe: My vote of 1 Pin
Anoop Unnikrishnan20-Dec-08 19:44
memberAnoop Unnikrishnan20-Dec-08 19:44 
Questioncan work on MS Access? Pin
guaike17-Dec-08 19:17
memberguaike17-Dec-08 19:17 
AnswerRe: can work on MS Access? Pin
Thomas C.P20-Dec-08 0:39
memberThomas C.P20-Dec-08 0:39 
GeneralMy vote of 1 Pin
[DK]KiloDunse16-Dec-08 10:13
member[DK]KiloDunse16-Dec-08 10:13 
GeneralRe: My vote of 1 [modified] Pin
Anoop Unnikrishnan16-Dec-08 18:46
memberAnoop Unnikrishnan16-Dec-08 18:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 16 Dec 2008


102 bookmarked