JohnKenedy Data Access Layer Library

• Download library - 162.1 (includes full documentation)

Introduction

JohnKenedy Library is a .NET 3.5 library. This library is an alternative approach to create n-tier database applications with ADO.NET and minimize the dependency of data access created by .NET.

The file consists of two projects:

• JohnKenedy.DataAccess (Main Library)
• JohnKenedy.DataAccessModule (Windows Application)

Main Features

1. Easy to create different database connection (MySQL, Microsoft SQL Server, Oracle and one that uses Oledb) by using a single wrapper class named DataAccessLayer which can adapt to different connection type, command type, adapter type and so on.
2. Automatically reads table information schema when required and stored in memory to prevent reading redundancy. This information is prepared by DataAccessTableDefinition which can be stored in ASP.NET session or used as a single instance in a Windows based application.
3. Table schema is used for automation features (DataAccessTableManager, DataAccessTableDefinition, and DataAccessTableFiller).
   The features are:
   • Automatically create T-SQL Command for all tables
   • T-SQL Command for tables that contain Identity column can be adjusted to suit them and easily retrieve the identity value generated by database
   • T-SQL Command for tables that contain not nullable column can be adjusted so that when the programmer does not fill a value for not nullable column, library can automatically fill a default value for those columns
   • T-SQL Command for tables that contain string, Library will truncate string value if it exceeds the maximum character length.
   • T-SQL Command for tables that contain fix string (char/varchar) Library will adjust value length to the length required.
4. Generate audit trail for every SQL statement (Insert/Update/Delete) that executes from this library, the audit trail can be associated to login user, could be stored in text file, database table or both. The job is done by DataAccessAudit class.
5. Can generate classes to simplify user (which is only a wrapper) while accessing data in table. JohnKenedy.DataAccessModule Windows Application is the code generator.
6. Users can create their own extended modules or library and put it in Plugin folder in JohnKenedy.DataAccessModule Windows Application. The Plugin consists of DLL files that inherit from ModuleExtendDefinition class. The Plugin adds new methods when generating codes. Programmers often have their own database model (several tables that relate to each other to create a functionality) and the model is often used by different projects that programmers created such as when storing Invoice in database, there will be a Header and Detail table, the programmers can write a new Plugin for that purpose, for example, named HeaderDetail.dll and put it in Plugin folder, and when generating codes, programmers specify a table to be Header and another table to be Detail, Header and Detail are called Role. Programmers often find that they are recreating models that they have created before, for example database model that stores historical information such as User Data that can change the user name, address, and so on. Programmers can actually write a new Plugin that generates code for retrieving the current data (name, address, etc.) and code for retrieving the data by date, for example, the user’s name in the past (before change).

Concepts

JohnKenedy.DataAccessModule can generate entity classes that are used directly from user interface for data operations or can be wrapped in Business Objects created by the user. JohnKenedy.DataAccess is the main library called by entity classes or can be called directly from Business Object. All crucial operations done in JohnKenedy.DataAccess are logged as audit trail and stored in Database. The audit process hides from the programmer.

JohnKenedy.DataAccessModule can generate different codes for different classes. The scenario is, user selects a module from module list display in JohnKenedy.DataAccessModule, and the module will display a list of roles and for each role, user must input a tablename. All these roles are used in the selected module. A module is a small system that utilizes one or more tables to perform a certain task.

JohnKenedy.DataAccessModule generates two classes for each table, Entity class and EntityCollection class. The code is adjusted based on the role the table has taken, each table may take different roles in different modules because two or more modules can use the same table.

Programmers can write their own module based on module occurrence every time they write an application. The code generated by JohnKenedy.DataAccessModule is fully utilized in methods prepared by JohnKenedy.DataAccess.

DataAccessTableFiller is a class that performs all Insert/Update/Delete methods. Because this class owns an object of DataAccessTableDefinition that stores information about the table, DataAccessTableFiller or we will call it filler from now on, uses this information to create an automatic T-SQL Command which is described in automation features. User gets a new instance of filler object, and then adds column value to the filler class. Then user just calls method start with GetInsert… GetUpdate… or GetDelete… statement that creates IDbCommand objects with properties set for CommandText, Parameters, and CommandType automatically.

