Calling Web Service from SQL Server 2005

 

(1) Create a web service ( .net framework 2.0, can not be higher) and deploy to server

link : http://colweb01/test/ConfiguratorWSNF2/Service.asmx

 

(2)  Create a SQL Server Project (net framework 2.0)

(3) Add web reference with above link

(4) Create a table-value function

code:

// Service1 is web service client class

#region Table-Value Function
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "GetOrderList_FillRow", TableDefinition = "Orderno NVARCHAR(50),AttHost NVARCHAR(10), Model NVARCHAR(10),MastType NVARCHAR(10),ElineonDate NVARCHAR(20),AlineonDate NVARCHAR(20),TFC NVARCHAR(10),WholeSaleCode NVARCHAR(100),WholeOPtions NVARCHAR(250)")]
public static IEnumerable GetOrderList()
{
     return new Service1().GetAS400OrderInfo();
}

public static void GetOrderList_FillRow(
     object OrderObj,
     out SqlString Orderno,
     out SqlString AttHost,
     out SqlString Model,
     out SqlString MastType,
     out SqlString ElineonDate,
     out SqlString AlineonDate,
     out SqlString TFC,
     out SqlString WholeSaleCode,
     out SqlString WholeOPtions
     )
{
     AS400A004PEntity order = (AS400A004PEntity)OrderObj;
     //   ProductID = new SqlInt32(Convert.ToInt32(r["ProductID"].ToString()));
     //  Name = new SqlString(r["Name"].ToString());
     Orderno = new SqlString(order.ADODN);
     AttHost = new SqlString(order.ADATT);
     Model = new SqlString(order.ADMDN);
     MastType = new SqlString(order.ADMST);
     ElineonDate = new SqlString(order.ADEON);
     AlineonDate = new SqlString(order.ADAON);
     TFC = new SqlString(order.TFC);
     WholeSaleCode = new SqlString(order.WholeSalesCode);
     WholeOPtions = new SqlString(order.WholeOptions);

}

#endregion

(5) build component , copy dll file to sql server local folder( reason:since need to call web service which is external access source, have to do manual deployment).

(6)deployment .sql ( need to deploy assembly to external-access security level and xmlserizlier , sgen.exe)

-------------------- clean up

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ConfiguratorServiceNF2')
   DROP FUNCTION ConfiguratorServiceNF2
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetFirstAS400')
   DROP FUNCTION GetFirstAS400
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SDY')
   DROP FUNCTION SDY
go
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetOrderList')
   DROP FUNCTION GetOrderList
go
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'XmlSerializers')
    DROP ASSEMBLY [XmlSerializers]
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'SQLServerConfiguratorNF2')
   DROP ASSEMBLY SQLServerConfiguratorNF2
GO
----------------------------- create sql server assembly
Create ASSEMBLY SQLServerConfiguratorNF2 FROM 'c:\vss\test\SQLServerConfiguratorNF2.dll'
WITH PERMISSION_SET = External_Access

CREATE ASSEMBLY [XmlSerializers] from
'c:\vss\test\SQLServerConfiguratorNF2.XmlSerializers.dll'
WITH permission_set = SAFE
GO

------------------------------- create function
CREATE FUNCTION [dbo].[GetOrderList]()
RETURNS  TABLE (
    [Orderno] [nvarchar](50) NULL,
    [AttHost] [nvarchar](10) NULL,
    [Model] [nvarchar](10) NULL,
    [MastType] [nvarchar](10) NULL,
    [ElineonDate] [nvarchar](20) NULL,
    [AlineonDate] [nvarchar](20) NULL,
    [TFC] [nvarchar](10) NULL,
    [WholeSaleCode] [nvarchar](100) NULL,
    [WholeOPtions] [nvarchar](250) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLServerConfiguratorNF2].[UserDefinedFunctions].[GetOrderList]
GO

EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'GetOrderList'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'ConfiguratorServiceNF2.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'GetOrderList'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=26 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'GetOrderList'
GO

--------------------------------- exceute
SELECT * FROM [ST_SUPPORT].[dbo].[GetOrderList] ()
GO