roy ashbrook

--copy the database file from
--http://www.cfdynamics.com/zipbase/
--and save the txt file in it
--to c:\projects if you want to 
--match my setup

--create the database
create database zipcodes
go

--use it
use zipcodes
go

--create a table
create table zip_codes(
 zip varchar (255)
 , latitude varchar (50)
 , longitude varchar (50)
 , city varchar (50)
 , state varchar (50)
 , county varchar (50)
 , zip_class varchar (50)
)
go

--bulk import our data. modify the path to match yours
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd ='bulk insert zip_codes
FROM ''c:\projects\zip_codes.txt'' 
WITH (FIELDTERMINATOR ='','',
ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

--remove the quotes we imported
update zip_codes set
 zip = replace(zip,'"','')
 , latitude = replace(latitude,'"','')
 , longitude = replace(longitude,'"','')
 , city = replace(city,'"','')
 , state = replace(state,'"','')
 , county = replace(county,'"','')
 , zip_class = replace(zip_class,'"','')

go

--modify the data types a little
alter table zip_codes alter column zip varchar (5)
alter table zip_codes alter column latitude float
alter table zip_codes alter column longitude float
alter table zip_codes alter column state varchar (2)
go

--create our proc to share
create proc dbo.zipcodes
 @zip varchar (5) = null
 , @latitude float = null
 , @longitude float = null
 , @city varchar (50) = null
 , @state varchar (2) = null
 , @county varchar (50) = null
 , @zip_class varchar (50) = null
as

--no locking
set transaction isolation level read uncommitted

--stupid code to fix empty values
select
 @zip
  =case when @zip = ''
  then null else @zip end 
 , @latitude
  =case when @latitude = 0
  then null else @latitude end 
 , @longitude
  =case when @longitude = 0
  then null else @longitude end 
 , @city
  =case when @city = ''
  then null else @city end 
 , @state
  =case when @state = ''
  then null else @state end 
 , @county
  =case when @county = ''
  then null else @county end 
 , @zip_class
  =case when @zip_class = ''
  then null else @zip_class end 

--get our values
select
 zip
 , latitude
 , longitude
 , city
 , state
 , county
 , zip_class
from
 zip_codes
where
 zip    like coalesce(@zip,zip)
 and latitude = coalesce(@latitude,latitude)
 and longitude = coalesce(@longitude,longitude)
 and city  like coalesce(@city,city)
 and state  like coalesce(@state,state)
 and county  like coalesce(@county,county)
 and zip_class like coalesce(@zip_class,zip_class)

go

--create an endpoint to share proc
CREATE ENDPOINT zipcodes
STATE = STARTED
AS HTTP (
    AUTHENTICATION = ( INTEGRATED ),
 SITE = '+',
    PATH = '/zipcodes',
    PORTS = ( CLEAR )
)
FOR SOAP (
  WEBMETHOD 
    'http://localhost/zipcodes'
    (NAME = 'zipcodes.dbo.zipcodes'),
    BATCHES = ENABLED,
    WSDL = DEFAULT
  ) 
go

using System;
using System.Data;
using System.Net;

namespace zipcodes
{
    class Program
    {
        static void Main(string[] args)
        {
          
            string zip = string.Empty;
            string city = string.Empty;
            string state = string.Empty;
            string county = string.Empty;
            string zip_class = string.Empty;
            double latitude = 0;
            double longitude = 0;
            bool showhelp = true;
            
            foreach (string a in args)
            {
                if (a.Contains("zip=")) zip = a.Remove(0, 4);
                if (a.Contains("city=")) city = a.Remove(0, 5);
                if (a.Contains("state=")) state = a.Remove(0, 6);
                if (a.Contains("county=")) county = a.Remove(0, 7);
                if (a.Contains("zip_class=")) zip_class = a.Remove(0, 10);
                if (a.Contains("latitude=")) latitude = Double.Parse(a.Remove(0, 9));
                if (a.Contains("longitude=")) longitude = Double.Parse(a.Remove(0, 10));
                if (a.Equals("nohelp=true")) showhelp = false;
            }

            if (showhelp)
               Console.WriteLine(
                 String.Format("{0}\n{1}\n{2}\n{3}\n\n{4}\n{5}\n{4}"
                    , "royashbrook.com =) sample syntax:"
                    , "zipcodes longitude=-103.847648"
                    , "spaces and wildcards --> zipcodes city=jacksonville \"zip_class=po box %\""
                    , "wildcard support --> zipcodes county=morr% zip=%5%"
                    , "*************************************"
                    , "USE nohelp=true to omit this message!"));
            
            using (localhost.zipcodes z = new localhost.zipcodes())
            {
                z.Credentials = CredentialCache.DefaultCredentials;
                foreach (DataRow dr in (
                    (DataSet)z.httplocalhostzipcodes(
                        zip, latitude, longitude, city, state, county, zip_class
                            )[1]).Tables[0].Rows)
                    Console.WriteLine(
                        String.Format("{0},{1},{2},{3},{4},{5},{6}",
                            dr[0],dr[1],dr[2],dr[3],dr[4],dr[5],dr));
            }
        }
        
    }
        
}