Background

I often felt that I have rebuilt the same database model again and again and written codes for it again and again. That's why I think there should be some way in which we can actually create a tool to list all database models that we had created and then the tools can generate the same code for us.

Using the Code

To use the code, you should read the full documentation. In short, here is the way we use the library:

Initialize

using JohnKenedy.DataAccess;

DataAccessLayer _dal = null;
protected void Page_Load(object sender, EventArgs e)
{
    _dal = new DataAccessLayer(SqlServerType.MSSQL, 
    System.Configuration.ConfigurationManager.
            ConnectionStrings["connection"].ConnectionString);
        
    if (DataAccessLayer.Manager == null) 
	DataAccessLayer.Manager = _dal.GetDataAccessTableManager();
    else DataAccessLayer.Manager.DALLayer = _dal;
        
    if (DataAccessLayer.Audit == null) 
        DataAccessLayer.Audit = _dal.GetAudit();
}        

You see a DataAccessLayer object is created, this is the main object of this library. The object is created by passing two parameters, the first is an enumeration that tells which database type, the second is the connection string which in this example is retrieved from web.config.

What you see later is, we check if DataAccessLayer.Manager is null, this Manager property is an instance of DataAccessTableManager class. This object is used to store a collection of table definitions or instance of DataAccessTableDefinition. We check if it is null, and when we trace back to the code, the code automatically checks a session name. This session is used to store any DataAccessTableManager object created before. If session contains a previous one, the previous one is used so this minimalized the library to query database to check for table schema. If not, then we will need to create one and store it in Manager property, and the library will automatically store it to session.

This session behaviors also apply to DataAccessLayer.Audit property. This property is an object of DataAccessAudit class. This object is used to log audit trail in database table or in file. To modify the default setting for Audit, check DataAccessStringAndDefault.GetDefaultAudit() properties.

Insert/Update/Delete Operations

Suppose we have a table that looks like this:

JohnKenedy Library enables users to insert to this table, update, and delete without creating any SQL command. The SQL command will be generated by libraries automatically and it has several automation features as described in the Introduction section.

_dal.OpenConnection();
DataAccessTableFiller _filler = DataAccessLayer.Manager["MFood"].GetFiller();
_Filler.AddColumnValue("Name", "Chicken Soup");
_Filler.AddColumnValue("Price", 1000);
_Filler.AddDefaultColumnValueForOtherNotNullableColumn();
IDbCommand _command = _filler.GetInsertStatementFilterIdentity();
long _value2 =
    DataAccessLayer.Manager["MFood"].
    ExecuteNonQueryInsertIdentityValueFromCommand(_command);
_dal.CloseConnection(); 

The _value2 will contain the NoFood identity value. If the Name length is more than 50 characters, the text will be truncated to 50 characters length before the insert to prevent error. Although we don't supply a value for Status, the library will automatically add default value for column Status, the default value for int is 0, that’s why the code will not error. The truncate and automatically add default value will be recorded in the Audit Trail. Read Preparing Audit Trail section to prepare Audit Trail for library in the Full Documentation (JohnKenedy DAL.pdf).

The example above uses DataAccess directly, in fact the programmer can use JohnKenedy.DataAccessModule tool to generate an entity class and entity collection code. The code for using the Entity and Entity Collection class, is given below.

Suppose we have MFood.cs and MFoodCollection.cs generated from JohnKenedy.DataAccessModule:

MFood _food = new MFood();
_food.Filler.AddColumnValue("Name", "Chicken Soup");
_food.Filler.AddColumnValue("Price", 1000);
_food.Filler.AddDefaultColumnValueForOtherNotNullableColumn();
long _value2 = _food.InsertIdentity();

Points of Interest

Feel free to use this library. Please let me know if there are bugs or suggestions. Thank you.

History

• 10^th July, 2008: This is the first release of this application.
  If there are changes in the library, I will update this article.