Data Access Layer Documentation

March 9, 2009

I tried to find a tool that can trace the dependency between “Data Access Layer” and database tables. I didn’t find a proper tool for that. Actually it’s very important to have this tractability. Especially if any change is required.
I tried to make new tool to maintain this. I started with quick and dirty web application to manage this. It needs a lot of work to be usable. Like:
1- Allow multi-project data to be on one installation.
2- Record the dependency between the Data Access Layer and the business or presentation layer.
Using this Application you can add your database tables. Then, create your Data Access functions and relate them to database table. You can also keep the query for each function. Finally you can get a report showing the dependency between data access layer and the tables.
It’s doing simple job but I think it’s important.
You can download the source code from here


Data Access Layer Documentation

September 28, 2008

Code documentation is a one from the most effective criteria makes code maintainable and usable. Change management board is relaying on such document also to find out which places the change will affect.

One of the important areas to cover in code documentation is “Data Access Layer”. For me, it should tell me a lot of information:

  • Which tables, stored procedures and DB functions the data access function hits?
  • Which columns from which tables the function retrieves (in case of selection)?
  • What are the conditions filters the data? In addition to grouping and sorting conditions?
  • Information about the connection and transaction.

Sometimes, I use this template for each data access layer function:

Function Name

Database objects

  • Table names
  • Stored procedures
  • Functions

Main operations

  • Table (add/update/remove)

Where conditions

  • Conditions

Group by conditions

  • Conditions

Having conditions

  • Conditions

External Usage

  • functions or modules using this function


Using documentation like this, the database administrator can tune the database by creating proper indexes. The requirement change board can decide changes either in database side or application side. In addition to: helping the data access layer functions users and helping developers trying to maintain the code in the future.

Multilingual System Design: (3)Data Layer (Stored Procedures)

May 16, 2008

In this post, I will discuss the logic contained in the data layer. The data layer logic is all in stored procedures and functions.

First, Let us discuss the logic for the Languages table. The following stored procedures may be defined on the languages table:

  • AddLanguage: Add new language to the system. You must take care that this stored procedure not only adds a record in the languages table but it also adds a record for that language into all child tables.
  • DeleteLanguage: Delete language from the system. You need to remove all the records related to that language from child tables. You have to check also if it’s the default language for any user then he/she has to change it.
  • UpdateLanguage: Update the details for a language.
  • GetLanguageDetailsById: Retrieves all the language data using language ID.
  • GetAllLanguages: Retrieves all the languages supported by the system.

Then we can define a group of stored procedures on every multilingual parent-child tables:

  • AddRecord: This stored procedure is defined on the parent child records. It first adds a record in the parent table then adds its child record for a specific language and adds empty child record for each other languages. This stored procedure may use two other stored procedures or functions:
    • AddParentRecord.
    • AddChildRecord.
  • UpdateRecord: Updates a parent record and one of its related child so it needs to take a language parameter. Also, it needs another two stored procedures:
    • UpdateParentRecord.
    • UpdateChildRecord.
  • DeleteRecord: Deletes a record from parent table and all of its related child records. Again, it needs the two stored procedures:
    • DeleteParentRecord.
    • DeleteChildRecord.
  • GetRecoredByLang: Retrieves the parent record and one of its children defined using the language paramenter.
  • GetAllRecoreds: Retrieves the parent record and all of its children.

After developing these procedures for all tables, you need to move to the data access layer. In this layer, you have to make functions that allows you to connect to the database and access your tables and stored procedures. It’s so basic and straight forward so I will skip it.

Next time, I will talk about the business logic.

Multilingual System Design: (2)Data Layer (data structures)

May 15, 2008

After taking a look on the system, in this post I will describe the suggested design for the Data Layer.

In the data layer we need to have a place to store the languages included in the system. This design support dynamic number of languages even while the system is running. So, a table to store the languages is needed.

In this table we have the Language ID (LangID) that stored a number uniquely indentifies the language I think using the Local ID as LangID will be better or it can be added as a new column in the table. Then we have the language name stored the name of the language written in that language so this column must have NVarChar type. Also we have Language direction identify if the language displayed in right-to-Left or left-to-write order. This information can be retrieved from the CultureInfo object so we can neglect it from the table.

Sometimes the system users table has a column that refers to the default language for the user. Some times its gained from the current culture of the operating system deployed in the user machine. I think prefer storing it in the users table.

Let us move now for the most important part of the system. How to design the database to support multilingual content? First design your database without considering its multilingual. Then, for each table has columns may contain information varying from language to another: first: leave the columns that not depending on the language in the same table then add a new table have a composed primary key (the key from the parent table and the language) and move all the columns depending on the language into that table. So in the new table the parent table primary key will be a foreign key to the parent table.

So, if we have a branches table contains: branch id, name, address, manager and telephone number. We can put the branch ID, manager and telephone in the parent table. While the child table will contains branch ID, language, name and address. Branch ID is a primary key for the parent table. In the child table, the primary key is composed from branch ID and language. Branch ID is a foreign key in the child table.

So if we have the branch data as following:


English Name

Arabic Name

Manager ID


English Address

Arabic Address


