JohnKenedy Data Access Layer Library

• Download JohnKenedy_Library - 436.38 KB (include full documentation)

Introduction

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

The file consist 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 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 read 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 single instance in Windows based application.
3. Table schema is used for automation features (DataAccessTableManager, DataAccessTableDefinition, and DataAccessTableFiller). The features are

- Automatically create T-SQL Command created for all tables

- T-SQL Command for tables that contain Identity column can be adjusted to suit the 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 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. User 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 add new methods when generating codes. Programmer 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 the programmer created such as when storing Invoice in database, there will be a Header and Detail table, the programmer can write a new Plugin for that purpose for example named HeaderDetail.dll and put it in Plugin folder, and when generating codes, programmer specify a table to be Header and another table to be Detail, Header and Detail is called Role. Programmer often found that they are recreating models that they have created before, for example database model that store historical information such as User Data that can changed the user name, address, and so on. Programmer can actually write a new Plugin that generate 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 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 programmer.
JohnKenedy.DataAccessModule can generate different codes for different class. The scenario is, user select a module from module list display in JohnKenedy.DataAccessModule, and the module will display a list of roles and for each roles, user must input a tablename. All these roles are used in the selected module, a module is a small system that utilize one or more tables to perform a certain task.

JohnKenedy.DataAccessModule generates two class 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 used a same table.
Programmer can write their own module based on module occurrence every time they write application. The code generated by JohnKenedy.DataAccessModule is fully utilized methods prepared by JohnKenedy.DataAccess.

DataAccessTableFiller is a class that performs all Insert/Update/Delete methods. Because this class own 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 automatic T-SQL Command which describe 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 writing codes for it again and again, that's why, I think there should be some way that 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 use.

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 an DataAccessLayer object is created, this is the main object of this library, the object is created by passing two parameters, first is an enumeration that tells which database type, the second is the connection string which in this example is retrieve from web.config.

What you see later is, we check if DataAccessLayer.Manager is null, this Manager properties 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 check a session name, which 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 stored it in Manager property, and the library will automatically store it to session.

This session behaviors is also apply to DataAccessLayer.Audit property, this property is an object of DataAccessAudit class. While 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 look like this

JohnKenedy Library enables user 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 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 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 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 Programmer can use JohnKenedy.DataAccessModule tool to generate entity class and entity collection code. The code for using the Entity and Entity Collection class, is

Suppose we have MFood.cs and MFoodCollection.cs generates 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 and please let me know if there is bugs, or suggestions, thank you.

History

This is the first release of this application, If there is changes in the library I will update this article.