FYI Layergen

icemanind

Member
Joined
Mar 20, 2008
Messages
12
Programming Experience
5-10
Hey guys,

I've been working on a program for some time (and I know this isn't the first one of its kind, but I like it..lol) that will automatically generate a Data Layer and Business Layer from a database in SQL Server. It will create both VB.NET and C# code. I thought you guys might find it useful and want to check it out, plus I'd appreciate the feedback. I developed it in Visual Basic .NET 2005 and the solution is a Visual Studio 2005 solution. To use it, you must have the Microsoft .NET Framework 2.0 installed on your machine. Its pretty simple to use the program. Run it, fill in the required information (SQL Server IP Address, username, password, and the language you want to create), then hit the "Create Layers" button. Once you do that, a dialog will pop up asking you to pick the table(s) and view(s) you want to generate layers for. There is also an advanced options dialog. This dialog will allow you to pick advanced options, such as the option to enable sorting, the option to automatically insert stored procedures into the sql server (experimental!), and the option to use data encryption. Data encryption will automatically encrypt all the text fields in the table, but you can still seamlessly access the data without worrying about it (it basically encrypts and decrypts on the fly) (btw, this feature has only been tested with VB.NET and not C#, so use at your own risk). Once the layers have been created, all you need to do is open the Procedures.SQL file and insert the stored procedures into the SQL Server (unless you automatically did that), then add all the C# or VB.NET files to your project. Here's an example of how to use the code in your own project. Let's say you have a database with 2 tables, Address (with the fields, AddressID, FirstName, LastName, Address, City, StateID, and Zip) and State (with the fields, StateID and StateName). Let's assume the Address table has a foreign key to the state table (StateID) and lets also assume you want to bind all the addresses to a datagrid (called dgAddress):

VB.NET:
Dim Addresses As New BusinessLayer.Addresss
Addresses.GetAll()
' The following line will sort the collection, if you enabled sorting
Addresses.Sort(LASTNAME, ASCENDING)
Me.dgAddress.DataSource=Addresses
Me.dgAddress.DataBind()

Thats it!

Now if you want to address just a single record, use something like the following:

VB.NET:
Dim Address As New BusinessLayer.Address(primary key)
Address.FirstName="Sam"
Address.LastName="Goody"
Me.lblState.Text=Address.FirstName & " " & Address.LastName & " lives in " & Address.FState.StateName
Address.Save()

Notice how you can easily access the primary key table from the foreign key? Basically, each table gets 2 classes generated. A main data access class and a collection class (that can be sorted, enumerated, indexed, etc...). Anyway, should be fairly simple to figure out. IF you have any questions, feel free to ask away and I will answer them! The only limitation really is that any table you use, must have a primary key and this primary key must be Int. I may change that requirement in the future, if it becomes a big deal.

I hope you guys enjoy it!
 

Attachments

  • LayerGenBinary.ZIP
    45.8 KB · Views: 76
  • LayerGenSource.ZIP
    67.3 KB · Views: 83
Last edited:
I Couldn't get this to connect to a sqlserver express server. Is it possible to use it with SQLSERVER EXPRESS.

Kind Regards

Scott
 
You know, I'm not sure. I've tested it with SQL Server 2000 and SQL Server 2005. Not sure if it works with express. If you are having difficulties, let me know and I will play around with it and see if I can't get it to connect to Express.
 
Sorry,

My mistake, I managed to get it to work with sqlexpress.

I am impressed at its n-tier code generation abilities. I am also impressed that you have written this application using vb.net. Most code generators I have seen have been done with c#

Would you mind if I played around with the source code. I have been looking for a good code generator for a long time and was going to attempt to write one myself.

Kind regards

Scott
 
Not at all. I'm going to probably make it open source anyway, so any improvements you want to make would be fine by me.
 
Hi Icemanid - not sure if you will see this message but I think your app is great and I am wondering if you are still developing it - is 0.98b the latest version?

I have had a peak at the source and think I will definately will use this for my future projects. One thing though is that I had to hard code the connection string in the code to make it work. I will debug this later and it was probably just me putting in some sort of wrong values. However another issue is that the field for choosing the database is greyed out? Is there any particular reason for this? I want to connect to an access database.

Finally when converting the project into VB 2008 I was asked for a password for the encryption key. Since I did not have this I ended up deleting the file from the project.
K
 
coud someone please explain to me the problem when i build dll file it says
member names cannot be the same as their enclosing type !
i havet his problem when i use the classes from the layergen to make a dll file ..
sorry for my english langauge if i had mistakes
Thansk
 
Back
Top