Cairo Branch

فرع القاهرة





Then we need to add a record the branch table:


Manager ID





And two records in the branch details table:



Branch Name

Branch Address



فرع القاهرة




Cairo Branch


In the next post I will continue the talk about the data layer

Multilingual System Design: (1)Overview

May 13, 2008

A lot of multinational companies and organizations in general have audiences around the world with different languages and cultures. These organizations have an increasing need to develop their information systems targeting these audiences with their own languages. Not only the interface but also the content of the system must match the audience language.

In this series I am suggesting a design for a multilingual system. This system has a multilingual content viewed in multilingual user interface. I used this design in many systems and was very useful. Especially when in case of medium to large systems. The systems done with this design are used in RTL and LTR orientation. But this model is design for windows application. I think it needs many modifications to fit to web based applications.

This design is designed in comply with Microsoft technologies. I tried to make it generic but when it gets specific it will tends to be Microsoftian. Because I made this design to be implemented with MS SQL and .NET technologies.

Let us start with a general overview on the system. The system is consisting of four layers:

  • Database Layer.
  • Data Access Layer
  • Logic Layer.
  • User Interface Layer.

The database layer deals with the multilingual content. Not only data structures but also the basic logic that manipulating the data. Data access layer is the layer used to retrieve data from database. Logic layer is the least layer that multilingual design affects but we will see that there are some modifications need to be done in this layer to be complied with the multilingual functionality. Finally the user interface layer is used to display the multilingual content but in a multilingual interface so the forms, messages…etc that used to view and take commands from user is also multilingual.

In the next post I will start with Data Layer ISA…

Converting C# Code to Flowchart

May 3, 2008


Expressing the program logic in a diagram give it the advantage of making it more understandable. This comes from the fact that the human brain is thinking in graphical way. Most of developers document their code with a flowchart. It will be better it we create a tool doing this job. We use a recursive decent parser parses a C# code and draw the corresponding flowchart.

C# flow control:

In C# language there are a lot of flow-control statements:

  1. IF-Else.
  2. While.
  3. Do-while.
  4. For.
  5. Foreach.
  6. Switch.

Each of these statements affects the flow of events in C# code. Some of these statements are considered decision statement (If-Else and switch). The others are considered loop statements (while, do-while, for and foreach).

Decision statements put a condition to go through a block of code. If the condition occurred the block of code will be executed. Otherwise, the block of code will be skipped. If-else statement exactly does this job CFG:

If-statement := if
{ statements1 } [else { statements2 }]

The if part is necessary put the else part is optional. So the flowchart of the if-else statement takes two forms:

The switch statement guides the program to a path from multiple paths. The CFG of switch statement is:

Switch_statement := switch
(expression) { { case value: statements break; } }

As the switch statement has multi-paths the flowchart will be in the form:

Loop statements repeat a block of statements according to a condition. Only the expression and the expression place changes from a statement to another. The do-while loop which have the following CFG:

Do-while := do
{ statements } while
(condition );

Takes the form:

The other statements have the same form in flowchart. There CFG’s are:

While-statement:= while
(condition )

For-statement:= for
{ statements }

Foreach := foreach(foreach-exp)
{statements }

The flowchart form of these statements is:


The process starts when a new code entered. This code enters a scanner that output tokens. The tokens enter a recursive decent parser gives a parse tree represents the entered code. The parse tree then enters the drawing module to be displayed as a model.


The scanner takes the code and tries to split the code into a set of tokens each token belongs to one of these types:

  1. Flow-control keyword: keywords represent one of the six flow-control statements discussed in C# flow control section.
  2. Block start: like “{” symbol.
  3. Block end: like “}” symbol.
  4. While spaces.
  5. Separators: set of symbols separate two tokens.

According to these types tokens will take there places in parse tree discussed in next section.

Parse Tree

The parse tree structure used in this tool must have two criteria. First it can represent the sequential statements. Second, it must represent the nested statements. So we use a tree structure represented by these classes:

Stake current=new TreeNode(“main block”);

Class TreeNode


Block current

Public Relation[] relation;


Class Relation


String Name;

treeNode from;

treeNode to;


TreeNode is a class represents a node in the parse tree. Each node have object from Block class represents a specified block in flowchart may be decision, loop start, loop end, process start state or end state.
In TreeNode
there is array of relations represents the relations from this TreeNode to its child nodes.


The function of parser is to take the tokens and place them in their places in a parse tree. The parser specifies the token place in parse tree according to token types. If the token is new flow control token it will be added as a new node in the node existing in the top of stack and this new node will be pushed in the current stack. If token is not a flow control token, the token will be added in the “current” block in the current node. Finally if the token is block termination token (like: } ) pop the first node in current stack.


After the parser produced the parse tree, the Drawer takes the parse tree and draws a flowchart representing it. The Algorithm used in the Drawer as follow:

An algorithm is needed to determine the place where the drawer should place the shapes it draws. The y-position is the same for nodes have the same parent. While the x-position should be incremented for a child than the previous one.


To Convert a C# code to flowchart you need to parse the code searching for the flow control statements and code blocks then its very simple to draw the flowchart if you have a well structured parse tree.