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

CREATE FUNCTION GetProximity(@city nvarchar(200), @state nvarchar(2),
   @count int, @entityTypeName nvarchar(200))
RETURNS TABLE 
(HitName nvarchar(200), HitAddress nvarchar(200), MapImage 
varbinary(max))

public static IEnumerable 
InitMap(string city, string state, int count,
   string entityTypeName)
public static void FillRow(Object obj, 
out SqlChars name, out SqlChars
   address, out SqlBinary map)

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);
}

HitName             HitAddress                          MapImage
----------------------------------------------------------------------
Woodgrove Bank      8502 160th Ave NE Redmond WA        <BINARY>
Woodgrove Bank      16025 NE 85th St Redmond WA         <BINARY>
Woodgrove Bank      16150 NE 85th St Redmond WA         <BINARY>
Woodgrove Bank      8867 161st Ave NE Redmond WA        <BINARY>
Woodgrove Bank      15600 Redmond Way Redmond WA        <BINARY>
No rows affected.
(5 row(s) returned)

SELECT TOP(40) Sales.Store.Name, Person.Address.City,
   Person.StateProvince.StateProvinceCode, GetProximity_1.HitName,
   GetProximity_1.HitAddress, GetProximity_1.MapImage
   FROM Sales.CustomerAddress
      INNER JOIN Person.Address
         ON Sales.CustomerAddress.AddressID = Person.Address.AddressID
         AND Sales.CustomerAddress.AddressID = Person.Address.AddressID
      INNER JOIN Sales.Store
      INNER JOIN Sales.StoreContact ON Sales.Store.CustomerID = 
Sales.StoreContact.CustomerID
         ON Sales.CustomerAddress.CustomerID = 
Sales.StoreContact.CustomerID
      INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID 
= Person.StateProvince.StateProvinceID
         AND Person.Address.StateProvinceID = 
Person.StateProvince.StateProvinceID
CROSS APPLY dbo.GetProximity(Person.Address.City,
   Person.StateProvince.StateProvinceCode, 5, 'SIC3578') AS 
GetProximity_1