posted
Anyone have any recommendations for automatic sproc generation? I have a relational DB (sql server 7.0) and want to generate the standard CRUD stored procedures for each table. They're all pretty much the same template (for each table and for each property that isn't the main id- since it's an autoincremented int). This DB is small (9 tables) but I have two more projects ahead of me with a much larger number of tables (20-30) and think it might be worth it to invest into something for this rather simple but tedious creation. That way I can concentrate on the sprocs with more logic in them. I suppose I could create something to do it. But I'd rather not reinvent the wheel if I don't have to.
Anyone? Fugu? Mr. Squicky? Tom?
Posts: 1346 | Registered: Jun 1999
| IP: Logged |
posted
Use a generic templating tool like Velocity or a transformational tool such as XSLT with XML versions of your database schema to produce them. Either way should be quick and relatively painless, as basic CRUD stored procedures are highly regular, and you only need to accomodate one db.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
The other alternative is to dump the table and field names to Excel, then use the formula features to create the stored procedures. Put each field on its own cell all lined up in a column, with the table name at the top of the column. For each CRUD procedure, use the concatenation feature to construct your formula. Use a separate column for each needed procedure. If you need to reference a field more than once, put it in the list more than once. Then use the formula autofill to fill in all the way.
I've used this technique for years, and it's a huge timesaver.
posted
Thanks. I will try either of those suggestions. I knew such tedious chores had to have been automated.
I can get the DB schema by pulling in the DB as a dataset in c# and then serialize it to an XML file. The schema comes out relatively painless- unless you know, fugu, of any irregularities between such generated schemas?
Posts: 1346 | Registered: Jun 1999
| IP: Logged |
posted
I know of none, and if its pretty and you're comfortable with XSLT that would be a fine way. The programming approach with Velocity (or other high quality templating system) would be more flexible than the Excel approach, but would require a minor learning curve.
It all comes down to what you're comfortable with, really.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
I also found another technique that worked quickly just for today, if you're using Visual Studio.NET 2003 (or 2005, for that matter). In the Server Explorer, dragging either the specific table or the entire DB onto the Windows Form of your project (a generic one) will generate sqlDataAdapters for each table (or xxxDataAdapters, depending on the type of DB you're using). Right click the first of them, configure the adapter to use a connection string to connect to the db. Then configure it to generate either SQL statements for CRUD to sprocs. Then, name the sprocs what you want and boom. They are placed in the DB. Then close and delete the project and you still have the sprocs for CRUD capabilities in the DB. You do have to do this for each table (except for creating a connection- just use the first one you made for each of the remainder), so I will definetely apply the time to learn the templating tool you linked to. But for speed today, this worked well.
posted
Ian, I'm kind of late to the party and it sounds like you're all set up, but if you're still looking I actually wrote something very similar to this in Java that I was planning on GPL'ing once the larger project I'm working on is finished. You'd have to do some code modification and it's not set up to work with SQL Server yet, so it's probably a lot more hassle than it's worth, but I can send you the code if you want.
Posts: 10177 | Registered: Apr 2001
| IP: Logged |
posted
DBArtisan by Embarcadero does an excellent job of this. However, I think it costs around $1500.
Posts: 289 | Registered: Jan 2002
| IP: Logged |
posted
That'd be great, MrSquicky. I've been thinking about writing something like that myself. Conversion shouldn't be too bad. C# is basically Java with a with modifications. Biggest change would be in the DB access, but I think I remember how it's done in Java.
iohlander at ogequip dot com
Posts: 1346 | Registered: Jun 1999
| IP: Logged |
Printer-friendly view of this topic
