all posts

WhatIWantMost Database I

Published to Blog on 13 Dec 2006

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.

Dan Hounshell
Web geek, nerd, amateur maker. Likes: apis, node, motorcycles, sports, chickens, watches, food, Nashville, Savannah, Cincinnati and family.
Dan Hounshell on Twitter

  • On 13 Dec 2006 "Community Server Daily News"" said:
    news of the day a grab bag for what's happening in Community Server For Aero Theme fans out there, Nick
  • On 13 Dec 2006 "Daily News List Blog"" said:
    Dan Hounshell introduces us to his database schema and database object generation in today's WhatIWantMost
  • On 15 Dec 2006 "Digging My Blog - Dan Hounshell"" said:
    In my last post I took a first pass at designing the database for The next step is
  • On 21 Dec 2006 "Digging My Blog - Dan Hounshell"" said:
    I've been getting cards and letters from people I don't even know asking, "Hey, how about a status update
  • On 12 Mar 2007 "Community Server Bits"" said:
    Dan Hounshell introduces us to his database schema and database object generation in today's WhatIWantMost
  • On 6 Jul 2007 "Digging My Blog - Dan Hounshell"" said:
    Following is a list of all the posts of the WhatIWantMost series. I'm listing them all here mostly for