Hi All
I have recently written a CRUD Generator that uses Microsoft.ApplicationBlocks.Data so if anyone is interested please let me know. The generated Classes and Stored procedures support ACID principles. The CRUD generator essentially encapsulates all my working knowledge into one neat little package to save myself considerable development time. (WinForms app)
I basically followed the KISS and 80/20 rules when writing this little app so it is by no means perfect. In my case the tool creates 70-90 percent of the data access code I will ever need for a given database in a matter of seconds.
The CRUD generator addresses the following issues:
Transactions - updates, inserts and delete methods are overloaded to support optional passing of transaction objects. In that way you could manually create a composite class that utilizes two or more CRUD generated classes and pass a transaction to all the participating objects. Either all the changes commit or everything is rolled back.
Optimistic concurrency - All a DBA needs to do is add CreatedDate (datetime) column to table and CRUD generator will create all the necessary logic to check for optimistic concurrency violation. Only stipulation is that the developer passes original Datetime value back to procedure so valid comparison can be made.
I leave cascading deletes and restricted deletes up to SQL server as it should be. In all delete stored procs the CRUD generator simply traps error 547 (foreign key violation) if user attempts to delete primary key that is referenced in another table. I then throw a friendly error message using RAISEERROR.
CRUD Generator Limitations: <-- I would not characterize these issues as limiatations but others may. ie C# developer
- Every table must use a single column to represent tables primary key. The column must utilize IDENTITY attribute and appear as first column in table.
- Binary data types are currently not supported.
- Only supports SQL Server
- Only VB code is generated
Some additional features of CRUD generator:
- User can point to new database by simply changing connection string in applications config file
- User can select one or more tables they wish to generate classes or stored procedures for
- User can specify namespace that all classes will utilize
- User can specify which methods and stored procedures are generated (i.e. insert, delete, update and so on)
- User can specify if they want datareaders or datasets returned to client applications.
- All the stored procedures are written to a single file for convenience
- Each table has its own class generated. The naming conventions is table name followed by DB suffix. For example, Employees table would have generated class called EmployeesDB.
- For CRUD generated select procedures the user may optionally define additional columns that are included from related tables
- The CRUD generator will generate select procedures for all foreign keys found in a table. By foreign keys I mean columns with a suffix of ID. ID is simply the convention I use. For example, imagine Employees table had a field called ManagerID. The CRUD generator would generate a stored procedure called EmployeesGetByManagerID that could be used to retrieve all the employees that fall under a given manager.
Regards,
Kevin Weir
[email protected]