Automatically Create Data Access Layers and Business Layers From Microsoft Access or SQL Server Databases

Introduction

This version of LayerGen is now obsolete and was replaced with LayerGen 3.5. Please refer to that project instead of this one!

Layergen is a program I wrote that will automatically create data access layers and business layers based off a SQL Server database or a Microsoft Access Database. Layergen will generate code in either C# or VB.NET, and is compatible with ASP.NET 1.1, ASP.NET 2.0, and ASP.NET 3.0 and up, in addition to Windows Forms applications and WFC apps. It also supports advanced stuff like data encryption and sorting.

The Goals of LayerGen

When I set out to design LayerGen, I did it with the following goals in mind:

1. Simplistic and easy to use: A lot of code generators out there require that you answer a lot of questions and be an expert in programming. I didn't want this. Other than a few basic questions, Layergen pretty much does what it's supposed to without a lot of technical mumble jumble. I feel this goal has been met.
2. Simple and straightforward code: Again, a lot of code generators create complex and hard to follow code. This can cause a problem when you need to modify the code to suit your needs. I feel LayerGen takes a straightforward approach. The code is clean and easy to understand, even for a novice programmer. Again, I feel this goal has been met.
3. Strong Compatibility: I wanted Layergen to be able to generate code for all versions of the language supported. This goal has been met because the generated code will work with VB.NET or C#.NET 1.1, up to the current 3.5 release.
4. Easily Extendable: I wrote LayerGen in such a way that, in future, additional database servers and additional languages would be easy to implement. This goal is not 100% complete, but it's probably about 80% complete. Once this is complete, I will draw up an API document, and third party developers will be able to make their own plug-ins to support any language or database server!

Using the Code

Using Layergen is pretty straightforward. When you run the program, you specify the SQL Server, the username, the password, the database, the target language, and a destination directory where the output files will be generated. If you click the Advanced Options button, then the following dialog appears:

From this dialog, you can enable sorting (strongly recommended) which will allow you to sort the record collections on any field in the table. You can also tell Layergen to automatically insert stored procedures into the table (experimental). If you do not enable this, then a Procedures.SQL file will be generated that will contain the needed Stored Procedures. Note that if you are using a Microsoft Access database, then the Stored Procedures will automatically be inserted whether you check this box or not. You can also enable data encryption. This will seamlessly encrypt all text data in the table.

After you pick your options, hit the Create Layers button and the table/view selector will pop up:

Now, you can put a checkmark next to each table or view that you want Layergen to create layers for. Note that if you select a table that has a foreign key to another table, then you must also generate layers for that table. Pushing the Select Dependents button will automatically select those tables for you.

Once you hit OK, the layers will be generated and will reside in the directory you specified earlier. It's that simple!

Using the Generated Code

To use the code, the first thing you need to do is insert the Stored Procedures into the database (unless you had Layergen automatically do this). You can copy the procedures out of the Procedures.SQL file and paste them into SQL Server. If your database is a Microsoft Access database, then you can skip this step because Layergen will automatically insert the procedures.

Once your procedures are in place, the next step is to include the generated files into your project. Each table has two files that are generated (one for the business layer and one for the data layer). In addition to these files, there is also a Universal file and an Interface file. The Universal file is basically your connection string to the SQL Server or Microsoft Access database. If you are using Microsoft Access, then you may want this connection string to be dynamic, based on where the user installed your application. There are comments in this file that show you how to put the connection string inside your Web.Config file, if you are working on an ASP.NET application.

Once these files have been included in your project, your project should compile with no errors. At this point, you are ready to use the code. Let's go through some examples. For these examples, let's assume you have two tables in your database, one called Address (with the fields: AddressID, FirstName, LastName, City, StateID, Zip) and another called State (with the fields: StateID, Statename).

To insert a new record into the database:

Dim State As New BusinessLayer.State

State.Statename = "California"
State.Save()

That's it! Pretty simple and straightforward, huh?

To retrieve a record from the database:

Dim State As New BusinessLayer.State(1) ' Retrieve record from State table
MessageBox.Show("The State is: " + State.StateName)

