using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Xml;
using System.Xml.XPath;
using System.Text;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
        public static void SPGeocode(string Address, string City, string State, string appid)
        {
            //Declare string for URL
            string URL;
            
            try
            {
                //Replace the spaces in your Address with the plus sign to build the URL needed for Yahoo
                Address = Address.Replace(" ", "+");
                
                //Build the URL
                URL = "http://api.local.yahoo.com/MapsService/V1/geocode?appid=" + appid + "&street=" + Address + "&city=" + City + "&state=" + State;
                
                //Get the XML response
                XmlTextReader xmlread = new XmlTextReader(URL);

                // Create the record and specify the metadata for the columns.
                SqlDataRecord record = new SqlDataRecord(
                    new SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100),
                    new SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100),
                    new SqlMetaData("GeoCodedAddress", System.Data.SqlDbType.VarChar, 100),
                    new SqlMetaData("GeoCodedCity", System.Data.SqlDbType.VarChar, 100),
                    new SqlMetaData("GeoCodedState", System.Data.SqlDbType.VarChar, 2),
                    new SqlMetaData("GeoCodedZip", System.Data.SqlDbType.VarChar, 10),
                    new SqlMetaData("GeoCodedCountry", System.Data.SqlDbType.VarChar, 10),
                    new SqlMetaData("Precision", System.Data.SqlDbType.VarChar, 100),
                    new SqlMetaData("Warning", System.Data.SqlDbType.VarChar, 100));

                //Create a Dataset to hold the results
                DataSet ds = new DataSet();

                //Fill the DS
                ds.ReadXml(xmlread);

                // Mark the begining of the result-set.
                SqlContext.Pipe.SendResultsStart(record);

                //If the response included an error, then the array length is different, 0-7
                if (ds.Tables[0].Rows[0].ItemArray.Length.Equals(9))
                {
                    record.SetString(0, ds.Tables[0].Rows[0].ItemArray[0].ToString());
                    record.SetString(1, ds.Tables[0].Rows[0].ItemArray[1].ToString());
                    record.SetString(2, ds.Tables[0].Rows[0].ItemArray[2].ToString());
                    record.SetString(3, ds.Tables[0].Rows[0].ItemArray[3].ToString());
                    record.SetString(4, ds.Tables[0].Rows[0].ItemArray[4].ToString());
                    record.SetString(5, ds.Tables[0].Rows[0].ItemArray[5].ToString());
                    record.SetString(6, ds.Tables[0].Rows[0].ItemArray[6].ToString());
                    record.SetString(7, ds.Tables[0].Rows[0].ItemArray[7].ToString());
                    record.SetString(8, ds.Tables[0].Rows[0].ItemArray[8].ToString());
                }
                else
                //If the response did not include an error, then the array length is 0-6
                {
                    record.SetString(0, ds.Tables[0].Rows[0].ItemArray[0].ToString());
                    record.SetString(1, ds.Tables[0].Rows[0].ItemArray[1].ToString());
                    record.SetString(2, ds.Tables[0].Rows[0].ItemArray[2].ToString());
                    record.SetString(3, ds.Tables[0].Rows[0].ItemArray[3].ToString());
                    record.SetString(4, ds.Tables[0].Rows[0].ItemArray[4].ToString());
                    record.SetString(5, ds.Tables[0].Rows[0].ItemArray[5].ToString());
                    record.SetString(6, ds.Tables[0].Rows[0].ItemArray[6].ToString());
                    record.SetString(7, "Precision Good");
                    record.SetString(8, "No Error");
                }


                SqlContext.Pipe.SendResultsRow(record);

                // Mark the end of the result-set.
                SqlContext.Pipe.SendResultsEnd();
            }
            catch(Exception ex)
            {
                //Catch any errors here
                ;
            }

        }
};