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):
Dim Addresses As New BusinessLayer.Addresss
' The following line will sort the collection, if you enabled sorting
Now if you want to address just a single record, use something like the following:
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.
Dim Address As New BusinessLayer.Address(primary key)
Me.lblState.Text=Address.FirstName & " " & Address.LastName & " lives in " & Address.FState.StateName
I hope you guys enjoy it!