This would retrieve a record whose primary key is 1 from the database.

To retrieve more than one record at once and bind them to a DataGrid:

Dim States As New BusinessLayer.States()
States.GetAll()
States.Sort(FIRSTNAME, ASCENDING) ' Only works if you enabled sorting
dgStates.DataSource=States
dgStates.DataBind()

Notice how when we are retrieving multiple records, we use States rather than State. State is an object representation of a single record. States, however, is a collection class designed to hold multiple State objects. The collection class can be indexed (accessed like an array) or can be enumerated (so you can use For Each to cycle through all the records).

Here is an example of loading a record and accessing the foreign key:

Dim Address As New BusinessLayer.Address(2)
MessageBox.Show(Address.FirstName + " " + Address.LastName + _
                " Lives in "+Address.FState.StateName)

All foreign keys can be accessed though F<tablename> (in this case FState).

You can create your own custom queries in Layergen too. For example, suppose we want to retrieve only records where FirstName = 'Joe'. To do this, first, create the appropriate Stored Procedure in the database. You can use the GetAll Stored Procedure as a sort of template. Next, open the generated data access file (for example, AddressData.VB) and scroll down to the region that says "Custom Query Methods". Simply uncomment out the code and read the comments to implement your custom procedure. Finally, open the business file (for example, AddressBusiness.VB), scroll down to the custom query region, and follow the directions there.

I've included a sample application which is a very basic application that will show you how to insert, update, delete, and select records. Layergen, as you can see, has a lot of power. If you need any more help or examples, I'm glad to help!

Known Bugs/Limitations

There are a few limitations to Layergen:

1. Your table must have at least one primary key (it can have more, but only the first one will be used).
2. Identity must be set to true on the primary key. (This limitation is now obsolete with the new version.)
3. The primary key must be of type Int. (This limitation is now obsolete with the new version.)

The Future of LayerGen

Here are some future enhancements/goals for Layergen:

1. Ability to support plug-ins so that a third party developer could write either a SQL plug-in or a language plug-in. This has kind of already started. The source code was written in a way that would support this, but much work still needs to be done.
2. Ability to encrypt specific fields rather than the entire table.
3. Ability to make changes to your database structure and have Layergen automatically just "merge" the changes into the generated code (rather than having to remove the file, re-generate it with Layergen, then add it to your project again).
4. The ability to create your own custom queries and Stored Procedures within Layergen and save those queries so you can reuse them if the database structure changes.
5. Fix the dependency tracker button and add the ability to visually see your database structure in a diagram view (similar to the diagram feature in SQL Server).
6. Add the ability to pull fields from different tables and save it in a single object. This would mean having the power of a view, but with Save() ability!

History

• 7/12/2008 -- Released version 0.98b of Layergen.
• 7/13/2008 -- Added a sample zip file.
• 8/5/2008 -- Released version 0.99 of LayerGen! LayerGen now supports Microsoft Access databases as well as fixes some bugs. Specifically, the bugs fixed were in the C# generated code logic. I hope you guys enjoy this release!
• 8/14/2008 -- Updated Layergen to support SQL Server tables that have "unusual" characters in them. Also fixed a couple of bugs.
• 11/5/2008 -- Released version 0.99e of LayerGen! Several things were fixed in this version. First of all, primary key fields no longer need to have identity set to true. Also, these fields can be not only integers, but any other data type including UniqueIdentifier (GUID). In addition, the automatic insertion of stored procedures had a bug in which you could not insert procedures already in a SQL Database. This bug has been fixed. Also, SQL Server Windows Authentication is now supported by Layergen. Finally, there were many C# bug fixes. I know it has been a while since my last update. My job keeps me soooo busy. I have not had much of a chance to test these changes. There were a lot of big changes in this version. As a result, I also kept the old version up on this site, in case there is some huge bug that I didn't catch. Thanks for your support and keep leaving the feedback and wish-list ideas. I really try to implement all that I can, so don't think your wishes have gone ignored. Just keep in mind that my full-time job keeps me extremely busy and makes it hard to fit time into LayerGen.