View Single Post
  #27 (permalink)  
Old March 27th, 2007, 04:44 PM
plb plb is offline
Authorized User
 
Join Date: Jan 2007
Location: Oakland, CA, USA.
Posts: 94
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have started upgrading the look of my ShowForums page on the model Jim's Forum:
http://www.jamestollan.com/ShowForums.aspx

I'm not through but I've done a few little upgrades to include Topics, Posts, and Last Post columns. Right now these are simple Bound Columns. Later I will template them and add other values, links and multiple lines.

What is remarkable about these added features is that there was essentially NO PROGRAMMING much less any looping. I'm too simple minded to create a complex solution so I did it is a very simple fashion.

I just dropped the forums table on the form and auto generated a SqlDataSource control. I then created three super simple Views. One each for Threads, Posts, and Last Post Date. I created them graphically in SQL Server (I teach students how to write SQL in Query Analyzer but I seldom do so myself). They are:

vw_Forum_NumPosts
Code:
SELECT 
   ForumID, 
   COUNT(PostID) AS NumPosts
FROM 
   dbo.tbh_Posts
GROUP BY 
   ForumID


vw_Forum_NumThreads
Code:
SELECT 
   ForumID, 
   COUNT(PostID) AS NumThreads
FROM 
   dbo.tbh_Posts
GROUP BY 
   ForumID, 
   ParentPostID
HAVING
   (ParentPostID = 0)



vw_Forum_LastForumPost
Code:
SELECT     
   ForumID, 
   MAX(LastPostDate) AS LastForumPostDate
FROM         
   dbo.tbh_Posts
GROUP BY 
   ForumID


Then I made one more View called vw_Forums that simply joined these three views to the Forums table (graphically). The effect of which is to add three new columns to the Forums table. Lastly I replaced the table Forums with the view Forums in the SELECT statemnt in the SqlDataSource.

I know I should have probably done this in the DAL. Some would object that I should do this sort of thing completely in code not in the database at all. But I hate to type (and/or think) and its so easy in SQL.

Pat

http://weboperahouse.com