Attention
Layergen 3.5 Has Been Released! Consider This Article Deprecated. Find the New One here: Layergen 3.5
Introduction
LayerGen MMX is a project I designed to automate and simplify the creation of business and data layers. It's been a work in progress since 2001 and finally it's at a point where I am proud to present it here for everyone else to enjoy. One of the goals of LayerGen was to make it simple and not add a lot of complex code and overhead. Many endless hours of hard work went into the creation of this product and hopefully it can be a good tool for everyone else to enjoy.
Background
For those of you unfamiliar with the business/data layer model, I will attempt to give a simplified explanation here. Most applications created today (both Winform and Web applications) have some kind of database backend for storing information. LayerGen MMX is designed to work with both Microsoft SQL Server (any version from 2000 and up) and Microsoft Access (any version from 2000 and up, although the 2007 native version of Access is not supported yet).
There are many ways your application can communicate with your database. One of the most accepted and effective ways, however, is through the use of layers (or sometimes called 3-tier programming). Three-tier programming is a programming style that divides your code into 3 layers (or tiers). The bottom most layer is called the Data Layer. The function of the data layer is to communicate directly with the database. How it communicates is of no concern to the other 2 layers. Although Layergen MMX uses ADO.NET for all its database communication, you could technically use any method you wanted. The second layer is called the Business Layer. This layer's job is to communicate with the data layer to retrieve and store data. In addition, it also applies any kind of business rules you may have. Business rules can be any kind of manipulation done to the data before it's presented to the third and final layer, our UI layer. The UI layer is responsible for what the users of your application will see.
Using the Code
When you run LayerGen MMX, the main screen pops up. I will go through each option in LayerGen MMX. In the "Language Options" panel, the first drop down is the language. LayerGen MMX can create code in either C# or VB.NET. To be consistent, you'll probably want to pick the language that your application will be written in.
Under that drop down is another drop down where you can select the version of .NET you will be targeting. Layergen can create compatible code for any version of .NET from 1.1 up to 4.0. If you select version 2.0 or higher of the .NET framework, then Layergen MMX will create partial classes so you can add your own functionality to the generated classes without having to edit the generated LayerGen MMX source files. Likewise, if you select 3.5 or higher, then LayerGen MMX will generate classes that are LINQ friendly (an example is shown later).
Below that drop down are 3 checkboxes. By selecting the "Suppress all comments" box, LayerGen will not generate any comments in the code. Unchecking this box will insert comments in both the source code and the database stored procedures. By selecting the box "Encrypt Text Fields", LayerGen MMX will store all text in the tables encrypted. This may be useful for tables that store passwords or credit cards, for example. Note that encrypted text is usually longer than regular text, so make sure if you enable this, you allocate enough length in your database to compensate.
The final checkbox in the "Language Options" panel requires a little explanation. Typically in database design, you will have many tables that relate to other tables. Because of this, deleting a record out of a database may cause an error if the primary key of that table relates to a foreign key in another table. Typically applications that delete rows in a table do not actually "delete" the rows. Instead the application will mark the row as deleted by setting a boolean or bit flag value in the table as deleted or void. If you check this option, a textbox will appear asking for a field name. This field name should be a bit or boolean field name. What LayerGen MMX will do is whenever a table is encountered that has a field with this name, it will generate a special Delete() function in the code that will set this field to true, thus "deleting" it. The row still remains in the database, however LayerGen MMX will not retrieve it through its GetAll() method.
Under the "Database Options" panel is a drop down list that populates with all the available plugins that LayerGen MMX can support. LayerGen MMX ships with 2 plugins, one for SQL server and one for Microsoft Access. Different options appear depending on the plugin selected. If you choose the Microsoft Access plugin, you must browse and find the .MDB file. If you choose the SQL Server plugin, you must specify the server name that has the SQL Server. In addition, you can choose whether you want to use SQL Server Authentication (in which case, you must specify a user name and password) or whether you want to use Windows Authentication. Also, you must type in the name of the database you want to create layers for. One final option is a check box that says "auto-insert stored procedures". If this is checked, LayerGen MMX will automatically create or update the needed stored procedures and insert them into the database. If this is not checked, then you must manually insert the stored procedures. LayerGen MMX will create a "Procedures.SQL" file that contains all the necessary stored procedures.
The final panel is "LayerGen Options". In this panel, you must specify an output directory. LayerGen MMX will place all generated code in this directory. There is also an option to either overwrite or append to any existing files. You will almost ALWAYS want to overwrite. Finally, there is one last check box that reads "Encrypt connection string". If this is checked, the database connection string will be encrypted. This is especially useful for web applications, however it should only be used when it will be necessary.
Once all the options are set, go ahead and hit the "Create Layers" button. Layergen MMX will popup with a dialog box that contains all of your database's tables and views (excluding system objects). Put a check mark next to each table you want LayerGen MMX to create code for. In addition, you can select all of them by pushing the "Select All" button or you can deselect all of them by pushing the "Deselect All" button. Hit the OK button and LayerGen MMX does its work. Be patient, depending on whether your database is local or remote, it could take a few seconds for LayerGen MMX to complete. Once its done, LayerGen will popup a dialog box that will report its success.
That is it! Navigate to the output directory you specified and you should see all your files!
Using the Newly Generated Code
If you targeted .NET 1.1, then there will be 2 files for every table you selected. If you targeted .NET 2.0 or higher, then there will be 4 files for every table. In addition to these files, there will also be 2 other files called "Universal.VB" or "Universal.CS" and "Interfaces.VB" or "Interfaces.CS". The "Interfaces" file contains some Interface definitions that are used internally. The "Universal" file contains a static (or shared) function that returns the database connection string. You are free to modify this file any way you want. You will find some comments on how to pull the connection string from your Web.config file, if you are creating an ASP.NET web application.
To get this code working in your application, you need to either add the existing code to your project or create a new class project and add the files to that (be sure to reference the new class project in your application).
For the purposes of this demonstration, I am going to pretend you have a database called Addresses with the following 2 tables: <Address> and <State>. The address table has a foreign key called "StateID" which links up with the State table. Both tables have autonumbering primary keys (or identity). After running LayerGen MMX on both of these tables and adding the code to your application, you can access the tables like so:
In C#:
BusinessLayer.State State = new BusinessLayer.State();
BusinessLayer.Address Address = New BusinessLayer.Address();
State.ShortName = "CA";
State.LongName = "California";
State.Save();
Address.FirstName = "Bill";
Address.LastName = "Murray";
Address.StateID = State.StateID;
Address.Save();
In VB.NET:
Dim State As New BusinessLayer.State();
Dim Address As New BusinessLayer.Address();
State.ShortName = "CA"
State.LongName = "California"
State.Save()
Address.FirstName = "Bill";
Address.LastName = "Murray";
Address.StateID = State.StateID;
Address.Save();
What this will do is, first create a new State object. If you do not specify a primary key value for the constructor, then it assumes it's a new record. We set the ShortName field to "CA" and the LongName field to "California". We then run the Save() method which commits the changes to the database (in this case, it will add a new row to the database). We do the same thing to create a new address record. Notice we are setting the StateID field (our foreign key) to the value of our State object's StateID. When we executed the Save() method on our State, it automatically populates the StateID with the identity value that SQL Server used as our primary key. To read this information back from the database, you would use code like this:
In C#:
BusinessLayer.State State = new BusinessLayer.State(1);
BusinessLayer.Address Address = new BusinessLayer.Address(1);
In VB.NET:
Dim State As New BusinessLayer.State(1)
Dim Address As New BusinessLayer.Address(1)
The "1" we are passing here in the constructor is the value of our primary key. LayerGen also creates a collection class for each of our tables. The collection class is strongly typed to store multiple rows or objects of our table. If you are using .NET 3.5 or higher, you can also use LINQ to execute queries on the Collection classes.
One other thing to note is that any foreign keys in a table can be accessed through a special property named F<ForeignKeyName>. This will create a new primary key object and allow access through it seamlessly. For example, to extend the example above, you could do this too:
In C#:
BusinessLayer.Address Address = new BusinessLayer.Address(1);
MessageBox.Show("Bill Murray lives in " + Address.FState.LongName);
In VB.NET:
Dim Address As BusinessLayer.Address(1)
MessageBox.Show("Bill Murray lives in " + Address.FState.LongName);
Bugs and Gotchas
There is one important rule to using LayerGen MMX. Your table must have a primary key. It doesn't matter what type or if its autonumber, but it must have a primary key. The only exception to this is views. Views in LayerGen MMX are created as read only objects (hence there is no Save() method or Delete() method). This makes sense since you can't add rows or delete rows from views. This is the only known limitation/bug in the program.
Contact or Help
I am creating a user's manual, however, it is not all the way done yet. I attached it as a download at the top of this article. If you need more help, feel free to contact me at icemanind@yahoo.com or leave comments on here if you need any help or have any bugs to report!
History
- 4th January, 2010: Initial post
