• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Geocoding CLR and t-sql - what's wrong with this code?

Ok I'm trying to rewrite a clr to use yahoo's placefinder ... the old one stopped working when yahoo deprecated its previous api.

So I'm not sure what I'm doing wrong here but I can't get this thing to return any data to sql server.  I can't debug it either because its a dll ... I'm not a windows programmer but rather a web developer.  So I don't really know much about creating a a windows form in order to debug it as I need an executable file to trigger a debug.  I can't see anything wrong with the code.  

Is there anyway to write the stacktrace to the sql server output window?  Here is the code:

 
using System;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Xml.Serialization;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SPGeocode(int userId, string Address, string City, string State, string Zip, 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://where.yahooapis.com/geocode?q=" + Address.Replace(" ", "+") + "," + City.Replace(" ", "+") + "," + State.Replace(" ", "+") + "," + Zip + "&appid=" + appid;


            //create a new httprequest
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(URL);
 
            //Get the response
            var response = request.GetResponse();
 
            //Serializing Response XML to C# Class
            XmlSerializer sr = new XmlSerializer(typeof(GeoCoordinates));
            var result = sr.Deserialize(response.GetResponseStream()) as GeoCoordinates;

            // Create the record and specify the metadata for the columns.
            SqlDataRecord record = new SqlDataRecord
            (
                new SqlMetaData("UserID", System.Data.SqlDbType.Int),
                new SqlMetaData("Latitude", System.Data.SqlDbType.VarChar, 100),
                new SqlMetaData("Longitude", System.Data.SqlDbType.VarChar, 100)
            );

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

            //populate the record
            record.SetInt32(0, userId);
            record.SetString(1, result.result.Latitude);
            record.SetString(2, result.result.Longitude);

            SqlContext.Pipe.SendResultsRow(record);

            // Mark the end of the result-set.
            SqlContext.Pipe.SendResultsEnd();
        }
        catch (Exception ex)
        {
            //Console.WriteLine (ex.StackTrace);
        }
    }
};

Open in new window


 
using System;
using System.Xml.Serialization;

[Serializable]
[XmlRoot(ElementName = "ResultSet", Namespace = "")]

public class GeoCoordinates
{
    [XmlElement(ElementName = "Result")]
    public Result result { get; set; }
}

public class Result
{
    [XmlElement(ElementName = "longitude")]
    public string Longitude { get; set; }
    [XmlElement(ElementName = "latitude")]
    public string Latitude { get; set; }
}

Open in new window

0
afacts
Asked:
afacts
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
I am not spotting it right away either, but if you believe the code is hitting the Catch block you can throw exception or use SqlPipe.Send (http://msdn.microsoft.com/en-us/library/ms127319.aspx) to send message back to client.
0
 
afactsNetwork EngineerAuthor Commented:
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now