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


Development on the (Traditional Egyptian Café) Style

January 24, 2009

In the Traditional Egyptian Cafés they are serving customers in a very unique way. The waiter is going to customer to collect the order without single piece of paper. After that he will call the person  making the drinks in a load voice: “And give me 1 cups of tea for Mr. X and make it well” as he knows every customer by name. In Arabic its it will be: “ We Ma’ak Wahed Shai Lee A’am X We Salaho”.

Lately I found out that some software makers are using the same technique. The person in charge will go and meet the customer to collect the requirement. As there is no written one from the customers the customer will tell the requirements verbally. Of course customer will forget or miss some points (X1). After that the person in charge will set with the developer(s) and tell them what the customer requirements are. Again the person in charge will miss or forget some points (X2). The developer again will miss some of the requirements while he is developing the program (X3). Finally, the requirements will be X – X1 – X2- X3= fight to figure out is it in scope or a change request. If you tried to tell them that they are better to write the requirements and singe it off from the customer.  There is no time for this, they will answer.

Let me explain also how much extra time they will spend if they didn’t write the requirements. First, the struggle about the requirements as discussed before. Then, imagine if the developer want to confirm something. As he/she don’t have reference to check in it, he will go back and ask the person in charge about the issue. Sometimes the person in charge also has to get back to the customer to confirm it. What if the tester wants to develop test cases?? The person in charge also has to set with the tester to transfer the requirements (and the same thing will happen). What if there is more than one developer involved in the thing. I will let you imagine how they will work and collaborate.

I can only tell these software makers one thing:

LET THE WAITER WRITE DOWN ORDERS


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.


SharePoint Content Deployment and Migration API

May 16, 2008
  • Using deployment and migration APIs contents may sync between different sites using deployment packages these packages may be translated before they uploaded to the target site.
  • Its move all of the list related data (permissions, pages, web Parts,…).
  • It’s Very strong if we talk about the way it gets the relative data for very item.
  • Reserve ID or create new one.
  • Supports incremental deployment. So, it can deploy new changes only.
  • Limitations:
    • Incremental export/import is supported only in the site scope.
    • In the lists the old items will not be overwritten they will duplicated. But in case of document libraries: the old items overwritten.
    • Unexpected behavior when using to transfer list from a variation site to another variation site have the same list.
    • Can’t export items from different site collections in the same operation.

My SharePoint Variations Notes

May 16, 2008
  • Features:
    • Building multilingual sites by building a site (called Label) for every language and transferring the changes from source language site to all other sites.
    • Redirecting the user to the site matching the browser language.
    • Placing a control to change the language in the top navigation bar.
    • A service is running every 20 min to propagate changes from the source language site to all other sites.
  • Limitations:
    • Transferring changes from the source language to others. So changes in any language other than the source are not reflected in other sites.
    • Pushing a new version from a page to all sites even if there is a minor change like spelling mistake.
    • One to one relationship with site collection (one variation per site collection).
    • Lists changes are transfer only when it’s contained in a new site created.
    • When creating sites using features the target sites created without localization.
    • The localization is working only when creating new sub site and the label sites.
    • List view web part references the source site list in every site it is propagated to(because it use the list ID).
    • Variation fails when trying to push a page when there is a page with the same name but with different content type in the destination site.
    • Permissions are not propagated.
    • Works only on publishing pages.
    • Can’t change default page layout.
    • Don’t support site restore.
    • Variation sites must be in the same level and their parent site must be the root of the variation.
  • Risks (from blogs):
    • Sometimes variations cause performance issues.
    • May cause an expected behavior with heavy look and feel customizations.
  • Variations in other Blogs:

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:

ID

English Name

Arabic Name

Manager ID

Telephone

English Address

Arabic Address

1

Cairo Branch

فرع القاهرة

200

+2022000

Cairo

القاهرة

Then we need to add a record the branch table:

ID

Manager ID

Telephone

1

200

+2022000

And two records in the branch details table:

ID

Lang

Branch Name

Branch Address

1

1025

فرع القاهرة

القاهرة

1

1033

Cairo Branch

Cairo

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