Selecting a Random File from a Directory and Outputting its Contents Demo

This demo illustrates how to have a random text file read from a directory and have its contents displayed. Below you will see the output of a random text file from the directory of (very) old WebWeekly issues.


WebWeekly: Retrieving the First N Records from a SQL QueryWebWeekly: Your weekly source for Web Technology Tips and Tricks!-Forward this email and subscription information to a fellowdeveloper!******************************************************************* Retrieving the First N Records from a SQL Query [ by: Scott Mitchell - http://4guysfromrolla.com/ScottMitchell ] ******************************************************************** http://www.4guysfromrolla.com/webtech/070605-1.shtmlIntroductionThere are often times when retrieving results from a database that youonly are interested in displaying the first N records from the resultingquery. For example, a news website might have a database table thatcontains a record for each story. On the news website's homepage, theymay want to display the 10 most recent stories from this table. Doing aSELECT * FROM NewsStoriesTable SQL query will return all of the recordsfrom that table, no just the 10 most recent ones. So how do they displayjust the 10 most recent?There are a couple ways to display the first N records from a query. Themost naive way is to simply return all of the records to the web pageand then use programmatic logic to only show the first N. This is a poorapproach because it requires that the database query all records fromthe table and transfer all of the data from the database to the web pagewhen only a small subset of the total results will even be used. Thisapproach's performance will worsen as the NewsStoriesTable grows overtime.A better approach is to use SQL Server's ROWCOUNT and/or TOP keywords.These two keywords actually limit the results SQL server queries andreturns, thus reducing the burden both on retrieving the results fromthe database and returning them to the web page. In this article we'lllook at both ROWCOUNT and TOP and see when to use each one. Read on tolearn more! Limiting Records with TOPThe TOP keyword was introduced with SQL Server 7.0 and provides a meanto limiting the results of a SQL SELECT query. With TOP you can specifyto retrieve the first N records or the first X percent of records. Thesyntax for TOP is as follows:SELECT TOP N select listFROM TableName -- or --SELECT TOP X PERCENT select listFROM TableName -- Concrete examples --SELECT TOP 5 *FROM authorsSELECT TOP 25 PERCENT au_lname, au_fnameFROM authorsWHERE zip = '92101'Here N and X must be positive integer values; if you are using thesecond pattern, X must be between 0 and 100, inclusive.Using TOP will access just the first N or X percent of records. Commonlysuch queries are accompanied by ORDER BY clauses. For example, in theexample discussed earlier with a news website, to return the 10 mostrecent news articles you'd need to use an ORDER BY clause to return theresults sorted by the PublicationDate field in descending order. Fromthis, you'd want just the top 10 results:SELECT TOP 10 NewsURL, ArticleTitle, ArticleAuthorFROM NewsStoriesTableORDER BY PublicationDate DESCLimiting Records with ROWCOUNTPrior to SQL Server 7.0, the only way to limit results from a SQL querywas to use the ROWCOUNT variable. With ROWCOUNT you'd proceed the actualSQL statement with a line like:SET ROWCOUNT NAfter which you could have your SQL statement. By setting ROWCOUNT tosome number greater than zero, all subsequent SQL statements in thescope would process only the first N records.ROWCOUNT still works with modern versions of SQL Server, and does haveits time and place. Keep in mind, though, that ROWCOUNT applies not onlyto SELECT statements, but also to INSERT, UPDATE, and DELETE statementsas well. Additionally, ROWCOUNT's effects apply to triggers that may befiring as well.Here's a simple example of using ROWCOUNT to retrieve the 10 most recentarticles from the NewsStoriesTable table:SET ROWCOUNT 10SELECT NewsURL, ArticleTitle, ArticleAuthorFROM NewsStoriesTableORDER BY PublicationDate DESCWhen to Use TOP vs. When to Use ROWCOUNTSo what approach should you use, TOP or ROWCOUNT? If you are using SQLServer 6.5 or earlier, you'll have to use ROWCOUNT, as TOP wasn'tintroduced until SQL Server version 7.0. Of course, unless you aresupporting a legacy system, chances are you are using at least SQLServer 7.0, if not SQL Server 2000 or SQL Server 2005.If you are simply doing a SELECT statement and know precisely how manyrecords you want to limit the results to, I'd recommend using TOP. TOPis designed to work with SELECT statements and doesn't have any of thepotentially far-reaching effects that ROWCOUNT does (i.e., applying tonon-SELECT statements, effecting triggers, etc.). Granted, for simpleSELECTs you won't have any issues using ROWCOUNT, but if you are callinga stored procedure that does a SELECT but also processes otherstatements, with ROWCOUNT you'll need to be more careful to ensure thatyour SET ROWCOUNT N statement applies only to those queries intended.There is one case where I'll use ROWCOUNT in a SELECT query: when youwant to let the web page developer specify how many results to return.For example, imagine you have a stored procedure named getRecentArticlesthat takes in as an integer input parameter named @ResultCount. Now, thestored procedure should return only the @ResultCount most recentarticles (rather than always returning the 10 most recent). This allowsthe page developer to indicate if she wants to get back the 10 mostrecent articles or, say, the 25 most recent articles. Say we have thefollowing:CREATE PROCEDURE getRecentArticles( @ResultCount int)AS ... TODO: Write SQL statement to return the @ResultCount most recent news articles ...This can be accomplished using TOP only if you use dynamic SQL in yourstored procedure. That is, you cannot do the following:CREATE PROCEDURE getRecentArticles( @ResultCount int)ASSELECT TOP @ResultCount NewsURL, ArticleTitle, ArticleAuthorFROM NewsStoriesTableORDER BY PublicationDate DESCInstead you will need to dynamically craft a string in the storedprocedure and then execute it using EXEC or sp_executesql. Thisapproach, while possible, tends to lead to messy and less maintainableSQL stored procedures, in my opinion. (If you are interested in learningmore on using dynamic SQL in your stored procedures be sure to read myearlier article, Using Dynamic SQL Statements in Stored Procedures -http://www.4guysfromrolla.com/webtech/102300-1.shtml )With ROWCOUNT, however, you can just slap in the @ResultCount inputparameter like so:CREATE PROCEDURE getRecentArticles( @ResultCount int)ASSET ROWCOUNT @ResultCountSELECT NewsURL, ArticleTitle, ArticleAuthorFROM NewsStoriesTableORDER BY PublicationDate DESCFinally, if you are wanting to limit the results performed by an INSERT,UPDATE, or DELETE, you'll have to use ROWCOUNT as TOP only applies toSELECT statements.ConclusionIn this article we examined how to return the first N records from a SQLquery. Specifically we looked at two SQL Server keywords, TOP andROWCOUNT, examining sample SQL syntax for each. Finally, we compared andcontrasted the two, looking at when to TOP versus when to use ROWCOUNT.Happy Programming!# By Scott Mitchellhttp://www.4guysfromrolla.com/ScottMitchell.shtml* http://www.4guysfromrolla.com/webtech/070605-1.shtml****************************************************************************************************************************************I hope you enjoyed this WebWeekly. If you have any ideas, comments,suggestions, rants, or raves, please let me know by sending feedbackfromhttp://nl.internet.com/ct.html?rtr=on&s=1,1pz3,1,d0vc,fgj8,i1e2,f46iAdditionally, be sure to check out the following ASP and ASP.NET books!For the beginning ASP developer...Sams Teach Yourself Active Server Pages 3.0 in 21 Dayshttp://nl.internet.com/ct.html?rtr=on&s=1,1pz3,1,g0ya,d4eg,i1e2,f46iFor the intermediate to advanced ASP.NET developer...ASP.NET: Tips, Tutorials, and Codehttp://nl.internet.com/ct.html?rtr=on&s=1,1pz3,1,3yuc,50va,i1e2,f46iFor in-depth information on the DataGrid, DataList, and Repeater...ASP.NET Data Web Controls Kick Starthttp://nl.internet.com/ct.html?rtr=on&s=1,1pz3,1,alfn,4ygd,i1e2,f46iFor the beginning ASP.NET developer...Sams Teach Yourself ASP.NET in 24 Hourshttp://nl.internet.com/ct.html?rtr=on&s=1,1pz3,1,10rj,5njh,i1e2,f46i*********************************************************************


Source Code
<%
Option Explicit

Function ReadDir(FolderName)
	On Error Resume Next
	Dim objFSO
	Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
		
	'Make sure the specified folder exists
	If Not objFSO.FolderExists(FolderName) Then
		ReadDir = "Folder does not exist. <br><b>This can be changed to " & _
				"a default message or nothing.</b>"
		Exit Function
	End If

	Dim objFolder
	Set objFolder = objFSO.GetFolder(FolderName)

	Dim I : I = -1
	ReDim arrFile(20)
	Dim objFile
	For Each objFile In objFolder.Files
		If Right(objFile, 4) = ".txt" Then
  			I = I + 1
			
			if I > UBound(arrFile) then ReDim Preserve arrFile(I + 20)
						
			arrFile(I) = objFile.Path
		End If
	Next
	
	'Make sure arrFile is the right size
	Redim Preserve arrFile(I)

    If I = -1 Then
		ReadDir = "No text files in this directory. <br><b>This can be " & _
		          "changed to a default message or nothing.</b>"
		Exit Function
	End If

	Randomize
	Dim RanFile : RanFile = Int((UBound(arrFile) - 1 + 1) * Rnd + 1)
	Dim strOutput : strOutput = ""
	Dim strFileName : strFileName = arrFile(RanFile)
	If objFSO.FileExists(strFileName) Then
		Dim objTextStream
		Set objTextStream = objFSO.OpenTextFile(strFileName, 1)
			Do While Not objTextStream.AtEndOfStream
				strOutput = strOutput & objTextStream.ReadLine
			Loop
		objTextStream.Close
	Else
		ReadDir = "File does not exist. <br><b>This can be changed to a " & _
		          "default message or nothing.</b>"
		Exit Function
	End If
 
	
    ReadDir = strOutput
 
 Set objTextStream = Nothing
 Set objFolder = Nothing
 Set objFile = Nothing
 Set objFSO = Nothing
End Function
%>

<h1>Selecting a Random File from a Directory and Outputting its Contents Demo</h1>
...

<%
  Response.Write(ReadDir(Server.MapPath("/webtech/WebDaily")))
%>


[Return to the User Tip]