Radius Search, Http Screen Scraping and String to Temp Table

In the last few days I have been assisting on a friend's website. I have here some code snippets and information I learned.

Comma Separated Id List to Temp Table
Some times you have to do a search on a set of values. Let us say the user is searching through a list of zipcodes for someone close to them. You can write dynamic sql to pass in the list of zipcodes. But then you have to grapple with table security and it will not be fast enough.
Instead, the standard method is to do this:

1. Pass in list of zipcodes as a comma separated varchar string.
2. Create a temp table with a zipcode column
3. Parse the passed in string so that it populates the temp table.
4. Now, join the temp table with the core tables to execute your query.

I have below a code snippet for this:

ALTER PROCEDURE [dbo].[Test_Sproc]
@IdString varchar(1000),
@numIds int
AS

CREATE TABLE #zip (zipcode varchar(5) NOT NULL);

DECLARE @count int, @index int, @start int, @id varchar(5), @length int, @previndex int
SELECT @count = 0, @start = 0, @index = 0, @length =0, @previndex =0
WHILE @count < @numIds
BEGIN
SET @index = CHARINDEX(',',@IdString, @index)

IF @index = 0
BEGIN
SET @length = LEN(@IdString)- @previndex+1
END
ELSE
BEGIN
SET @length = @index - @previndex
END
SET @id = SUBSTRING(@IdString,@start, @length)
INSERT INTO #zip (zipcode) VALUES (@id)
SET @count = @count + 1
SET @start = @index + 1
SET @index = @index + 1
SET @previndex = @index

END

SELECT u.UserId, u.UserName
FROM [User] u
WHERE u.ZipCode IN
(SELECT zipcode FROM #zip)

DROP TABLE #zip

Radius Search
I found that there is a requirement to do zipcode based searches in a geographic circle. Let us say you want to find some restaurants in your neighborhood. Your application lets you search restaurants in a widening circle - such as within a circle of 5 miles, 10 miles and so on.
For this purpose, you need a database of restaurants with address and zipcode. But you also need a zipcode database. The only way you can do a radius search is by getting the lattitude and longitude information.
So you need two components to perform a radius search:
1. A zipcode database that gives you lattitude and longitude information also. Such databases are available commercially for around $100.
2. You need a component that can take the lattitude and longitude and figure out the radius. Such components in Java and .NET are available commercially. You can also build them pretty easily.
Using these you can build a radius search for your users.

Parsing a webpage
In a hurry, I also needed to parse a certain web page for some information, such as a date. If I got the webpage contents in a string I could use regular expressions to parse it. Here is a simple code for getting the contents of a webpage, in C#:

string str = String.Empty;
string url = "http://www.yahoo.com";
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
using (HttpWebResponse response = (HttpWebResponse)req.GetResponse())
{
if (response.StatusCode != HttpStatusCode.OK)
{// throw exception
throw new Exception("Could not get response from web pageUrl");
}
// Read Content
Encoding enc = Encoding.GetEncoding(1252);
using (StreamReader responseStream = new StreamReader(response.GetResponseStream(), enc))
{
str = responseStream.ReadToEnd();
}
}

At the end of the this code, str contains the entire front page contents of yahoo.com. You can then use Regular Expressions to parse it. The above code requires the namespaces System.Net and System.IO.