Two Things
-
Two things today. First I had someone ask me about stored procedure generators. Since I haven't really used them, I couldn't really comment. I'm also not sure to what extent they really help unless your schema is in quite a bit of flux.
I used to work for a small, growing startup company and we were making schema changes every month, sometimes adding 5 or 6 tables as the business changed. Since we tended to use identity columns as our primary keys (no boos please), I wrote a generator that would create the insert, update, delete, and select all procedures automatically for each table. I don't think it saved me a ton of time, but it did ensure that a standard set of procedures were available for each table and the developers could count on them. However the number of custom stored procedure to join tables together and get data for most business processes still had to be written by me, so I think the generator just saved some busy work.
So I guess I'm wondering if any of you use generators and what your opinions on them are. I'd be interested to know which products you like and don't like as well.
The second thing is a contest. Well, not so much a contest as a random drawing, but win a free copy of Pro SQL Server 2005!!! Answer today's question and 2 randomly chosen people will win a copy complements of Wiley Publishing.
This book takes a deep look at the full range of SQL Server 2005 enhancements. Relevant examples show how these new features work and how you can architect applications effectively. The book's core topics include enhancements to database programmability features and languages, such as ADO.NET, XML, .NET assemblies, and T-SQL.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
I use Codesmith for generating CRUD (go on, have a little giggle) sprocs. This saves me a lot of time. Also, the standard nature of the sprocs means that my VB.NET layer can be standardised and I get Codesmith to generate that too.
I can quickly go from creating a new table to having an ASP.NET page for updating that table. The use of Codesmith has been invaluable in this regard.
-
I prefer to write any and all data access code by hand, which extends from a bad experience I once had entering a shop where no one was sure how the existing procedures worked, or even which ones were working with the current version of the application. Even with hundreds of stored procedures, I've found the time spent writing them manually pays off in terms of insight into the schema, the business functions, etc. It is still possible to template the procedures, so consistency isn't lost.
Having said that, I can see how a generator might be valuable during some stages of development, as it could get the process moving along more rapidly. But where's the fun in auto-generated code? 😉
-
Like Michael, I use CodeSmith to generate common stored procedures. Writing standard insert, update, delete, and select statements manually is a waste of time and invites errors. A tried and true code generator gets it right every time. Having to manually update all those procedures whenever a schema change is made in development also leads to missed procedures and more errors. There will never be a code generator that gets 100% of the queries that you need but the time savings and assurance of good code for the 80-90% is well worth it.
BTW, there is also an Open Source code generator similar to CodeSmith but I haven't tried it yet. It's called TaHoGen (http://tahogen.tigris.org/).
BTW, I just ran a handy CodeSmith script against the Northwind database on my computer. It generated over 140 stored procedures in less than 50 seconds.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I have used a custom generator for the last few years. This saves a lot of time and gives us some very standardized basic procedures. Once the standard procedure is created, if some special data handling is needed, all we have to do is modify the generated procedure.
Since I like for almost all data to be accessed through stored procedures this can be a real benefit. This also allows us to have a data layer that can be reused from project to project.
-
My company has a small app that generates a few basic sprocs from sqlserver. All you need do is create table definitions and relationships and BOOM, u have insert, update, delete, get and getalls, and some finders sprocs based on table relationships. Now I prefer to use codesmith because its very flexible. I have a template that generates the sprocs and business entities my application needs with class level attributes that a in-house object persistence framework leverages on. It does save a lot of work for real but of course I start writing my sprocs after the basic ones are done. Business requirements vary too much for any templlate.
-
We have been using ERwin for over a decade now to generate our stored procedures. It has a fairly powerful macro language that has permitted us to develop quite useful templates which can generate the basic CRUD routines. Of course when more complex business rules have to be implemented, single templates are then built.
We have built header and footer templates which are included in all procedures which enforce standard documentation and error handling. This also imposes standardization to an extent. These templates are attached to the tables in the ERwin physical view of the data model. This enables encapsulation of a sort in that we restrict Insert/Update/Delete activities against a table to occur only in those routines directly attached.
-
I wrote my own generator also, in an HTA. In addition to creating procedures based on a table, it also creates ADO parameter snippets and VB variable declare statements, based on the table fields names and data types (ie, "Private mp_str<VarcharFieldName> as String"). Since I was building procedures that loop through tables, I also built it to generate a generic ASP page with database procedures, a report and a simple form. The form builds text boxes for everything except bits (checkboxes) and text (textarea). I have found that this saves a tremendous amount of time when prototyping and during initial development.
Thanks,
Brett Hacker
-
I've tried and used a myriad of tools to assist in generating procs as well as triggers. I have found that I have never been satisifed completely with one solution. We currently use some custom Apex templates for generating some of our most basic procs and triggers. Codesmith is fairly effective at generating basic crud procs, but I find that we rarely use basic crud procs for our application so it hasnt been an effective tool for us. Erwin does a great job generating triggers though I must confess I havent used it for proc generation.
We have probably close to 2000 procs
across several interrelated apps and of late we have slowly been moving towards using paramaterized queries, and embedding them as resources in a dll as part of our app and generating sql on the fly, this allows us some more flexibility as we often deal with different client implementations with some varying business logic (and as a result we have a good number of procs that are over 700 lines long). Not the end all be all, but it works. I am also very interested in investigating using some of the new products being offered by MS, such as dsl tools to assist in sql code generation.
In the end it means that generation solves some of the basic problems, but complex issues always require customization.
-
We have probably close to 2000 procs
across several interrelated apps and of late we have slowly been moving towards using paramaterized queries, and embedding them as resources in a dll as part of our app and generating sql on the fly, this allows us some more flexibility as we often deal with different client implementations with some varying business logic (and as a result we have a good number of procs that are over 700 lines long). -- nix2kThat sounds like you're putting a lot of the business logic in the stored procedures. It is possible that you would have a lot fewer stored procedures if you broke them down into smaller units and used business logic in the business components to call the right ones. I have found that a lot of the performance benefits of stored procedures are lost as the complexity of the procedures increases. It is often better in these cases to make a few stored procedure calls in the context of a single transaction rather than call one stored procedure that includes a lot of business logic.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Business Components???
Yeah we are aware of the limitations of the current architecture, particularly in performance as well as maintaining the complexity of the procs, and are working on transitioning the business logic from the procs to the business layer, but as you must know that is often a slow, painful process.
-
Very slow and extremely painful. I hope you have a good test plan in place!
It sounded like you were backsliding a little but by going with the dynamic SQL generation from the SQL resource file. It sounds like you would be better served by using the DLL with a code generation tool to generate stored procedures. I don't know enough about your environment to know if that would work or not but I thought I would throw the idea out.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
If anybody's still following this thread, do any of these tools integrate well with Visual Studio and/or Visual Source Safe?
Thanks,
Chris
-
A little bit but I don't use it. CodeSmith has Visual Studio integration which allows you to keep scripts and config files in a project. When you need to re-generate the code you can right-click on the configuration file and choose Run Custom Tool (or something like that). I am using an older version of the product so this may have improved in more recent versions. You can get a 30-day trial at http://www.codesmithtools.com/license.aspx.
I usually keep a database project in visual studio. This project is maintained is VSS and I copy/paste from the code generator to files in Visual Studio. This has the added benefit of keeping all the database code in the same project, auto-generated or otherwise.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I also use CodeSmith. It saves a lot of time for new and existing development. You can customize the proc syntax to meet you particular company needs and your development style.
I also use it to generate the c# data access layer classes.
I just started a new project, with 10 tables (a simple schema I admit), and once the DB design was complete I was able to generate the procs I needed and their corresponding DAL methods in less than 1/2 an hour. If I just wanted to generate CRUD procs and methods for all tables, I could have done that in less than a minute, but I don't like to bloat my code base and only generate code that I need.
CodeSmith ships with a default proc generation template. The template format is very similar to a asp.net page, so if you are familiar with asp.net you will find it easy to create your own templates. If you are uncomfortable doing that, you can easily tweak the templates that ship with CodeSmith.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply