WhatIWantMost Database I

In my last post in the WhatIWantMost series I further defined the basic entities for the project by adding attributes I think they need.

The next step is to make a first pass at designing/implementing a database to support those entities. I simply added some tables to a SQL Server database - one for each entity described earlier, added a field in each table that roughly matches to the attributes described before and then defined my relationships by adding primary and foreign keys to the schema. 

Following is a screen shot of part of the data diagram that includes most of my new  tables. The database also includes the ASP.NET 2.0 Membership schema.

In a previous post I talked about using a set of stored procedures that I read about in ”Advanced T-SQL: Automate the Generation of Stored Procedures for your Database”, published in the April 2003 edition of MSDN by Peter W. DeBetta and J. Byer Hill. I downloaded the code examples and modified them to suit my needs. The end result is that I can run one stored procedure and it will query the master database to discover all the tables in a database and generate Insert, Update, Delete, SelectByID and SelectAll stored procedures for each table. The procedure will also generate a SelectAll stored procedure for any views in the database as well, but to this point I have not defined any. For this database I added some conditional logic to ignore the ASP.Net membership tables, any beginning with “aspnet”, because they come with their own set.

To make all the magic happen I open a new query window and type: exec dbo.proc__SYS_MakeProcAllTables 1 The parameter “1” is a bit field to tell the procedure whether to just generate the necessary SQL for the stored procs or to execute them as well. In this case I am executing the SQL because I want the stored procs generated.

The screen shot below shows some of the stored procedures that were generated:

Yes I realize I could have done most if not all of this using SQL Server’s built-in Stored Procedure Wizard. The wizard is a useful tool, however it does not provide all the power and range of customization that the scripts that I demonstrated do.   

There are a couple different views on using code generation, which basically is what we have done - just a different kind of code. The beginner’s view, and one that I adopted when I first delved into it, is “Cool, this got me half way there, now I can just tweak this code to get me the rest of the way”. While being true, I realized it was not the best approach for me. Requirements change over time and thus code and data must adapt to those changes. If I start modifying this code and later realize that I need to add more fields then I will have to manually add those fields to all the stored procedures. There is no easy way to regenerate the stored procedures keeping any modifications I might make to them in place. Because of this I will choose to leave the generated procs pristine and any special modifications that I may need to make will done by creating new ones. Doing so will allow me to regenerate all the stored procs any time the schema changes. We will take the same approach later in the project with respect to code generation.

My next step will be to open up CodeSmith, run the database that we just created against the .netTiers template and then see what we get.