讲述如何使用SQL CLR表值函数进行扩展

public static IEnumerable 
InitMap(string city, string state, 
int count, string entityTypeName)
{
   FindServiceSoap find = new FindServiceSoap();
   find.PreAuthenticate = true;
   find.Credentials = new NetworkCredential(username, passwd);
      
   // 对初始城市和州进行地理编码（Geocode）
   FindAddressSpecification findSpec 
= new FindAddressSpecification();
   Address findAddr = new Address();
   findAddr.CountryRegion = "US";
   findAddr.Subdivision = state;
   findAddr.PrimaryCity = city;
   findSpec.InputAddress = findAddr;
   findSpec.DataSourceName = "MapPoint.NA";
   findSpec.Options = new FindOptions();
   findSpec.Options.ThresholdScore = 0.45;
   FindResults results = find.FindAddress(findSpec);

   if (results.NumberFound > 0)
   {
      // 如果城市和州已经存在，则获取经度和纬度
      Location startLocation = results.Results[0].FoundLocation;
      LatLong startPoint = startLocation.LatLong;

      // 查找附近的实体
      FindNearbySpecification findNearby = new 
FindNearbySpecification();
      FindFilter filter = new FindFilter();
      filter.EntityTypeName = entityTypeName;
      findNearby.Filter = filter;

      FindOptions options = new FindOptions();
      options.Range = new FindRange();
      // 设置计数限制
      options.Range.Count = count;
      findNearby.Options = options;
      findNearby.DataSourceName = "NavTech.NA";
      findNearby.LatLong = startPoint;
      findNearby.Distance = 10.0;
      results = find.FindNearby(findNearby);

      Route[] routes = new Route[results.Results.Length];
      RouteServiceSoap routeService = new RouteServiceSoap();

      routeService.PreAuthenticate = true;
      routeService.Credentials = new NetworkCredential(username,passwd);

      RouteSpecification spec = new RouteSpecification();
      spec.DataSourceName = "MapPoint.NA";

      // 创建到每个实体的路线
      spec.Segments = new SegmentSpecification[2];
      spec.Segments[0] = new SegmentSpecification();
      spec.Segments[0].Waypoint = new Waypoint();
      spec.Segments[0].Waypoint.Location = startLocation;
      spec.Segments[0].Waypoint.Name = "start";
      for (int x = 0; x < results.Results.Length; x++)
      {
         spec.Segments[1] = new SegmentSpecification();
         spec.Segments[1].Waypoint = new Waypoint();
         spec.Segments[1].Waypoint.Location = 
results.Results[x].FoundLocation;
         spec.Segments[1].Waypoint.Name = "end";
         routes[x] = routeService.CalculateRoute(spec);
      }
      return routes;
   }
   return null;
}

public static void FillRow(Object obj, out SqlChars name, out SqlChars 
address, out SqlBinary map)
{
   Route route = (Route)obj;

   // 构建地址字符串
   Address endAddress = 
route.Specification.Segments[1].
Waypoint.Location.Address;
   string entityAddress = endAddress.AddressLine;
   string enitityCity = endAddress.PrimaryCity;
   string entityState = endAddress.Subdivision;
   string entityName = 
route.Specification.Segments[1].
Waypoint.Location.Entity.DisplayName;

   // 对两列的值进行分配
   name = new SqlChars(entityName);
   address = new SqlChars(entityAddress + ' ' + enitityCity + ' ' + 
entityState);

   // 获取路线视图
   ViewByHeightWidth view = route.Itinerary.View.ByHeightWidth;
   RenderServiceSoap renderService = new RenderServiceSoap();

   renderService.PreAuthenticate = true;
   renderService.Credentials = new NetworkCredential(username, passwd);

   // 显示带有路线的地图
   MapSpecification mapSpec = new MapSpecification();
   mapSpec.DataSourceName = "MapPoint.NA";
   mapSpec.Views = new MapView[]{view};
   mapSpec.Route = route;

   // 将地图指定给地图列
   MapImage[] image = renderService.GetMap(mapSpec);
   map = new SqlBinary(image[0].MimeData.Bits);
}