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)
- 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.
- 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.
- Table schema is used for automation features (
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
varchar) Library will adjust value length to the length required.
- Generate audit trail for every SQL statement (
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
- Can generate classes to simplify user (which is only a wrapper) while accessing data in table.
JohnKenedy.DataAccessModule Windows Application is the code generator.
- 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 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).
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
DataAccessTableFiller is a class that performs all
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
GetDelete… statement that creates
IDbCommand objects with properties set for
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:
DataAccessLayer _dal = null;
protected void Page_Load(object sender, EventArgs e)
_dal = new DataAccessLayer(SqlServerType.MSSQL,
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
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
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.
DataAccessTableFiller _filler = DataAccessLayer.Manager["MFood"].GetFiller();
_Filler.AddColumnValue("Name", "Chicken Soup");
IDbCommand _command = _filler.GetInsertStatementFilterIdentity();
long _value2 =
_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
MFood _food = new MFood();
_food.Filler.AddColumnValue("Name", "Chicken Soup");
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.
- 10th July, 2008: This is the first release of this application.
If there are changes in the library, I will update this article.