Yesterday I decided to fiddle around with MS Visio for Enterprise Architects database modelling tools. After drawing up and validating a pretty straight-forward use case i created a new Database Model Diagram Template from visio. After telling Visio to use the Sql Server ODBC driver I got all the Sql datatypes correct for my Entity construction. Relationships creates foreign keys automatically.
Usually after designing the ER diagram I've set out to Enterprise Manager for a long day of boring table/view contruction. Now I rather hit Database -> Model -> Error Check in Visio, followed by a very pleasant Database -> Generate. This starts a wizard that generates DDL scripts, and even executes it on your database if it's configured with ODBC. Further alterations are done with the ever so sweet Database -> Update.
The only reason that I haven't discovered this before is that I've always chosen ER Model Diagram Template instead of Database Model Diagram in Visio (which doesn't enable the Generate and Update menuchoices). And sure, there are things that could be better (such as view creation), but still; this saved me at least a day of work! And I don't even have the latest version of Visio.
Can't wait to see potential cool VS.NET integration with Visio in Whidbey. Rational Rose never did it for me anyways.
Next on the menu is to hook my VS.NET solution up to the database and create some really nice typed datasets with annotations and let VS.NET generate the SP's in the DataAdapter wizard. I'll probably won't have to write a single line of SQL!
This article gives a nice intro to the subject. And setting Identity on your tables in Visio might be a little tricky. Select your table, choose the Columns page in Categories inside the Database Properties window. Then choose your primary key column and hit Edit. In column properties choose Data Type and click Edit again. There you should find a checkbox for Identity. Phuuuiii..